Use one form but have different record sets

[67flyer]

lets see if i can explain this correctly. If there is a better way please point that out to me also. Thanks.

I have multiple forms that do just about the same thing. I want to clean up the database so I am going to use one form and switches to vary the data or displays if needed. the form can be opened directly, linked to a button and used as a subform. the form is based off one table, but depending on how its opened, the criteria for which record is shown changes. when used in the subform it needs to be based of a field in the main form. When used on the form with the button that opens the form it is using data from the button.

I have 2 different qry's for each problem. I just can't get the form to pick the correct qry va VBA code. I want to have the forms switch to determine which qry to use.

thanks,

 

[pbaldy]

I just can't get the form to pick the correct qry va VBA code. ,

What is your failing code, and how does it fail? Sounds like a situation for OpenArgs, but hard to say without seeing what you''re trying.

 

[67flyer]

failing code. If the form is not opened by one of the 3 ways no data is shown. (which is fine I only want there to be 3 ways to use the form. there will be no other way to access the form other than the 3 ways.) will OpenArgs let me select which qry to use as the data source for the form? One way of opening the form would be, If I click button with sn# 101, I want to open the form to sn#101 only. When i click on 101 button. i set my switch to "sb" and then I want to open the form with the qrysb that has forms!frm1!sn in the criteria for the qry under sn#.

Now when i want to open the form in the subform instance. the forms switch is now "ws" and in the form is based on qryws and that it will have criteria forms!frm22!tmpsn in the qry under sn#.

maybe I just need one qry that the form is using as data and change the criteria each time in the qry. I tried using a public variable and use that in the criteria but i couldn't get that to work. (not really sure how to) I think having a variable would be easier than changing the record source of the form everytime.

Thanks,

 

[67flyer]

Got it to work using a variable in the qry criteria field. When I attempted before I did not have a public variable to pass the info to. I just tried a function and then pass then criteria to that function.

Thanks for the help. If there is a better way to work this please let me know.

 

[pbaldy]

The function should work fine, if it fits your situation.