Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Requery for Forms and Combo boxes (Access2002)

    I have a frontend/backend shared database. If a new record is entered it doesn't show up in the select record combo until the form is refreshed manually. The same for the combo boxes. I need to be able to requery? the tables on individual frontends so they can see the most current records and choices for the combo boxes. How would I do this? TIA, Tammy

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery for Forms and Combo boxes (Access2002)

    Tammy
    You need to requery the combo box.
    I would imagine from your post, the combo resides on the same form (& same table for both) as where you create the new record from.
    If it does, then you only need to requery. ie:

    DoCmd.GoToRecord , , acNewRec (Create a new record
    DoCmd.RunCommand acCmdSaveRecord (Save record)
    Me.MyCombo.Requery (Requery the combo)

    This will work if the source table is the same for the form and combo.
    If it's not, post back with more details.

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Brookings, South Dakota, USA
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery for Forms and Combo boxes (Access2002)

    Dave,
    This is the unbound combo box that I use to find a record. I did try to put the code in but it closes the database down when I use it.

    Private Sub cboSelect_AfterUpdate()
    'Combo box to find record on main form
    'Written by Helen Feddema 2/17/2002
    'Last modified 2/17/2002

    On Error GoTo ErrorHandler

    Dim strSearch As String

    strSearch = "[StudentsStudentId] = " & Me![cboSelect]

    'Find the record that matches the control
    Me.RecordsetClone.FindFirst strSearch
    Me.Bookmark = Me.RecordsetClone.Bookmark

    ErrorHandlerExit:
    Exit Sub

    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub

    This is a combo box used on a form based on the academicdetails table that gets its data from the academics table.

    Private Sub cboClass_NotInList(NewData As String, Response As Integer)

    If MsgBox("" & NewData & " does not occur in the list." & vbCrLf & _
    "Do you want to add it?", vbYesNo + vbQuestion) = vbYes Then
    Dim strSQL As String
    strSQL = "INSERT INTO tblAcademics (Academicsclass) VALUES (" & _
    Chr$(34) & NewData & Chr$(34) & ")"
    CurrentDb.Execute strSQL
    Response = acDataErrAdded
    Else
    cboClass.Undo
    Response = acDataErrContinue
    End If

    End Sub

    I don't really know code, but am trying to learn. Thanks, Tammy

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

    Re: Requery for Forms and Combo boxes (Access2002)

    Hi Tammy,
    Since Dave is in the UK, he's likely taken to his pillow. I'll see if I can help in the mean time. What do you mean by "it closes the database down" - do you get a message that says Access is going away, or do you just get a code error of some sort? You might want to put a breakpoint in the code and then step through it to see where it is failing.

    Your code looks like it should work for the most part, but it looks like it may have been created for Access 97. Access 2002 generates code that looks like this:
    <pre>Private Sub Combo0_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[UniqueID] = " & Str(Nz(Me![Combo0], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    </pre>

    The code Dave gave you would go in the NotInList routine right before the Else statement

    I don't really like to use the RecordsetClone method. My objection to it is that a user can move to the wrong record using the PageUp or PageDown keys and edit something they didn't mean to (I've also seen it occasionally fail on Access 2000 systems). I prefer to apply a filter to the form based on the combo box, so that only one record is ever visible to the user.
    Wendell

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Requery for Forms and Combo boxes (Access2002)

    Tammy.
    As Wendell pointed out, I would have been in slumberland with the neccessary help of a couple of beers <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    I have a form "frmDetails" which the control source is tblDetails.
    On there I have an unbound combo which is unbound and only contains two lines:

    Me.RecordsetClone.FindFirst "[EstimateNo] = " & Me![Cmbsearch] & ""
    Me.Bookmark = Me.RecordsetClone.Bookmark

    This I use on the after update property.
    Perhaps you could try this:

    Me.RecordsetClone.FindFirst "[StudentsStudentID] = " & Me![cboSelect] & ""
    Me.Bookmark = Me.RecordsetClone.Bookmark

    You could add your own error checking to it.

    Dependant on where you enter the new record, you would have to requery the form after this event (adding new)
    (do you use a command button or something ?)


    Let us know how you get on.

Posting Permissions

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