Solved Problems using openargs to filter on a new form

[HillTJ]

So, I have a form containing a subform. That subform is a continuous form including a control to open a popup/modal form to display filtered records, based upon the [HiveID]. The PK of that subform.

I use DoCmd.OpenForm "frmHivePhotos", , , "HiveID = " & HiveID, , , Me.[HiveID] on the subform to open the pop up form and display only the filtered photos. And on the pop up form i use the openargs value passed as the FK for adding new records. This works fine for filtering existing records, but does not work on a new record where there is clearly no existing HiveID to filter on. In this instance, the pop up form opens but shows no fields at all.

Clearly, i have my logic wrong & would appreciate recalibrating.

 

[theDBguy]

You could amend your code to check if there are any records and remove the filter if there are none.

 

[Eugene-LS]

but does not work on a new record where there is clearly no existing

Try:

    If Me.NewRecord = True Then ' On new record
        DoCmd.OpenForm "frmHivePhotos", , , , acFormAdd
    Else ' on an existing record
        DoCmd.OpenForm "frmHivePhotos", , , "HiveID = " & Me.HiveID, , , Me.HiveID
    End If

 

[HillTJ]

On form_load event? Consider that i still need to 'link' a new record to the 'calling' record by the pk which i pass as an openarg. I guess i can still pass the openarg even if the form is not filtered.

 

[Eugene-LS]

On form_load event?

No.
On "Open Selected Record" procedure.

 

[HillTJ]

Will do. Thanks

 

[HillTJ]

All, this is what I came up with;

Private Sub bttnGotoPhoto_Click()


    If DoesRecordExist(Me.HiveID) Then


        DoCmd.OpenForm "frmHivePhotos", , , "HiveID = " & HiveID, , , Me.[HiveID] ' openargs to pass the hive name to frmHivePhotos
   
    Else
   
        DoCmd.OpenForm "frmHivePhotos", , , , acFormAdd, , Me.[HiveID]
   
    End If
       
End Sub

it calls the following routine to check if the value exists;

Private Function DoesRecordExist(HiveID As Long) As Boolean

On Error GoTo MyErrorProc:
  
    Dim db As Database
    Dim rs As DAO.Recordset
  
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblHivePhotos", dbOpenDynaset)
  
  
    rs.FindFirst "HiveID = " & HiveID
        If Not rs.NoMatch Then
            DoesRecordExist = True
       Else
            DoesRecordExist = False
    End If
  
ExitError:
      
        Set rs = Nothing
        Set db = Nothing
        On Error Resume Next
        Exit Function

MyErrorProc:
      
ErrorHandler:
    Call DisplayErrorMessage(Err.Number, "DoesRecordExist")
  
        Resume ExitError

End Function

Would appreciate any feedback if anyone can see any loopholes. Thanks to all.

 

[ebs17]

DoCmd.OpenForm "frmHivePhotos", , , "HiveID = " & Me.HiveID, , , Me.HiveID

In Form frmHivePhotos, for example in Form_BeforeUpdate:

If Not IsNull(Me.OpenArgs) Then
   If Me.NewRecord Then Me.txtHiveID = CLng(Me.OpenArgs)
End If