Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Add entry to drop down list

Discussion in 'Information Technology' started by Risikio, Jul 28, 2006.

  1. Risikio

    Risikio
    Expand Collapse
    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.
     
  2. Loading...


  3. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    once again www.mps.org/acces

    "Risikio" <Risikio@discussions.microsoft.com> wrote in message
    news:FFBE06D5-C8D6-4B91-9C38-DFD214A3AA36@microsoft.com...
    >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.
     
  4. xRoachx

    xRoachx
    Expand Collapse
    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

    "Risikio" wrote:

    > 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.
     
  5. Risikio

    Risikio
    Expand Collapse
    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.

    "Risikio" wrote:

    > 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.
     
  6. Risikio

    Risikio
    Expand Collapse
    Guest

    Thanks. I will give this a try.

    "xRoachx" wrote:

    > 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
    >
    > "Risikio" wrote:
    >
    > > 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.
     
  7. xRoachx

    xRoachx
    Expand Collapse
    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.

    "Risikio" wrote:

    > 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.
    >
    > "Risikio" wrote:
    >
    > > 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.
     
  8. Risikio

    Risikio
    Expand Collapse
    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.

    "xRoachx" wrote:

    > 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.
    >
    > "Risikio" wrote:
    >
    > > 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.
    > >
    > > "Risikio" wrote:
    > >
    > > > 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.
     
  9. xRoachx

    xRoachx
    Expand Collapse
    Guest

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

    "Risikio" wrote:

    > Thanks again. I had posted my finding at the same time your posted your
    > code. I am working on it now.
    >
    > Thank you.
    >
    > "xRoachx" wrote:
    >
    > > 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.
    > >
    > > "Risikio" wrote:
    > >
    > > > 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.
    > > >
    > > > "Risikio" wrote:
    > > >
    > > > > 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.
     

Share This Page