coding for search page

  • Thread starter Thread starter Lauren B
  • Start date Start date
L

Lauren B

I am creating a page for users to search customer information. I have
created an unbound text box for each corresponding field on the customer
form. The user can then enter customer information on the search page and
be taken directly to that customer's form.

I am using the following code to do this:
DoCmd.OpenForm "Form 1", acNormal "Field1 = '" & TextBox1.Value&"'"

Is there any way to write this code so that the user does not have to put in
exact information but so that if the users types "a" in the Name field, for
example, all records will open that have an "a" anywhere in the name?

Thank you in advance for any assistance.

LB
 
Lauren B said:
I am creating a page for users to search customer information. I have
created an unbound text box for each corresponding field on the customer
form. The user can then enter customer information on the search page and
be taken directly to that customer's form.

I am using the following code to do this:
DoCmd.OpenForm "Form 1", acNormal "Field1 = '" & TextBox1.Value&"'"

Is there any way to write this code so that the user does not have to put in
exact information but so that if the users types "a" in the Name field, for
example, all records will open that have an "a" anywhere in the name?

Thank you in advance for any assistance.

LB
Click to expand...

You were close. You need 2 commas after acNormal, replace "=" with "Like"
and add two "*".

DoCmd.OpenForm "Form1", acNormal, , "[Field1] Like '*" & Me.Text3 & "*'"

The 'Where' part above looks like this , Spaces added for viewing ease:
...... Like ' * " & Me.Text3 & " * ' "
^^^ ^^^^

BTW,
"Value" is the default property so it is not necessary to add it after the
control name.

Spaces in object names causes headaches. "Form1", "MainForm" or
"frmMainForm" is just as easy to read and caused less problems if coding is
necessary.

HTH
 
Heres an alternate method, using SQL

Option Compare Database

Private Sub searchbutton_Click()


Dim LSQL As String
Dim LSearchString As String


If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You have to enter a part number!"

Else

LSearchString = txtSearchString

'Filter results based on search string
LSQL = "SELECT * FROM TblNme WHERE [Column] = '" & LSearchString & "';"

Form_PartSearchfrm.RecordSource = LSQL

'Clear search string
txtSearchString = ""

MsgBox "Results have been filtered to Part# " & LSearchString & "."

End If



End Sub
 
Back
Top