Desperate to filter main form

[TraderAnalyst]

I am a new user to Forms but not to Access. I have created a form
withthese fields from Traders table: Location, Trader Name, Acro, Net
PnL, Projected % Inc/Dec (data entry by user) and a calculated field
that multiplies the % and current Net PnL. I would like the form to
be used by multiple users but they can only see the data related to
their location. I have created a query that filters the form by
location w/ the parameters of [Please select the physical location of
your office: A = AMEX; C=CBOE; P=ARCA; X=PHLX] and they query works
great but I can't bring it is as sub-form to filter when the form is
opened. I get the prompt as stated above but it doesn't filter. Can
I use a subform to do this or should I create a pop-up (?)or combo box
that prompts the user for their location before they view any data? I
am not too familar with SQL so please explain the steps I need to
take. I have spent way too much time trying to figure this out.... I
really appreciate your help.

 

[kissybean via AccessMonster.com]

Hi,

I would create a pop up form and ask the user what location they want to view.
You can open a new form in design mode and put a combo box on there with the
4 choices, amex, cboe, arca, phlx. Then put an ok button below it or wherever.


Let the combo box default to the first value on the list. To do that, on
form load event of your pop up form, put this code where combo10 is the name
of your combo box.

If IsNull(Me.Combo10) Then
Combo10.Value = Combo10.ItemData(0)
End If

Then right click on properties on the ok button and go to on_click event.
Click on the ellipses (three dots) and the code section should come up.
(Sorry I don't mean to be condescending just thorough)

Declare some variables to hold your combo box value like so:

dim trdr_location as string

trdr_location = combo10.value

now you can use this variable to filter your table by writing a query:

docmd.runsql (" select field1, field2, field3, etc " & _
" into filteredtable " & _
" from originaltable " & _
" where originaltable.location = " & trdr_location ")

now that query made a filtered table with only the results the user asked for.


Then:

me.close ' close the pop up form

Set your "result" form to use filteredtable as it's source. Then when you
open it, it will display the results.

you can also open the "result form" from the code above: just add docmd.
OpenForm ("result form") after the me.close line.....

This is just one way to do it. It's pretty down and dirty and I'm sure there
might be some sophisticated stuff with filters and such but to get it done
quick, this will work.

Hope this helps!

I am a new user to Forms but not to Access. I have created a form
withthese fields from Traders table: Location, Trader Name, Acro, Net
PnL, Projected % Inc/Dec (data entry by user) and a calculated field
that multiplies the % and current Net PnL. I would like the form to
be used by multiple users but they can only see the data related to
their location. I have created a query that filters the form by
location w/ the parameters of [Please select the physical location of
your office: A = AMEX; C=CBOE; P=ARCA; X=PHLX] and they query works
great but I can't bring it is as sub-form to filter when the form is
opened. I get the prompt as stated above but it doesn't filter. Can
I use a subform to do this or should I create a pop-up (?)or combo box
that prompts the user for their location before they view any data? I
am not too familar with SQL so please explain the steps I need to
take. I have spent way too much time trying to figure this out.... I
really appreciate your help.