setting the default value of a combo box based on a table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating a database for auditing purposes. I am fairly new to Access
and am not sure I am approaching the problem from the best angle, but here is
what I've got....I have a form with many combo boxes (over 100) that are
bound to fields on the main table. The values that are stored in these
fields will either be: correct, incorrect, FYI or N/A. Rather than create a
value list for every combo box, I created a second table and made the row
source type Table/query, and the row source is the name of the 2nd table
(tblDetermination). The value in the combo box is more often than not,
going to be "correct", so I want to make that the default value. I have
tried using the defaultvalue property, but I just can't seem to make it work.
Any suggestions?
 
How many fields are in the second table and, if more than one, which field
is associated with the Bound Column of the combo boxes? When you set the
Default Value, you need to put in the value that will be in the Bound Column
for the desired selection.

You may also prefer to use a query instead of the table directly as the Row
Source. The query will let you set the order of the items in the drop down
part of the combo box. Using the table directly, there is no guarantee as to
what order the items will be listed in.
 
Wayne Morgan said:
How many fields are in the second table and, if more than one, which field
is associated with the Bound Column of the combo boxes? When you set the
Default Value, you need to put in the value that will be in the Bound Column
for the desired selection.

You may also prefer to use a query instead of the table directly as the Row
Source. The query will let you set the order of the items in the drop down
part of the combo box. Using the table directly, there is no guarantee as to
what order the items will be listed in.
Click to expand...
 
There are only 2 fields in the second table (deterID, and DeterDesc), and
there are only 4 entries into that table (correct, incorrect, FYI, N/A). The
combo box is bound to the 1st column.
I have used the following code, which does populate the combo box with the
1st entry of the 2nd table (value = correct).

Private Sub Calculate_Click()

EOAFollowed.Value = Me!EOAFollowed.ItemData(0)

End Sub
However, if the combo box already has a value (i.e. user has selected
"incorrect, FYI, or N/A") then the line of code above overrides what the user
has chosen and puts the value of "correct" into the combo box. I want that
only to happen if the user doesn't select any other value.

I probably need some type of conditional statement, but I don't know enough
about coding to make that happen.
 
Open the form in design view, right click the combo box, and choose
Properties. On the Data tab, enter

=EOAFollowed.ItemData(0)

in the Default Value box instead of using the code. The Default Value is
used whenever the form goes to a new record. It won't affect current
records.
 
I thought I had tried that before, but to no avail. This time when I tried
it, it worked, so I must have done something different from before. Thanks
for your help. Related question...how would I handle it if a combo box was
disabled (some of the combo boxes are disabled in certain situations). I
don't want it to populate if the combo box is disabled. Any advise?
 
In the BeforeUpdate event of the form, try

If Me.NewRecord and Me.NameOfCombo.Enabled = False Then
Me.NameOfCombo = Null
End If

If you have multiple combo boxes, you could also do this:
Dim ctl As Control
If Me.NewRecord Then
For Each ctl In Me
If ctl.ControlType = acComboBox Then
If ctl.Enabled = False Then
ctl = Null
End If
End If
Next
End If

The reason for two If statements inside the For Each is because not all
controls have an Enabled property, so checking the property for those
controls will give an error. If you only have 2 or 3 combo boxes, it may be
easier just to use the first example for each one.
 
No such luck, I have a TON of combo boxes. Thanks for the help, I can't wait
to get back to the database to try it out!!!
 
Back
Top