Update Records in table using combo box as input

[dnelson24]

Probably a very basic newby question...here goes...

I have a form (form1) that has 4 combo boxes (Combo23, Combo25, Combo27, and Combo29).

The row source for each of these 4 boxes is:
SELECT [Size Table].[Size] FROM [Size Table];

The Size Table has one field (Size) and 8 records.

I would like when the user is presented with the form to select from each of the 4 combo boxes one of the 8 records and have another table Size Capture Table to update.

The Size Capture Table has 4 fields (Size1, Size2, Size3, and Size4) and 1 record (this one record would included the 4 size selected in the step above).

Is there a way to update this other table by using some sort of exit procedure for each of the combo boxes or do I need to create a button that would update the Size Capture Table all at once.


What if I have instead of 4 combo boxes, I have just text boxes? How could I get another table to be updated as well. I think I will need to do both. I.E. have combo boxes and text boxes as input for another table.


I would need some detail in setting this up for I am very new to VB.


thanks in advance

david

 

[scriverb]

I would suggest that you have a seperate command button to initiate the insertion of a new record in your table with the values from the four comboboxes. The reason for this is the user may make the four selections but then after review of them find they made a mistake in one or more of the selections. If the new add record process was intiated when the last combobox was updated with the selection there would be no time for this review process. It is better to let the user make a position action like clicking a command button.

Now to make it easier on you we can create a saved UPDATE query that references each of the forms comboboxes to update the values selected at the time of running the UPDATE query.

Query Example: Copy and save this query and name it qryUpdSizes. Update the form, control, and field names if necessary.

UPDATE [Size Capture Table] as A SET A.Size1 = FORMS![form_name]![cboSize1], A.Size2 = FORMS![form_name]![cboSize2], A.Size3 = FORMS![form_name]![cboSize3], A.Size4 = FORMS![form_name]![cboSize4];

Now in the Command Buttons OnClick event procedure you can copy and put in this code:

DoCmd.Setwarnings False
DoCmd.OpenQuery "qryUpdSizes"
DoCmd.SetWarnings True

This code will update your tables one record with the values from the forms comboboxes. Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[vbajock]

I would use a button. it would be a lot simpler than trying to manage a bunch of combo events. In the click event of the button, you would need to put code in to validate the combos like so:

if isnull(forms!myform!mycombo.value) then
'the box is blank, tell the user
msgbox "Sorry, can't update
endif
etc for each combo

The next block of code would execute an update query:
Docmd.setwarnings false
docmd.openquery "qryMyUpdateQry"
docmd.setwarnings true

The qryMyUpdateQry would have forms!myform!mycombo.value inserted in the Update To box in the appropriate column

 

[dnelson24]

Thanks scriverb worked great!

How bout this one....I have a form that I added check boxes to. I would like to be able to, based on the check box being checked, include on a report a field from a table?

thanks again

david

 

[scriverb]

What you are asking about is a very complicated process where you are creating a report general process. It is very difficult and usually not recommended. The only effecient way to do this would be to list the field names in a subform continuous form with a selection checkbox and a field to indicate the order to be placed on the report from left to right. After the user would update the subform records some VBA code in the Reports OnOpen event procedure would loop through the records in the subform records and fill some generic control textboxes in the Detail Section and update the ControlSource property with the field names. The width of the textboxes would have to be adjusted depending up on the fields selected etc. etc. etc. VERY MESSY. NOT RECOMMENDED UNLESS YOU HAVE A LOT OF VBA BACKGROUND AND A GOOD AMOUNT OF TIME TO WORK ON THE DETAILS.

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]