I am attempting to create several forms for my database. The frmMain form will have access to all of the records from my tblData. However, I would like to then go to a 2nd form filtered by an account number or customer name from frmMain form. What would be the best way to go about this? Thanks.

Send the criteria on a button click event.

There are sooooooo many posts on how to do this.

How are you selecting the data to filter your results? Is it a text field or an integer field?


mmcdonal

Currently the data field is a number, long integer.

This is how I was able to do it with the help of the command wizard.
I'm trying to understand the linking/filtering logic.

Private Sub cmdUnderwriting_Click()
On Error GoTo Err_cmdUnderwriting_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmUnderwriting"

    stLinkCriteria = "[txtAccountNumber]=" & Me![txtAccountNumber]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Call Close_AccountData
    Form_frmAccountData.Refresh
    Form_frmAccountData.Requery

Exit_cmdUnderwriting_Click:
    Exit Sub

Err_cmdUnderwriting_Click:
    MsgBox Err.Description
    Resume Exit_cmdUnderwriting_Click

End Sub

If you read the DoCmd line as part command, part query parameter, it makes sense. In pseudo code the line says:

Open form frmUnderWriting, but limit the records to WHERE txtAccountNumber is equal to the txtAccountNumber currently displayed on this form.

Consider the underlying SQL statement to be:

SELECT *
FROM (the table you are basing frmUnderWrting on)
WHERE txtAccountNumber = Some Value from the first form

Let's say the txtAccountNumber (which is an Integer, by the way and should not be prefixed with "txt") is 8, and the table the frmUnderWriting is based on is called tblUnderWriting.

SELECT *
FROM tblUnderWriting
WHERE [txtAccountNumber] = 8

That is basically what is going on with the code.




mmcdonal

That makes more sense. Thank you for your help.