Question about combo boxes

[trifest]

I have a list of companies in a combo box and I want a user to be able to select a company in the box and the form to go directly to that record with the associated information (Address, phone,etc) I tried doing a filter by passing a condition to the openform command but no luck. thanks for the help.

 

[fneily]

On your form with the company info, you could create a combobox with the companyID. For my example, it's named Findit. On the AfterUpdate event, type:

Private Sub Findit_AfterUpdate
Dim R As Recordset
Set R = Me.RecordsetClone
R.FindFirst "[CompanyID] = " & Char(34) & Me![Findit] & Char(34)
Me.BookMark = R.BookMark
End Sub

Char(34) means " (I got fancy)

Neil

 

[AlienRay]

What I always do is set up your combo box with the index field (usually called "ID&quot as the first column, and call it "LookupBox". Then all you need to do is set an On Exit event for Lookup Box:

DoCmd.GoToControl Me.ID.Name
DoCmd.FindRecord Me.LookupBox

That should be all you need.

 

[fneily]

I always forget about those DoCmd commands!
Thanks
Neil

 

[trifest]

can't seem to get it working. it says method or data member not found for the Me![Findit] part of it.

 

[fneily]

Go to Design view. Do ALT+F11 to go to the VBA window. Click on Tools, References. Look for Microsoft DAO 3.6 library (or the latest version). Place a check next to it. Move the library up to about the third or fourth position from the top of the list. Close the window.

Also, make sure you named your combobox Findit.

Neil

 

[trifest]

now i'm getting "object variable or With block variable not set" the combo box is named FindIt

 

[fneily]

*&(*&^ libraries!
Change Dim R As Recordset to Dim R As DAO.Recordset

Check to see that after you type the . you get a dropdown list.

 

[trifest]

no luck with that either. i've never seen this before. but then again i've never worked with DAO's really

 

[AlienRay]

Use the DoCmds. You'll live longer...

 

[trifest]

can't seem to get that to work either. i'm getting the method or data member not found for the .Name part

 

[fneily]

No more fancy stuff. Here's the very basic:
Again create your combobox using the wizard. Then on the AfterUpdate event put:

Dim R As DAO.Recordset
Set R = Me.RecordsetClone
R.FindFirst "[nameID] = " & Me![Findit]
Me.Bookmark = R.Bookmark


Findit is the name of the combobox (click on the combobox itself in design view, bring up its' property sheet, click the ALL tab and scroll to the top to the option Name).
Then click on the event tab and click on After Update. Click on the three dots, select code builder and paste the code.

I know this works, I use it everyday.

Neil

 

[trifest]

that worked perfectly, thanks so much for the help. i thought that's what i had before but apparently not. thanks again everyone

 

[fneily]

Whew!