Double click to open form
in [General]
|
From: KenSheridan via AccessMonster.com on 21 May 2010 13:43 Amy: I'd forget about a macro for this; use code in the Po Number control's DblClick event procedure: 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_Here: 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/Forums.aspx/access/201005/1
From: dirtrhoads on 24 May 2010 09:24
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 procedure: > > 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_Here: > 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/Forums.aspx/access/201005/1 > > . > |