Search Commond Button

[Jessica]

I HAVE A FORM CALLED SEARCH. WHERE I COULD SEARCH BY CRITERIA(CLIENTID OR
NAME) iN THIS FORM I ALSO HAVE A SEARCH COMMOND WHEN I CLICK ON THIS BUTTON I
WANT IT TO TAKE ME TO THE CLIENTS FORM, BUT I WOULD LIKE IT TO GIVE ME THE
INFO. ONLY FOR THE RECORD I SEARCH FOR NOT ALL OF THE RECORDS. I CURRENTLY
HAVE THE FOLLOWING CODE
Private Sub Command49_Click()
On Error GoTo Err_Command49_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Clients Form"

stLinkCriteria = "[ClientID]=" & "'" & Me![ClientID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command49_Click:
Exit Sub

Err_Command49_Click:
MsgBox Err.Description
Resume Exit_Command49_Click

End Sub
wHICH GIVES ME THE CORRECT RECOR, BUT IF I SEARCH BY LASTnAME I WANT IT TO
GIVE ME THE SPECIFIC LAST NAME. wHAT CAN I ADD TO THIS CODE SO WHEN I TYPE IN
A LAST NAME IT GIVES IT TO ME. WHAT DO I NEED TO ADD TO THE CODE

 

[Dennis]

I have put an If in your code below (assuming you type in complete last names
and not part of a last name)

I HAVE A FORM CALLED SEARCH. WHERE I COULD SEARCH BY CRITERIA(CLIENTID OR
NAME) iN THIS FORM I ALSO HAVE A SEARCH COMMOND WHEN I CLICK ON THIS BUTTON I
WANT IT TO TAKE ME TO THE CLIENTS FORM, BUT I WOULD LIKE IT TO GIVE ME THE
INFO. ONLY FOR THE RECORD I SEARCH FOR NOT ALL OF THE RECORDS. I CURRENTLY
HAVE THE FOLLOWING CODE
Private Sub Command49_Click()
On Error GoTo Err_Command49_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Clients Form"

if Nz(Me![LastName],"") = "" Then

stLinkCriteria = "[ClientID]= '" & Me![ClientID] & "'"

Else

stLinkCriteria = "[LastName] = '" & Me![LastName] & "'"
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Jessica]

It tells me that i have to have and if after the else i put it in but it
still doesn't work.

 

[Dennis]

Can you post your new code with my bit so I can check it for you

It tells me that i have to have and if after the else i put it in but it
still doesn't work.

I HAVE A FORM CALLED SEARCH. WHERE I COULD SEARCH BY CRITERIA(CLIENTID OR
NAME) iN THIS FORM I ALSO HAVE A SEARCH COMMOND WHEN I CLICK ON THIS BUTTON I
WANT IT TO TAKE ME TO THE CLIENTS FORM, BUT I WOULD LIKE IT TO GIVE ME THE
INFO. ONLY FOR THE RECORD I SEARCH FOR NOT ALL OF THE RECORDS. I CURRENTLY
HAVE THE FOLLOWING CODE
Private Sub Command49_Click()
On Error GoTo Err_Command49_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Clients Form"

stLinkCriteria = "[ClientID]=" & "'" & Me![ClientID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command49_Click:
Exit Sub

Err_Command49_Click:
MsgBox Err.Description
Resume Exit_Command49_Click

End Sub
wHICH GIVES ME THE CORRECT RECOR, BUT IF I SEARCH BY LASTnAME I WANT IT TO
GIVE ME THE SPECIFIC LAST NAME. wHAT CAN I ADD TO THIS CODE SO WHEN I TYPE IN
A LAST NAME IT GIVES IT TO ME. WHAT DO I NEED TO ADD TO THE CODE

 

[Jessica]

I try it but it give me no information. I alstry putting the IF NZ statement
after the CLientID statement and it still doesn't work. If i put in the
client id i get the info. But for LastName i get anything.

I have put an If in your code below (assuming you type in complete last names
and not part of a last name)

I HAVE A FORM CALLED SEARCH. WHERE I COULD SEARCH BY CRITERIA(CLIENTID OR
NAME) iN THIS FORM I ALSO HAVE A SEARCH COMMOND WHEN I CLICK ON THIS BUTTON I
WANT IT TO TAKE ME TO THE CLIENTS FORM, BUT I WOULD LIKE IT TO GIVE ME THE
INFO. ONLY FOR THE RECORD I SEARCH FOR NOT ALL OF THE RECORDS. I CURRENTLY
HAVE THE FOLLOWING CODE
Private Sub Command49_Click()
On Error GoTo Err_Command49_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Clients Form"

if Nz(Me![LastName],"") = "" Then

stLinkCriteria = "[ClientID]= '" & Me![ClientID] & "'"

Else

stLinkCriteria = "[LastName] = '" & Me![LastName] & "'"
End If
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command49_Click:
Exit Sub

Err_Command49_Click:
MsgBox Err.Description
Resume Exit_Command49_Click

End Sub
wHICH GIVES ME THE CORRECT RECOR, BUT IF I SEARCH BY LASTnAME I WANT IT TO
GIVE ME THE SPECIFIC LAST NAME. wHAT CAN I ADD TO THIS CODE SO WHEN I TYPE IN
A LAST NAME IT GIVES IT TO ME. WHAT DO I NEED TO ADD TO THE CODE

 

[Tim Ferguson]

stLinkCriteria = "[ClientID]=" & "'" & Me![ClientID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

What type of field is your [ClientID]? You are passing a filter that looks
like

[ClientID] = '1099'

and if it's a number this may or may not work. Try getting rid of the
internal quotes.

Tim F

 

[Jessica]

It is a number field do you mean get rid off the quotes only

stLinkCriteria = "[ClientID]=" & "'" & Me![ClientID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

What type of field is your [ClientID]? You are passing a filter that looks
like

[ClientID] = '1099'

and if it's a number this may or may not work. Try getting rid of the
internal quotes.

Tim F

 

[Tim Ferguson]

What type of field is your [ClientID]? You are passing a filter that
looks like

[ClientID] = '1099'

and if it's a number this may or may not work. Try getting rid of the
internal quotes.

It is a number field do you mean get rid off the quotes only

stLinkCriteria = "[ClientID]=" & Me![ClientID]

But just after posting, I realised that it's not what your problem is
anyway. If what you have is working, don't fix it!

I think the correct answer to your question is going to be what Dennis is
suggesting:

If cboLastNames.ListIndex > -1 Then
' user picked a last name, look up on that
criterion = "LastName = """ & cboLName.Value & """"

ElseIf Not IsNull(txtHomeTown.Value) Then
' look up by home town, etc etc
criterion = "Address LIKE ""*" & txtHomeTown.Value & "*"""

Else
' nothing selected, use a ID number lookup instead
criterion = "ClientID=" & txtClientID.Value

EndIf

' don't forget to check what you are doing until you
' know it's the right thing!!
MsgBox criterion

' okay, now open the form
DoCmd.OpenForm stDocName, , , criterion

and so on and so forth. Alternatively you can use a bit of AND or OR to
join the separate bits of the criterion until you have something like

"LastName = ""Jones"" AND Address LIKE ""*Vegas*"" AND Hair="Red"""

et cetera et cetera.

Hope that helps


Tim F