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

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.
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
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 ...
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
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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)
    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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

gtmathewDallasAuthor Commented:
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
gtmathewDallasAuthor Commented:
I am requesting somebodies help, Please..
gtmathewDallasAuthor Commented:
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"
GozrehConnect With a Mentor Commented:
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.
gtmathewDallasAuthor Commented:
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)
    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) .
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.