Opening form using 2 criteria

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.
 
K

Ken Snell [MVP]

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

Paolo

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...
 
N

Naz

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...
 
P

Paolo

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...
 
N

Naz

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...
 
P

Paolo

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...
 
G

GB

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...
 
K

Ken Snell [MVP]

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...
 
K

Ken Snell [MVP]

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run time error 2494 1
Help with opening a form to a specific record 7
Open alternate form with criteria 2
How to go to new record when opening form from command if no link 6
Passing Two Argument in OpenFrom 4
How to go to last record when opening form 7
stLinkCriteria Help 2
Criteria when opening form 1

Top