Query within 2 tabs

[Afrosheen via AccessMonster.com]

Thanks for reading this. I have a query that will pull records from the days
shift and the night shift.
I also have a form that will display those records.
I have two tabs and what I want to do is when I click on one tab it will only
show records on the day shift.
When I click on the second tab it will show records from the night shift.

Is there any way this can be done?

This query is done on the fly and not a "hard" query because there's other
information I need in it.

Thanks for your direction.

 

[Al Campagna]

Afrosheen,
Some sample data from a "day" record and a "night" record would be
helpful... but...
How can you tell a record is a day record or a night record?

For example... (one of many) if you had a checkbox field in your work
records table named [Night], and on those night records, Night = True...
that would be one of many ways to differentiate day from night.
Then it's simple matter to create two identical subforms.... with one
RecordSource query filtered for Night = False, and the other for Night =
True.

A method I've used is to use just one sub, and allow the user to alter
the RecordSource to display Night = True, or Night = False, or Show All Work
Records for this employee.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Afrosheen via AccessMonster.com]

Thanks for replying Al, Sorry I couldn't get back to you sooner as I had to
work the weekend.

I'll try and give you the details. Before I get to the actual form, I have
another form that will create a query.
stwhere = "position IN ('custody','sgt','lieutenant','captain') and shift IN
('a-days','day shift','afternoon shift','a-nights','night shift')"
DoCmd.OpenForm "form2roster", , , stwhere, , , "A-Day Rotation"

This will open my form where I can enter the data. Right now everything is
in tab1. What I want to do is separate the difference between the "day" and
"night" and place the "night" in tab 2.

I took your suggestion to put a True/False check box in a field called
[shift1]. Now what I have to try and do is put all the "True" together in
tab1 and all the "False" together in tab2. That's where I'm having my problem.
When tab1 comes up do I have a filter set up for "True" and then another
filter for tab2?

By the way tab1 and tab 2 are laid out the same way with the same information.
Not as a subform. Maybe that would be the better way.

Any help and code would be appreciated.

Thanks

Afrosheen,
Some sample data from a "day" record and a "night" record would be
helpful... but...
How can you tell a record is a day record or a night record?

For example... (one of many) if you had a checkbox field in your work
records table named [Night], and on those night records, Night = True...
that would be one of many ways to differentiate day from night.
Then it's simple matter to create two identical subforms.... with one
RecordSource query filtered for Night = False, and the other for Night =
True.

A method I've used is to use just one sub, and allow the user to alter
the RecordSource to display Night = True, or Night = False, or Show All Work
Records for this employee.

Thanks for reading this. I have a query that will pull records from the
days

[quoted text clipped - 11 lines]

Thanks for your direction.

 

[Al Campagna]

