multi criteria code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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


mhmaid said:
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.?
Click to expand...
 
Thanks for help
that worked .


Klatuu said:
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


mhmaid said:
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.?
Click to expand...
Click to expand...
 
Back
Top