multiple link criteria in form

G

Guest

What is wrong with this code below? It works with either the category field
or the subcategory field but not with them both. What I am trying to do is
open GroupDetailsSubFrm and only show the records in that query for the
category and the subcategory on the original form... What am I doing wrong?

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "GroupDetailsSubFrm"
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
 
G

Guest

The And should be inside the quote

Yours
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]

Should be
stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]=" &
Me![SubCategory]
 
G

Graham Mandeno

The "And" needs to be part of the SQL string and therefore inside the
quotes:

stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]="
& Me![SubCategory]
 
C

Cilla

BLTibbs said:
What is wrong with this code below? It works with either the category field
or the subcategory field but not with them both. What I am trying to do is
open GroupDetailsSubFrm and only show the records in that query for the
category and the subcategory on the original form... What am I doing wrong?

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "GroupDetailsSubFrm"
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
Click to expand...

Not sure what your working from and too.

Are you opening one form from another form?

Is "Me" Form 1 and Category field 1 and subCategory field 2 on the Me
form?

Is the GroupDetailsSubFrm on a different form that the "Me" form is to
open?

Is both Category and SubCategory fields on the GroupDetailsSubFrm as
seperate Fields?

If all these are true then I would do it a little differently:

Dim xxx As String
Dim zzz As String
xxx = [Text0]
zzz = [Combo3]
DoCmd.OpenForm "GroupDetailsSubFrm"
DoCmd.SelectObject acForm, "GroupDetailsSubFrm"
DoCmd.ShowAllRecords
Dim SrcSQL As String
SrcSQL = "SELECT GroupDetailSubFrmsTbl.* FROM GroupDetailSubFrmsTbl
WHERE
(((GroupDetalSubFrmsTbl.Category)=[Forms]![MeFormName]![Category]) AND
((GroupDetailSubFrmTbl.subCategory)=[Forms]![MeFormName]![subCategory]))"
DoCmd.ApplyFilter SrcSQL
Exit Sub

Note: Replace GroupDetailSubFrmsTbl with the table name of the
GroupDetailSubFrm thorugh the sql string

Replace the MeFromName with the Form name for "Me" in your above code.
 
C

Cilla

Cilla said:
BLTibbs said:
What is wrong with this code below? It works with either the category field
or the subcategory field but not with them both. What I am trying to do is
open GroupDetailsSubFrm and only show the records in that query for the
category and the subcategory on the original form... What am I doing wrong?

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "GroupDetailsSubFrm"
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
Click to expand...

Not sure what your working from and too.

Are you opening one form from another form?

Is "Me" Form 1 and Category field 1 and subCategory field 2 on the Me
form?

Is the GroupDetailsSubFrm on a different form that the "Me" form is to
open?

Is both Category and SubCategory fields on the GroupDetailsSubFrm as
seperate Fields?

If all these are true then I would do it a little differently:

Dim xxx As String
Dim zzz As String
xxx = [Text0]
zzz = [Combo3]
DoCmd.OpenForm "GroupDetailsSubFrm"
DoCmd.SelectObject acForm, "GroupDetailsSubFrm"
DoCmd.ShowAllRecords
Dim SrcSQL As String
SrcSQL = "SELECT GroupDetailSubFrmsTbl.* FROM GroupDetailSubFrmsTbl
WHERE
(((GroupDetalSubFrmsTbl.Category)=[Forms]![MeFormName]![Category]) AND
((GroupDetailSubFrmTbl.subCategory)=[Forms]![MeFormName]![subCategory]))"
DoCmd.ApplyFilter SrcSQL
Exit Sub

Note: Replace GroupDetailSubFrmsTbl with the table name of the
GroupDetailSubFrm thorugh the sql string

Replace the MeFromName with the Form name for "Me" in your above code.
Click to expand...

Opps: Didn't replace my text0 and combo 3. Try this one

Dim xxx As String
Dim zzz As String
xxx = [Category]
zzz = [SubCategory]
DoCmd.OpenForm "GroupDetailsSubFrm"
DoCmd.SelectObject acForm, "GroupDetailsSubFrm"
DoCmd.ShowAllRecords
Dim SrcSQL As String
SrcSQL = "SELECT GroupDetailSubFrmsTbl.* FROM GroupDetailSubFrmsTbl
WHERE
(((GroupDetalSubFrmsTbl.Category)=[Forms]![MeFormName]![Category]) AND
((GroupDetailSubFrmTbl.subCategory)=[Forms]![MeFormName]![subCategory]))"
DoCmd.ApplyFilter SrcSQL
Exit Sub

Note: Replace GroupDetailSubFrmsTbl with the table name of the
GroupDetailSubFrm thorugh the sql string

Replace the MeFromName with the Form name for "Me" in your above code.
 
G

Guest

You were right on the form operation! Thanks. I tried your same format for
a dlookup in a field control dealing with the same fields, but it gives me
back a result of 0.00 every time regardless of what is should come out to. Do
I have something wrong in the following dlookup string?

=DLookUp("Ingresos","InQry","[category]= " &
[Forms]![GroupsubCategorySubFrm]![category] And "[subcategory]= " &
[Forms]![GroupsubCategorySubFrm]![subcategory])

I tried putting the and inside the quotes like you said for the form, but it
tells me that is invalid syntax and won't allow it. What now?

Brian

Ofer Cohen said:
The And should be inside the quote

Yours
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]

Should be
stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]=" &
Me![SubCategory]

--
Good Luck
BS"D


BLTibbs said:
What is wrong with this code below? It works with either the category field
or the subcategory field but not with them both. What I am trying to do is
open GroupDetailsSubFrm and only show the records in that query for the
category and the subcategory on the original form... What am I doing wrong?

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "GroupDetailsSubFrm"
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
Click to expand...
Click to expand...
 
G

Guest

Scratch that last post - I found the obvious error - thanks again!

Ofer Cohen said:
The And should be inside the quote

Yours
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]

Should be
stLinkCriteria = "[Category]=" & Me![Category] & " And [subCategory]=" &
Me![SubCategory]

--
Good Luck
BS"D


BLTibbs said:
What is wrong with this code below? It works with either the category field
or the subcategory field but not with them both. What I am trying to do is
open GroupDetailsSubFrm and only show the records in that query for the
category and the subcategory on the original form... What am I doing wrong?

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "GroupDetailsSubFrm"
stLinkCriteria = "[Category]=" & Me![Category] And "[subCategory]=" &
Me![SubCategory]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command19_Click:
Exit Sub
Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click
End Sub
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

Record Count...Almost! 8
Multiple Search Command criteria in a form 2
Open a form for specific record. 3
search command 2
Code Only works first time! 2
Pass multiple criteria with VBA 7
transfer a field using button 1
Trying to get Current User info only 2

Top