NotinList Event

[Guest]

This has got to be a very common problem but i have not found an answer
anywhere?!

When this event occurs I want to pop up a form that allows the user to add a
record to the table. But a new record requires data in 3 fields not just the
one in the combo box. I have a form that does this but the NotInList event
does not allow just opening a form.

How do i get this event to just open the form?

 

[Douglas J Steele]

You need to open the form as modal, and then have the original NotInList
event set Response to acDataErrAdded once control is passed back to it.

 

[Joshua A. Booker]

Sailormike,

Open the form using the acdialog mode like in this code. This will make the
code wait until the user closes the form to continue. You can also pass the
combo entry using the openargs.

Private Sub Combo_NotInList(NewData As String, Response As Integer)
If vbYes = MsgBox("Not In List." & vbCrLf & "Add To List?", vbYesNo +
vbQuestion, "Not In List") Then
DoCmd.OpenForm "puf_AddRecord", , , , , acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub

Then, in the form that opens, you can set the value to the openargs value
like this:

Private Sub Form_Open(Cancel As Integer)
If Not 0 = len(Me.OpenArgs) then
Me!Combo = Me.OpenArgs
End If
End Sub

HTH,
Josh

 

[Guest]

1st thank u both for your quick response and for your suggestions.

But perhaps I didn't make myself clear. I want this event to open the pop up
form (which is modal, btw). NOTHING else. No msgbox, no choices offered, no
response from the user - just open the form. These two tables are related 1
to ~ enforced. The user can select an existing location. add a location or
they can cancel adding the lot. These are vacant lots for sale, they all have
some location. If they do add a location then it has 3 parts, city, county,
state.

Mr. Booker, I have played around with the code u suggested and while I did
get it to work it does offer this "yes,No" option. I have tried to work
around that IF then Else statement but no go.

I do not understand ur code for the PopUp. Is it to set a field on the PopUp
to the value in the combobox? If so, i would rather not do that. Better the
user has to think about, and perhaps verify, the location data they r adding.

Again thank u both and if u have any further suggestions I would welcome
them. This has been a headache for several weeks now and would like to get it
solved.

 

[Joshua A. Booker]

Sailormike,

You can remove the msgbox like this:

Private Sub Combo_NotInList(NewData As String, Response As Integer)

DoCmd.OpenForm "puf_AddRecord", , , , , acDialog
Response = acDataErrAdded

End Sub

I assumed you wanted to have the pop-up add a record to the combo list and
suppress the not-in-list messages. To do this you must set the response var
depending on whether the users adds the record or not. If they change the
value of the entry then you will have problems and might even get stuck in a
recursive not-in-list trap. (CRTL+Break to stop it...just in case) That's
why it helps to fill the value into the pop-up. Not to mension it avoids
double entry since they alleady put it in the combo.

There are ways around this too, but now I understand you want to do NOTHING
else.

HTH,
Josh

 

[Guest]

Thank u, I did get it to work. The lines u suggested were the 1st thing i
tried and couldn't get it to work I did find my error and ur suggestion does,
indeed, do what I need.

The question of double entry of the same data is not a big deal. This entire
process will be repeated by another user off a different set of hard copy
before the record is added to the app in chief. (These records are all added
to temp tables.) This is a case were close enough is not good enough. So
before we add a lot to the app in chief it just HAS to be right, exactly
right, these r legally enforceable contracts. We have been offered for
purchase contracts that cover lots that do not legally exist. Before we put
out that deposit check we just have to be SURE of what we r buying. Hence the
double check and 3X check sometimes.

Again, thank u, this does gets rid of one of my aggravations.