docmd.openform where statement not working

I'm going crazy.  I've been messing with this all day and can't see what must
be a simple problem.

A form contains a field called order_attending.  It populates doctors' names
from a dropdown list or free text.  If the submitted free text is not on the
list, this initiates a "not in list event,"  which takes the user to a form
called frm_newdoc on which a new doctor name, pager number, etc, can be
entered. On the other hand, if the operator is happy with the text in the
field but wants to see pager numbers, etc, he or she can double-click on the
field and go to the secondary form.  Problem is that the secondary form opens
blank.  The "where" data is not being transferred. Why not?

Here's the code:

On the primary form:

Private Sub Order_attending_DblClick(Cancel As Integer)
Forms!exam_history.TimerInterval = 0 'pause timer on background form
DoCmd.OpenForm "frm_newdoc", , , "order_attending = forms![exam_history]!
[order_attending]", , acDialog, "old"
Forms![exam_history].TimerInterval = 1000 'restart timer"
End Sub

Private Sub Order_attending_NotInList(NewData As String, Response As Integer)
Forms!exam_history.TimerInterval = 0 'pause timer on background form
   Dim intResponse As Integer
   strMsg = NewData & " is not a known Referring Attending. Add him or her?"
   intResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Unknown Referrer")
    Select Case intResponse
     Case vbYes
        DoCmd.OpenForm "frm_NewDoc", acNormal, , , acFormAdd, acDialog, "new"
& NewData
     Case vbNo
        Response = acDataErrContinue
     End Select
Forms![exam_history].TimerInterval = 1000 'restart timer
End Sub

On the secondary form:

Private Sub Form_open(Cancel As Integer)
dim strNewDoc as string
If OpenArgs = "old" Then 'doc is known.  Just show his record
Else 'doc is new. start data collection
    DoCmd.GoToRecord acDataForm, "frm_newdoc", acNewRec
    strNewDoc = Right(OpenArgs, Len(OpenArgs) - 3)
    If strNewDoc Like "*" & "," & "*" Then
        Me.ref_lastname = Left(strNewDoc, (InStr(strNewDoc, ",") - 1))
        Me.Ref_firstname = LTrim(Right(strNewDoc, (Len(strNewDoc) - InStr
(strNewDoc, ","))))
    ElseIf strNewDoc Like "*" & " " & "*" Then
        Me.Ref_firstname = Left(strNewDoc, (InStr(strNewDoc, " ") - 1))
        Me.ref_lastname = LTrim(Right(strNewDoc, (Len(strNewDoc) - InStr
(strNewDoc, " "))))
    Else
        Me.ref_lastname = strNewDoc
    End If
End If
Me.Ref_firstname.SetFocus
End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

0
vircalendar
1/4/2010 5:05:03 AM
access.formscoding 7495 articles. 0 followers. Follow

3 Replies
1543 Views

Similar Articles

[PageSpeed] 39

Access does not recognize the Where condition.  If order_attending is a
number:

DoCmd.OpenForm "frm_newdoc", , ,  _
   "order_attending = " & Forms![exam_history]![order_attending],  _
   ,acDialog, "old"

If it is text:
DoCmd.OpenForm "frm_newdoc", , ,  _
   "order_attending = " " " & Forms![exam_history]![order_attending] & " " "
",  _
   ,acDialog, "old"

You could also do this for text if you are sure there will be no apostrophes
in order_attending:
DoCmd.OpenForm "frm_newdoc", , ,  _
   "order_attending = ' " & Forms![exam_history]![order_attending] & " ' ",
_
   ,acDialog, "old"

In both text examples the spaces between the quotes are added here for
clarity.  The VBA editor should get rid of them.

