VBA search in MS Access 2007 Form

Reyesrj
Reyesrj used Ask the Experts™
on
Hi All,

I’m working on MS Access 2007.  I have a form (Main Form) with information that is filled out by the user.  What I am trying to do is make another form that opens when the user launches access (This I can do).

In the form that opens at startup, I would like it to have a search / find field or button.  This will search for the record ID.  So when the user types in the record ID it will launch the main form and display that record ID.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
The simplest way to open another form to a specific record is to use the OpenForm method:

DoCmd.OpenForm "YourForm", acNormal, , "YourIDField=" & Me.YourPopupFormValue

This would be run on the Popup form, perhaps on a Button click or something. Note too that if YourIdField is a Text value, you must do this:

DoCmd.OpenForm "YourForm", acNormal, , "YourIDField='" & Me.YourPopupFormValue & "'"

Note these methods will open a Filtered form, which means your user would only see a single record (assuming your ID returns a single record, of course).

Author

Commented:
Thanks LSMConsulting,

I am a little lost as I’m new to VBA.  But I am very eager to learn.

DoCmd.OpenForm "YourForm", acNormal, , "YourIDField=" & Me.YourPopupFormValue
DoCmd.OpenForm "MainTBL",      acNormal, , "CaseID="         & Me.???

What do gets entered in “YourPopupFormValue”
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
What do gets entered in “YourPopupFormValue”
The name of your Search/Find field (i.e. Textbox). So if you build a form with a Textbox named "txSearch", you'd have this:

DoCmd.OpenForm "YourForm", acNormal, , "YourIDField=" & Me.txSearch

You'll also have to replace "YourForm" with the name of the form you wish to open, and "YourIDField" with the name of that ID field from "YourForm".

Author

Commented:
Sorry, typing toooooo fast!

What gets entered in.....

I think I understand.  I'll try tomorrow.  I'm not at work.

Thanks

Author

Commented:
I tried something else.
Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial