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
0
Utf
5/19/2010 5:00:01 PM
access 16762 articles. 3 followers. Follow

6 Replies
1817 Views

Similar Articles

[PageSpeed] 55

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
0
Utf
5/19/2010 5:16:01 PM
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
0
Utf
5/19/2010 5:22:01 PM
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
0
Utf
5/19/2010 7:22:01 PM
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
0
Utf
5/21/2010 4:22:02 PM
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

0
KenSheridan
5/21/2010 5:43:54 PM
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
> 
> .
> 
0
Utf
5/24/2010 1:24:01 PM
Reply:

Similar Artilces:

Custom Outlook 2007 forms to sharepoint
Hello, Can someone please confirm and help with me trying to publish of send a custom outlook 2007 form that i have created to a sharepoint list of even a database? Can this be done and if so is it hard to achieve? Thank you so much in advance! ...

Form Header Control problem
I have a form based on a query that displays fields in the form header and data is entered in the detail section. When I use the record selector to go through the records the data in the detail area is updated but not the header section. I added the header section so that I could insert a tab control at the top of the detail section. Thanks in advance for any input. ...

Workaround for Opening Word 6 Files (other than File>Open)?
Hi all, I know in Word 2008 I can open my Word 6 files using File>Open. This works fine but annoys the heck out of me. MS has made a "security" choice here that is about *their* security from possible lawsuits, not *my* security from possible phishers. Sheesh, I am trying to look at my dissertation; no one has embedded dangerous code in it! So, my question is: Is there a workaround for this? Ideally there'd be a preference to turn off this "feature," but I'm sure there isn't one. Alternately, perhaps a way to batch change the document type on o...

Blank Form 04-25-07
Hello. I have a form with a combo box in it which has a list of part numbers. When the user enters a part number from the list and hits the "Execute" button it takes them to another form which has detailed info about that particular part number. HOWEVER, if the user enters a bogus part number, it takes them to a completely blank form. I would like to know how to make it so that instead of going to the form it simply says "Part Number Does Not Exist." Any help on this is greatly appreciated. Thanks :) You can set the LimitToList Property of the combo to Yes, tha...

How to remove dashes and slashes form a sequence of numbers & lett
Hi I have a sequence of numbers in column D and I require to extract just the numbers and letters to column E. D 190/0-01 31-0014 pp7/44-1 uf-744-5 E 190001 310014 pp7441 uf7445 Any pointers would be much appreciated. Kind Regards Celticshadow Put this in E1: =3DSUBSTITUTE(SUBSTITUTE(D1,"/",""),"-","") and copy down as required. Hope this helps. Pete On Oct 14, 11:34=A0am, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > > I have a sequence of numbers in column D and I require to extract just th= e > ...

Form background
Hi. I'm using VC++ 7.0 do develope my MFC dialog based application. Is there some way how to set background for my form? Just like property. Drawing in runtime is unwanted. There's no problem to set background in C# project, but in MFC project I din't find any way. And the second minor question: Does MFC prject in VS.NET support the same MFC libraries just like VS 6.0 or I should provide with my program newer version of MFC? Thanks. -- German Koninin --------------------------------------------------------------------- For every complex problem, there's solution that is si...

Customizing menu item names, form names etc.?
Hi all, Is there any way to customize the names of forms - for example if I want to call an "account" a "business partner" and modify it in the menu items and form/view names as well, can I do it? Thanks in advance, -- Anurag ...

csv file will not open
Hi, we are exporting (save as) report data to csv on a Novell network. when Excel 2003 sp1 attempts to open the csv file, we get a message that the file is read only. the file does not open. we can export the data to csv on the local computer and open it in excel just fine. we can open the csv file that has been created on the network drive using notepad, word and access. Your help is greatly appreciated. Scott ...

Shared User Issue When Opening Database
I have an issue this afternoon which, I feel probably isn't directly related to Access, but would be grateful for guidance if anyone has experienced this before. I have a database (default open mode - shared) which, this afternoon, won't allow a second user to open it. If a second user tries to open the database a message box is displayed quoting the path of the database file plus "The parameter is incorrect" The database is located on a shared folder on our network. Thanks for any assistance. Dave ONLY the data should be on a shared folder, not th...

