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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

gtmathewDallasAuthor Commented:
I am requesting somebodies help, Please..
Scott McDaniel (Microsoft Access MVP - EE MVE )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 ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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"
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) .
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

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.