docmd.openform where statement not working

[vircalendar via AccessMonster.com]

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

 

[BruceM via AccessMonster.com]

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.

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

 

[vircalendar via AccessMonster.com]

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:

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

 

[BruceM via AccessMonster.com]

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?

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]