Adding a Search form to a database

[doran_doran]

Hello,

I am trying to put a search form in the database. I am not proficient creating codes. In my table I have three primary key. My table name is tbl_groups. Primary fields are [GA_Number], [PYE] and [V-System_Plan_no]. I would end-users to plug all three information and click on the search or open button. Upon clicking the button the system should load the form called frm_groups.

OR a form where user will only put [GA_Number] and the system loads all the records that has same GA_Number 's.

[PYE] = stands for Plan Year End.

I don't know if I was clear. This is my first time.

Prompt respond will be appreciated.....

 

[jimbrooking]

Let's see if this works:

Say you have a search form with a text box named tbxLoadGA and a command button named cmdSearch.

Say further that you have a form called frmFoundGAs that displays the information from all the GAs that match the search term. This form will have a record source that includes the field name GA_Number, which your code will rely on.

Looking at your search form in Design View, click tbxLoadGA, and click the Properties' Event tab. Click the space to the right of "On Click", then pull down the menu for that box and click [Event Procedure]. Then click the Build button to the right (an ellipsis: "...").

The Visual Basic window will open and show you a

Private Sub something

End Sub

with a blank line between them. Click on that blank line and copy 'n' paste the following there:

'''Begin Code'''
If Nz(Me.tbxLoadGA,"") = "" then 'Diagnose missing search term
MsgBox "No search term provided",,"Search Command Ignored"
Exit Sub
End If
DoCmd.OpenForm "frmFoundGAs",acNormal,,"GA_Number = " & Me.tbxLoadGA
'''End Code'''

This code will exit if the user clicks the search button with nothing in the search box. If there is something in the search box, the code will open the form (frmFoundGAs) and display only the records where GA_Number is the same as the search box data.

I assumed the GA_Number is in fact a number, not a character-type field. If it is a character field, change the DoCmd.OpenForm statement to

DoCmd.OpenForm "frmFoundGAs",acNormal,,"GA_Number = """ & Me.tbxLoadGA & """"

You could do something similar with a three-field search, but it's getting late and I need to sign off.

Suggest you get to know coding if you are going to do much Access. It will multiply your powers of wizardry wonderfully!

Jim