For the secondary form frm_newdoc, the Open event is too soon to do anything
with the records, as they have not yet been loaded.  Load is where such code
would generally go, but I'm not sure you need to do anything in the Load
event.  Also, I'm not sure OpenArgs is needed, at least not for "old" or
"new".  If you open the form by double clicking, the Where condition is
applied, and you view a filtered recordset.  If you open it from the
NotInList event there is no Where condition, and the form opens as specified
in NotInList. You could pass NewData in OpenArgs, as you seem to have done to
populate the FirstName and LastName fields, but there is no need to pass
"new", then remove it from the string.  I would be careful about assuming
users will enter names in the correct format, with a comma between LastName
and FirstName (if I am reading correctly).  Perhaps you could display a
message box after parsing FirstName and LastName, displaying the result
before you add it to the fields.  You may want to add a command button so
that users can go directly to frm_NewDoc if they are sure before they begin
that the user is not in the list.  Also, perhaps you would want a Me.FilterOn
= False command button on frm_NewDoc in case users who open to a filtered
recordset want to look at or add other names while the form is open.

For clarity in future posts, I suggest distinguishing between fields and
controls.  It is possible to figure out from context what you are trying to
do, but that will not always be the case.  A field is where a specific piece
of information such as FirstName is stored in a table (or in a query a field
may contain  a calculated value).  A control is a text box, combo box, or
just about anything on a form.  A control may be bound to a field, but it is
not the same as the field.

vircalendar wrote:
>I'm going crazy.  I've been messing with this all day and can't see what must
>be a simple problem.
>
>A form contains a field called order_attending.  It populates doctors' names
>from a dropdown list or free text.  If the submitted free text is not on the
>list, this initiates a "not in list event,"  which takes the user to a form
>called frm_newdoc on which a new doctor name, pager number, etc, can be
>entered. On the other hand, if the operator is happy with the text in the
>field but wants to see pager numbers, etc, he or she can double-click on the
>field and go to the secondary form.  Problem is that the secondary form opens
>blank.  The "where" data is not being transferred. Why not?
>
>Here's the code:
>
>On the primary form:
>
>Private Sub Order_attending_DblClick(Cancel As Integer)
>Forms!exam_history.TimerInterval = 0 'pause timer on background form
>DoCmd.OpenForm "frm_newdoc", , , "order_attending = forms![exam_history]!
>[order_attending]", , acDialog, "old"
>Forms![exam_history].TimerInterval = 1000 'restart timer"
>End Sub
>
>Private Sub Order_attending_NotInList(NewData As String, Response As Integer)
>Forms!exam_history.TimerInterval = 0 'pause timer on background form
>   Dim intResponse As Integer
>   strMsg = NewData & " is not a known Referring Attending. Add him or her?"
>   intResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Unknown Referrer")
>    Select Case intResponse
>     Case vbYes
>        DoCmd.OpenForm "frm_NewDoc", acNormal, , , acFormAdd, acDialog, "new"
>& NewData
>     Case vbNo
>        Response = acDataErrContinue
>     End Select
>Forms![exam_history].TimerInterval = 1000 'restart timer
>End Sub
>
>On the secondary form:
>
>Private Sub Form_open(Cancel As Integer)
>dim strNewDoc as string
>If OpenArgs = "old" Then 'doc is known.  Just show his record
>Else 'doc is new. start data collection
>    DoCmd.GoToRecord acDataForm, "frm_newdoc", acNewRec
>    strNewDoc = Right(OpenArgs, Len(OpenArgs) - 3)
>    If strNewDoc Like "*" & "," & "*" Then
>        Me.ref_lastname = Left(strNewDoc, (InStr(strNewDoc, ",") - 1))
>        Me.Ref_firstname = LTrim(Right(strNewDoc, (Len(strNewDoc) - InStr
>(strNewDoc, ","))))
>    ElseIf strNewDoc Like "*" & " " & "*" Then
>        Me.Ref_firstname = Left(strNewDoc, (InStr(strNewDoc, " ") - 1))
>        Me.ref_lastname = LTrim(Right(strNewDoc, (Len(strNewDoc) - InStr
>(strNewDoc, " "))))
>    Else
>        Me.ref_lastname = strNewDoc
>    End If
>End If
>Me.Ref_firstname.SetFocus
>End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1

