Requerying Combos Causes Form to Revert to First Record

[MeldrethMan]

I'm using a command button on a Client form to move to a second form that displays the current option lists for a number of combos on the Client form and its Jobs subform. Each combo's NotInList event just instructs the user to do this.

The second form has this update/close procedure

Private Sub UpdateDropDowns_Click()

'If Client form isn't loaded just exit
If CurrentProject.AllForms("frmClients").IsLoaded = False Then
DoCmd.Close acForm, "frmDropDownMaintenance", acSaveYes
Exit Sub
End If

'Otherwise requery all combos on Client form
Dim frm As Form, sfm As Form
Dim ctl As Control

Set frm = Forms!frmClients
Set sfm = frm!sfmJobs.Form

For Each ctl In frm.Controls
If ctl.Tag = "cbx" Then ctl.Requery
Next

For Each ctl In sfm.Controls
If ctl.Tag = "cbx" Then ctl.Requery
Next

DoCmd.Close acForm, "frmDropDownMaintenance", acSaveYes

Set sfm = Nothing
Set frm = Nothing

End Sub

This works fine except that the Client form has now jumped back to the one that appears on first opening it. This is just an irritation. How can I prevent it?

 

[dhookom]

Rather than requery, you could try something like:

For Each ctl In frm.Controls
    If ctl.Tag = "cbx" Then
        ctl.RowSource = ctl.RowSource
    End If
Next

Duane
Hook'D on Access
MS Access MVP

 

[MeldrethMan]

Tried this thanks but it still reverts to default record.

Is there a technique using a bookmark? Don't know how to implement it.

 

[PHV]

You may try this:

Dim tmpVal
For Each ctl In frm.Controls
    If ctl.Tag = "cbx" Then
        tmpVal = ctl.Value
        ctl.RowSource = ctl.RowSource
        ctl.Value = tmpVal
    End If
Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MeldrethMan]

Duane

Still not doing it but thanks for your input. I have a workaround by saving the current ContactID to varID, closing the Client form then reopening it to this bookmark.

Command button to leave frmClients and go to frmDropdownMaintenance

varID = Me.ContactID
Debug.Print varID
DoCmd.Close acForm, "frmClients"
DoCmd.OpenForm "frmDropdownMaintenance", acNormal, "", "", , acNormal


Command button to close frmDropdownMaintenance and return to frmClients

DoCmd.Close acForm, "frmDropDownMaintenance", acSaveYes
DoCmd.OpenForm "frmClients"


frmClients Open Event

Dim strBookmark As String
If Not IsEmpty(varID) Then
strBookmark = "ContactID = " & varID
With Me.RecordsetClone
.FindFirst strBookmark
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If

 

[jkl0]

The below might work:
After saving the current ContactID to varID

DoCmd.GoToRecord , , acGoTo, varID