Hi Nanette,
Rather than trying to rewrite what you have, I think I'll post some code
I've used in one of my applications. It took me quite a bit of time kicking
around to get things to work the way I wanted them to so I don't see much use
in trying to reinvent the wheel.
Probably the most important difference is the use of a form to add the new
entry into the underlying table or query's recordset. There may be a place
or two where the code has been returned due to space issues, but hopefully
you can see where. If not, let me know.
CW
Private Sub Requesting_Party_NotInList(NewData As String, Response As Integer)
Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String
Dim strMod As String
On Error GoTo Requesting_Party_NotInList_Error
strMsg = "Add " & NewData & " as a new Party?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Party Name")
Select Case mbrResponse
Case vbYes
DoCmd.OpenForm "PopupContacts", DataMode:=acFormAdd,
WindowMode:=acDialog, OpenArgs:=NewData
Me.Requesting_Party.Undo
Response = acDataErrContinue
Me.Requesting_Party.Requery
Me.Facility.Requery
Me.Requesting_Party.Text = Trim(Forms!PopupContacts.txtLastName & ",
" & Forms!PopupContacts.txtFirstName & " " & Forms!PopupContacts.txtMI)
DoCmd.Close acForm, "PopupContacts"
Case vbNo
Response = acDataErrContinue
End Select
Exit_Requesting_Party_NotInList:
Exit Sub
Requesting_Party_NotInList_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name)
End Sub
Nanette said:
Hi Cheese_whiz,
I used the code from the link you provided, but it doesn't work. A message
box asks if I want to add it to the list. I chose yes, then I get the second
message from the Else statement "Please choose a Charge (EL) No from the
list." I click "OK" and am sent back to the form with the combo box list
showing.
Can you figure out why this isn't working correctly? I'm new a VBA and can't
figure out why it won't work.
The code is:
Private Sub ChargeNo_EL_NotInList(NewData As String, Response As Integer)
On Error GoTo ChargeNo_EL_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Charge (EL) No " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "RFQ Database")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblChargesAndMOD([ChargeEL]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Charge (EL) No has been added to the list." _
, vbInformation, "RFQ Database"
Response = acDataErrAdded
Else
MsgBox "Please choose a Charge (EL) No from the list." _
, vbInformation, "RFQ Database"
Response = acDataErrContinue
End If
ChargeNo_EL_NotInList_Exit:
Exit Sub
ChargeNo_EL_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume ChargeNo_EL_NotInList_Exit
End Sub
Cheese_whiz said:
Hi Nanette:
You had the settings right to start with (2 cols, 0", 1" widths, bound 1,
limit to list yes), you just need to add an "On Not in List" event to the
combo box.
Here's a link that you might find useful:
http://www.fontstuff.com/access/acctut20.htm#fullcode
Hope that helps,
CW
:
I have 3 Combo Boxs that get their values from a table.
The table contains 4 columns.
1st Column (id_Change)
2nd Column (ChangeEL)
3rd Column (ChangeEM)
4th Column (ChangeP)
For the 1st (and 2nd and 3rd) Combo Box the bound column is the 1st column,
the column count is 2, the column widths are 0";1.25". With this set up, the
combo box works fine but does not allow items to be added to the list. When I
tried to change the "Limit To List" option to No, I get a message about
changing the 1st column width to match the 1st column. Since I don't want to
see the column id (1st column), I tried making the width .007. When I do
this, the output in the combo box is the number of the row, NOT the data in
the row. This happens for all three combo boxes.
How can I set the Limit To List to YES and get the correct data from the
table to appear in the combo box?
Also, these combo boxes are in a subform that is within another subform.
Thanks in advance for your help!
