Welcome to SPN

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

Sign Up Now!

NotInList adding new entry as a new record each time

Discussion in 'Information Technology' started by robingSA@gmail.com, Jul 28, 2006.

  1. robingSA@gmail.com

    robingSA@gmail.com
    Expand Collapse
    Guest

    Hi...I have a number of Combo Boxes on a form. If one types a name
    which is not in the table, I want it to add the typed, new item. I am
    using the following code to do this and it works well, except for one
    thing - each new item is added as a new record. I want the new name to
    be added after the last entry in the relevant field...is this possible
    - preferably by tweaking the code I am using already?

    Private Sub Supermarket_NotInList(NewData As String, Response As
    Integer)
    Dim Db As DAO.Database
    Dim Rs As DAO.Recordset
    Dim Msg As String
    Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    Msg = Msg & "Do you want to add it?"
    If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    Response = acDataErrContinue
    MsgBox "Try again."
    Else
    Set Db = CurrentDb
    Set Rs = Db.OpenRecordset("Businesses", dbOpenDynaset)
    Rs.AddNew
    Rs![Supermarket] = NewData
    Rs.Update
    Response = acDataErrAdded
    End If
    End Sub

    Thanks...Rob
     
  2. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    I don't understand what you mean by "added after the last entry in the
    relevant field".

    Your combo box is presumably based on your Businesses table. If what you've
    typed isn't already in the Businesses table, you have to add it as a record
    to the Businesses table in order to continue.

    Are you complaining about where in the list the new data appears? Never make
    any assumptions about the order in which data is returned unless you're
    using a query with an appropriate ORDER BY clause. If you've based your
    combo box on a query with the appropriate ORDER BY clause and the data still
    isn't coming back in the order you expect, post back with more details.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <robingSA@gmail.com> wrote in message
    news:1147934084.472983.315000@j33g2000cwa.googlegroups.com...
    > Hi...I have a number of Combo Boxes on a form. If one types a name
    > which is not in the table, I want it to add the typed, new item. I am
    > using the following code to do this and it works well, except for one
    > thing - each new item is added as a new record. I want the new name to
    > be added after the last entry in the relevant field...is this possible
    > - preferably by tweaking the code I am using already?
    >
    > Private Sub Supermarket_NotInList(NewData As String, Response As
    > Integer)
    > Dim Db As DAO.Database
    > Dim Rs As DAO.Recordset
    > Dim Msg As String
    > Msg = "'" & NewData & "' is not in the list." & vbCr & vbCr
    > Msg = Msg & "Do you want to add it?"
    > If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
    > Response = acDataErrContinue
    > MsgBox "Try again."
    > Else
    > Set Db = CurrentDb
    > Set Rs = Db.OpenRecordset("Businesses", dbOpenDynaset)
    > Rs.AddNew
    > Rs![Supermarket] = NewData
    > Rs.Update
    > Response = acDataErrAdded
    > End If
    > End Sub
    >
    > Thanks...Rob
    >
     
  3. robingSA@gmail.com

    robingSA@gmail.com
    Expand Collapse
    Guest

    Hi Doug,

    The Row Source for my combo box is as follows:
    SELECT [Businesses].[Supermarket] FROM Businesses;

    I don't care where in the list the new entry appears (although
    alphabetical would be good). What is happening is this - let's say I
    have 4 combo boxes all using the select statement above with different
    fields obviously. The Businesses table consists of these 4 fields. If I
    enter a new item in Combo box A, it places the new entry in row 1 under
    Field Aof the Businesses table. If I then enter a new item in Combo box
    B, it places the new entry in row 2 under Field B...and so on... I
    would like the entry in box B in this example to go in row 1 as well.
    The way it is now, I am getting a lot of blanks in the combo boxes.

    Hope this makes it a little clearer!
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    So you've got 4 different combo boxes all based on the same Businesses
    table. However, in the NotInList code you posted previously, the only value
    you're adding to the Businesses table is the Supermarket, so the row
    corresponding to that new supermarket is going to have Nulls (or default
    values) for all of the other fields.

    What you might need to do is rather than opening the recordset and adding
    NewData to it is open a maintenance form that will let the user add all of
    the fields associated with that supermarket. Open it with the WindowMode set
    to acDialog, so that they must deal with the form before they can continue.
    Alternatively, prompt them for the other values, and insert all of them into
    the table, not just the Supermarket value.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <robingSA@gmail.com> wrote in message
    news:1147954430.246306.238180@i40g2000cwc.googlegroups.com...
    > Hi Doug,
    >
    > The Row Source for my combo box is as follows:
    > SELECT [Businesses].[Supermarket] FROM Businesses;
    >
    > I don't care where in the list the new entry appears (although
    > alphabetical would be good). What is happening is this - let's say I
    > have 4 combo boxes all using the select statement above with different
    > fields obviously. The Businesses table consists of these 4 fields. If I
    > enter a new item in Combo box A, it places the new entry in row 1 under
    > Field Aof the Businesses table. If I then enter a new item in Combo box
    > B, it places the new entry in row 2 under Field B...and so on... I
    > would like the entry in box B in this example to go in row 1 as well.
    > The way it is now, I am getting a lot of blanks in the combo boxes.
    >
    > Hope this makes it a little clearer!
    >
     

Share This Page