Combo Box and Limit To List

G

Guest

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!
 
G

Guest

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
 
G

Guest

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
 
G

Guest

Hi Cheese Wiz,

I get "Sub or Function Not Defined". When I hit debug the " Call ErrorLog"
is highlighted in blue. When I took out the Error code, and entered a Charge
No that is not on the list I get the message "Add a new charge No?". I click
on the yes and then the combo box'es options are droped down.

HELP!

I also took out the closing of the form (which I don't want) as well as the
trim function (which I don't need). Is this ok?

Private Sub ChargeNo_EL_NotInList(NewData As String, Response As Integer)

Dim mbrResponse As VbMsgBoxResult
Dim strMsg As String
Dim strMod As String

On Error GoTo ChargeNo_EL_NotInList_Error" =

strMsg = "Add " & NewData & " as a new Charge No?"
mbrResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Invalid Charge No")
Select Case mbrResponse
Case vbYes

DoCmd.OpenForm "sfrmOPMInputB", acNormal, , , acFormAdd, acDialog,
OpenArgs:=NewData

Me.ChargeNo_EL.Undo

Response = acDataErrContinue
Me.ChargeNo_EL.Requery

Case vbNo
Response = acDataErrContinue
End Select

Exit_ChargeNo_EL_NotInList:
Exit Sub

ChargeNo_EL_NotInList_Error:
Call ErrorLog(Err.Description, Err.Number, Me.Name)

End Sub

Cheese_whiz said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top