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

Posted on 2014-01-27
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.
Question by:gtmathewDallas
  • 4
  • 2
  • 2
LVL 10

Expert Comment

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
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)
    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:

Author Comment

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 39836730
I am requesting somebodies help, Please..
LVL 84

Accepted Solution

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 ...

Author Comment

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"
LVL 10

Assisted Solution

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.

Author Comment

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)
    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) .

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

840 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