Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Up date not working (A2002)

    Hi all,

    Has anything changed for the following since 2000

    Private Sub ShipperID_NotInList(NewData As String, Response As Integer)
    MsgBox "There is no record of this shipper, please click OK to set up a new account", vbOKOnly, "Add New Record"
    DoCmd.OpenForm "frmShipper", , , , , acDialog
    ShipperID.Undo
    ShipperID.Requery
    End Sub

    I have it in the Not in List as an event procedure but if I type in a new name I get the general error message saying to select from the names already in the list.

    Thanks

    Steve

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

    Re: Up date not working (A2002)

    The Response argument of the NotInList event procedure can have one of three values:
    <UL><LI>acDataErrDisplay - display the standard error message; this is the default.
    <LI>acDataErrAdded - tells Access that you have added the item to the row source; suppresses the standard error message and automatically requeries the list.
    <LI>acDataErrContinue - just suppress the standard error message.[/list]So instead of

    ShipperID.Undo
    ShipperID.Requery

    you should have

    Response = acDataErrAdded

    This has not changed between versions of Access.

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Up date not working (A2002)

    Thanks Hans I will try this, the code I posted though was from a form on another db I created a couple of years ago which does work.

    Cheers

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Up date not working (A2002)

    Hi Hans

    Sorry to trouble you (as usual)

    I already had the Added statement there already but it still would not work for me. decided though that it would perhaps be better to update the table from the form, this is not a problem for me as all my dbs are for personal use in the course of my days work.

    Searching around help files etc. I found an example of a form that updated one field in a table, I however needed to update 5 fields, I have now arrived at the following which opens a 'Would you like to add this new record' box but when you click OK I get the following.

    runtime error '3265' item not found in this collection

    this is the complete code I now have

    Private Sub cboShipper_NotInList(NewData As String, Response As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblshipper")

    If MsgBox(NewData & " is not in the selection provided." & vbCrLf & vbCrLf & "Would you like to add it?", vbQuestion + vbYesNo, "Unknown data") = vbYes Then
    With rs
    .AddNew
    .Fields("cboShipper") = StrConv(NewData, vbProperCase)
    .Fields("txtAddress") = StrConv(NewData, vbProperCase)
    .Fields("txtCountry") = StrConv(NewData, vbProperCase)
    .Fields("txtContact") = StrConv(NewData, vbProperCase)
    .Fields("txtTelephone") = StrConv(NewData, bProperCase)
    .Update
    .Close
    End With
    Response = acDataErrAdded
    Else
    Response = acDataErrContinue
    End If

    End Sub

    I have checked that all of the fields mentioned match those above but as usual I am out of my depth. Could you please let me know what you think I am doing wrong (apart from being allowed near MS Access)

    Thanks

    Steve

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

    Re: Up date not working (A2002)

    It seems bizarre to set Shipper, Address, Country, Contact and Telephone *all* to the value entered in the combo box (NewData), So if the user entered "Michael Jackson" in the combo box,, you would have

    Shipper = "Michael Jackson"
    Address = "Michael Jackson"
    Country = "Michael Jackson"
    Contact = "Michael Jackson"
    Telephone = "Michael Jackson"

    I don't think that is what you intend, is it?

  6. #6
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Up date not working (A2002)

    No, I did not rfealise that would happen. I wanted to be able to up date trhe name, address, country, contact and telephone fields on the table. Am I asking the impossible here?

    Thanks

    Steve

  7. #7
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Up date not working (A2002)

    Thanks, I think I have a solution, it is not 'pretty' but as it is only for me it does not really matter, what I did was to go back to my original idea of opening the form and used the following

    Private Sub cboShipper_NotInList(NewData As String, Response As Integer)
    MsgBox "This shipper does not exist, a new record will now be added", vbOKOnly, "Add new record"
    DoCmd.OpenForm "frmShipper", , , , , acDialog
    cboShipper.Undo
    cboShipper.Requery

    End Sub

    Cheers

    Steve

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

    Re: Up date not working (A2002)

    Since the item you want to add requires other fields to be filled in too, you will have to use the approach from the first post in this thread: display a form in which the user can provide the information. Here is a slightly modified version:

    Private Sub ShipperID_NotInList(NewData As String, Response As Integer)
    If MsgBox("There is no record of this shipper." & vbCrLf & "Do you want to set up a new account?", _
    vbYesNo + vbQuestion, "Add New Record") = vbYes Then
    DoCmd.OpenForm FormName:="frmShipper", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=NewData
    Response = acDataErrAdded
    Else
    ShipperID.Undo
    Response = acDataErrContinue
    End If
    End Sub

    As you can see, I pass the name entered by the user in the OpenArgs argument of DoCmd.OpenForm. You can use this in the On Load event of the frmShipper form to fill in the Shipper name, so that the user doesn't have to retype it:

    Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
    Me.Shipper = Me.OpenArgs
    End If
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Up date not working (A2002)

    Thanks Hans

    Thats much 'prettier', one thing though at the start I substituted ShipperID for CboShipper as the reference and again after the Else statement but when I tried it on the form it errored on cboShipper.Undo. I removed that line completely and everything seems to work fine, so my question is, is what part was that supposed to have played in this event?

    As usual my total thanks

    Steve

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

    Re: Up date not working (A2002)

    The purpose of Undo was to undo the changes if the user clicks No in the message box, but if it works for you without it, that's fine.

Posting Permissions

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