Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    RecordSet.AddNew (A97 SR-2)

    Have been looking at AddNew to add a new record from a not_in_list event.

    My Table, tblUOM (Units of Measure) has only one field (called UOM and is the keyfield of course), which records the UOM Description (eg Each, Packet, Bottle) etc

    From HELP, this, and other boards, I can see how this works if you assume that there is an autonum type key, and assigning values to the fields. But I can't for the life of me see how to determine the key field when using AddNew. I dont particualarly want to create an autonum field, but if that is the only solution then......

    But maybe someone out there knows a simple solution that I have not yet spotted.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: RecordSet.AddNew (A97 SR-2)

    From you post I gather that you have a table with a single column that is the primary key of the table, and is also the data element you want someone to put into a column in another table. If that is a text field, then the field in your other table will also need to be a text field, and you may be storing lots more data than you need to. Text fields typically take at least 3 bytes if there is any data, and an autonumber only takes 4. The other advantage of using a autonumber for the key is that if you decide you want to tweak the number a little bit then you simply change the entry in the text field of the description and Bob's your uncle.

    Actually, the field doesn't have to be an autonumber - if you will never have more that 255 UOMs then you could make it a byte field and do your own autonumber by storing the current (or next) number in a table, and looking it up and incrementing it before you add the UOM record. And it really should work if you want to just have the text field and nothing else - I presume you will be using a combo box and prompting to find out whether they just typed something in error, or whether they really want to add a new UOM. Hope this doesn't muddy the waters too much.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: RecordSet.AddNew (A97 SR-2)

    Thx for your reply Wendell,
    Actually I have a Combo box linked to the Units of Measure table (tblUOM). You are correct in assuming it has only one field. I am trying to create code behind the Not_In_List event to add an entry to the tblUOM table, not another table. (Missunderstanding was probably my Aussie accent).

    Hope this explains much more betterer.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RecordSet.AddNew (A97 SR-2)

    Try something like the following:

    Private Sub cboUOM_NotInList(NewData As String, Response As Integer)
    Dim rst As Recordset
    If MsgBox("Do you want to add '" & NewData & "' to the list?", vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If
    Set rst = CurrentDb.OpenRecordset("tblUOM")
    With rst
    .AddNew
    !UOM = NewData
    .Update
    .Close
    End With
    Set rst = Nothing
    Response = acDataErrAdded
    End Sub

    or

    Private Sub cboUOM_NotInList(NewData As String, Response As Integer)
    Dim strSQL As String
    If MsgBox("Do you want to add '" & NewData & "' to the list?", vbQuestion + vbYesNo) = vbNo Then
    Exit Sub
    End If
    strSQL = "INSERT INTO tblUOM (UOM) Values ('" & NewData & "')"
    CurrentDb.Execute strSQL, dbFailOnError
    Response = acDataErrAdded
    End Sub

    Take good note of the use of quotes. The end of the strSQL string in the second example is
    <pre>Values ('" & NewData & "')"</pre>

    because the value to be added must be enclosed in quotes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •