Double click to open form

I have a form which has subforms to show me my active orders and inventory
information. I want to be able to double click or just click the PO Number to
open that record in my Order Details form. I tried creating a macro by
mimicking the one in the Northwind 2007 database but when I do it in mine I
receive an error which states:

"Syntax Error (missing Operator) in query expression '[PO_Number]=PHX
0085453'"

Is this because the PO_Number is not the primary key? Should I be using the
primary key to double click?

Any suggestions will be greatly appreciated.

Thank you,
Amy

Dirtrhoads -

I suspect you need delimiters for your text value. Your end results would be:
[PO_Number]='PHX 0085453'
but you are probably passing this in, so it would be built something like
this (using your form and control names):
"[PO_Number]='" & forms!yourformname!yourPONumberControlName & "'"

--
Daryl S


"dirtrhoads" wrote:

I have a form which has subforms to show me my active orders and inventory
information. I want to be able to double click or just click the PO Number to
open that record in my Order Details form. I tried creating a macro by
mimicking the one in the Northwind 2007 database but when I do it in mine I
receive an error which states:

"Syntax Error (missing Operator) in query expression '[PO_Number]=PHX
0085453'"

Is this because the PO_Number is not the primary key? Should I be using the
primary key to double click?

Any suggestions will be greatly appreciated.

Thank you,
Amy

Would I put this in OpenForm Arguments field of the Macro?

"Daryl S" wrote:

Dirtrhoads -

I suspect you need delimiters for your text value. Your end results would be:
[PO_Number]='PHX 0085453'
but you are probably passing this in, so it would be built something like
this (using your form and control names):
"[PO_Number]='" & forms!yourformname!yourPONumberControlName & "'"

--
Daryl S


"dirtrhoads" wrote:

I have a form which has subforms to show me my active orders and inventory
information. I want to be able to double click or just click the PO Number to
open that record in my Order Details form. I tried creating a macro by
mimicking the one in the Northwind 2007 database but when I do it in mine I
receive an error which states:

"Syntax Error (missing Operator) in query expression '[PO_Number]=PHX
0085453'"

Is this because the PO_Number is not the primary key? Should I be using the
primary key to double click?

Any suggestions will be greatly appreciated.

Thank you,
Amy

Dirtrhoads -

Depending on the form you are opening, you can pass this in as as a WHERE
condition, or as OpenArgs. If you use the WHERE clause, then you won't need
to code for it in the new form. If you use OpenArgs, you will need to
evaluate it (usually in the Open event of the form), and then apply it
appropriately. The WHERE condition is easier to use for just a filter.

--
Daryl S


"dirtrhoads" wrote:

Would I put this in OpenForm Arguments field of the Macro?

"Daryl S" wrote:

Dirtrhoads -

I suspect you need delimiters for your text value. Your end results would be:
[PO_Number]='PHX 0085453'
but you are probably passing this in, so it would be built something like
this (using your form and control names):
"[PO_Number]='" & forms!yourformname!yourPONumberControlName & "'"

--
Daryl S


"dirtrhoads" wrote:

I have a form which has subforms to show me my active orders and inventory
information. I want to be able to double click or just click the PO Number to
open that record in my Order Details form. I tried creating a macro by
mimicking the one in the Northwind 2007 database but when I do it in mine I
receive an error which states:

"Syntax Error (missing Operator) in query expression '[PO_Number]=PHX
0085453'"

Is this because the PO_Number is not the primary key? Should I be using the
primary key to double click?

Any suggestions will be greatly appreciated.

Thank you,
Amy

I'm sorry. I've never created a macro before, so I have no idea what any of
this means. Would it be easier to put code in the double click event? I tried
putting the statement you gave me in the WHERE clause of the macro and it
still didn't work. Are you saying that I also need to do something on the
FORM property sheet as well if I go with the Macro?

"Daryl S" wrote:

Dirtrhoads -

Depending on the form you are opening, you can pass this in as as a WHERE
condition, or as OpenArgs. If you use the WHERE clause, then you won't need
to code for it in the new form. If you use OpenArgs, you will need to
evaluate it (usually in the Open event of the form), and then apply it
appropriately. The WHERE condition is easier to use for just a filter.

--
Daryl S


"dirtrhoads" wrote:

Would I put this in OpenForm Arguments field of the Macro?

"Daryl S" wrote:

Dirtrhoads -

I suspect you need delimiters for your text value. Your end results would be:
[PO_Number]='PHX 0085453'
but you are probably passing this in, so it would be built something like
this (using your form and control names):
"[PO_Number]='" & forms!yourformname!yourPONumberControlName & "'"

--
Daryl S


"dirtrhoads" wrote:

I have a form which has subforms to show me my active orders and inventory
information. I want to be able to double click or just click the PO Number to
open that record in my Order Details form. I tried creating a macro by
mimicking the one in the Northwind 2007 database but when I do it in mine I
receive an error which states:

"Syntax Error (missing Operator) in query expression '[PO_Number]=PHX
0085453'"

Is this because the PO_Number is not the primary key? Should I be using the
primary key to double click?

Any suggestions will be greatly appreciated.

Thank you,
Amy

Amy:

I'd forget about a macro for this; use code in the Po Number control's
DblClick event procedu

Const FORMNAME = "Order Details"
Dim ctrl As Control
Dim strCriteria As String