0
BruceM
1/4/2010 1:00:02 PM
Thanks for your comprehensive answer.  Unfortunately, none of the suggestions
works.  I can't seem to get the where expression to work with any of them.
Not sure why, but I'll keep looking.  I'm sure there's a simple answer--some
misplace comma or something.

BruceM wrote:
>Access does not recognize the Where condition.  If order_attending is a
>number:
>
>DoCmd.OpenForm "frm_newdoc", , ,  _
>   "order_attending = " & Forms![exam_history]![order_attending],  _
>   ,acDialog, "old"
>
>If it is text:
>DoCmd.OpenForm "frm_newdoc", , ,  _
>   "order_attending = " " " & Forms![exam_history]![order_attending] & " " "
>",  _
>   ,acDialog, "old"
>
>You could also do this for text if you are sure there will be no apostrophes
>in order_attending:
>DoCmd.OpenForm "frm_newdoc", , ,  _
>   "order_attending = ' " & Forms![exam_history]![order_attending] & " ' ",
>_
>   ,acDialog, "old"
>
>In both text examples the spaces between the quotes are added here for
>clarity.  The VBA editor should get rid of them.
>
>For the secondary form frm_newdoc, the Open event is too soon to do anything
>with the records, as they have not yet been loaded.  Load is where such code
>would generally go, but I'm not sure you need to do anything in the Load
>event.  Also, I'm not sure OpenArgs is needed, at least not for "old" or
>"new".  If you open the form by double clicking, the Where condition is
>applied, and you view a filtered recordset.  If you open it from the
>NotInList event there is no Where condition, and the form opens as specified
>in NotInList. You could pass NewData in OpenArgs, as you seem to have done to
>populate the FirstName and LastName fields, but there is no need to pass
>"new", then remove it from the string.  I would be careful about assuming
>users will enter names in the correct format, with a comma between LastName
>and FirstName (if I am reading correctly).  Perhaps you could display a
>message box after parsing FirstName and LastName, displaying the result
>before you add it to the fields.  You may want to add a command button so
>that users can go directly to frm_NewDoc if they are sure before they begin
>that the user is not in the list.  Also, perhaps you would want a Me.FilterOn
>= False command button on frm_NewDoc in case users who open to a filtered
>recordset want to look at or add other names while the form is open.
>
>For clarity in future posts, I suggest distinguishing between fields and
>controls.  It is possible to figure out from context what you are trying to
>do, but that will not always be the case.  A field is where a specific piece
>of information such as FirstName is stored in a table (or in a query a field
>may contain  a calculated value).  A control is a text box, combo box, or
>just about anything on a form.  A control may be bound to a field, but it is
>not the same as the field.
>
>>I'm going crazy.  I've been messing with this all day and can't see what must
>>be a simple problem.
>[quoted text clipped - 56 lines]
>>Me.Ref_firstname.SetFocus
>>End Sub

-- 
Message posted via http://www.accessmonster.com

0
vircalendar
1/5/2010 3:37:58 AM
Of all the things I wrote, absolutely none of them have any benefit whatever?
Frankly, I find that hard to believe.  One thing that will not work for sure
is trying to work with a form's data before it has been loaded.  The Open
event is too soon.  Did you try the Load event?

"Does not work" is vague.  Are you getting a compile error?  Run-time error?
Can you open the form without adding the Where condition, OpenArgs, and so
forth?  If so, next try adding a hard-coded Where value, as a test.  If that
works, try the Forms![exam_history]![order_attending] syntax for the Where
condition, with quotes appropriate to the data type. By the way, the form
"exam_history" needs to be open.  I don't recall if it was.

For another thing, try adding a line of code for testing purposes, either:

MsgBox Forms![exam_history]![order_attending]

or

Debug.Print Forms![exam_history]![order_attending]

Place it early in the code, to be sure you are getting the correct value (or
any value at all).

Have you tried stepping through the code?

