Access2K help w/searching... 1

[stellJess]

Everyone,

i am in desperate need of your help. i need to implement a search from a command button on a main data entry form.

this is how it should work (i just do not know how to do it). will you please break it down to the lowest terms for me? i am at wits end.

@ the main data entry form when click the [search] button a form pops up that enables the user to enter:

scenario1 - ssn and/or clientNumber OR
scenario2 - lastName

@ scenario1 the closed main data entry form should open to display the record(s) for the ssn or the client number.

@ scenario2 another form should open with a list of all possible records with that same last name (smith). then when one name is selected (via a button or a click), the main data entry form should open to display the record(s) for the chosen name (smith, john).

i do not know how make this work... i do not know how to make this work. but it is what the client wants. can someone out there help me out, plz.

in advance, thanks
stellJess

 

[jrbarnett]

Hi stellJess,

This is quite simple to do, but it requires a bit of forethought. I have assumed in writing this that the main data entry form is called frmDataEntry and that the field names above are the same on both forms. I have put loads of comments in to try and help you understand what is going on, and have assumed that all data are stored in string fields in the database.

I have assumed in the surname search that you want an exact match, so that entering Smith finds SMITH and not Smithers, Smithson etc but is case insensitive. If not, change the Wherecondition to LIKE Me.txtLastname & "*"

Try the following:

1. Create a new unbound form. On it, put three text boxes, txtSSN, txtClientNumber and txtLastname with appropriate labels. Add two command buttons - cmdSearch and cmdCancel with appropriate labels and save it as frmSearch.
Set the Default button property to Yes on cmdSearch and the Cancel Button property to Yes on cmdCancel. Set the form's mode to acDialog and the modal property to Yes so it will appear as a dialog box.

Open the form's module and add the following VBA code:

Private Sub cmdCancel_Click
' close the form if the user hits cancel
DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdSearch_Click

Dim strWhere as String

' this is triggered when the user hits the Search button.
' test for second scenario first - it is easier to check for one surname then assume if it is not that then the client ssn / client number.
If Len (Me.txtSurname & "&quot > 0 then
' they have entered something in surname box. using the above trick avoids testing for nulls as well as zero length strings
DoCmd.OpenForm Formname := "frmDataEntry" WhereCondition := "Lastname='" & Me.txtSurname & "'"
Else
' this is a bit more complicated as we have to cope with two fields which may or may not exist.
If len (Me.txtSSN & "&quot >0 Then
strWhere = " AND SSN='" & Me.strSSN & "'"
End If
If len (Me.txtClientNumber & "&quot >0 Then
strWhere = strWhere & " AND ClientNumber='" & Me.txtClientNumber & "'"
End If
strWhere = Mid (strWhere, 6) 'strip off the first AND
DoCmd.OpenForm Formname := "frmDataEntry" WhereCondition := strWhere ' and open data entry form with where clause applied.
End If
DoCmd.Close acForm, Me.Name ' and close the search form.
End Sub

Open the main data entry form in design mode, right click on the search button, and choose View Code.
If it doesn't exist yet (not clear) add a new one, call it cmdSearch and put the caption as Search.

Clear any junk between the Private Sub and End Sub lines.
Put in the line

DoCmd.OpenForm "frmSearch" Mode:=acDialog

Save and finish.

This should do the job if you are using Access 97 or 2000, but if you are using XP (2002), you will need to go to Tools -> References while in the code editor and tick the DAO 3.6 Object library.

Regards,

John

 

[stellJess]

John,

thank you so very much for your help. i'm eager to implement it. ...just wanted to let you know that 'i'm on it' and will keep you posted.

again, thanks.
stellJess