RE: Combo Box and Limit To List

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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" wrote:

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

"Nanette" wrote:

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!


.

Relevant Pages

  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: Is there a way to prevent a RichTextBox from scrolling?
    ... Private _isRegex As Boolean ... Public Sub New(ByVal thispattern As String, ... Dim entry As tDict ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Excel Listing tool using VB
    ... Sub ListFiles2() ... Dim directories() As String, CurrentDirectory As String ... Dim dirtopaste, dirok ...
    (microsoft.public.vb.general.discussion)
  • Form Error
    ... SMSDS_CallerID As String ... Private Declare Sub Sleep Lib "kernel32" ... Dim ComString As String ... Dim AppPath As String, FreeFileNo% ...
    (microsoft.public.vb.bugs)
  • RE: Add Record with combo box
    ... Private Sub JCTARSectionLayer1ID_NotInList(NewData As String, Response As ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)