multi criteria code

[mhmaid]

hello every one

I have this code from Northwind sample.

Private Sub ReviewProducts_Click()
' This code created in part by Command Button Wizard.
On Error GoTo Err_ReviewProducts_Click

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String, strLinkCriteria As String

' If CompanyName control is blank, display a message.
If IsNull(Me![CompanyName]) Then
strMsg = "Move to the supplier record whose products you want to
see, then press the Review Products button again."
intStyle = vbOKOnly
strTitle = "Select a Supplier"
MsgBox strMsg, intStyle, strTitle
Me![CompanyName].SetFocus
Else
' Otherwise, open Product List form, showing products for current
supplier.
strDocName = "Product List"
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If

Exit_ReviewProducts_Click:
Exit Sub

Err_ReviewProducts_Click:
MsgBox Err.Description
Resume Exit_ReviewProducts_Click

End Sub

in this code , the criteria used is
strDocName = "Product List"
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria
ok , what if i want more than one criteria , how the code will be
and also , if i the criteria is from a subform.

ie.i want to open a form called "Patients list:

my current form"the main form " is Expense reports by Patient", with a
subform called "Patients subform" with fields like batchno , Patientid, etc.

now , i want to open the form "Patients list" where the Patientid =
patientid in the subform, and at the same time batchno =batchno in the subform

how the code should be.?

 

[Klatuu]

What you are putting in strCriteria is nothing more than an SQL WHERE clause
without the word where. So, you can use AND and/or OR or anthing else that is
valid in a WHERE clause.

I know it is not in the sample code you sent, but in most cases, you will be
using the current form, so you can use:
strLinkCriteria = "[SupplierID] = Me.SupplierID"
instead of
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"

If you need to address a control on a subform, you have to qualify it by
addressing the name of the subform control. The subform control name may or
may not be the same as the form being used as a subform, but the proper
referencing is to use the name of the subform control.

=Me!subControlName.Form!SomeControlName

Then to use multiple criteria it is:

strLinkCriteria = "[SupplierID] = Me.SupplierID" & " AND
[SomeOtherField] = " & Me.subControlName.Form!SomeControlName

Now, the code you sent and what I added assume both field in the table as
being numeric because the code has no delimiters around the values. Assume
SomeOtherField is a text data type field. The syntax would then be:

strLinkCriteria = "[SupplierID] = Me.SupplierID" & " AND
[SomeOtherField] = '" & Me.subControlName.Form!SomeControlName & "'"
--
Dave Hargis, Microsoft Access MVP

hello every one

I have this code from Northwind sample.

Private Sub ReviewProducts_Click()
' This code created in part by Command Button Wizard.
On Error GoTo Err_ReviewProducts_Click

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String, strLinkCriteria As String

' If CompanyName control is blank, display a message.
If IsNull(Me![CompanyName]) Then
strMsg = "Move to the supplier record whose products you want to
see, then press the Review Products button again."
intStyle = vbOKOnly
strTitle = "Select a Supplier"
MsgBox strMsg, intStyle, strTitle
Me![CompanyName].SetFocus
Else
' Otherwise, open Product List form, showing products for current
supplier.
strDocName = "Product List"
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If

Exit_ReviewProducts_Click:
Exit Sub

Err_ReviewProducts_Click:
MsgBox Err.Description
Resume Exit_ReviewProducts_Click

End Sub

in this code , the criteria used is
strDocName = "Product List"
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria
ok , what if i want more than one criteria , how the code will be
and also , if i the criteria is from a subform.

ie.i want to open a form called "Patients list:

my current form"the main form " is Expense reports by Patient", with a
subform called "Patients subform" with fields like batchno , Patientid, etc.

now , i want to open the form "Patients list" where the Patientid =
patientid in the subform, and at the same time batchno =batchno in the subform

how the code should be.?

 

[mhmaid]

Thanks for help
that worked .

What you are putting in strCriteria is nothing more than an SQL WHERE clause
without the word where. So, you can use AND and/or OR or anthing else that is
valid in a WHERE clause.

I know it is not in the sample code you sent, but in most cases, you will be
using the current form, so you can use:
strLinkCriteria = "[SupplierID] = Me.SupplierID"
instead of
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"

If you need to address a control on a subform, you have to qualify it by
addressing the name of the subform control. The subform control name may or
may not be the same as the form being used as a subform, but the proper
referencing is to use the name of the subform control.

=Me!subControlName.Form!SomeControlName

Then to use multiple criteria it is:

strLinkCriteria = "[SupplierID] = Me.SupplierID" & " AND
[SomeOtherField] = " & Me.subControlName.Form!SomeControlName

Now, the code you sent and what I added assume both field in the table as
being numeric because the code has no delimiters around the values. Assume
SomeOtherField is a text data type field. The syntax would then be:

strLinkCriteria = "[SupplierID] = Me.SupplierID" & " AND
[SomeOtherField] = '" & Me.subControlName.Form!SomeControlName & "'"
--
Dave Hargis, Microsoft Access MVP

hello every one

I have this code from Northwind sample.

Private Sub ReviewProducts_Click()
' This code created in part by Command Button Wizard.
On Error GoTo Err_ReviewProducts_Click

Dim strMsg As String, strTitle As String
Dim intStyle As Integer
Dim strDocName As String, strLinkCriteria As String

' If CompanyName control is blank, display a message.
If IsNull(Me![CompanyName]) Then
strMsg = "Move to the supplier record whose products you want to
see, then press the Review Products button again."
intStyle = vbOKOnly
strTitle = "Select a Supplier"
MsgBox strMsg, intStyle, strTitle
Me![CompanyName].SetFocus
Else
' Otherwise, open Product List form, showing products for current
supplier.
strDocName = "Product List"
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria
End If

Exit_ReviewProducts_Click:
Exit Sub

Err_ReviewProducts_Click:
MsgBox Err.Description
Resume Exit_ReviewProducts_Click

End Sub

in this code , the criteria used is
strDocName = "Product List"
strLinkCriteria = "[SupplierID] = Forms![Suppliers]![SupplierID]"
DoCmd.OpenForm strDocName, , , strLinkCriteria
ok , what if i want more than one criteria , how the code will be
and also , if i the criteria is from a subform.

ie.i want to open a form called "Patients list:

my current form"the main form " is Expense reports by Patient", with a
subform called "Patients subform" with fields like batchno , Patientid, etc.

now , i want to open the form "Patients list" where the Patientid =
patientid in the subform, and at the same time batchno =batchno in the subform

how the code should be.?