Not in List question (Acc 97 sr2 on 95b)

Topic

New Reply

I have a quote form that is tied to a tblQuote table. There is a combo box on the form that is bound to a field in the table called customers. This field is a lookup field to a table called tblCustomers.

Limit to list is currently set to off so that a quote may be raised for an existing customer or a new customer name can be entered. This new customer name is saved to the customer field in tblQuote but not saved in the tblCustomer table.

If the quote goes live (becomes a job) then a button is clicked which uses data from the quote form (including the name) to create OpenArgs and the new job form is opened and the values placed.

In this form, limit to list is set so a new customer name triggers the sub which opens the new customer input form. When this is closed and the new job form becomes active, a requery is triggered so that the new customer name is now accepted.

..Sort of……well it almost works…..I have a problem with the last step, the Not in list seems to fire twice, because of the requery I think.

Rather than ask one of you people to debug my forms, I want to try another way…..

Does the Not inList function only trigger if Limit to List is set to true?

I was wondering if I could use Not in list to set a yes/no in another field to flag a new customer, but still have the customer accepted into the quote table? this way, when a quote goes live, I can check the flag and open the New Customer form if needed before opening the New Job form.

Then again, if anyone wouldn’t mind having a look at my forms I would be grateful.

Reply | Quote

Replies

If you set Response to acDataErrAdded after the customer form closes, the requery shouldn’t be necessary (Access should handle that automatically) and the NotInList event shouldn’t fire again.

In situations like this, I use code resembling the following:

Private Sub CustomerID_NotInList(NewData As String, Response As Integer)
If MsgBox(“‘” & NewData & “‘ is not a customer yet. Do you want to add it?”, vbYesNo) = vbYes Then
DoCmd.OpenForm “frmCustomers”, acNormal, , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
CustomerID.Undo
Response = acDataErrContinue
End If
End Sub

If this doesn’t help, post back or do a search on this Forum. There are plenty of threads on NotInList.

NewData is passed to frmCustomers in the OpenArgs argument. I use this in the OnLoad event of frmCustomers to fill in the corresponding field, so that the user doesn’t have to type it again.

Reply | Quote

Many thanks for the info Hans. I didn’t have acDataErrAdded, nor was I using acDialog. I have added both to my event and the whole process now works flawlessly (or so it seems).

The more I learn, the more I realize I need to learn

Reply | Quote

Hans forgot to mention that yes, the NotInList event is only triggered if the Limit to List property is set to Yes.

Reply | Quote

How stupid of me. Must be old age setting on…

Reply | Quote

You’re only as old as you feel, Hans.

Reply | Quote

Right, but forgetting to answer questions makes me *feel* old!

Reply | Quote

Well, some of us are just plain

Reply | Quote

Thankyou Charlotte, for clearing up that point. I thought that would be the case, but with my lack of programming skills, lots of things I think impossible I find here on the forum as quite do-able.

Reply | Quote