Add entry to drop down list

[Guest]

I need to find out how to allow a user to add an item to a drop down list
that does not already exist in the drop down list. I am not sure If i need
to write a macro to do this or if there is a setting that can be used.

For instance. A user is entering data in the database. The come to a field
in the form that has a drop down list of categories including "staff",
"trainees", and "agencies". The information they are entering needs to to go
into the category "vendors". I have the field set to No for limit to list,
but how do I get the data they enter to automatically add to the list?

Thanks.

 

[Pieter Wijnen]

once again www.mps.org/acces

 

[Guest]

Hi Risikio -- Below is a sub from one of my DBs that adds an item to the
list. You can substitute you fields, table, etc.

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

Dim db As DAO.Database
Dim YesNo As VbMsgBoxStyle

YesNo = Msgbox("Enter New Value?", vbYesNo + vbQuestion +
vbDefaultButton1, "NEW VALUE")

Select Case YesNo
Case vbYes
Set db = CurrentDb()

DoCmd.OpenForm "frmEditExpenses", acNormal, , , acFormAdd, acDialog

'Add the new value to the field
' db.Execute "INSERT INTO tblPaidToRef (EntityName) " & _
' "VALUES (""" & NewData & """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

Case vbNo
cboPaidTo.Text = ""
End Select

End Sub

 

[Guest]

I think I found the answer. I had to go into the form to set a On Not in
List [Event Procedure]. I was looking for it in the tables. Does the [Event
Procedure] automatically write the correct code? I am not real good with the
visual basic side of access.

 

[Guest]

Thanks. I will give this a try.

Hi Risikio -- Below is a sub from one of my DBs that adds an item to the
list. You can substitute you fields, table, etc.

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

Dim db As DAO.Database
Dim YesNo As VbMsgBoxStyle

YesNo = Msgbox("Enter New Value?", vbYesNo + vbQuestion +
vbDefaultButton1, "NEW VALUE")

Select Case YesNo
Case vbYes
Set db = CurrentDb()

DoCmd.OpenForm "frmEditExpenses", acNormal, , , acFormAdd, acDialog

'Add the new value to the field
' db.Execute "INSERT INTO tblPaidToRef (EntityName) " & _
' "VALUES (""" & NewData & """)", dbFailOnError

'Tell Access you've added the value
Response = acDataErrAdded

db.Close
Set db = Nothing

Case vbNo
cboPaidTo.Text = ""
End Select

End Sub

 

[Guest]

No, you'll need to write the code, which you have in my sub. The
only thing you have to do is substitute the appropriate names. Also, make
sure the SQL execute statement isn't commented out.

I think I found the answer. I had to go into the form to set a On Not in
List [Event Procedure]. I was looking for it in the tables. Does the [Event
Procedure] automatically write the correct code? I am not real good with the
visual basic side of access.

I need to find out how to allow a user to add an item to a drop down list
that does not already exist in the drop down list. I am not sure If i need
to write a macro to do this or if there is a setting that can be used.

For instance. A user is entering data in the database. The come to a field
in the form that has a drop down list of categories including "staff",
"trainees", and "agencies". The information they are entering needs to to go
into the category "vendors". I have the field set to No for limit to list,
but how do I get the data they enter to automatically add to the list?

Thanks.

 

[Guest]

Thanks again. I had posted my finding at the same time your posted your
code. I am working on it now.

Thank you again.

No, you'll need to write the code, which you have in my sub. The
only thing you have to do is substitute the appropriate names. Also, make
sure the SQL execute statement isn't commented out.

I think I found the answer. I had to go into the form to set a On Not in
List [Event Procedure]. I was looking for it in the tables. Does the [Event
Procedure] automatically write the correct code? I am not real good with the
visual basic side of access.

I need to find out how to allow a user to add an item to a drop down list
that does not already exist in the drop down list. I am not sure If i need
to write a macro to do this or if there is a setting that can be used.

For instance. A user is entering data in the database. The come to a field
in the form that has a drop down list of categories including "staff",
"trainees", and "agencies". The information they are entering needs to to go
into the category "vendors". I have the field set to No for limit to list,
but how do I get the data they enter to automatically add to the list?

Thanks.

 

[Guest]

Great, let me know if you run into any problems by posting your own code.
Good Luck.

Thanks again. I had posted my finding at the same time your posted your
code. I am working on it now.

Thank you.

No, you'll need to write the code, which you have in my sub. The
only thing you have to do is substitute the appropriate names. Also, make
sure the SQL execute statement isn't commented out.

I think I found the answer. I had to go into the form to set a On Not in
List [Event Procedure]. I was looking for it in the tables. Does the [Event
Procedure] automatically write the correct code? I am not real good with the
visual basic side of access.

:

I need to find out how to allow a user to add an item to a drop down list
that does not already exist in the drop down list. I am not sure If i need
to write a macro to do this or if there is a setting that can be used.

For instance. A user is entering data in the database. The come to a field
in the form that has a drop down list of categories including "staff",
"trainees", and "agencies". The information they are entering needs to to go
into the category "vendors". I have the field set to No for limit to list,
but how do I get the data they enter to automatically add to the list?

Thanks.