storing an unbound combo box in a table

[Rich767]

Hello!
I own a small manufacturing company which I am trying to organize using an Access 2010 database. I manufacture parts from coils of steel which are fed through a die which stamps out the parts. The die can produce numerous different parts depending on the length of steel fed into the die.

I am trying to construct a form to track how many of each part is made and from which die and coil of steel the part comes from. I'm trying to make a continuous form. In the header I have unbound combo boxes for the die "cmbDie" and for the steel coil "cmbTag" and an unbound text box for the date "txtDate".

In the detail section of the form I have a combo box for the part "cmbPart" and a text box for the number of parts made "txtQuantity".

The parts and quantities are stored in the table "tblPartsStamped". The table has five columns: MadeOn, Die, Tag, Part, Quantity.

I can enter the part numbers and quantities fine with the form but I don't know how to get the unbound data for the die,steel coil, or the date into the table. If anyone can give me some direction I would really appreciate it. Thanks!

Rich

 

[rodmc]

If you want to store the value in a table, the control must be bound

 

[Rich767]

Thanks. I didn't know if it was possible to make a variable to store the value of the unbound control and then store that in the table.

 

[mdlueck]

I didn't know if it was possible to make a variable to store the value of the unbound control and then store that in the table.

Yes, you may use an unbound control to display / edit / update a value stored in a database table. You are totally responsible for the value... that is what "unbound" means.

When desiring to display the current value, you must execute a piece of SQL to locate the correct value. Once that is done, simply transfer the looked up value to the .Value property of the GUI control.

When desiring to save the changes, then read the GUI Control's .Value, transfer to a VBA variable, from there use a SQL UPDATE to save the current value back to the database table.

Since you are working with VBA variables and controls, I would suggest using ADO type queries which mesh up with VBA variables very well. Here are some example posts to guide you:

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 INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

 

[Rich767]

Thanks for pointing me in the right direction. I'm just starting to learn VBA and at almost 60 years old it is like trying to fill a sink with knowlege with the drain open! Back to my book....