Not_In_List event updating combo box

  • Thread starter Thread starter S.Low
  • Start date Start date
S

S.Low

I have a form for entering new jobs. I select a customter
from a combo box and the results of that filter another
combo box with customer contacts. I need to track
individuals that actually bring in the job. I am trying to
set up a not in list event that will 1. open a form
2. add the new contact, company name, and account # to the
form
3. After I close the contact form-update the combo box.
Step 3 is the problem. Steps 1 and 2 work fine, however
when I close the new contact form, and the new job form
has focus, I get the not in list message again when I move
from the contact field. I tried requery action and it
gives an error message about saving the current record.
The code I am using is below. Can someone please tell me
what I am missing, besides a orking knowledge of vba.
Thanks
Private Sub ContactID_NotInList(NewData As String,
Response As Integer)
Dim Form As String
Dim ctl As ComboBox
Dim Msg As String
Dim CR As String


Form = "frmAddContactfromNewJob"
Set ctl = ContactID
CR = Chr$(13)

Msg = "' " & NewData & "' is not in the contact list for"
& CR & CR
Msg = Msg & "this customer. Do you want to add this name?"

If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then

Response = acDataErrContinue

DoCmd.OpenForm Form, acNormal, , , acFormAdd

Forms!frmAddContactfromNewJob.Contact = NewData
Forms!frmAddContactfromNewJob.AccountNo = Forms!
frmNewWorkOrder.Key
Forms!frmAddContactfromNewJob.Company = Forms!
frmNewWorkOrder.AccountNo.Column(1)

Else
Response = acDataErrContinue
ctl.Undo
End If

End Sub
 
1) Don't use Form as a variable name. It is a reserved word. Try strForm
instead.

2) When you answer Yes, you need to tell the combo box that data was added.
Change the first Response=acDataErrContinue line to Response=acDataErrAdded.

3) When you open your "add" form, you need to open it as a "pop-up" to stop
the calling code from running until you close the form. To do this, use the
acDialog window mode option.

DoCmd.OpenForm strForm, acNormal, , , acFormAdd, acDialog
 
When I use the "acDialog" the data does not go into the
form, and then when I close the new contact form an error
pops up about not being able to find the
form "frmAddContactfromNewJob"
 
You are correct, because those next lines of code haven't run yet. They
don't get run until the form closes. You'll need to pass these values
another way. The NewData value could be passed in the OpenArgs argument of
the DoCmd.OpenForm call. You would then check for a value in OpenArgs in the
Open or Load event of the pop-up form. The other values are available in the
current form's controls. In the Open or Load event of the pop-up form, refer
back to those controls and "pull" the values from the pop-up instead of
"pushing" them from the initiating form.
 
"Pull" worked, thank you!

S.Low
-----Original Message-----
You are correct, because those next lines of code haven't run yet. They
don't get run until the form closes. You'll need to pass these values
another way. The NewData value could be passed in the OpenArgs argument of
the DoCmd.OpenForm call. You would then check for a value in OpenArgs in the
Open or Load event of the pop-up form. The other values are available in the
current form's controls. In the Open or Load event of the pop-up form, refer
back to those controls and "pull" the values from the pop- up instead of
"pushing" them from the initiating form.

--
Wayne Morgan
Microsoft Access MVP





.
Click to expand...
 
Back
Top