docmd.openform where statement not working

  • Follow


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
Reply vircalendar 1/4/2010 5:05:03 AM

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
Reply 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
Reply 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
Reply BruceM 1/5/2010 12:43:04 PM

3 Replies
1088 Views

(page loaded in 0.018 seconds)


Reply: