Search and open form

[evelynls]

I'm trying to find the best way to do the following:

1. the user select desired search option from the option group (opgSearchOption) - see 1st attachment
2. Validate at least one characteristic has been entered in Search Box (txtSearch)
3. when the user click Search button, open the form called frmParentMapEntrysearch (see 2nd attachment) and show the search result

Here is what i have for now:

Private Sub btnSearch_Click()
'1. Declare Variables
Dim strSearch As String

'2. Validate that at least 1 character has been entered for the search
If IsNull(strSearch) Then
MsgBox "Please enter search values of the search options you have selected"
End If

'3. Input variable
strSearch = txtSearch.Text

'4. Search and find the customer for corresponding search option
If opgSearchOption = 1 Then
DoCmd.OpenForm "frmParentMapEntry", acNormal, "strSearch = " & [CUSTOMER_NAME]
End If
If opgSearchOption = 2 Then
DoCmd.OpenForm "frmParentMapEntry", acNormal, "strSearch = " & [CUSTOMER_SID]
End If
If opgSearchOption = 3 Then
DoCmd.OpenForm "frmParentMapEntry", acNormal, "strSearch = " & [CUSTOMER_NUMBER]
End If

End Sub

Seems like there are many problems with my code.:banghead: Anyone any ideas?

Thank you!

 

[sneuberg]

First you don't have the WHERE condition in the right argument. You need an additional comma after acNormal. So if [CUSTOMER_NUMBER] is a number it would be like:

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "strSearch = " & [CUSTOMER_NUMBER]

the [CUSTOMER_NAME] is most likely text so you need to put it in single quotes like

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "strSearch = '" & [CUSTOMER_NAME] & "'"

But if a single quote can occur in the name like O'Malley, then you need to escape the single quote which you can do with the replace function like:

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "strSearch = '" & Replace([CUSTOMER_NAME], "'", "''") & "'"

also Like might work better for you in this type of search.

 

[sneuberg]

Oh by the way if [CUSTOMER_SID] and [CUSTOMER_NUMBER] are numbers you don't put single quote around them, but you need to make sure the user actually enters a number. So I suggest checking them with the IsNumeric function and presenting a message box if they are not. A non numeric input will give you a syntax error in these cases.

 

[MarkK]

And this field name--in blue--is almost certainly wrong . . .

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "[COLOR="Blue"]strSearch[/COLOR] = '" & [CUSTOMER_NAME] & "'"

You are constructing a filter here for the form you opening, and that form almost certainly knows nothing about 'strSearch.' What is the field you are applying that filter too?

 

[evelynls]

Oh by the way if [CUSTOMER_SID] and [CUSTOMER_NUMBER] are numbers you don't put single quote around them, but you need to make sure the user actually enters a number. So I suggest checking them with the IsNumeric function and presenting a message box if they are not. A non numeric input will give you a syntax error in these cases.

If I want to use like for CUSTOMER_NAME, is this correct:

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "strSearch like '" & [ CUSTOMER_NAME] & "'"

Since I define strSearch as a string, do I need to turn CUSTOMER_NUMBER and CUSTOMER_SID as string too? Like STR$[CUSTOMER_NUMBER]

 

[missinglinq]

Before you even get to the opening of a Form

strSearch = txtSearch.Text

would be very problematic! To reference the Text Property of a Control, the Control has to have Focus; since the code is in the OnClick event of a Command Button, the Command Button has Focus...not txtSearch!

Use

strSearch = txtSearch.Value

or, because Value is the Default Property of a Textbox, simply

strSearch = txtSearch

will do just fine!

Linq ;0)>

 

[sneuberg]

If I want to use like for CUSTOMER_NAME, is this correct:

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "strSearch like '" & [ CUSTOMER_NAME] & "'"

Since I define strSearch as a string, do I need to turn CUSTOMER_NUMBER and CUSTOMER_SID as string too? Like STR$[CUSTOMER_NUMBER]

No I got this backwards. Please standby. I'll get you a better answer.

 

[evelynls]

And this field name--in blue--is almost certainly wrong . . .

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "[COLOR="Blue"]strSearch[/COLOR] = '" & [CUSTOMER_NAME] & "'"

You are constructing a filter here for the form you opening, and that form almost certainly knows nothing about 'strSearch.' What is the field you are applying that filter too?

If users search customer information using CUSTOMER NAME then the filter apply to CUSTOMER_NAME in the frmParentMapEntry form. If they search using CUSTOMER NUMBER then the filter apply to CUSOMTER_NUMBER.

 

[evelynls]

Before you even get to the opening of a Form

strSearch = txtSearch.Text

would be very problematic! To reference the Text Property of a Control, the Control has to have Focus; since the code is in the OnClick event of a Command Button, the Command Button has Focus...not txtSearch!

