NotInList event 3

[aristos]

Well, the search function on the site is down, so I can't look for previous posts on this.

I'm hoping someone will take pity and present a solution for me.

Here's the issue: I have a form called frmAddNewJob that allows people to enter a new job (duh). On the form is a field cmbClientID that is a combo box that has two columns: ClientID and ClientName. Control source is a qryClientLoookup query. First column is bound and hidden, second column is the name, which is what I want people to select and get the ClientID into the field. It all works fine.

Now for the fun part. If the user adds a client that is not in the list, then I want the system to open the frmAddClient form and allow the user to add the client as a new one, save the form, and, in the frmAddJob form, have the new client show in the ClientID field. This is where I'm having problems.

I hope this makes sense, but here's the short version:

1. Open frmAddJob
2. Enter name that isn't in client list
3. Code asks if you want to add the client as a new one - answer yes
4. frmAddClient form pops up, the name you typed in on the frmAddJob shows as the company name
5. Add the rest of the client info
6. Click on the done button - you go back to the (still open) frmAddJob form
7. The client name you entered before is there and is also now part of the drop-down list so you can now carry on with entering the rest of the job info.


Okay, that's it.

Thanks in advance

Mike

 

[FirstAndGoal4]

Pass the client name to frmAddClient using OpenArgs (see DoCmd.OpenForm) and set the proper form control in the OnOpen event.

Enter the other info and store the ClientId in a global variable using the AfterUpdate event. Close the form.

In the Activate event of frmAddNewJob, do a combobox.Requery and then set the combobox to the value in the global variable.

 

[SBendBuckeye]

Also, there is an option in docmd.openform call acDialog. If you include that option, your form1 will wait patiently around until form2 is closed or hidden at which time it begins to run again. Do something like this in form1 code:

me.visible = false
docmd.openform "form2",,etc including acDialog,,"OpenArgs"
me.visible = true

Good Luck!

 

[annsolomon]

Hi Mike. Another way to handle this if frmAddNewJob has all the fields on it that you would need for a new customer would be to let the user add the new customer right on frmAddNewJob (without popping up a separate form). Here's what I've done on a Work Order form when the user enters a customer that's not in the combo box.

Private Sub ContactID_NotInList(NewData As String, Response As Integer)
On Error GoTo HandleErr

If MsgBox("Would you like to add this contact to the database?", _
vbYesNo + vbQuestion, "Contact not in JSJ database&quot = vbYes Then
Response = AddToList("Contacts", "ContactName", NewData)
Else
Response = acDataErrDisplay
End If


ExitHere:
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbOKOnly, _
"JSJ, Inc."
End Select
Resume ExitHere
Resume

End Sub

 

[aristos]

Thanks, folks. All good suggestions, but SBendBuckeye's answer was the one that worked for us.

As a followup question:

The form works as expected now. I can enter a client who is not listed and have the addNewClient for pop up, fill it in, hit Done, go back to the form.

At this point, however, the frmAddJob form says that the item is not in the list and that I should pick one. It *is* in the list now (which is what I want), but I don't want that error dialog box. I put a docmd.SetWarnings False at the top of the code, but I still get the message box.

Suggestions?

Thanks, all!

Mike

 

[Dalain]

Before you close the Form2 tyou have to requery the combobox info in form1.

In the On Close event of form2

Forms!Form1!combobox.Requery

Which will refresh the information the the Combobox.


Pierre