Opening form using 2 criteria

  • Thread starter Thread starter Naz
  • Start date Start date
N

Naz

Hi

I'm creating a macro to open a form based on two criteria. I used the wizard
to create a form opener using specific data and the tried (endlessly) to
modify to code to include 2 criteria, this is what it looks like but i kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error, exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know how to
correct it?

All help is appreciated.
 
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"
 
Hi Ken Snell [MVP],
I would use two double quotes instead of the single quote just in case in
the jobtitle or in the payrollNo the single quote is used
stLinkCriteria = "[PayrollNo]=" & """" & Me![PayrollNo] & """ And
[jobtitle]=" & """" & Me![JobTitle] & """"

if the payrollNo is a number there's no need to quote the me!payrollNo
stLinkCriteria = "[PayrollNo]=" & Me![PayrollNo] & " And
[jobtitle]=" & """" & Me![JobTitle] & """"

Regards Paolo

Ken Snell said:
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Naz said:
Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly) to
modify to code to include 2 criteria, this is what it looks like but i kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
 
Hi Ken

I tried pasting this and is comes up with an error saying "expected end of
statement" and it highlightes the [JobTitle] any idea why this is happening?

Thanks

--

_______________________
Naz,
London


Ken Snell said:
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Naz said:
Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly) to
modify to code to include 2 criteria, this is what it looks like but i kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
 
Hi Naz,
it's all on the same line, not on two.

HTH Paolo

Naz said:
Hi Ken

I tried pasting this and is comes up with an error saying "expected end of
statement" and it highlightes the [JobTitle] any idea why this is happening?

Thanks

--

_______________________
Naz,
London


Ken Snell said:
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Naz said:
Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly) to
modify to code to include 2 criteria, this is what it looks like but i kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
Click to expand...
 
Hi,

I put it on the one line this is how i joined it just by pressing delete
until the second line moved to the first,

No] & "' And "[JobTitle] = "


--

_______________________
Naz,
London


Paolo said:
Hi Naz,
it's all on the same line, not on two.

HTH Paolo

Naz said:
Hi Ken

I tried pasting this and is comes up with an error saying "expected end of
statement" and it highlightes the [JobTitle] any idea why this is happening?

Thanks

--

_______________________
Naz,
London


Ken Snell said:
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly) to
modify to code to include 2 criteria, this is what it looks like but i kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
Click to expand...
 
All this must be on the same line

stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And [jobtitle]="
& "'" & Me![JobTitle] & "'"

Here goes on two lines because the system have a limited lenght for a line.
You just have to paste it in your code and remove the line feed.

Regards Paolo

Naz said:
Hi,

I put it on the one line this is how i joined it just by pressing delete
until the second line moved to the first,

No] & "' And "[JobTitle] = "


--

_______________________
Naz,
London


Paolo said:
Hi Naz,
it's all on the same line, not on two.

HTH Paolo

Naz said:
Hi Ken

I tried pasting this and is comes up with an error saying "expected end of
statement" and it highlightes the [JobTitle] any idea why this is happening?

Thanks

--

_______________________
Naz,
London


:

stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly) to
modify to code to include 2 criteria, this is what it looks like but i kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
Click to expand...
 
You can have it on two or more lines, if at the end of each line you have
closed out any quotes, insert a space after the last quote, insert the
ampersand (&), another space, and then the underscore character (_) so if you
have a line that ends like:

"' And [jobtitle]=" & _
the next line could start:
"'" & Me!.....etc.

Although it doesn't seem to me that you need to append the ' by using & "'"
&, you could have:
"' And [jobtitle]='" & Me![Jobtitle] & "'" (Guessing it was just easier to
copy and paste & "'" where needed than to include within the quotes)


Paolo said:
All this must be on the same line

stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And [jobtitle]="
& "'" & Me![JobTitle] & "'"

Here goes on two lines because the system have a limited lenght for a line.
You just have to paste it in your code and remove the line feed.

Regards Paolo

Naz said:
Hi,

I put it on the one line this is how i joined it just by pressing delete
until the second line moved to the first,

No] & "' And "[JobTitle] = "


--

_______________________
Naz,
London


Paolo said:
Hi Naz,
it's all on the same line, not on two.

HTH Paolo

:

Hi Ken

I tried pasting this and is comes up with an error saying "expected end of
statement" and it highlightes the [JobTitle] any idea why this is happening?

Thanks

--

_______________________
Naz,
London


:

stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly) to
modify to code to include 2 criteria, this is what it looks like but i kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
Click to expand...
 
stLinkCriteria = "[PayrollNo]=" & "'" & _
Me![PayrollNo] & "' And [jobtitle]=" _
& "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Naz said:
Hi Ken

I tried pasting this and is comes up with an error saying "expected end of
statement" and it highlightes the [JobTitle] any idea why this is
happening?

Thanks

--

_______________________
Naz,
London


Ken Snell said:
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Naz said:
Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly)
to
modify to code to include 2 criteria, this is what it looks like but i
kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know
how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
Click to expand...
 
Your approach works except when there is a double-quote character in the
PayrollNo or jobtitle field. So the same situation can exist regarding which
approach you use.

To resolve the presence of a quote in the data:

stLinkCriteria = "[PayrollNo]=" & "'" & _
Replace(Me![PayrollNo], "'", "''") & "' _
And [jobtitle]=" & "'" & _
Replace(Me![JobTitle], "'", "''") & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Paolo said:
Hi Ken Snell [MVP],
I would use two double quotes instead of the single quote just in case in
the jobtitle or in the payrollNo the single quote is used
stLinkCriteria = "[PayrollNo]=" & """" & Me![PayrollNo] & """ And
[jobtitle]=" & """" & Me![JobTitle] & """"

if the payrollNo is a number there's no need to quote the me!payrollNo
stLinkCriteria = "[PayrollNo]=" & Me![PayrollNo] & " And
[jobtitle]=" & """" & Me![JobTitle] & """"

Regards Paolo

Ken Snell said:
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "' And
[jobtitle]=" & "'" & Me![JobTitle] & "'"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Naz said:
Hi

I'm creating a macro to open a form based on two criteria. I used the
wizard
to create a form opener using specific data and the tried (endlessly)
to
modify to code to include 2 criteria, this is what it looks like but i
kep
getting a text mismatch error.


Private Sub cmd_OpenRecord_Click()
'The arrow button to the left of the search results used to open
'the record

On Error GoTo Err_cmd_OpenRecord_Click 'Error handing, stop show error,
exit

Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.Minimize 'Minimise this form
stDocName = "frm_Employees" 'This is the form that will be opened
stLinkCriteria = "[PayrollNo]=" & "'" & Me![PayrollNo] & "'" And
"[jobtitle]=" & "'" & Me![JobTitle] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Close acForm, "frm_ReturnSearchResults", acSaveNo 'Close the
return search results form

Exit_cmd_OpenRecord_Click:
Exit Sub

Err_cmd_OpenRecord_Click:
MsgBox Err.Description
Resume Exit_cmd_OpenRecord_Click

End Sub

i'm sure its my strLinkCriteria = line that is wrong does anyone know
how
to
correct it?

All help is appreciated.


--

_______________________
Naz,
London
Click to expand...
Click to expand...
Click to expand...
 
Back
Top