On Error Goto Err_Handler

Set ctrl = Me.ActiveControl
strCriteria = "[Po Number] = """ & ctrl & """"

DoCmd.OpenForm FORMNAME, WhereCondition:=strCriteria

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

A few words of explanation:

1. The form name is assigned to a constant FORMNAME as the value is fixed,
so there is no need to use a variable.

2. As this code is in an event procedure which only executes if the control
in question is the active control you can refer to the control by declaring
an object variable, ctrl, and then setting this to the active control, which
is returned by the form's ActiveControl property.

3. The criterion to open the Order Details form is assigned to a variable
strCriteria as this will of course change depending on what the current PO
Number is. As the PO Number is a text data type it has to be wrapped in
quotes characters when building the expression for the criterion. To include
quotes characters in a string already delimited by quotes characters you use
a pair of contiguous quotes characters "" to represent each literal quotes
character.

4. When calling the OpenForm method, the WhereCondition argument is named
and followed by := to assign the value of the strCriteria variable to it.
This saves you having to remember where an argument appears in the argument
list and avoids extra commas having to be put in to allow for missing
arguments.

5. The error handling is a basic generic way of handling any unexpected
errors and exiting gracefully if an error occurs. Often error handling will
be more complex than this and will also handle anticipated specific errors.
It's always advisable to include error handling, however, even if only basic
stuff as above. This is one big advantage code has over macros as the latter
do not include error handling. Experienced developers will almost invariable
use code rather than macros, which, while cheap and cheerful, are vey limited.


I imagine that you are familiar with inserting code in a control's event
procedure, but if not this is how it's done:

1. Select the control and open its properties sheet if its not already open.

2. Select the relevant event property and select the 'build' button (the one
on the right with 3 dots).

3. Select Code Builder in the dialogue and click OK. This step won't be
necessary if you've set up Access to use event procedures by default.

4. The VBA editor window will open at the event procedure with the first and
last lines already in place. Enter or paste in the code as new lines between
these.

Ken Sheridan
Stafford, England

dirtrhoads wrote:
I'm sorry. I've never created a macro before, so I have no idea what any of
this means. Would it be easier to put code in the double click event? I tried
putting the statement you gave me in the WHERE clause of the macro and it
still didn't work. Are you saying that I also need to do something on the
FORM property sheet as well if I go with the Macro?

Dirtrhoads -

[quoted text clipped - 30 lines]
Thank you,
Amy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

Thank you Ken, I do in fact prefer code over Macro's but unfortunately I'm
not overly familiar with the SQL language. (Even less familiar with Macro's!!)

This is working perfectly. Have a wonderful day and thank you again.


"KenSheridan via AccessMonster.com" wrote:

Amy:

I'd forget about a macro for this; use code in the Po Number control's
DblClick event procedu

Const FORMNAME = "Order Details"
Dim ctrl As Control
Dim strCriteria As String

On Error Goto Err_Handler

Set ctrl = Me.ActiveControl
strCriteria = "[Po Number] = """ & ctrl & """"

DoCmd.OpenForm FORMNAME, WhereCondition:=strCriteria

Exit_He
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here

A few words of explanation:

1. The form name is assigned to a constant FORMNAME as the value is fixed,
so there is no need to use a variable.

2. As this code is in an event procedure which only executes if the control
in question is the active control you can refer to the control by declaring
an object variable, ctrl, and then setting this to the active control, which
is returned by the form's ActiveControl property.

3. The criterion to open the Order Details form is assigned to a variable
strCriteria as this will of course change depending on what the current PO
Number is. As the PO Number is a text data type it has to be wrapped in
quotes characters when building the expression for the criterion. To include
quotes characters in a string already delimited by quotes characters you use
a pair of contiguous quotes characters "" to represent each literal quotes
character.

4. When calling the OpenForm method, the WhereCondition argument is named
and followed by := to assign the value of the strCriteria variable to it.
This saves you having to remember where an argument appears in the argument
list and avoids extra commas having to be put in to allow for missing
arguments.

5. The error handling is a basic generic way of handling any unexpected
errors and exiting gracefully if an error occurs. Often error handling will
be more complex than this and will also handle anticipated specific errors.
It's always advisable to include error handling, however, even if only basic
stuff as above. This is one big advantage code has over macros as the latter
do not include error handling. Experienced developers will almost invariable
use code rather than macros, which, while cheap and cheerful, are vey limited.


I imagine that you are familiar with inserting code in a control's event
procedure, but if not this is how it's done:

1. Select the control and open its properties sheet if its not already open.

2. Select the relevant event property and select the 'build' button (the one
on the right with 3 dots).

3. Select Code Builder in the dialogue and click OK. This step won't be
necessary if you've set up Access to use event procedures by default.

4. The VBA editor window will open at the event procedure with the first and
last lines already in place. Enter or paste in the code as new lines between
these.

Ken Sheridan
Stafford, England

dirtrhoads wrote:
I'm sorry. I've never created a macro before, so I have no idea what any of
this means. Would it be easier to put code in the double click event? I tried
putting the statement you gave me in the WHERE clause of the macro and it
still didn't work. Are you saying that I also need to do something on the
FORM property sheet as well if I go with the Macro?

Dirtrhoads -

[quoted text clipped - 30 lines]
Thank you,
Amy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

.