Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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
0
westbrjb
Asked:
westbrjb
  • 6
  • 5
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
 
westbrjbAuthor Commented:
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
0
 
RyanProject Engineer, ElectricalCommented:
You can use the filter property of the form to select only that record also.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
westbrjbAuthor Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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  & "'"


0
 
westbrjbAuthor Commented:
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?
0
 
RyanProject Engineer, ElectricalCommented:
Use that last one, its much easier, won't have to add references.
0
 
westbrjbAuthor Commented:
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"""

0
 
RyanProject Engineer, ElectricalCommented:
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 & """
0
 
RyanProject Engineer, ElectricalCommented:
Also depending what your datatype is for that ID# field, will determine how you setoff that text0.text criteria.  If its a number you can just use
DoCmd.OpenForm "TSE Input Form", , , "[ID#]=" & Text0.Text

if its text use
DoCmd.OpenForm "TSE Input Form", , , "[ID#]='" & Text0.Text & "'"

I made a slight typo in the previous, althought I think it'll work.  its a single quote surrounded by double quotes (" ' ") without the spaces.
0
 
westbrjbAuthor Commented:
Dear Mr. Bullwinkle:
Please tell me does YourIdField='" & me.txtSearch & "'"
your field = the field name referenced from the table?
0
 
westbrjbAuthor Commented:
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.
0
 
RyanProject Engineer, ElectricalCommented:
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now