Add to Lookup Table from PopUp Form (1 Viewer)

[Gilrucht]

I have a form with a combobox based on a query of a lookup table. If the name entered is not found in the combobox, I want a popup form to open allowing the user to enter a record for this new name in the lookup table, then close. What is the best way to this? A command button? The Not in List Event? A msgbox? I'm not sure.

 

[trucktime]

Lookup

Check out this link:

http://www.fontstuff.com/access/acctut20.htm


or this one:

http://www.microsoft-accesssolutions.co.uk/not_in_list.htm

 

[Gilrucht]

I looked at both of them and tried this code bound to the not in the list event but It isnn't reading the "openform strfrm" command.

-------------------------------

Private Sub Combo18_NotInList(NewData As String, Response As Integer)
Dim StringFrm As String
StringFrm = "frm_Doctors"
intAnswer = MsgBox("The Dr's Name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, )


If intAnswer = vbYes Then
DoCmd.OpenForm strFrm
MsgBox "The new Dr's Name has been added to the list." _, vbInformation
Response = acDataErrAdded

Else
MsgBox "Please choose a Dr's Name from the list." _
, vbInformation, "Acme Oil and Gas"
Response = acDataErrContinue
End If

End Sub
-------------------------

 

[RuralGuy]

Hi Gilrucht,

If you had this:

Option Compare Database
[B]Option Explicit[/B]

...at the top of your code module you would not have had this problem.

Dim [B]StringFrm [/B] As String
[B]StringFrm [/B] = "frm_Doctors"
intAnswer = MsgBox("The Dr's Name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, )

If intAnswer = vbYes Then
DoCmd.OpenForm [B]strFrm[/B]

 

[Gilrucht]

Rural, Thanks. As usual, you came thru. I added your 2 lines and it solved the problem. However, now that I have the popup form opening I have 2 other problems. First, when msgbox asks if I want to add to list and I choose yes, the popup form opens but displays Access default message that text I entered is not in list. I have to close this message before I can enter data in new record in popup form. How do I get rid of that message?

Second, after I enter the data in the popup form and return to my combobox the new name is not appearing in the combo list. I tried adding a requery command but I get a message telling me I have to save the field first. I think it may be a question of where I have my requery located in the sub routine. Heres the code.
------------
Option Compare Database
Option Explicit
Private Sub Combo18_NotInList(NewData As String, Response As Integer)
Dim StrFrm As String
Dim intAnswer As Integer

StrFrm = "frm_Doctors"

intAnswer = MsgBox("The Dr's Name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Doctor")



If intAnswer = vbYes Then
DoCmd.SetWarnings False

DoCmd.OpenForm StrFrm

Response = acDataErrAdded
Me.Combo18.Requery

Else
MsgBox "Please choose a Dr's Name from the list." _
, vbInformation, "Doctor"
Response = acDataErrContinue
End If

End Sub
-------------

 

[RuralGuy]

Hello again Gilrucht,

You can just call me RG for short. My suggestions are in-line in the code:

'-- You do not need the next line
DoCmd.SetWarnings False
'-- If you ever do use the above line in a subroutine then
'-- make sure you issue a [B]DoCmd.SetWarnings True [/B] before you exit!

'-- Use the acDialog parameter to halt execution in the current subroutine
[B]DoCmd.OpenForm StrFrm, , , , , acDialog[/B]
'-- The next line will tell Access to do a Requery of the ComboBox
Response = acDataErrAdded
'-- Therefore you do not need the next line
Me.Combo18.Requery

These changes should resolve all of the issues you mentioned.

 

[Gilrucht]

Thankd, RG
As usual you were right on the money. Much appreciated.

 

[RuralGuy]

You're welcome and glad I could help.