User new addition to combo box with related fields that need to be populated.

[Smythe32]

Hi,

I have this code below that will add a new item to a combo box if it
is not in the combo box. I also would like the user to add the
description for the new addition to the combo box and select a
category that it falls into. The description field is a 255 text
field. The category will need to be selected from a dropdown list and
then added to the main reason table.

tblReasonCodes has the following fields, all 255 text.

reason code, description, category

I would like to be able to query the category column to populate the
dropdown box for the user to select a category. Otherwise, I can just
make a category table to use for the dropdown box.

Here is the code I have that will add a new reason code to the drop
down list on the form.

I found the code on Dev Ashish's site.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String
strMsg = "'" & NewData & "' is not an available reason code. " &
vbCrLf & vbCrLf
strMsg = strMsg & vbCrLf & vbCrLf & "If you would like to add it,
click Yes otherwise Click No."

If msgbox(strMsg, vbQuestion + vbYesNo, "Add new code?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblReasonCodes", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![Reason Code] = NewData
rs.Update

If Err Then
msgbox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub

Thanks

[Salad]

Smythe32 wrote:

Hi,

I have this code below that will add a new item to a combo box if it
is not in the combo box. I also would like the user to add the
description for the new addition to the combo box and select a
category that it falls into. The description field is a 255 text
field. The category will need to be selected from a dropdown list and
then added to the main reason table.

tblReasonCodes has the following fields, all 255 text.

reason code, description, category

I would like to be able to query the category column to populate the
dropdown box for the user to select a category. Otherwise, I can just
make a category table to use for the dropdown box.

Here is the code I have that will add a new reason code to the drop
down list on the form.

I found the code on Dev Ashish's site.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available reason code. " &
vbCrLf & vbCrLf
strMsg = strMsg & vbCrLf & vbCrLf & "If you would like to add it,
click Yes otherwise Click No."

If msgbox(strMsg, vbQuestion + vbYesNo, "Add new code?") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblReasonCodes", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![Reason Code] = NewData
rs.Update

If Err Then
msgbox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End If
End Sub

Thanks

The question is...what is your question? It appears you are telling us
what you are doing. Did you need help on a combo box? Setting
properties. What the rowsource should be in the combo?

[Smythe32]

This is my question.....as shown below...

I also would like the user to add the description for the new addition
to the combo box and select a category that it falls into.

So, to reiterate....I can get them to enter the data so it is added to
the table for the combo box but I need them to be prompted to update
the related fields in the underlying table that go with the new combo
box entry.

So, if I add a new code to the box, I have to add a description and a
category.

Thanks,