Entries made to Combo Boxes

[jparker1954]

I've got a form that uses a combo box to help users fill in information from a table. The ROW SOURCE says something like:

SELECT DISTINCT Info.Criteria1, Info.Criteria2, Info.Criteria3 FROM Info WHERE Info.Criteria3="XYZ";

This works great for the selections I want users to have; HOWEVER, is there a way to combine this with a code that will have the combo box field automatically default to the information from the previous record if no changes are needed, yet still allow for the dropdown selections if changes are wanted?

[randman1]

The combobox would have to be unbound from any ControlSource. It will contain no value when the form is first opened but retain the value that was selected until it was changed or the form is closed.

This may be useful for data entry where the user is adding new records but not very useful for displaying existing records as the unbound combobox would not display the existing value. You would also have to set the value to the field in the BeforeUpdate event.

Private Sub Form_BeforeUpdate
    Me!FieldName = Me.ComboboxName
End sub

[jparker1954]

Thank you for the reply. If I have to keep one or the other, I need to keep the dropdown list. I was just hoping that there was a way to set the value of the dropdown based upon the prior record. However, your comment may help me with other issues along the way. Thanks again!

[randman1]

I'll clarify. The above method will still provide the dropdown list as there are no changes to the RowSource property.

RowSource property of the combobox is where your query is (SELECT DISTINCT Info.Criteria1, Info.Criteria2, Info.Criteria3 FROM Info WHERE Info.Criteria3="XYZ")

ControlSource property of the combobox is the field to which the control is linked.

When you have an unbound control, it has no initial value when the form is opened unless you set the DefaultValue property. The user can still select from the choices though. The choice however, will not be automatically applied to the underlying table because its not "bound" to a field. The code in the BeforeUpdate sub is required to set the contents of the combobox to the desired field in the table.

[jparker1954]

Unfortunately, I tried this code, but if I try to change a value in a combo box it changes ALL the values in ALL the records to that changed value. I need something that will leave prior values as they were and only make changes to the new record. Got any ideas for that?

[randman1]

My apologies. The code needs to go in the BeforeInsert event rather than BeforeUpdate. This way, it will only apply the combobox value to new records instead of any record that is changed.

Do you only use the form to enter new records or is it also used to maintain existing records?

[jparker1954]

I use it to enter and maintain records. Actually, I found this site http://support.microsoft.com/kb/210236 and it solved my problems. Thanks,