CODEWITHSUNDEEP
ms-accessvba
user2204315
user2204315

Reputation:

Trying to open a form using DoCmd.OpenForm based on sub form control

I have an unbound form with an unbound control (Text0). In Text0 I enter a RefNo. I want to open another form (frmDisclosure) which has a subform (frmRefNosList) containing the RefNo. My code for opening frmDisclosure is: Private Sub Command8_Click()

Dim Refce As Variant

DoCmd.OpenForm "frmDisclosure"
Forms!frmDisclosure.FilterOn = False
Refce = "Forms!frmDisclosure!frmRefNosList.Form!RefNo"
DoCmd.OpenForm "frmDisclosure", acNormal, "", Refce & " = " & Me.Text0, , acNormal

End Sub

Upvotes: 0

Views: 1219

Answers (2)

Matt Hall
Matt Hall

Reputation: 2412

After our discussion in chat, I mocked-up something that hopefully looks like what you were trying to do:

Link to Access file mock-up.

To others that might read this later here are a few clarifications:

  • Form Form1 has a textbox called Text0 on it and a button.
  • When the button is pressed it is supposed to open Form2 that has a subform on it.
  • Form2 is then supposed to open to a certain record and then filter the records shown on its subform all using value provided in Text0

Here's what my mock-up of Form1 looks like:

enter image description here

So when I provide a RefNo and click the button...

enter image description here

... the following code (annotated) is run on the click event of that button:

Private Sub cmdOpen_Click()

    ' check user has provided a RefNo in the textbox...
    If _
        Me.Text0 = "" Or _
        IsNull(Me.Text0) _
    Then

        ' ...if a RefNo has not been provided, give an error message
        MsgBox "Please provide a Ref No.", vbExclamation Or vbOKOnly, "Disclosure Search"

    Else

        ' ...if a RefNo has been provided, look in the table that lists an applicant's
        ' forms and return the ApplicantID (or equivalent field you're using) for the RefNo provided
        Dim varApplicantID As Variant
        varApplicantID = DLookup("ApplicantID", "tblRefNos", "RefNo=" & Me.Text0)

        ' ...check that an applicant record can be actually be found from the RefNo provided
        If _
            IsNull(varApplicantID) _
        Then

            '... if an applicant record is not found, give an error message
            MsgBox "Could not find an applicant with the RefNo provided.", vbExclamation Or vbOKOnly, "Disclosure Search"

        Else

            '... if an applicant record is found, open Form2 to that applicant's record
            DoCmd.OpenForm "Form2", , , "ApplicantID=" & varApplicantID

            '... and then filter the subform on Form2 by the RefNo provided
            Forms!Form2!frmApplicantForms_sub.Form.Filter = "RefNo=" & Me.Text0
            Forms!Form2!frmApplicantForms_sub.Form.FilterOn = True
            Forms!Form2!frmApplicantForms_sub.Form.Requery

        End If

    End If

End Sub

This should result in Form2 opening to the correct applicant record derived from the RefNo provided and also filter the subform by the RefNo provided:

enter image description here

Your exact project layout may differ to what I've mocked-up, so some of this may need to be tweaked for your set up, but hopefully the principles I've illustrated here will be easy enough to translate.

Good luck!

Upvotes: 1

user2204315
user2204315

Reputation:

This was not the answer I wanted but: I created a query that yields the ID number of frmDisclosure corresponding to Text0 on the unbound form. I then retreive that ID number using DLookup from within the unbound form and open frmDisclosure that way. It would have been nicer to do it all from within the unbound form.

Upvotes: 0

Related Questions