Change Form Record Source

[mjoachim]

I am trying open a form from VBA to view a record that was moved from the original table to an Archive Table. I am having a hard time changing the record source of the form to look at the Archive table instead of the original table. If I open the form first, I am able to change the record source, but am then unable to filter to the selected record since the form is already open.

Does anyone know if I am able to change the form properties before the form is open?

Here is the code that am I currently working with:

Public Sub RptEquipNumber_Click()
 
    Forms!Equipment.Form.RecordSource = "Equipment Archive"
    DoCmd.OpenForm "Equipment", , , "EquipNumber ='" & RptEquipNumber & "'", acFormEdit, , SrcRcd = "Equipment Archive"
 
End Sub

[NeoPa]

Put code in the Form_Open() event procedure to update the RecordSource, and the Filter if necessary, of the form when it's opened.

[mjoachim]

Thanks for the tip. With that advice, I looked further into how to pass an argument and seem to have everything working now.

On click even from a report field:

Public Sub RptEquipNumber_Click()
    ArEq = RptEquipNumber
 
    DoCmd.OpenForm "Equipment", , , , acFormReadOnly, , ArEq
 
End Sub

On Load event of the form using the passed argument:

Public Sub Form_Load()
    Dim ArchEq As String
    ArchEq = Nz(Me.OpenArgs, 0)
 
    If ArchEq > 0 Then
    Forms!Equipment.Form.RecordSource = "Equipment Archive"
    Me.Filter = "EquipNumber =" & ArchEq
    Me.FilterOn = True
    End If
 
    Me.EquipNumber.SetFocus
End Sub

If you see any more efficient way that I should be handling this, please let me know!

[NeoPa]

MJoachim:
Thanks for the tip. With that advice, I looked further into ...

What a perfect attitude. Lovely to see that you found your way from a small tip and have a working solution. I'm very pleased for you.