Newbie - Help - Bound Form - Browsing

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

Hi,

I have a form that is bound to a table. I can use the
navigation buttons to scroll through records but not the
combo boxes I have on the form. When I use the combo
boxes, scroll through records and then close the form I
get an error message saying that I am trying to add a
duplicate record to the table.

There are 3 combo boxes and when the user chooses from the
first I do a requery in the OnClick event to populate the
list for the second and when the user chooses in the
second I do a requery in the OnClick event for the third.
These 3 boxes, together, make up the primary key of the
table. For each of these combo boxes the Limitto List is
set to yes.

I have tried setting the form's cycle to Showall and to
current record and get the same results for both.

Any and all suggestions are welcome.

Thanks
Anne L.
 
I have a form that is bound to a table. I can use the
navigation buttons to scroll through records but not the
combo boxes I have on the form. When I use the combo
boxes, scroll through records and then close the form I
get an error message saying that I am trying to add a
duplicate record to the table.
Click to expand...

It sounds like you're using *bound* combo boxes. Open the Form in
design view and examine the Properties of the combos - is the Control
Source blank? It should be, for this purpose!

Combo boxes have two quite distinct reasons for being: one is to
update records in a table; for this purpose, you set the combo's
Control Source to the field that you want to update. Selecting a value
from the combo writes the combo's Bound Column into that field of the
table, overwriting whatever was there before - that's why you're
getting the "duplicate record" message.

A different use of a combo is to *find* records. For this purpose the
combo should be *unbound* - have nothing in its Control Source.
Instead, you would have VBA code in the combo's AfterUpdate event to
navigate to the chosen record.

If you have a three-field primary key, I'd suggest using the Form's
Filter property to narrow down the search. Put code in each (unbound!)
combo's AfterUpdate event like:

Private Sub cboField1_AfterUpdate()
Dim strSQL As String ' create a variable for the filter string
strSQL = "True"
If Not IsNull(Me!cboField1) Then
strSQL = strSQL & " AND [Field1] = " & Me!cboField1
End If
If Not IsNull(Me!cboField1) Then
strSQL = strSQL & " AND [Field2] = " & Me!cboField2
End If
If Not IsNull(Me!cboField1) Then
strSQL = strSQL & " AND [Field3] = " & Me!cboField3
End If
Me.Filter = strSQL
Me.FilterOn = True
End Sub

If the user selects none of the combos, the filter will be just TRUE
(i.e. retrieve all records); if any one, two, or all three combos have
values, the form will be filtered to match.
 
Back
Top