Using a command button to open and filter a form

[Johnny Drama]

Question:

I have two forms: Main and Testing. The Main form functions as a main menu and I'm creating command buttons to open other forms. I want the first button to open the Testing form and apply a filter at the same time.

The testing form pulls it's data from the testing query. I would like to filter on the field named "category". Any thoughts? I tired to used the command button wizard, but it errors out saying that I'm trying to link two forms that can't be linked.

Thoughts?

Thanks

 

[Mr. B]

Use the On Click event of your command button and use code like:

docmd.openform "FormNameNere", , ,"category = "somevalue""

The "somevalue" will have to be in the correct format to pass it to the next form. If the value for the filter of the category field is a string the the statemtne will work as above. If the value is numeric then the Where part will need to be: "category = Number". (No additional quotes)

 

[SOS]

Use the On Click event of your command button and use code like:

docmd.openform "FormNameNere", , ,"category = "somevalue""

The "somevalue" will have to be in the correct format to pass it to the next form. If the value for the filter of the category field is a string the the statemtne will work as above.

No, it wouldn't. You can't put double quotes together like that.

docmd.openform "FormNameNere", , ,"category = 'somevalue'"

would work however.

 

[Johnny Drama]

That didn't quite work. Clicking the button on the Main form opened up a criteria dialog box, which I don't want...I want the criteria to be passed directly. Also, even when I did enter the criteria I was looking for, it didn't get passed to the testing form, so all categories were still present.

Any other thoughts while I try and muddle through?

 

[SOS]

That didn't quite work. Clicking the button on the Main form opened up a criteria dialog box,

That means you didn't spell something right, the form isn't open, or the item doesn't exist.

 

[Johnny Drama]

Found the problem, but it still doesn't filter properly. The issue seems to be the that I open the Testing form with the Main form, but when that happens the Testing form isn't passing the criteria to the query...so I guess I have to figure out a way to pass the criteria twice. Does that sound about right?

 

[Mr. B]

Although I did actually post the VBA statement to open the form incorrectly, the modification that SOS provided to my original posting is all you need to do. Be assured that when created correctly, the VBA code will open the form to the specific record.

Can you post your database or at least the code you now have?

Perhaps we can help if we can see what you are doing.

 

[SOS]

Found the problem, but it still doesn't filter properly. The issue seems to be the that I open the Testing form with the Main form, but when that happens the Testing form isn't passing the criteria to the query...so I guess I have to figure out a way to pass the criteria twice. Does that sound about right?

No it doesn't sound right. If a form is based on the query then passing the criteria when opening the form passes it to the query that the form is using.

 

[Johnny Drama]

Unfortunately I can't post the DB as it has confidential data in it; however, the event that occurs when I clink the command button on the first form is:

Private Sub FinancialButton_Click()
On Error GoTo Err_FinancialButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTesting"
stLinkCriteria = "Forms!frmTesting!CategoryID = 1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_FinancialButton_Click:
Exit Sub

Err_FinancialButton_Click:
MsgBox Err.Description
Resume Exit_FinancialButton_Click

 

[Mr. B]

Unfortunately I can't post the DB as it has confidential data in it; however, the event that occurs when I clink the command button on the first form is:

Private Sub FinancialButton_Click()
On Error GoTo Err_FinancialButton_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmTesting"
stLinkCriteria = "Forms!frmTesting!CategoryID = 1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_FinancialButton_Click:
Exit Sub

Err_FinancialButton_Click:
MsgBox Err.Description
Resume Exit_FinancialButton_Click

This may be a situation where you are attempting to pass the wrong data type. If the "CategoryID" is holding an auto number type field, then you do not want to assign the value from this control on your form to a "string" type variable.

To test this, change:
Dim stLinkCriteria As String
to:
Dim stLinkCriteria As Variant

Then also change this line:
stLinkCriteria = "Forms!frmTesting!CategoryID = 1"
to this:
stLinkCriteria = Forms!frmTesting!CategoryID

Try this a see if it helps.

 

[Johnny Drama]

Nope, CategoryID is not an auto number. It's a fixed number that is either 1, 2, or 3, and I need the form to open only those with a CategoryID of 1.

 

[Mr. B]

Ok, sorry for not understanding that.

Leave off the "Form!frmTesting!" and just use the field name = to value.

stLinkCriteria = "CategoryID = 1"

 

[Johnny Drama]

I'm sitting here laughing at myself because I'm such an idiot. Standing in the forest but can't see any trees. Ugh.

Thanks. That worked!

 

[Aiken004]

plz mention how to disable this option .

 

[vbaInet]

Spammer reported.