Requery recordset in subform by clicking listbox on mainform

[MrHJL]

Hi guys,

I have following troubles. I did made an access database that worked with macros just fine so far, but I kinda mixed vba with macros and sometimes that makes it hard to combine. So I started writing the macros into vbas. For the most part that wasn't much of a problem, but following function just won't work for me.

I've got a listbox in my mainform and by screen.activecontrol it used to give a selcted value to a subform (macro: GotoRecordset). Then the recordset was requeried also by a macro. I tried different solutions so far I will only write a few down. Most of them didn't give me an error but they won't work neither. The shown recordset of the subform stays with the first record and won't change.

Dim sub3 As Form
Set sub3 = Forms!mainform!subform.Form
sub3.Visible = True
sub3.RecordsetClone.FindFirst "[GSVB] = " & Me!List126
sub3.Refresh
sub3.Requery

(Here I tried whether refresh will give me a differnt outcome and whether the defintion of sub3 might change anything)

Forms!mainform!subform.Form.Visible = True
Forms!mainform!subform.Form.Recordset.FindFirst "[GSVB] = " & Me!List126
Forms!mainform!subform.Form.Requery

(Here I changed Me! to the full written form and substituded activecontrol)

Me!subform.Visible = True
Me!subform.Recordset.FindFirst "[GSVB] = " & Nz(Screen.ActiveControl, 0)
Me!subform.Form.Requery

(That's how it looked in the beginning, except for Recordset used to be RecordsetClone)

I have no idea what's wrong here, since there are no errors either. Still they won't change my subform.

[jforbes]

RecordsetClone will make a copy of a recordset in memory for you to perform actions against. It can be a copy of the current form's Recordset, but it is only a copy. Once you have found the record you are looking for in the Clone, you then need to set that record as the Active record on the Form. A common way to do this is use a Bookmark

This is the example code from the above url. The highlighted piece is the magic that you are looking for:

Private Sub cmdFindContactName_Click() 
 
     Dim rst As DAO.Recordset 
     Dim strCriteria As String 
 
     strCriteria = "[ContactName] Like '*" & InputBox("Enter the " _ 
     & "first few letters of the name to find") & "*'" 
 
     Set rst = Me.RecordsetClone 
     rst.FindFirst strCriteria 
     If rst.NoMatch Then 
          MsgBox "No entry found.", vbInformation 
     Else 
          Me.Bookmark = rst.Bookmark 
     End If 
 
     Set rst = Nothing 
 
End Sub

Taking the sample code and merging it with yours would give something like this. I don't guarantee that it will work, but it probably will.

Private Sub cmdFindContactName_Click() 
 
     Dim rst As DAO.Recordset 
     Dim strCriteria As String 
 
     strCriteria = "[GSVB] = " & Me!List126
 
     Set rst = Me.RecordsetClone 
     rst.FindFirst strCriteria 
     If rst.NoMatch Then 
          MsgBox "No entry found.", vbInformation 
     Else 
          Me.Bookmark = rst.Bookmark
     End If 
 
     Set rst = Nothing 
 
End Sub

[MrHJL]

I tried it but it didn't work.

Just to describe the situation again: The listbox is in the mainform and the recordset to be changed is that of a subform. I already have a functional code working that changes the recordset of the mainform from a listbox in the mainform, that is not the issue.

Could you rewrite your suggestion to a form that would do that very task @jforbes?

[zmbd]

somewhat vague here...

If you are altering the original recordsource for the main and subforms... that is to say, you are changing the SQL that established the recordset for the form or controls regardless if it's a filter or where conditional...

then

If you change the recordsource for the mainform:
you have to force the mainform to requery
then you have to force the subform to requery

Think of the form (or control therein) as being a snap-shot (view) of the current state of the recorsource. If you alter he record set by adding, deleting, or in a multi-user environment when another user changes a record, the current records shown in the form/control do not reflect that change.

Once you have requeried the forms, then you can move to the record of interest.

However, this doesn't sound like a normal parent/child form relationship where the two forms are linked on the related fields. When done this way, going to a record in the parent form will cause the child form to show the related records.

So, what is it you are doing here?