adding new values the value list in a combo box

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hello there.

I have a form which contains 9 combo boxes arranged as shown below:

Problem No. 1:
OperationType1
OperationSubtype1
PartOfBody1

Problem No. 2:
OperationType2
OperationSubtype2
PartOfBody2

Problem No. 3:
OperationType3
OperationSubtype3
PartOfBody3

I want to be able to add new values to each of these combo boxes, so that if
I add a value to OperationType1, for example, the value list in
OperationType2 and OperationType3 will also be updated.

I have created event procedures in OnNotInList for each of these combo boxes
(and have also set the Limit to List value to "yes") and the code is shown
below:

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

Dim ctl As Control

Set ctl = Me!OperationType
Set ctlA = Me!Combo22
Set ctlB = Me!Combo38
If MsgBox("This operation is not in the list. Do you want to add it to
the list ?", _
vbOKCancel) = vbOK Then
Response = acDataErrAdded
ctl.RowSource = ctl.RowSource & ";" & NewData
ctlA.RowSource = ctlA.RowSource & ";" & NewData
ctlB.RowSource = ctlB.RowSource & ";" & NewData

Else
Response = acDataErrContinue
ctl.Undo
End If
End Sub


This event procedure successfully adds the new values to the value list for
each of the required combo boxes, but these values only exist on these value
lists while the form is open. Once I close the form, the new values
disappear from the value list.

Any suggestions as to how I can retain new values on the value list once
they are entered?

Thanks in advance
Anthony
 
You must add the value to each of the affected tables then requery the
combo. Remember too, that you must add any of the required fields to those
tables. Knowing that, I'd open an unbound form using the NotInList event,
fill it in with the required values, then use an Insert query to write those
values to each of the affected tables.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Anthony said:
Hello there.

I have a form which contains 9 combo boxes arranged as shown below:

Problem No. 1:
OperationType1
OperationSubtype1
PartOfBody1

Problem No. 2:
OperationType2
OperationSubtype2
PartOfBody2

Problem No. 3:
OperationType3
OperationSubtype3
PartOfBody3

I want to be able to add new values to each of these combo boxes, so that if
I add a value to OperationType1, for example, the value list in
OperationType2 and OperationType3 will also be updated.

I have created event procedures in OnNotInList for each of these combo boxes
(and have also set the Limit to List value to "yes") and the code is shown
below:

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

Dim ctl As Control

Set ctl = Me!OperationType
Set ctlA = Me!Combo22
Set ctlB = Me!Combo38
If MsgBox("This operation is not in the list. Do you want to add it to
the list ?", _
vbOKCancel) = vbOK Then
Response = acDataErrAdded
ctl.RowSource = ctl.RowSource & ";" & NewData
ctlA.RowSource = ctlA.RowSource & ";" & NewData
ctlB.RowSource = ctlB.RowSource & ";" & NewData

Else
Response = acDataErrContinue
ctl.Undo
End If
End Sub


This event procedure successfully adds the new values to the value list for
each of the required combo boxes, but these values only exist on these value
lists while the form is open. Once I close the form, the new values
disappear from the value list.

Any suggestions as to how I can retain new values on the value list once
they are entered?
Click to expand...

Put them in a table.
If you've just type the values in I believe they are "hard coded" in the
form.
 
Hello Anthony.
You are storing the values in the form, so when you close the form, you will
need to save it.

eg, DoCmd.Close acForm, "FormName", acSaveYes
this sould solve your problem.
however, I would say it would be a better Idea to put the values into a
table, and update the table. (and it your going to distribute your app with
a runtime, you will not be able to save the form.

hope this helps.
.... Philip Middleton
 
Thank you Arvin, Mike and Philip for your suggestions.

Arvin, I will do as you suggest and create new tables to store the data and
a linked form for data entry, which will be accessed by pressing a command
button located on the original form. I have actually used this approach
elsewhere in the database and it works fine. In these instances, however,
there were entire records to add, each with several fields and only one of
these fields appearing in the value list of the combo box.

The original plan for these new combo boxes was to add new values to the
value lists directly in the original form, because there would only be the
one field in each record.

Philip, I tried incorporating that line of code you suggested into the code
already used for a command button on the form (which is already used to
close this form). Unfortunately, it didn't work.

Thank you all once again for your suggestions.
Anthony
 
Back
Top