choosing between queries when opening form

[scottyboyb]

Greetings,

I have two queries. Both based on the same two tables. One query gives me a
list of current members only and one gives a list of past members only. I
would like to have a form that asks me what query to use when it opens. Is
this possible?

Many thanks.
Best,
Scott

 

[John W. Vinson]

Greetings,

I have two queries. Both based on the same two tables. One query gives me a
list of current members only and one gives a list of past members only. I
would like to have a form that asks me what query to use when it opens. Is
this possible?

Many thanks.
Best,
Scott

Sure. You can use one query; use the WhereCondition argument of the OpenForm
method to select which subset:

DoCmd.OpenForm "yourformname", WhereCondition := "Current = True"

or whatever the appropriate criterion would be.

 

[scottyboyb]

Someday I will learn to code. I need a class and I live in the middle of
nowhere, so no classes.

So here is what I put in the VB window and checked it with debug/compile and
then saved it.

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmVestryPastCurrent", WhereCondition:="Current = True"
End Sub

That part worked, which is a first for me. When I open the form I get a
Parameter Dialog box that is labled Current. Guess it gets that from
...."Current = True"... the form opens (YEAH!!!) and all 9 test records appear.

So I have created an event procedure that opens the form with some
condition. I am lost as to what to do about the condition and also how does
the code know what two fields I want to choose from and how do I enter them?

I am sorry. I know this is probably vb 101 for dummies, but I do not know
how to do these things. I keep hoping if I read enough posts and read enough
code snippets of what people are tryng to do that I will eventually
understand it. But not yet. This is how I learned to use expressions.

qryVestryPastCurrent from tblPerson & tblVestry
SQl for query=
SELECT tblPersonal.first, tblPersonal.middle, tblPersonal.last,
tblVestry.pastMember, tblVestry.currentMember, [first] & " " & [middle] & " "
& [last] AS Name
FROM tblPersonal INNER JOIN tblVestry ON tblPersonal.personKEY =
tblVestry.nameSubKEY;

What do I replace current with in event procedure and how does code know
what to do to the form?

Best,
Scott

 

[scottyboyb]

Good Morning,

I think I have made a little progress. I changed "current" to one of the
fields that I want to use (currentMember) and compiled and saved the code.
Now when I open the form I get the correct 9 names from the table/query. If I
change the code to pastMember, compile and save I get the correct 5 records.
So I tried putting in an OR and adding the other field and I get only one of
the two fields. I was hoping for a dialog box asking or input? HMMMMM. So
maybe I am on the right track and I do not know how to write this? Or (no
pun) I am still in the dark. HMMMM again.

First OR:
DoCmd.OpenForm "frmVestryPastCurrentRO", WhereCondition:="currentMember =
True" OR "pastMember = True" does not work

Second OR:
DoCmd.OpenForm "frmVestryPastCurrentRO", WhereCondition:="currentMember =
True" OR
DoCmd.OpenForm "frmVestryPastCurrentRO", WhereCondition:="pastMember = True"
does not work.

Changing OpenForm to OpenQuery does not work.

This is also probably important and I just thought of it. Both currentMember
and pastMember are Y/N fields. Or is that why the DoCmd statment uses true?

What wording would I use to search Help on this?

Best,
Scott

 

[John W. Vinson]

Someday I will learn to code. I need a class and I live in the middle of
nowhere, so no classes.

So here is what I put in the VB window and checked it with debug/compile and
then saved it.

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "frmVestryPastCurrent", WhereCondition:="Current = True"
End Sub

That part worked, which is a first for me. When I open the form I get a
Parameter Dialog box that is labled Current. Guess it gets that from
..."Current = True"... the form opens (YEAH!!!) and all 9 test records appear.

So I have created an event procedure that opens the form with some
condition. I am lost as to what to do about the condition and also how does
the code know what two fields I want to choose from and how do I enter them?

I am sorry. I know this is probably vb 101 for dummies, but I do not know
how to do these things. I keep hoping if I read enough posts and read enough
code snippets of what people are tryng to do that I will eventually
understand it. But not yet. This is how I learned to use expressions.

qryVestryPastCurrent from tblPerson & tblVestry
SQl for query=
SELECT tblPersonal.first, tblPersonal.middle, tblPersonal.last,
tblVestry.pastMember, tblVestry.currentMember, [first] & " " & [middle] & " "
& [last] AS Name
FROM tblPersonal INNER JOIN tblVestry ON tblPersonal.personKEY =
tblVestry.nameSubKEY;

What do I replace current with in event procedure and how does code know
what to do to the form?

I don't know for sure, because I don't know how your database is structured.
Are the fields pastMember and currentMember checkboxes of which one will be
checked? If so, then use a wherecondition of

currentMember = True

instead of Current.

IF that is the case though... you have a design flaw. The two fields are
redundant, in that *logically* only one can be true, and the other must be
false. But there's nothing in the structure of the table that prevents both
from being false (that is, a person is neither a current member nor a past
member, yet is a member); or both being true (The Once and Future King...?)
Better would be a single yes/no field which is True for a current member,
False for a past one.

 

[John W. Vinson]

Good Morning,

I think I have made a little progress. I changed "current" to one of the
fields that I want to use (currentMember) and compiled and saved the code.
Now when I open the form I get the correct 9 names from the table/query. If I
change the code to pastMember, compile and save I get the correct 5 records.
So I tried putting in an OR and adding the other field and I get only one of
the two fields. I was hoping for a dialog box asking or input? HMMMMM. So
maybe I am on the right track and I do not know how to write this? Or (no
pun) I am still in the dark. HMMMM again.

First OR:
DoCmd.OpenForm "frmVestryPastCurrentRO", WhereCondition:="currentMember =
True" OR "pastMember = True" does not work

Second OR:
DoCmd.OpenForm "frmVestryPastCurrentRO", WhereCondition:="currentMember =
True" OR
DoCmd.OpenForm "frmVestryPastCurrentRO", WhereCondition:="pastMember = True"
does not work.

Changing OpenForm to OpenQuery does not work.

This is also probably important and I just thought of it. Both currentMember
and pastMember are Y/N fields. Or is that why the DoCmd statment uses true?

See my other reply but... are currentMember and pastMember both included in
frmVestryPastCurrentRO's Recordsource query?

I'd really recommend correcting the redundant data structure prior to changing
your code, but you can have a checkbox on the form with this code and use IT
as a criterion: [Forms]![yourformname]![yourcheckboxname].