Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add a record on the Fly... (2000)

    I have a daabase that when you open the customer form has a subform called issues in the sub form there is a field called subcontractor. The way I have it set if I double click the filed it pulls up the subcontractor form to add a new subcontractor but I think I do not have the code complete because when the form pops up I have to click to add the new record and when I close the form the new record is in the drop down list but I have to then pick it.

    What I want to do is when the Subcontractor form pops up I can just type in the new record then when I close it I want that info to be in the field that I originally double clicked.

    Thanks in advance for the help.

    I have posted the stripped DB.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Add a record on the Fly... (2000)

    I attach a modified version.

    I have actually done it a different way. Instead of using the db click event to force the user to go off to a form to add the new record, the On Not In List Event asks whether you want a new record to be created, then adds the new record using code.

    Because there is only one field for a new subcontractor, everything needed is already available. I would only force the user to a form if there were other fields required.

    Private Sub Subcontractor_NotInList(NewData As String, Response As Integer)
    Dim db As DAO.database
    Dim rs As DAO.Recordset
    Dim sql As String

    Dim newEntry As Integer
    Dim Title As String
    Dim msgDialog As Integer
    Const MB_YESNO = 4
    Const MB_ICONEXCLAMATION = 48
    Const MB_DEFBUTTON1 = 0, IDYES = 6, IDNO = 7

    Title = "Subcontractor not in list"
    msgDialog = MB_YESNO + MB_ICONEXCLAMATION + MB_DEFBUTTON1
    newEntry = MsgBox("The subcontractor you have entered is not in the list. Do you want to add a new subcontractor to the list ?", msgDialog, Title)
    If newEntry = IDYES Then
    Set db = CurrentDb
    sql = "Select * from subcontractor"
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    rs.AddNew
    rs!Subcontractor = NewData
    rs.Update
    rs.Close
    Set db = Nothing
    Set rs = Nothing
    Response = DATA_ERRADDED
    End If

    End Sub

    When you add a new record this way, the combo is automatically requeried.

    Added later - you might get references problems with running this code. Open any code window, then go to Tools...References.. and if it is not already ticked, find Microsoft DAO 3.6 Object Library and tick it. If you dont have 3.6, tick whatever version of DAO you have.
    Attached Files Attached Files
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Oct 2001
    Location
    Blakeslee, Pennsylvania, USA
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a record on the Fly... (2000)

    I tried putting that code in for the sub form and also tried it in the customer form for the code for the one filed and I also tried it in the subcintractor form and it does not work for me when I double click nothing happens now. It does open a blank form to enter the new sub but when you close the form you still have to select the new sub from the list. What I am trying to ge is that new sub in that field after the add.


    I just noticed the way it is set not for double click just type it in. That works great.
    Thanks

Posting Permissions

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