Solved

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

Posted on 2014-01-27
8
502 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:gtmathewDallas
ID: 39836730
I am requesting somebodies help, Please..
Thanks
0
 
LVL 85

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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