NotInList procedure--missing a detail

[Gary Schuldt]

I'm using the LimitToList and NotInList technique for the first time and
basically have the Event Procedure working.

I want to add more than one field to the new record, so I'm bringing up the
frmPlantSource in acAdd & acDialog mode to do that, and that seems to work.

However . . . I can't figure out a good way to get the NewData into the
appropriate field in the frmPlantSource. I mean, the user has already typed
it once, so they shouldn't have to do it again!

I thought of pushing the value in, but I can't push it until the frm is
open; but once opened, my code is suspended (because of acDialog), and I
don't get control back until its closed.

I could pull it in OnOpen, but then I'd have to test to make sure the form I
want to pull the cbx value from is actually open. That doesn't seem like
good design.

What's the best way to handle this?

I'll append the code. (I commented out a line of code I was using before I
changed the OpenForm to Dialog mode. I think in that version the
acDataErrAdded caused Access to requery the combo box before the record was
actually added by the popup form.)

Thanks

Gary
====================
Private Sub Plant_Source_ID_NotInList(NewData As String, Response As
Integer)

' Precondition: User keyed in a Plant Source name not in list (in
NewData)
' Action: Ask if wants to enter new one
' If so, bring up form in Add mode and push NewData into it
' If not, do nothing.

Dim sMsg As String
Dim intReply As Integer

Response = acDataErrContinue
If Not IsNull(NewData) _
Then
sMsg = "Do you wish to add a new Plant Source " & NewData & "?"
intReply = MsgBox(sMsg, vbYesNo + vbQuestion, "Add New Value")

If intReply = vbYes _
Then
DoCmd.OpenForm "frmPlantSource", , , , acAdd, acDialog
' Push in value (which user may override / modify)
' Forms!frmPlantSource!psName = NewData
' Tell Access to requery the combo box
Response = acDataErrAdded
' Else vbNo, do Nothing and exit
End If
Else 'NewData is null
MsgBox "Plant Source cannot be blank."
End If

End Sub
================

 

[Dirk Goldgar]

I'm using the LimitToList and NotInList technique for the first time
and basically have the Event Procedure working.

I want to add more than one field to the new record, so I'm bringing
up the frmPlantSource in acAdd & acDialog mode to do that, and that
seems to work.

However . . . I can't figure out a good way to get the NewData into
the appropriate field in the frmPlantSource. I mean, the user has
already typed it once, so they shouldn't have to do it again!

I thought of pushing the value in, but I can't push it until the frm
is open; but once opened, my code is suspended (because of acDialog),
and I don't get control back until its closed.

I could pull it in OnOpen, but then I'd have to test to make sure the
form I want to pull the cbx value from is actually open. That
doesn't seem like good design.

What's the best way to handle this?

I'll append the code. (I commented out a line of code I was using
before I changed the OpenForm to Dialog mode. I think in that
version the acDataErrAdded caused Access to requery the combo box
before the record was actually added by the popup form.)

Thanks

Gary
====================
Private Sub Plant_Source_ID_NotInList(NewData As String, Response As
Integer)

' Precondition: User keyed in a Plant Source name not in list (in
NewData)
' Action: Ask if wants to enter new one
' If so, bring up form in Add mode and push NewData into it
' If not, do nothing.

Dim sMsg As String
Dim intReply As Integer

Response = acDataErrContinue
If Not IsNull(NewData) _
Then
sMsg = "Do you wish to add a new Plant Source " & NewData & "?"
intReply = MsgBox(sMsg, vbYesNo + vbQuestion, "Add New Value")

If intReply = vbYes _
Then
DoCmd.OpenForm "frmPlantSource", , , , acAdd, acDialog
' Push in value (which user may override / modify)
' Forms!frmPlantSource!psName = NewData
' Tell Access to requery the combo box
Response = acDataErrAdded
' Else vbNo, do Nothing and exit
End If
Else 'NewData is null
MsgBox "Plant Source cannot be blank."
End If

End Sub
================

How about passing the new data to the form via the OpenArgs argument,
then having code in the form to check that argument and add the record?
So your NotInList proc might say something like:

DoCmd.OpenForm "frmPlantSource", , , , _
acAdd, acDialog, "ADD=" & NewData

And your form could have an event procedure for the Load event like
this:

'----- start of example code -----
Private Sub Form_Load()

Dim strArgs As String

strArgs = Me.OpenArgs & vbNullString

If strArgs Like "ADD=*" Then

If Not Me.DataEntry Then
RunCommand acCmdRecordsGoToNew
End If

Me.psName = Mid(strArgs, 5)

End If

End Sub
'----- end of example code -----

 

[Gary Schuldt]

Well! Now if THAT isn't *really kewl*!

(I wonder how many millenia of VBA coding experience I'd have to have had to
come up with that?!)

I read it over and can even imagine how it might work. And it looks like a
good reusable technique, since the frmPlantSource doesn't have to know "who"
called it. I'll give it a try.

Thanks, Dirk!

Gary

 

[Gary Schuldt]

I read Help about OpenArgs while dinner is on the stove.

Why is it plural? It looks like there's just an option for one strArgument.
If I want to pass multiple parameter values, do I then have to parse the
OpenArgs string myself in the opened-form code?

Gary

 

[fredg]

I read Help about OpenArgs while dinner is on the stove.

Why is it plural? It looks like there's just an option for one strArgument.
If I want to pass multiple parameter values, do I then have to parse the
OpenArgs string myself in the opened-form code?

Gary

Yup. Just use a separator within the string argument.
DoCmd.OpenForm "frmName", , , , , , "Arg1,Arg2,etc."

Then in the newly opened form use Left(),Mid() and Instr() in the Load
event to find the comma delimiter(s), and parse everything to the
left, between and to the right, as needed.
You can also use the Split() function if you have a newer version of
Access.

 

[Gary Schuldt]

I have the Split function, according to VBA Help, so it looks like I'm in
good shape.

Thanks, Fred

Gary