Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Updating a Field (Access 2000)

    Below is my code. I need to be able to simultaneously insert a date (given by parameter) into two different records. For example...

    I am terming Bill Clinton 1/1/04 and hiring George Bush 1/1/04. I only want the user to enter the date once. So the queries are updating the term date and than the second query is inserting a new record with the paramter given name and the same term date as the first one.

    I have created a dlookup statement, but it is not reading the underlining query to insert the new date into the new record.

    Any ideas?

    Private Sub List0_DblClick(Cancel As Integer)

    DoCmd.SetWarnings (WarningsOff)

    If MsgBox("Are you sure you want to add a new employee", vbYesNo, "Add New Employee") = vbYes Then
    Me.txtPrimaryID = List0.Column(4)
    Me.txtPositionID = List0.Column(3)
    Me.txtReportsTo = List0.Column(0)


    DoCmd.OpenQuery "qryNewHireUpdate"


    Me.txtTermDate = DLookup("term_date", "qryNewHireTermDateLookup", "primary_id = txtPrimaryID")


    DoCmd.OpenQuery "qryNewHireInsert"

    MsgBox "Your new hire has been added", vbInformation, "New Hire Added"

    DoCmd.Close
    DoCmd.SetWarnings (WarningsOn)

    Else
    End If

    DoCmd.SetWarnings (WarningsOn)

    End Sub


    Thanks

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

    Re: Updating a Field (Access 2000)

    Try changing the line

    Me.txtTermDate = DLookup("term_date", "qryNewHireTermDateLookup", "primary_id = txtPrimaryID")

    to

    Me.txtTermDate = DLookup("term_date", "qryNewHireTermDateLookup", "primary_id = " & Me.txtPrimaryID)

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Field (Access 2000)

    It is still not writing to that field. My second query runs off of that field and it is not picking anything up to insert into the new record.

    It seems like it is not saving the record before going on.

    Thanks

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

    Re: Updating a Field (Access 2000)

    I don't think I understand what you are doing. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Field (Access 2000)

    See attached. You start with an open position that has a requistion number. You double click the record in the list box to choose. You then assign the hire date (which is the same as the term date of the open position). I just want to be able to only have the user enter the date once because the term date of the open position should be the effective date of the new position so there is now overlap.

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

    Re: Updating a Field (Access 2000)

    The lookup query is circular: you have the text box on the form as criteria, and use the lookup query to populate the same text box <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Remove the criteria from the lookup query.
    You must recalculate the form instead of requerying the text box.

    See attached version.

    Note: why not let the user enter the date on the form?

  7. #7
    Star Lounger
    Join Date
    Mar 2003
    Location
    Summerville, South Carolina, USA
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating a Field (Access 2000)

    Makes sense.

    I just put all of the parameters as text boxes on the form instead of pop up query parameters. I think this will be less confusing for the end user. And probably less mistakes.

    Thanks Hans.

Posting Permissions

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