vircalendar wrote:
>Thanks for your comprehensive answer.  Unfortunately, none of the suggestions
>works.  I can't seem to get the where expression to work with any of them.
>Not sure why, but I'll keep looking.  I'm sure there's a simple answer--some
>misplace comma or something.
>
>>Access does not recognize the Where condition.  If order_attending is a
>>number:
>[quoted text clipped - 51 lines]
>>>Me.Ref_firstname.SetFocus
>>>End Sub

-- 
Message posted via http://www.accessmonster.com

0
BruceM
1/5/2010 12:43:04 PM
Reply:

Similar Artilces:

How convert Docmd.Domenuitem
I'm converting a 97 db to 2000. A form has a button that executes a Docmd.Domenuitem command. This is supposed to invoke Find and Replace. It does, but the subject is grayed out. I need the find to be based on the control where the cursor was when the button was pressed. Any ideas? Access help in 2000 does not even mention this command neither does my Access book. Maybe you need to do Screen.PreviousControl.SetFocus DoCmd.RunCommand acCmdFind or DoCmd.FindRecord On Thu, 29 Sep 2005 13:43:03 -0700, "mscertified" <rupert@tigerlily.com> wrote: >I'm converti...

Working With Two Computers & Outlook
I am planning to have a desktop computer at home, and carry a laptop with me while I work. Is there an easy way to bring the laptop home at night and sync it to the desktop and vice versa. I presently have Outlook 2000 but am thinking about purchasing the Outlook 2003 upgrade. Sky <anonymous@discussions.microsoft.com> wrote: > I am planning to have a desktop computer at home, and > carry a laptop with me while I work. Is there an easy way > to bring the laptop home at night and sync it to the > desktop and vice versa. I presently have Outlook 2000 but > am think...

Using existing fields in an IIf statement
I am trying to create a text field which displays an existing field is another field is null. The expression I have used is: =IIf([Home_Address]="",[Manager],Null). Unfortunately this doesn't work in that nothing is ever displayed, even if there is nothing in the [Home_Address] field. If I replace the [Home_Address] field with some text (like "No Data") then it works. Help would be appreciated. -- Keith W Try: =IIf([Home_Address] Is Null,[Manager], Null) Null is not the same thing as "" (a zero-length string.) For more info, see: Common erro...

Logical Statement on a SwitchBoard Menu.
Is it possible to use a logical statement before open an switchboard menu. Example: Check if user is authorized to access that switchboard menu. Thank Ileana Yes but you would have to do that as part of the switchboard form not any part of the switchboard itself. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "iholder" <iholder@discussions.microsoft.com> wrote in message news:3FD4B318-845E-481E-B719-20D00B7190CE@microsoft.com... > Is it possible to use a logical statement before open an switc...

Working with street addresses?
How do I split the numbers from the text in a street address? Data>text to columns, fixed delimiter double click thos parsing indicators that you don't need -- Regards, Peo Sjoblom "Sully" <Sully@discussions.microsoft.com> wrote in message news:DE61D2B6-B114-41F7-AD43-3E062EEF6F42@microsoft.com... > How do I split the numbers from the text in a street address? ...

downloading statements
I have 2 checking accounts with the same credit union and when I download the history from each account it automatically goes to one of the accounts and ignores the other. How do I get Money to prompt me as to which checking account to download to? Thank you, Beth ...

