Solved

Update table records and add new records if any - based on values in a Listbox

Posted on 2014-01-27
8
450 Views
Last Modified: 2014-02-26
Hi Experts,
I am confused to how do update the existing records in a table and add new records to it based on a list box values from a a form.
example - I have 6 items in my list box(with three columns) - in that, 4 items are old one and the other 2 items are newly added to the list box.
How can I do update the old four records in the table and add other two as new record to the table during the form submission(update)?
Please help.
0
Comment
Question by:gtmathewDallas
  • 4
  • 2
  • 2
8 Comments
 
LVL 10

Expert Comment

by:Gozreh
ID: 39812576
you will have to create 2 Queries, 1 to update the old records and 1 to insert the new.

For more help you will need to explain more, to which table is the List bound ?
and how dos the list have more records from your table ?
so if its 2 separate tables you will need to join the 2 table to see which records are old and which are new
0
 
LVL 84
ID: 39814596
How do the items in your list correspond to the existing data in your database?

You mention that you have 4 records in the Listbox that are "old ones", which I assume mean they somehow tie into existing records. How do you know they're "old ones", and how can your code determine that they're old, and how would your code get a handle on the records associated with those "old ones"?

Once you figure that out, you can write simple VBA routines to update data:
Dim i as Integer
Dim sql As String
For i = 0 to Me.YourListbox.Count-1
  If Me.YourListbox(i) = "Old Record" Then
    sql = "UPDATE SomeTable Set Field1=" & Me.YourListbox.Column(0,i) & ", Field2=" & Me.YourListBox.Column(1,i) & " WHERE SomeTable.IDField=" & Me.YourListbox.Column(5,i)
  Else
    sql = "INSERT INTO SomeTable(Field1, Field2, Field3) VALUES(" & Me.YourListbox.column(0,i) & "," & Me.YourListbox.Column(1,i) & "," & Me.YourListbox.Column(3,i) & ")"
  End If

  Currentdb.Execute sql
Next i

Open in new window

Essentially the For - Next loop cycles through every row in the listbox, and you then determine if this is an old record or a new one. The SQL created is then run with the Execute command.

Note the syntax here: Me.YourListbox.Column(0,i). Columns and Rows in a Listbox are zero-based, so this bit tells VBA To get the record in the FIRST Column of the row identified by the i variable. So in the first pass through that code, it would grab data in Column(0,0), in the second it would get data in Column(0,1), and so on.

Note too that you will have to change the SQL to match the field datatypes. For example, if Field1 is a Text or Memo field, you'd use Single Quotes around that field:

sql = "UPDATE SomeTable Set Field1=  '  " & Me.YourListbox.Column(0,i) & "  '  , Field2=" & Me.YourListBox.Column(1,i) & " WHERE SomeTable.IDField=" & Me.YourListbox.Column(5,i)

I added some whitespace so that you can see them more easily, but in your code you don't need to do that.

If Field1 is a Date field:

sql = "UPDATE SomeTable Set Field1=  #  " & Me.YourListbox.Column(0,i) & "  #  , Field2=" & Me.YourListBox.Column(1,i) & " WHERE SomeTable.IDField=" & Me.YourListbox.Column(5,i)

If Fiel1 is a Numeric field, you don't need any delimiters.

See here for more information n the Listbox Column: http://msdn.microsoft.com/en-us/library/office/ff834793.aspx
0
 

Author Comment

by:gtmathewDallas
ID: 39833819
The above method is helpful, but to get the "old records" i have to write another query, the record result may be with 0 to 'n' number of records, I know its makes more complexity to write the code but please help, Thanks
0
 

Author Comment

by:gtmathewDallas
ID: 39836730
I am requesting somebodies help, Please..
Thanks
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 39837210
As I mentioned, it's a fairly simple matter to insert or update records, but you must provide some idea of how your code can determine what to do - should your code INSERT a NEW record, or should it UPDATE an EXISTING record?
but to get the "old records" i have to write another query,
What query would you write to get the old records? That would tell you how to determine the difference between your listbox records ...
0
 

Author Comment

by:gtmathewDallas
ID: 39837314
Here is the query i used to load the list box(LB2)
strSQL2 = "SELECT DISTINCT Employee_Room_Status.Person_Number, Employee_Room_Status.F_Name, Employee_Room_Status.L_Name, Employee_Room_Status.M_Name FROM Room INNER JOIN Employee_Room_Status ON Room.ID = Employee_Room_Status.Room_ID where Employee_Room_Status.Room_ID=cvar('" & Me.Combo_Room.Value & "') and Employee_Room_Status.Active=Yes;"

The form controls including this list box(LB2) filling items based on a combo value(room_id), Here the admin should be able to remove/add new users(items) to LB2.There is another list box LB1 to show the users(items) those are not in LB2,which is using to add new data to LB2.  If some of the item in LB2  removed/added - we have to set "yes"/"no" to the corresponding field(Active) based on the user(item) id. If it is a new item, add with status field active="Yes"
0
 
LVL 10

Assisted Solution

by:Gozreh
Gozreh earned 100 total points
ID: 39842649
When Admin Click "Add User" then you should execute "UPDATE Employee_Room_Status SET Employee_Room_Status.Active = True
WHERE (((Employee_Room_Status.Person_Number)=" & Me.LB1 & "));"
and when he click "Remove User" then you should execute "UPDATE Employee_Room_Status SET Employee_Room_Status.Active = False
WHERE (((Employee_Room_Status.Person_Number)=" & Me.LB2 & "));"
Done forget to Requery the list boxes, to see the changes.
0
 

Author Comment

by:gtmathewDallas
ID: 39848790
Yes but I am facing issue when this code running through another record set result,
Here is my current working code,

Dim i As Integer
Dim sqlu As String
For i = 0 To Me.List2Edit.ListCount - 1
  If (Me.List2Edit.Column(3, i)) = 2323 Then
  sqlu = "UPDATE Employee_Room_Status Set L_Name = '" & Me.List2Edit.Column(0, i) & "', F_Name = '" & Me.List2Edit.Column(1, i) & "', M_Name = '" & Me.List2Edit.Column(2, i) & "' , Employee_Active = '1'   WHERE Person_Number = " & Me.List2Edit.Column(3, i)
  Else
    sqlu = "INSERT INTO Employee_Room_Status(L_Name,F_Name,M_Name,Person_Number,Room_ID,Date_Added,Employee_Active)" _
          & "Values ('" & Me.List2Edit.Column(0, intLoop) & "', '" _
                               & Me.List2Edit.Column(1, i) & "', '" _
                               & Me.List2Edit.Column(2, i) & "', '" _
                               & Me.List2Edit.Column(3, i) & "','" _
                               & Me.Combo_Room.Column(0) & "', Date(),1)"
  End If
CurrentDb.Execute sqlu
Next i


But instead of 2323 I have to give a result set there to check the condition that the value is to update or insert(old/new) .
Thanks
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now