On Not In List With Separate Form

[Guest]

I'm trying to offer the user the option to enter a new record from the
OnNotInList event. Sinc the source table has more than one field, I want to
present a popup form, let them enter the new record, and requery the combo
box on closing the form.

The code below passes NewData as the OpenArgs parameter of the OpenForm
method, but triggers the error, "You can't assign a value to this object." on
the line assigning the txtProjectName control.

Can anyone tell me what I'm doing wrong?

Thanks.

Sprinks

' Combo Box Code
Private Sub cboProjectNumber_NotInList(NewData As String, Response As Integer)
Dim intx As Integer
Response = acDataErrContinue
intx = MsgBox(NewData & " has not yet been added. " & _
"Add it now?", vbYesNo, "Project Not Found")
If intx = vbNo Then
Response = acDataErrDisplay
Else
DoCmd.OpenForm "Projects", acNormal, , , acFormAdd, acDialog, NewData
DoCmd.Save
Me!cboProjectNumber.Requery
End If
End Sub

' Popup form code
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(OpenArgs) Then
Me!txtProjectName = OpenArgs
Me!txtProjectNumber.SetFocus
End If
End Sub

 

[Guest]

OpenArgs is a property and needs to be qualified:
Me!txtProjectName = Me!OpenArgs
I would also check for 0 length.
If Len(Nz(Me.OpenArgs,"")) > 0 Then
Me!txtProjectName = Me!OpenArgs

 

[Roger Carlson]

It is an Order of Events problem. When assigning values to a control, you
have to do it in the OnLoad event. It might work to move your whole routine
to the OnLoad event, but I usually load the OpenArgs value into a form-level
variable in the OnOpen, then load the control with the value on the OnLoad
event.

Dim OpenArgVariable as String

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(OpenArgs) Then
OpenArgVariable = OpenArgs
End If
End Sub

Private Sub Form_Load()
Me!txtProjectName = OpenArgVariable
Me!txtProjectNumber.SetFocus

End Sub


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Roger,

Thank you; as usual your advice is on target, and the form loads correctly,
with the passed new data. However, I'm getting an "You must save the current
field before the Requery action" error message on the last line before the
End If. I thought the previous line took care of it. Can you identify the
problem?

Thanks. Sprinks

Private Sub cboProjectNumber_NotInList(NewData As String, Response As Integer)
Dim intx As Integer
Response = acDataErrContinue
intx = MsgBox(NewData & " has not yet been added. " & _
"Add it now?", vbYesNo, "Project Not Found")
If intx = vbNo Then
Response = acDataErrDisplay
Else
DoCmd.OpenForm "Projects", acNormal, , , acFormAdd, acDialog, NewData
DoCmd.Save
Me!cboProjectNumber.Requery
End If
End Sub

 

[Roger Carlson]

Try: Response = acDataErrAdded
instead of the DoCmd.Save. If you do that, I don't think you'll even need
the requery.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

That was it. Thanks, Roger.

Try: Response = acDataErrAdded
instead of the DoCmd.Save. If you do that, I don't think you'll even need
the requery.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L