How to creat a Query in Code

[Ayo]

Thank you so much, for all your help Ken. I really appreciate everything you
did for me. I am very greateful.
Thank you.

Ayo:

The line:

DoCmd.OpenForm "Invoice by Reviewer and Vendor", acNormal, , , , acDialog,
OpenArgs:=(cmbMarketReviewer.Value,cmbVendorName.Value)

is not valid as you cannot pass two separate values directly via the
OpenArgs mechanism. It is possible to do so, however, by making use of a
module which was developed by my former colleague Stuart McCall and myself
which allows value lists (of literal values only, so not applicable in your
case) or a list of named arguments (which could be used in your case).
You'll find a file which demonstrates the use of the module at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=24091&webtag=ws-msdevapps


As far as the code for the whole procedure is concerned I'd make a few points:

1. Its best to declare the variables as the relevant types even though the
code will work without doing so. Leaving them untyped as you've done makes
them all of type Variant. Msg, Title, strReviewer and strVendor would be
declared As String (See my further comments below with regard to the
strReviewer and strVendor, however); Style and Response would be declared As
Integer. Note that if you declare multiple variables in one line each should
be individually given a type, even if all are of the same type.

2. The 'If IsNull(strReviewer) Or IsNull(strVendor) Then' line means that
the message box will only pop up if one or both of the controls is left Null.
To my mind the message "You need not select a both Reviewer and Vendor.
Click Yes to continue." sounds more as though both controls have been used
and you are alerting the user to the fact that its not actually necessary to
use both. My approach would have been to make this clear by means of a label
on the form, in which case there would be no need to do so in the code.

However, strReviewer and strVendor are, to judge by the 'str' tags, clearly
meant to be of String data type. Only a variable of Variant data type can be
Null, however, so if you do keep to your current approach of using the code
to inform the user of the optional nature of each control, these should be
declared as such, e.g.

Dim varReviewer As Variant, varVendor As Variant

and then you'd use:

If IsNull(varReviewer) Or IsNull(varVendor) Then

3. The following lines:

Else ' User chose No.
Exit Sub ' Perform some action.

are unnecessary as if the user responds with 'No' the code execution will
immediately pass to the End Sub line.

If there are any new points, rather than developments of this thread, which
you need to raise please do so in new threads. This will give more
opportunity for others to chip in.

Ken Sheridan
Stafford, England

Hi Ken,
It's me again. Sorry to bother you but, is the following a valid code?
DoCmd.OpenForm "Invoice by Reviewer and Vendor", acNormal, , , ,
acDialog, OpenArgs:=(cmbMarketReviewer.Value,cmbVendorName.Value)

This is the whole code module:
Private Sub cmdVMR_Rep_Click()
Dim Msg, Style, Title, Response, strReviewer, strVendor

Msg = "You need not select a both Reviewer and Vendor. Click Yes to
continue." ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
Title = "Selected Reviewer and Vendor" ' Define title.
strReviewer = Forms![Select Vendor and Reviewer].cmbMarketReviewer.Value
strVendor = Forms![Select Vendor and Reviewer].cmbVendorName.Value

If IsNull(strReviewer) Or IsNull(strVendor) Then
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then ' User chose Yes.
DoCmd.OpenForm "Select Vendor and Reviewer", acNormal, , , ,
acDialog ' Perform some action.
Else ' User chose No.
Exit Sub ' Perform some action.
End If
Else
DoCmd.OpenForm "Invoice by Reviewer and Vendor", acNormal, , , ,
acDialog, OpenArgs:=cmbMarketReviewer.Value
DoCmd.Close acForm, "Select Vendor and Reviewer"
End If
End Sub