Use

strSearch = txtSearch.Value

or, because Value is the Default Property of a Textbox, simply

strSearch = txtSearch

will do just fine!

Linq ;0)>

Is strSearch = Me.txtSearch Also works?

 

[sneuberg]

If users search customer information using CUSTOMER NAME then the filter apply to CUSTOMER_NAME in the frmParentMapEntry form. If they search using CUSTOMER NUMBER then the filter apply to CUSOMTER_NUMBER.

This would be more like:

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "[CUSTOMER_NAME]  Like '" & strSearch & "*'"

but there are other problems that I will address shortly.

 

[sneuberg]

Starting at the beginning this

If IsNull(strSearch) Then
MsgBox "Please enter search values of the search options you have selected"
End If

need to be changed as you need to check the contents of the textbox, not the string, and you need to exit the sub if it's not right. Also it's best to check for empty strings. I suggest this:

If Len(Me.txtSearch & vbNullString) = 0 Then
    MsgBox "Please enter search values of the search options you have selected"
    Exit Sub
End If

Then the line

strSearch = Me.txtSearch

will be ok.

If the CUSTOMER_NAME is the field name in the "frmParentMapEntry" record source and is a text field then the following should work for the first option.

If opgSearchOption = 1 Then

DoCmd.OpenForm "frmParentMapEntry", acNormal, , "[CUSTOMER_NAME]  Like '" & Replace(strSearch, "'", "''") & "*'"

End If

More to come about the other options.

 

[evelynls]

Those perfectly works! Thanks a lot.

I realized there are some case sensitivity issues so I had some minor changes.

So right now they are like:

strSearch = LCase(Me.txtSearch)
DoCmd.OpenForm "frmParentMapEntry", acNormal, , LCase("[CUSTOMER_NAME] Like '") & Replace(strSearch, "'", "''") & "*'"

 

[sneuberg]

For options 2 and 3 I suggest checking to make sure they are numbers. After the code that checks if the input is null or an empty string I suggest something like.

If opgSearchOption = 2 And Not IsNumeric(Me.txtSearch) Then
    MsgBox "Please enter a number for Customer SID"
    Exit Sub
End If


If opgSearchOption = 3 And Not IsNumeric(Me.txtSearch)  Then
    MsgBox "Please enter a number for Customer Number"
    Exit Sub
End If

Then

If opgSearchOption = 2 Then
DoCmd.OpenForm "frmParentMapEntry", acNormal, , "[CUSTOMER_SID] = " & strSearch
End If
If opgSearchOption = 3 Then
DoCmd.OpenForm "frmParentMapEntry", acNormal, , "[CUSTOMER_NUMBER] = " & strSearch
End If

should work. but if they get the number wrong the user will just see a blank form. I suggest considering an entirely difference approach. Rather than having options to select just have three combo boxes on the search form populated with the distinct values in the forms record source. The form could be opened in the afterupdate events of the combo boxes. The SQL for the row source for the customer name combo box for example would be something like:

SELECT DISTINCT CUSTOMER_NAME
FROM [The appropriate query or table name]

that way the user would never end up with a blank form.

 

[evelynls]

I really appreciate your time and effort! They work perfectly!
That is a good point. Thank you for your suggestion.

 

[Sodslaw]

i have a question...
If you don't want to go to a new record, if what you are searching for cant be found (as this overrides data entry=no in the form property) how can this be possible and perhaps with a messagebox ?

 

[missinglinq]

That requires a different approach to opening the secondary Form and searching,

In the Primary Form:

Private Sub Go2SecondaryForm_Click()

 If Nz(Me.RecordID,"") <> "" Then
  DoCmd.OpenForm "Secondary Form", , , , , , Me.RecordID
 Else
  MsgBox "A RecordID Must Be Entered First!"
 End If

End Sub

In the Secondary Form:

Private Sub Form_Load()

Dim rst As Recordset

If Nz(Me.OpenArgs,"") <> "" Then

 Set rst = Me.RecordsetClone
 
 rst.FindFirst "[RecordID] = " & Me.OpenArgs 
 
   If Not rst.NoMatch Then
    Me.Bookmark = rst.Bookmark
   Else
    Msgbox "Matching Record Not Found!"
   End If

rst.Close
Set rst = Nothing

End If

End Sub

The above code assumes that RecordID is Numeric. If it is actually defined Text, replace the line

rst.FindFirst "[RecordID] = " & Me.OpenArgs

with

rst.FindFirst "[RecordID] = '" & Me.OpenArgs & "'"

You'll also need to replace Go2SecondaryForm with the actual name of your Command Button and SecondaryForm.

Linq ;0)>

 

[Sodslaw]

Perfect! i will try this