Open new form based on two criteria
I have a main form that I need to be able to open another form that is based on two different fields on the main form. This is what I have: Private Sub cmdOpenForm_Click() On Error GoTo Err_cmdOpenForm_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "SFRM_AllLocationsperContract" stLinkCriteria = (((TBL_LocationContract.ContractNumber) = "&[Forms]! [FRM_CompanyAddContracts]![txtContractID]&") And ((TBL_LocationContract. CompanyID) = "&[Forms]![FRM_CompanyAddContracts]![txtCompanyID]&")) DoCmd...

problem Opening jpg's only
Recently, in Outlook 2003, I noticed that I can';t open and view jpg's anymore. Other attachements (mpg, pdf) are fine. I get the message: 'The system cannot find the file specified' I can save the file to disk and can view it easily just by clicking on it. It happens whether McAfee antivirus is enabled or disabled. I don't think it is level 1 attachments which are being blocked as in Microsoft Knowledge Base Article - 829982. Any ideas would be appreciated. Thanks Craig I am having the same issue. Any suggestions would be helpful! Thanks Jeremy >-----Original M...

unable to open password protected PST file...
I have password protected a private PST file. When I open Outlook 2002 and click on the PST file - it asks for my password. If I just click cancel there - I see no way to open that file again unless I quit Outlook and come back in. When I click on the PST file, I would have thought it would have asked for the password again, but it gives a different error and no where can you specify the password... Any ideas? Thanks, Brad Can you clarify why you are clicking on Cancel the first time Outlook prompts for your password? Normally, one would just enter the pw at the prompt and your'...

opening forwarded mail
I have looked through all my setting and still can't figure this one out. When someone forwards me a message that they received, i will sometimes get it as a small envelope (attachment). When I double click on it, nothing happens. When I save it to my desktop, it isn't recognized. But, when I open it with my webmail there isn't a problem. I can double click on pictures and they open fine, but not these types of email. Does anyone have anything they can assist with so I don't always have to go through my webmail? Thanks, Sam dreamer <safer@home.net> wrote: >...

Duplicate the Record in a Form and many SubForms
I am using Allen Browne's tip that is available in his web site. However, I get a compile error stating that the variable dbFailOnError is not defined. I have search the MS Knowdledgeable base and found out the DBEngine (0) (0).Execute is no longer used in current versions. I change it to the following but I still get the same compile error message. If Me.SF_Adjacencies.Form.RecordsetClone.RecordCount > 0 Then strSql = "INSERT INTO [Adjacencies] ( DataRmID, AdjID,_ AdjRoomName, AdjRelevance ) " & _ "SELECT " & lngID &...

Using a listbox selection to limit records in a form and report
I am trying to use a listbox of names to limit the results of a form to just those names. On a similar track, I want to use the selected names to limit a report. I have the listbox created and have added a button next to it to initiate the next step, but cannot figure out how to do it. Also, would like to (if possible) use multiple listboxs in the same way (ie, name and/or department). "dascooper" <dascooper@discussions.microsoft.com> wrote in message news:1227B7CC-3345-4FDC-8EB0-94BEF46660FD@microsoft.com... >I am trying to use a listbox of names to limit the resu...

summarize open issues
i have 3 different open issues lists on 3 tabs of a workbook that i want to summarize the priority items onto one list on another tab. i want to tick off items in priority order by a,b,c etc. and collect them in sorted priority order all on one tab. does anyone have any ideas how i can do this? i appreciate any help you can provide. thank you If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you...

mail does not find temporay directory to open attached .xls file
Since few weeks, I cannot open attached .xls file, for the temporary directory is not found. I bypass it by saving the xls file in a a folder and then open it from there. It does't work if saved on desktop. All other attached files, pps or world, are ok -- Are all your Windows Updates current? I seem to recall there was an update for IE8 a couple of months ago which fixed the failure to open attachments. --=20 Gary VanderMolen, Microsoft MVP (Mail) Microsoft MVP program: http://mvp.support.microsoft.com "escofie" <escofie@discussions.microsoft.com&...

