Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2006
    Location
    Jhongli, Taiwan
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    datasheet combo box refresh (2000/SP-3)

    I use an Access 2000 database to schedule persons who assist lecturers.The tblAssignments table for the lecturing assignments has a LastAssignment date field as well as an AssistantID field for each record. A separate tblAssistants table has a LastAssisted field for the last date each AssistantID who assist lecturers (on a rotational basis) was last assigned to assist. When a new lecture is scheduled, the LastAssignment date is simply changed for existing AssignmentID's (the lecturers themselves) rather than adding new records to the tblAssignments table. A subformform called AssistantDetailsSubform in datasheet view is used for editing the lecturing assignments. A query based on the tblAssistants table lists the AssistantID and LastAssisted fields in a combo box in ascending order according to the date. So when the combo box is clicked, the assistant having not assisted for the longest time appears at the top of the list. The following code automatically updates the blAssistants table date field when a new assistant is selected for the tblAssignments record:

    Sub AssistantID_AfterUpdate()
    Me.Parent!AssistantDetailsSubform!LastAssisted.Val ue=Me!LastAssignment.Value
    End Sub

    All works well except that when the AssistantID combo box is re-opened in another record for entering a new assistant, the LastAssisted date for the tblAssistants is now not listed in ascending order. Rather, the original assistant is still in the same location in the list as it was before the date was changed. The date for the last record has indeed changed but I cannot figure out how to requery or refresh the combo box in code when the form is in datasheet view.

    Any suggestions how to alter the above code?

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

    Re: datasheet combo box refresh (2000/SP-3)

    Welcome to Woody's Lounge!

    Does the following work for you?

    Sub AssistantID_AfterUpdate()
    With Me.Parent!AssistantDetailsSubform!LastAssisted
    .Requery
    .Value=Me!LastAssignment.Value
    End With
    End Sub

  3. #3
    Lounger
    Join Date
    Sep 2006
    Location
    Jhongli, Taiwan
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datasheet combo box refresh (2000/SP-3)

    Thank you Hans! However, the list still does not appear in date sequence after the Update for some reason... Anything else in your 'bag of tricks' that might work?
    Brady

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

    Re: datasheet combo box refresh (2000/SP-3)

    I'm afraid we'd have to see the database. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Lounger
    Join Date
    Sep 2006
    Location
    Jhongli, Taiwan
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datasheet combo box refresh (2000/SP-3)

    Hans, attached you will see the School.mdb problem

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

    Re: datasheet combo box refresh (2000/SP-3)

    I think the cause of the problem is that you set a value in a form. When you requery the combo box, the underlying table hasn't been updated yet. Try the following instead. It updates the tblAssistants table, then requeries both the combo box and the other subform.

    Private Sub AssistantID_AfterUpdate()
    Dim strSQL As String
    On Error GoTo HandleErr

    ' automatically updates tblAssistants table date field when
    ' assistant record entered into tblAssignments table
    strSQL = "UPDATE tblAssistants SET LastAssisted=#" & _
    Format(Me.LastAssignment, "mm/dd/yyyy") & "# WHERE AssistantID=" & _
    Chr(34) & Me.AssistantID & Chr(34)
    CurrentDb.Execute strSQL, dbFailOnError
    Me.AssistantID.Requery
    Me.Parent!AssistantDetailsSubform.Requery

    ExitHere:
    Exit Sub

    ...

  7. #7
    Lounger
    Join Date
    Sep 2006
    Location
    Jhongli, Taiwan
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: datasheet combo box refresh (2000/SP-3)

    Hans, you are indeed a genius... I guess you get that all the time. Anyway, I thank you TREMENDOUSLY. Also thanks for the explanation as to why this is so. Even though the code you wrote is a little out of my league, it sure is nice to know why I have been banging my head against the wall for two days. Thanks again.
    Sincerely,
    Brady

Posting Permissions

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