Problems Searching in Access

Question

Wonder if any of y'all can help me. I keep getting errors on this. Here's my table:

 - tblCutting
    - PartNumber (Primary Key Text Field)
    - CuttingStep1 (Number)
    - CuttingStep2 (Number)
    - CuttingStep3 (Number)

I'm trying to use a combo box (cmbPartNumber1) to pick from PartNumber and then a text box fills in with the corresponding CuttingStep1. Here's are the various formulas I've tried underneath the textbox:

=DLookUp("CuttingStep1","tblCutting","cmbPartNumber1=" & [tblCutting]![PartNumber])

=DLookUp("CuttingStep1","tblCutting","[cmbPartNumber1]=" & [tblCutting]![PartNumber])

=DLookUp("CuttingStep1","tblCutting","cmbPartNumber1=" & [PartNumber])

=DLookUp("CuttingStep1","tblCutting","[tblCutting]![PartNumber]=" & [cmbPartNumber1])

=DLookUp("CuttingStep1","tblCutting","[PartNumber]=" & [cmbPartNumber1])

None of these have worked and I have no idea why. Any suggestions?

Or am I way off on how this is supposed to work?

Edit: added field types above.

Include the CuttingStep1 field as a column in combobox RowSource. Then simply reference that column by index. Index begins with 0 so if field is in third column its index is 2: `=[cmbPartNumber1].Column(2)`. — June7, Oct 21 '22 at 15:05
@June7, adding the Column reference didn't work. I had already added in CuttingStep1 into the column, hoping that would have worked. I'm stumped. — IndianaRedneck, Oct 21 '22 at 18:55
@exception, it's a Primary Key text field. I updated the original question to show this and the Types of the other fields. — IndianaRedneck, Oct 21 '22 at 18:56
What is the combobox RowSource SQL? If the fields are included, then referencing them by index should be possible and DLookup not needed. — June7, Oct 22 '22 at 01:43
@exception, while I didn't have to do the Sub, your DLookUp formula worked for me. I can't believe I overlooked that. Thank you. — IndianaRedneck, Oct 24 '22 at 15:44

exception · Accepted Answer · 2022-10-21T19:34:45.343

Text values need to be delimited:

=DLookUp("CuttingStep1","tblCutting","PartNumber='" & [cmbPartNumber1] & "'")

Also, since your textboxes are using a calculated formula, they are "Unbound", therefore you need to update their contents yourself.

To handle this you'll need an event sub for the "Change" event of your ComboBox. Add the following code or similar, since I do not know the names of your Textboxes. Add to your Form Module:

Private Sub cmbPartNumber1_Change()
    ' Refresh (recalculate) values.  textBox1,2,3 are names of your Textboxes that contain calculated values based on value of cmbPartNumber1.
    textBox1.Refresh
    textBox2.Refresh
    TextBox3.Refresh
End Sub

Problems Searching in Access

1 Answers1