Payment Calculator not working correctly
Recently I tried to program an Excel spreadsheet to calculate a mortgage Payment using the =PMT(Rate/12,Term,Loan Amount)*-1 formula that I have used on many other spreadsheets. These older spreadsheets all work properly, but they were all written on another computer and imported into the one I am now using. The formula “=PMT(10/12, 360,100000)*-1” should give me a payment of $877.57, but it gives me $83,333.33 instead! Is their a flaw in the newer Excel versions, or a patch I don’t have? Any help would be appreciated. -- SwimBob No doubt you meant =PMT(0.1/12,360,100000)*-1 (10% ...

If & Find Statement
I got the problem which is cell A1 = maybe contain text -> apple, orange, pears, bubbles I just only can set up a formula with this =IF(FIND("Apple",A1)>0,"apple" but I cannot add another condition into this, actually I want it ca find orange, pears & bubbles.... Anyone can help me????? Pls pls pl ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way =IF(ISNUMBER(FIND("apple",A1)),"apple",IF(ISNUMBER(FIND("oran...

Is it true about DoCmd.SendObject
That DoCmd.SendObject will find your default Mail client? -- Thanks in advance for any help with this......Bob MS Access 2007 accdb Windows XP Home Edition Ver 5.1 Service Pack 3 "Bob Vance" <rjvance@ihug.co.nz> wrote in message news:u91TwlG3KHA.4964@TK2MSFTNGP05.phx.gbl... > That DoCmd.SendObject will find your default Mail client? > > -- > Thanks in advance for any help with this......Bob > MS Access 2007 accdb > Windows XP Home Edition Ver 5.1 Service Pack 3 > > It's true if your default Mail client is Outlook :~) ...

Payee and Memo swiched on downloaded statements
This really ticks me off. I thought my old credit union was just stupid because they would put something like "ATM Ck Card Withdraw" as the payee on every single transaction, and then fill the name of the actual merchant in the "memo" field. This always ticked me off because Microsoft Money tries to "recognize" that I changed one transaction to a different payee so it changes them all and I end up with crap like Wal-Mart being filled in as the name of every gas station in town. So, I switched credit unions, and guess what? This new credit union does the exact sam...

My Save As menu is not working
Save As under the file menu in Excel is "grayed out" so I cannot select it. How can I fix this problem Hi, right-click the menubar select Customise from list >> Toolbars click Standard click Reset Button ok - Mark >-----Original Message----- >Save As under the file menu in Excel is "grayed out" so I cannot select it. >How can I fix this problem >. > ...

Help with "Form DoCmd.Open and DoCmd.Close
The following two lines of code is not operating correctly. Private Sub cmdChangeVendor1_Click() DoCmd.Close acForm, "Invoice by Market Reviewer" DoCmd.OpenForm "Selections Vendor and Reviewer", acNormal End Sub When I click on the cmdChangeVendor1 button, I expect the form "Invoice by Market Reviewer" to close and the form "Selections Vendor and Reviewer" to open. But that is not the result I get. The form "Invoice by Market Reviewer" closes but that is about it. Nothing else opens. I have two forms that behaves this way. I have ...

download statement problem
after I installed XP sp2, I can no longer download the ofx file. I would see the save file window flash by but it won't stay open. does anyone know what internet setting that might have been changed and is causing this problem? Thanks! >-----Original Message----- >after I installed XP sp2, I can no longer download the >ofx file. I would see the save file window flash by but >it won't stay open. does anyone know what internet >setting that might have been changed and is causing this >problem? > >Thanks! >. >Clear your temporary internet files...

Change statement date
On a 401K account in Money 2002, I entered the wrong date (April 2005). This is giving me a problem now that I am trying to enter my July statement (earlier than last statement). How do I change the statement date to April 2004 so that I can enter my July statement without it complaining? It doesn't really matter what the previous date is -- when you begin the reconciliation this time, enter the April date and proceed. Money may comment, but don't worry, the reconciliation will be successful. -- "Rick Smith" <rsmith@revivant.com> wrote in message news:17...

IF Statement Problem
I am having trouble with Excel not returning a blank in an IF statement. My spreadsheet has three tabs. The first tab is a list of tracking numbers that need checked (12 digits). The second tab is a list of tracking numbers (16 digits) that are scanned into the spreadsheet in column A as we receive packages. In column B rows 2 through 600 is the formula =left(a2,12) - adjusted for each row. The third tab reconciles the two lists and has the following formula: Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2) Column B - =IF(A2=""...

MONEY Works for Canadians???
I am trying to set up MONEY to access my bank but it is not listed in the online services database. Can this be updated or is there another way to set up the account? I think the only way is if it's not listed to login to your bank's web site and download your statements manually each time you want to update. It's crazy how most banks aren't connected -- even E*TRADE BANK -- which is an online only bank. Don't tell Money you are Canadian though, it will give you errors. ;) -Max "C. Ahrensback" <cahrensback@shaw.ca> wrote in message news:upe7WfF...

Sharebuilder statement downloads
Is anyone else having problems with the Sharebuilder account download? It always wants to change my share totals, even though the account is not active now. I called sharebuilder and they were clueless. Thanks ...

When I close a form with DoCmd.Close I get a parameter popup
This is a repost. I will try to explain the problem a little better than I did the last time. When I execute DoCmd.Close on one of my forms I get one of those little unknown parameter popups, the kind you get when there's something wrong with your query. But all I'm trying to do is close the form, not read any data. This popup appears to be gernerated by the row source query in a list box on the form. This row source query references another text box on the form and this appears to be what the popup is asking for. But there's a value in this text box. And why should th...

Workflow Accelerator Not Working
I installed the first part of the CRM Workflow Accelerator, the Marketing Process. When I added my first test lead, the Workflow Monitor shows the Lead rule running but the log shows an error and the process is paused. The error in the log is: "<description>The specified attribute does not exist on this entity</description><details>The attribute 'CFIMarketingStage' is not an attribute of the entity 'Lead'</details><file>D:\CRM\Core\src\platform\include\OMCommon\CRMPropertyBagUtil.inl</file><line>43 The D: drive is my CD ROM on the...

convert from works to access
How do convert database files in Microsoft Works to database files in Access 2003? Hello, Check out following kb article: HOW TO: Import a Works Database into Microsoft Access 2002 WGID:147 ID: 327147.KB.EN-US http://support.microsoft.com/default.aspx?scid=kb;EN-US;327147 Regards, Ki Yi Microsoft Support This posting is provided AS IS with no warranties, and confers no rights. ...

NESTED IF STATEMENTS #2
Having a little problem in an excel class, hoping someone might be able to help. I'm trying to fill in the discount column on a spreadsheet with the following info. The problem states use a nested form of the IF function to calculate 15% if Amount is greater that $300., 10% if Amount is greater than $100., and 0% if Amount is less than $100. The Amount column is cell D5. Anyone know how to write the formula and explain how to go about it? Any help would be much appreciated. Let's see. This is homework so you should come up with the answer yourself. Did you start by looking in the he...

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS
I have an insert trigger that works just fine when I insert one row When I perfrom a bulk insert, the trigger only seems to work on the last row in the input file. What should the trigger be doing to process each row of the inserted data? Cheers Bob What's your trigger code look like? -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "thing" <someone@microsoft.com> wrote in message news:O4c4cSGeKHA.3792@TK2MSFTNGP02.phx.gbl... >I ha...

docmd.runcmd SQL functions
I have a table with fields a,b,c,d. If I have a row (x) where x.d is blank, I want to search the table and see if there is a row (y) such that x.c=y.c and x.b=y.b and if y.d is not blank, then I want x.d to be filled in with y.d. I can't seem to figure out how to start this. Thanks Carl Frankly, you don't want to do this. You have an un-normalized data model and you are storing redundant data. If b and c determine d, then that should be stored *once* a record in its own table. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want ans...

Pasted formula doesn't work unless "re-initialized"
I'll apologize in advance if this has already been answered but I couldn't come up with a search phrase to find an answer. I have an existing worksheet which I have been using successfully for all of 2006 but now I want to add rows for 2007. Whether I copy and paste, or drag a formula down from the last existing row, the new cells display the value that was in the original cell rather than the new value that the formula in the new cell should rerturn. When I click on the cell to display the formula in the formula bar, the formula looks fine but if I select any portion of the ...

Forwarding rule not working
Hello! I have Outlook 2003 connecting to an SBS 2003 server. I have an Outlook rule (not client-only) set up to forward one particular email to my pager (mail from Vonage telling me I have a voice mail) and then move the mail to a "Vonage Voice Mail Alerts" folder I added under my Inbox. If I run the rule manually, the message gets moved, but I never get a message on my pager (tried it to my cell phone and it failed as well). If I manually forward one of the voice mail messages, it makes it to my pager and/or cell phone. What is the difference between manually forwarding it ...