asked on

MS Access Record Search and Form Population

I'm running an Access db in a multiuser environment . I need to create a command button that will allow end users to search for a specific record that populates a form that I have already created.
Step #
1. Open database, "menu" form is loaded by the startup
2. User completes the form
3. User notes the ID number (primary key) and needs to have the capability to go back to just that record.
4. I would prefer that the user can simply type in the ID # into a text box field, it looks up the specific record and populates a form.
Please provide me with the code to perform this (command) operation and tell me the best way to implement.
Thanks,
-- Joe

Scott McDaniel (EE MVE )

You can add a Textbox (txtSearch) to the Header of your form, then add a button that will search for the value entered:

Sub cmdSearch()

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

rst.FindFirst "YourPrimaryKeyField=" & Me.txtSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
Msgbox Me.txtSearch & " was not found."
End If

Set rst = Nothing
End Sub

westbrjb

ASKER

So to be clear, the steps are as follows:
Add Textbox
Add a Command Button

User will type in the record ID # then clicks the command button with the above script as the command button action?

What needs to be added to populate a form named TSE Input Form with the data from the selectd record?

Thanks,
- Joe

Ryan

You can use the filter property of the form to select only that record also.

westbrjb

ASKER

I wanted to create a form called "Menu" and add the text box/command button that form. This form will be called "Request Search Form" . When the user populates the text box with the ID # field and clicks on the command button, it launches a form called TSE Input Form with all of the data populated from the selected record.

Scott McDaniel (EE MVE )

If that's the case, then just use the Where clause argument of the OpenForm method:

Sub YourCommandButton_Click()

DoCmd.OpenForm "TSE Input Form", , , "YourIDField=" Me.txtSearch
DoCmd.Close acForm "Request Search Form"
End Sub

Note that you'll need to change YourIdField to point to the field that should be searched on ... and if that is a Text field, you'll need to surround that with single quotes:

"YourIdField='" & me.txtSearch & "'"

Also, if "ID #" is the actual name of your field (and if it is you REALLY need to look into nameing conventions), you may need to put square brackets around it:

"[ID #]='" & Me.txtSearch & "'"

westbrjb

ASKER

rst As DAO.Recordset
Compile Error:
User-defined type not defined

Also for my clarification, BOTH of the scripts that you provided should be subed to the command button?

Ryan

Use that last one, its much easier, won't have to add references.

westbrjb

ASKER

OK, it pops up a seperate box that solocits the parameter, I enter the ID Number and it opens the form, however, the form is empity..

DoCmd.OpenForm "TSE Input Form", , , "[ID#]="" Text0.Text"""

Ryan

If its popping up a box then you probably have something misspelled. As was said earlier, that name [ID#] is not a good idea. I'd recommend changing it to IDNum adleast, or LogID is what I more commonly see.

need to add &s to it.
DoCmd.OpenForm "TSE Input Form", , , "[ID#]="" & Text0.Text & """

ASKER CERTIFIED SOLUTION

Ryan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

westbrjb

ASKER

Dear Mr. Bullwinkle:
Please tell me does YourIdField='" & me.txtSearch & "'"
your field = the field name referenced from the table?

westbrjb

ASKER

OK, this is the closest that I've gotten to getting this to work.
Problems:
I type in an ID number into the ID# blank field and another box pops up prompting me to enter a parameter. I type in a number and the form "TSE Input Form" opens up with the first record in the set, not the record that was requested.

Ryan

That popup box asking about a parameter means that parameter didn't have a value. Usually caused by a typo.

I would expect the proposed solution to only return the record with matching ID or nothing at all. Make sure you have the correct number of commas in your openform so that it is in the WHERE part of the function.