Afrosheen,
Sounds like you have a table (the form's RecordSource) behind each
subform. Using a table as a RecordSource for a subform does not allow for
filtering.

When tab1 comes up do I have a filter set up for "True" and then another
filter for tab2?

Use a query as your RecordSource of each subform, and... in Query Design
mode, in the Shift1 column, use a criteria of either True for only day
records, or False for only night records.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks for replying Al, Sorry I couldn't get back to you sooner as I had
to
work the weekend.

I'll try and give you the details. Before I get to the actual form, I have
another form that will create a query.
stwhere = "position IN ('custody','sgt','lieutenant','captain') and shift
IN
('a-days','day shift','afternoon shift','a-nights','night shift')"
DoCmd.OpenForm "form2roster", , , stwhere, , , "A-Day Rotation"

This will open my form where I can enter the data. Right now everything
is
in tab1. What I want to do is separate the difference between the "day"
and
"night" and place the "night" in tab 2.

I took your suggestion to put a True/False check box in a field called
[shift1]. Now what I have to try and do is put all the "True" together in
tab1 and all the "False" together in tab2. That's where I'm having my
problem.
When tab1 comes up do I have a filter set up for "True" and then another
filter for tab2?

By the way tab1 and tab 2 are laid out the same way with the same
information.
Not as a subform. Maybe that would be the better way.

Any help and code would be appreciated.

Thanks

Afrosheen,
Some sample data from a "day" record and a "night" record would be
helpful... but...
How can you tell a record is a day record or a night record?

For example... (one of many) if you had a checkbox field in your work
records table named [Night], and on those night records, Night = True...
that would be one of many ways to differentiate day from night.
Then it's simple matter to create two identical subforms.... with one
RecordSource query filtered for Night = False, and the other for Night =
True.

A method I've used is to use just one sub, and allow the user to alter
the RecordSource to display Night = True, or Night = False, or Show All
Work
Records for this employee.

Thanks for reading this. I have a query that will pull records from the
days

[quoted text clipped - 11 lines]

Thanks for your direction.

 

[Afrosheen via AccessMonster.com]

Hi Al,
I tried putting the subform on the tab and when it does it all the tabs
disappear. The information in tab1 or tab 2 are identical. Is there a way
where I can use a different query on each tab?
For example: first name and last name are the same on both tabs.

Thanks.

Afrosheen,
Sounds like you have a table (the form's RecordSource) behind each
subform. Using a table as a RecordSource for a subform does not allow for
filtering.

When tab1 comes up do I have a filter set up for "True" and then another
filter for tab2?

Use a query as your RecordSource of each subform, and... in Query Design
mode, in the Shift1 column, use a criteria of either True for only day
records, or False for only night records.

Thanks for replying Al, Sorry I couldn't get back to you sooner as I had
to

[quoted text clipped - 49 lines]

 

[Al Campagna]

Afrosheen,
Your jumping around a bit. Let's stay with one problem at a time...

The last question we were dealing with was how to get one subform (on
one tab) to
display Day transactions, and the other (on another tab) to display the
Night records.

Name the "day" subform frmDayTransactions , and take a duplicate of
that subform and name it frmNightTransactions.
In the query that is the RecordSource for frmDayTransactions, the Shift1
field should have a criteria of TRUE.
In The query that is the RecordSource for frmNightTransactions, the
Shift1 field should have a criteria of FALSE.

I can't think of a better, or another way, to say that...
Well not on each tab... but on each of the subforms.
That's what I'm saying. Each subform CAN have it's own individual
query...
as it's RecordSource.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Hi Al,
I tried putting the subform on the tab and when it does it all the tabs
disappear. The information in tab1 or tab 2 are identical. Is there a way
where I can use a different query on each tab?
For example: first name and last name are the same on both tabs.

Thanks.

Afrosheen,
Sounds like you have a table (the form's RecordSource) behind each
subform. Using a table as a RecordSource for a subform does not allow for
filtering.

When tab1 comes up do I have a filter set up for "True" and then another
filter for tab2?

Use a query as your RecordSource of each subform, and... in Query
Design
mode, in the Shift1 column, use a criteria of either True for only day
records, or False for only night records.

Thanks for replying Al, Sorry I couldn't get back to you sooner as I had
to

[quoted text clipped - 49 lines]

Thanks for your direction.

 

[Afrosheen via AccessMonster.com]

Thanks again for your reply. I'll take one problem at a time then all I
should have to do is duplicate it and change the query.

Thanks

Afrosheen,
Your jumping around a bit. Let's stay with one problem at a time...

The last question we were dealing with was how to get one subform (on
one tab) to
display Day transactions, and the other (on another tab) to display the
Night records.

Name the "day" subform frmDayTransactions , and take a duplicate of
that subform and name it frmNightTransactions.
In the query that is the RecordSource for frmDayTransactions, the Shift1
field should have a criteria of TRUE.
In The query that is the RecordSource for frmNightTransactions, the
Shift1 field should have a criteria of FALSE.

I can't think of a better, or another way, to say that...
Well not on each tab... but on each of the subforms.
That's what I'm saying. Each subform CAN have it's own individual
query...
as it's RecordSource.

Hi Al,
I tried putting the subform on the tab and when it does it all the tabs

[quoted text clipped - 20 lines]

 

[Al Campagna]

Afrosheen,
I think you've got it now...
Let me know how it works out.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thanks again for your reply. I'll take one problem at a time then all I
should have to do is duplicate it and change the query.

Thanks

Afrosheen,
Your jumping around a bit. Let's stay with one problem at a time...

The last question we were dealing with was how to get one subform (on
one tab) to
display Day transactions, and the other (on another tab) to display the
Night records.

Name the "day" subform frmDayTransactions , and take a duplicate of
that subform and name it frmNightTransactions.
In the query that is the RecordSource for frmDayTransactions, the
Shift1
field should have a criteria of TRUE.
In The query that is the RecordSource for frmNightTransactions, the
Shift1 field should have a criteria of FALSE.

I can't think of a better, or another way, to say that...

Is there a way where I can use a different query on each tab?

Well not on each tab... but on each of the subforms.
That's what I'm saying. Each subform CAN have it's own individual
query...
as it's RecordSource.

Hi Al,
I tried putting the subform on the tab and when it does it all the tabs

[quoted text clipped - 20 lines]

Thanks for your direction.

 

[Afrosheen via AccessMonster.com]

Hi Al, Thanks for your help. This is what I've got so far.

I've created a new form with tabs and put the subform that I need to work
with and all appears to be working. Now here is my next question. Besides
working on Days and Nights there is a rotation. A-Rotation and B-Rotation.
Can I use just the two subforms for both rotations or do I need to two
subforms for A-Rotation and two subforms for B-Rotation? Before I started re-
working this database I had one form for both rotations and then created a
filter. This was the code:

280 stwhere = "position IN ('custody','sgt','lieutenant','captain')
and shift IN ('a-days','day shift','afternoon shift')"

340 DoCmd.OpenForm "frm_roster", , , stwhere, , , "A-Day Rotation"

The other rotation was the same except for ('b-days','day shift','afternoon
shift')"

Maybe I could even program to use the different querys for the forms. I don't
know. Right now I have 4 querys. Adays, Anight, Bdays,Bnights.

Again, Thanks for your help. Any suggestions are greatly appreciated.

Afrosheen,
I think you've got it now...
Let me know how it works out.

Thanks again for your reply. I'll take one problem at a time then all I
should have to do is duplicate it and change the query.

[quoted text clipped - 29 lines]