command button - pass a parameter to open a form based on a query

[Guest]

How do I set a paramater value when opening a form which is based on a query
using a command button
Thanks
Danny

 

[Rick B]

Just put your criteria in the query. When you open the form, it will run
the query, and prompt the user.

 

[Guest]

Hi Rick

That is what I have now - but wanted to automate this input - which is
predefined
by doing this I was hoping to avoid input error on the parameter input.

Thanks
Danny

 

[Rick B]

Automate it how? Where would Access get the values from? If they are
always the same, just replace the parameters in your query with the values.
Not sure what you mean when you say they are predefined. Where?

 

[Guest]

Hi Rick

I have a main form with about 15 command buttons the code for the command
button is

Private Sub Command18_Click()
On Error GoTo Err_Command155_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Entity Form"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command155_Click:
Exit Sub

Err_Command155_Click:
MsgBox Err.Description
Resume Exit_Command155_Click
End Sub

Each command button represents a department (pre defined department number
say ranging from 10 to 25)

The main form is based on the a table of all the deparments numbers
so when you click one of the command button you choose a department "say 10"
which bring up the department details

The sub form is based on all sales for that department, one of the filed is
deparment number , which is linked with the master and child to the main form
so any transaction on the sub form are filtered by the dep no on the main
form.

So coming back to my original thought was that when one of the the command
button on the main form is selected it would open up the main form with the
predefinded department number somewhere in the code above.

Long winded but thats all of it.
Thanks
Danny

 

[Rick B]

I believe you would need to put a "where" clause in that stLinkCriteria.

I don't know the exact syntax, though.

 

[Rick B]

Just FYI, rather than have 18 buttons, you might think about building a drop
down box with the various ranges and then ONE button to click. Or have an
option group with the 18 ranges and one button to click. It would make your
form look cleaner, but even more important, it would result in ONE piece of
dynamic code instead of 18 pieces of stringent code.

 

[Guest]

Hi Rick

The only reason i have it the way I have it at the moment is that the layout
of the command buttons presented type of a org chart - easier for users to
identify with the structure of the department layout.


Danny

 

[Guest]

Yes, that would be the right way to do it. And, consider what happens when a
new department is added, then you have to modify and redeploy.