Add to Lookup Table from PopUp Form

Gilrucht

Registered User.
Local time
Today, 07:47
Joined
Jun 5, 2005
Messages
132
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.
 
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
-------------------------
 
Hi Gilrucht,

If you had this:
Code:
Option Compare Database
[B]Option Explicit[/B]
...at the top of your code module you would not have had this problem.
Code:
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]
 
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
-------------
 
Hello again Gilrucht,

You can just call me RG for short. ;) My suggestions are in-line in the code:
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.
 
Thankd, RG
As usual you were right on the money. Much appreciated.
 

Users who are viewing this thread

Back
Top Bottom