Incompatible version Money 2004 error when opening with Money 2005
I installed Money 2005, download the update, then try to use my existing money 2004 file. I have also make my money 2004 repaired. And success with no error in data structure. But the money 2004 still failed to open with 2005. It said incompatible version with Money 2005. Why? I also have tried using salvage as suggested by microsoft help support, but still failed to open with money 2005. Can somebody help my problem? Thank you. You must stay within region when upgrading UK > UK and US > US is OK but you can't do UK > US. See http://umpmfaq.info/faqdb.php?q=1 -- Re...

Contact Forms
Hello Using OL2003 and I want to slightly change the "Contact" form to add another field. When I edit it, it has a different layout and the field for the contact's image is not there. How can I retain the default "contact" form and add the extra field. Am I missing something here. I have been trying to find info and do it for several hours!! Thanks Rob The new contact form layout in OL2003 and the included picture control = are not available to programmers, who will see the older layout when = they put an Outlook 2003 contact form into design mode. One work...

Print records in continuous form
I have a Continuous Form that based on a Query that selects record according to the Month. What I want to do is to be able to print all the records in this Form by one Command button. So I have the report designed and I have created a Macro “Open Report” +Print Out, but it opens only the first record to print. I did the Where condition on the Command Button On Click: [PM#]=[Forms]![FormName]![PM#]. But it’s not working how I want it to work. How I can print all the records? Thanks for the help. I have tried that and it opens the report with all the records from the original table, bu...

Writing data back to main form
I have a form that pops up when a certain value is selected in an Option Group on my main form. The user enters free from data in an unbound box on the popup form, then submits the "comment" through a background macro. The macro should write the "comment" back to the main form via a SetValue command, however, the macro is halted because it can't find the original form, which has remained open. Is SetValue the correct command to use in the macro? ...

Problem with date in user form
Hello, I create user form for database that contain dates in one column. But when I fill user form it copy the date in the database column like text and the formulas related to this cells dosen't work.The cells in the column are formated like dates. How to solve this problem? Ivo Ivo, Use DateValue to convert the text to a date, like: DataBaseCell.Value = DateValue(me.TextBox1.Text) HTH, Bernie MS Excel MVP "Ivo_69" <Ivo_69@discussions.microsoft.com> wrote in message news:DA7115AD-3107-4151-AF21-1089D3C30AF3@microsoft.com... > Hello, > I create user form f...

Opening an Excel template from a 3rd party app
I am running a 3rd party app & have have created a menu item in this app which opens a Excel template. The only problem that it opens the Excel template and not a xls file based on the template & this causes sharing problems as multple users use this spreadsheet. Is there a switch or different command in the command line that I have to put in to open as an xls file? The command line I have is: Excel.Sheet.8, [Open("F:\Excel Forms\Application Form.xlt")] Using Excel 2002 TIA -- Tony Barb. I tried "New" in the command line & it worked!. Many thanks ...

can't open microsoft outlook on vista machine
I loaded outlook express on my machine, newlaptop with vista and I can't open it. Any suggestions? "pagegirl26" <pagegirl26@discussions.microsoft.com> wrote in message news:6CDD0072-7CFB-4F6B-ABEF-91F74BE25BFE@microsoft.com... >I loaded outlook express on my machine, newlaptop with vista and I can't >open > it. Any suggestions? You can load Outlook Express on a Vista machine. -- Brian Tillman [MVP-Outlook] On Tue, 21 Oct 2008 13:27:01 -0700, pagegirl26 <pagegirl26@discussions.microsoft.com> wrote: > I loaded outlook express on my machine, ne...

Crm3 to Crm4: error on page when opening/closing contacts
OK, another similar to a lot of people, but I do not have a solution. I am presently testing in lab upgrading from CMR 3 to 4 and finds myself with a strange one. Anytime I open a contact, and this happens only with contacts, I get an "error on page" at the bottom of the browser window and once I close it, there is a "pop-up" to send information to Microsoft for the error which contains the following; 1.0 Expected ')' 75 /Orthofab/sfa/conts/edit.aspx /Orthofab/sfa/conts/ edit.aspx Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; FunWebProducts; SLCC1; .NET CLR 2....