Open Form thru cmd buttons with changing of Record Source

[FryGirl]

I have a form with the record source set to a table. All works good, but I need to filter the form.

On the property sheet I changed the record source to

SELECT tblData.ID, tblData.[Hire Date], tblData.[Expire Date], tblData.Clasification FROM tblData WHERE (((tblData.Clasification)="Food"));

and again it works fine and filters the form to only records with meet the criteria of "Food".

Here's where I get stuck. I would like to set the record source thru cmd buttons on a main navigation form.

I have two cmd buttons on that main navigation form, cmdFood and cmdDrink.

I would like when I click cmdFood the record source for the form (frmData) to be the SQL above, but when cmdDrink is clicked the SQL record source changes to

SELECT tblData.ID, tblData.[Hire Date], tblData.[Expire Date], tblData.Clasification FROM tblData WHERE (((tblData.Clasification)="Drink"));

I know this can be done thru a query, but I would like to just use SQL if possible.

 

[FryGirl]

I found my answer thanks.

 

[HiTechCoach]

I found my answer thanks.

Would you mind paying it forward by sharing your answer so others may benefit.

Curious, did you use a TempVars? If you did you would not need to change the record source.

Or maybe you passed the Where clause using a parameter on the OpenForm method? Again you would not need to change the record source.,

 

[FryGirl]

Hi Boyd,

Thanks for the response.

Since this project is at work I do not have access to the answer right now but should be able to post on Monday.

My best recollection is a Where clause was used, but since Access is not a strong suit for me I just can't remember.

I would be very interested in your thoughts once the method used is posted.

 

[HiTechCoach]

I would be very interested in your thoughts once the method used is posted.

Sure. I will watch for your reply.

 

[FryGirl]

Hi Boyd,

Here is what I've found that seems to work, but looking back at it, another criteria is required.

Not only does it require the form being limited to what type of Classification, but there is another field Closed with should be Is Null.

How do I add another criteria to the where string?

Private Sub cmdDrink_Click()
    Dim DrinkSQl As String
    DrinkSQl = "select * from tblData where Classification='Drink'"
    DoCmd.OpenForm "frmData", acDesign
    Forms!frmData.RecordSource = DrinkSQl
    DoCmd.OpenForm "frmData", acNormal
End Sub

Private Sub cmdFood_Click()
    Dim FoodSQl As String
    FoodSQl = "select * from tblData where Classification='Food'"
    DoCmd.OpenForm "frmData", acDesign
    Forms!frmData.RecordSource = FoodSQl
    DoCmd.OpenForm "frmData", acNormal
End Sub

EDIT: I just tried the below and it seems to work also

DrinkSQl = "select * from tblData where Closed Is Null and Classification='Drink'"

 

[HiTechCoach]

Hi Boyd,

Here is what I've found that seems to work, but looking back at it, another criteria is required.

Not only does it require the form being limited to what type of Classification, but there is another field Closed with should be Is Null.

How do I add another criteria to the where string?

Private Sub cmdDrink_Click()
    Dim DrinkSQl As String
    DrinkSQl = "select * from tblData where Classification='Drink'"
    DoCmd.OpenForm "frmData", acDesign
    Forms!frmData.RecordSource = DrinkSQl
    DoCmd.OpenForm "frmData", acNormal
End Sub

Private Sub cmdFood_Click()
    Dim FoodSQl As String
    FoodSQl = "select * from tblData where Classification='Food'"
    DoCmd.OpenForm "frmData", acDesign
    Forms!frmData.RecordSource = FoodSQl
    DoCmd.OpenForm "frmData", acNormal
End Sub

EDIT: I just tried the below and it seems to work also

DrinkSQl = "select * from tblData where Closed Is Null and Classification='Drink'"

First, I NEVER open a form in design mode with VBA like you are doing. I also run all my front end compiled into a .accde. This does not allow any design mode for forms and reports.

I have helped new clients who had created a multiple user database that were using code like you posted. and had many issues be use the database was not split into a app/front end and data /back end. I have also seen major issue with the database was split but the front end was still shared.


To successfully use the code you posted for entering design mode for a form you must:

1) always use asplit the database
2) every user must have their own copy of the front end,.
3) You can NEVER compile the database into a .accde (which is a deal breaker for me.)

IMHO, you are making it much more difficult than needed.

About multiple criteria:

I recommend that you create a query in the designer using multiple criteria then switch to SQL view to see how to write the WHERE clause. I do this a lot and just copy and paste the WHERE clause. I let Access write it the way if likes it.

Here is how I always do it:

1) Set the form frmData to have a record sournce of "select * from tblData"

2) use the following to open the form passing a WHERE clause

Private Sub cmdDrink_Click()
   
    DoCmd.OpenForm "frmData", acNormal, , "Closed Is Null and Classification='Drink'"
End Sub

 

[FryGirl]

Thanks Boyd. This is good information to have.

 

[HiTechCoach]

Thanks Boyd. This is good information to have.

.
You're welcome. Glad we could assist