Lookup and goto

[Caddie]

Hi -

I've got a lookup on a form that pulls data from multiple fields and auto-populates multiple fields on my form, but access is not going to the proper record in the table it is just displaying the info on the form so the form is always saying it's on record 1 even though the data it just looked up might be record 100, so what I'd like the lookup to do is more like, "lookup, display the information and goto the appropriate record" is this possible?

Thanks a bunch.

 

[MStef]

As I can understand you, you need something like
"DemofindA2000.mdb" (attachment, zip).
Open form and see.

 

[Caddie]

Is this possible though given that I'm already using the combobox as a lookup to populate other fields? Also, I'm using Access 2007 and I don't see that option in the combobox wizard.

 

[vbaInet]

I'm guessing you don't have a subform? Is your combo box an unbound control?

Have you thought of using the Filter property of your form?

 

[Caddie]

I do have have multiple subforms in this form. The lookup is happending on the main form:

Customer Information (main form)
Contract Details (subform)
Service address details (sub of sub)
Service details (sub of sub of sub)

That's the layout of the form.

 

[vbaInet]

Only 1 of 3 questions answered.

Also, in which form is your combo box located?

 

[Caddie]

The combobox is unbound.

I'm not sure what a filter property is.

The combobox is on the Main Form, not in a subform.

 

[Caddie]

Here is the code I'm using in the combobox, I'm getting an error on the bolded line.

Private Sub cmbCustNum_AfterUpdate()
Me.Customer_Name = Me.cmbCustNum.Column(2)
Me.Billing_Street_Address = Me.cmbCustNum.Column(3)
Me.Billing_Street_Address_2 = Me.cmbCustNum.Column(4)
Me.Billing_City = Me.cmbCustNum.Column(5)
Me.Billing_Province = Me.cmbCustNum.Column(6)
Me.Billing_Postal_Code = Me.cmbCustNum.Column(7)
Me.Billing_Country = Me.cmbCustNum.Column(8)
Me.Sector = Me.cmbCustNum.Column(9)
Me.Active = Me.cmbCustNum.Column(10)
Me.Comments = Me.cmbCustNum.Column(11)
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer #] = " & Me.cmbCustNum
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub

 

[SOS]

Is [Customer #] really a number or is it stored as text in the table?

Also, do NOT use special characters in your field or object names.

 

[Caddie]

I was able to get it to work using the following code:

Private Sub cmbCustNum_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer #] = """ & Me.cmbCustNum.Column(1) & """"
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub


Thanks so much.

 

[JANR]

rs.FindFirst "[Customer #] = """ & Me.cmbCustNum.Column(1) & """"

So CustNum was a textfield after all

JR

 

[SOS]

So CustNum was a textfield after all

JR

Yep, gotta love it when the name of the field doesn't match the datatype. Makes for some interesting happenings.