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 = acDataErrContin ue
Else
Set db = CurrentDb
Set rs = db.OpenRecordse t("tblReasonCod es", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs![Reason Code] = NewData
rs.Update

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

End If

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

Thanks

[Salad]

Re: User new addition to combo box with related fields that need to bepopulated.

Smythe32 wrote:
[color=blue]
> 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 = acDataErrContin ue
> Else
> Set db = CurrentDb
> Set rs = db.OpenRecordse t("tblReasonCod es", dbOpenDynaset)
> On Error Resume Next
> rs.AddNew
> rs![Reason Code] = NewData
> rs.Update
>
> If Err Then
> msgbox "An error occurred. Please try again."
> Response = acDataErrContin ue
> Else
> Response = acDataErrAdded
>
> End If
>
> rs.Close
> Set rs = Nothing
> Set db = Nothing
> End If
> End Sub
>
> Thanks[/color]

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]

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

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,