Update multiple records at once from a form

[brharrii]

I am working in access 2010 on a product information database. My main table has a "LastUsed" field in it. This field is updated the night before each time a product is used. Traditionally I've been updating each record one at a time, but this take a decent amount of time and I'd prefer to automate it if possible.

I've created a form with 40 unbound combo boxes. These cbo boxes can be populated with item numbers that represent the products we will be running the following day. I'd like to be able to populate some or all of these boxes with the item number for tomorrow's products and then push a button and have every record update the last used field to tomorrow's date.

Is VBA the correct way to go about this?
What would my code have to look like in order to accomplish this?

thank you

Bruce

 

[mdlueck]

I would suggest some VBA code driving ADO database objects issuing the correct SQL to make the changes you require. Here are some posts which I hope will be helpful:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730&posted=1#post1214730

 

[spikepl]

What is the point of the 40 combos? If that is to select between 0 and 40 single items from your db then that is definitely not the way to do it. Listboxes are used for such things. Either one with multiselect, or two where each selected item in one is removed and added to the second listbox (and vice versa). Then an update query can be run.

 

[brharrii]

That is what I was using the combo box for. Thank you for the suggestion.

The reason I chose combobox is because the list i'm chosing from is over 700 items long and I couldn't figure out how to type into the list box to find the specific record I was looking for. I could only type the first letter of the item and then scroll from there. Anyways, if you say the list box is the best way to go about this, I'll check it out.

What is the best way to prompt the update query after the List box is setup?

Thanks!

 

[brharrii]

mdlueck - Thank you for all this. It looks like good info, I worry that it might be slightly beyond my level of comprehension, but I'll definitely look at it and see what I can figure out.

Thanks!

I would suggest some VBA code driving ADO database objects issuing the correct SQL to make the changes you require. Here are some posts which I hope will be helpful:

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

Example of SQL SELECT using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=230610#post1176746

Example of SQL SELECT using ADODB.Recordset object to Access FE temp table to scan the FE temp table and perform operations
http://www.access-programmers.co.uk/forums/showthread.php?p=1214730&posted=1#post1214730