Can continuous form be in a tabl control?

[Dennis]

All,

I'm on XP Pro w SP3 using Access via Xp Office Pro w SP3.

I think I already know the answer to this question, but I'll ask it anyhow.

I know it is called a continuous "form", but is there anyway I can display a
continuous form in a tab control?

What I need to do is display a list of cars in inventory after the user
enters certain selection / filtering criteria in the header of the form.
I'll then apply a filter to the form using the user entered selection
criteria. I want to have the results displayed in a series of row (hense the
continuous form). The user will then click on the specific car and I want to
set focus to the next tab that will contain the complete details of the car.

When I put my row of controls in a tab, Access prints a continuous form, but
each row has a tab.

If I have to, when the user clicks on a row, I will open up a new form where
they can add, change, or view the detailed information. I just think
switching tab pages is a bit "prettier" than open (hide = true / false) and
closing a window. But if that is what I have to do, oh well.

Thank you in advance for your assitance.

 

[tina]

you can do what you're attempting with a mainform/subform setup. create a
form, SingleForm view, with RecordSource set to your inventory table; this
is your mainform - i'll call it frmMain. add a tab control with two pages,
and put the controls you need to show record details on the *second*
tabpage. add a subform control to the first tabpage, i'll call it ChildList.
close the mainform for now.

create another form, ContinuousForms view, and bound to the inventory table;
this is your subform - i'll call it frmSub. add controls for the fields you
want to see on the continuous view form. close the subform for now.

open frmMain in Design view, click on the subform control, and set the
SourceObject property to frmSub. do *NOT* set the LinkChildFields and
LinkMasterFields properties - both should remain blank.

add the "filtering" controls to the Header section of frmMain. you sound
confident of the filtering process, so i won't go into it - just keep in
mind that you're filtering the subform's record source, *not* the mainform's
record source.

still in frmMain's Design view, click in the RecordSource property line, and
click the Build (...) button at the right side. in the builder, add the
fields you need for the form, making sure you include the primary key field,
and set criteria on the primary key field, as

Forms!frmMain!ChildList.Form!NameOfPrimaryKeyField

replace NameOfPrimaryKeyField with the actual name of the pk field in the
inventory table.

back in frmSub's Design view, add code to the form's Current event, as

Me.Parent.Requery

okay, now it should be ready. when you open frmMain, filter criteria
controls show in the Header section, and the first tabpage shows the
continuous subform. apply your criteria to see the group of records you want
in the subform. as you move from record to record in the subform, the
mainform record (on the second tabpage) changes to match the subform record.
you can add code in the subform so that you can double-click (better than
single-click, IMO) a record and jump to the second tabpage. i'd probably use
a transparent command button, stretched to cover the entire record (in
frmSub's Design view), with code on the double click event, as

Me.Parent!NameOfControlOnSecondTabPage.SetFocus

replace NameOfControlOnSecondTabPage with an actual controlname, of course.
or you can change the tabcontrol instead, as

Me!TabCtlName = 1

replace TabCtlName with the name of the tab control. the "1" refers to the
index value of the second page in a tab control's Pages collection.

this is a very non-traditional way of using a mainform/subform setup, but it
should work fine in A97 to A2003 versions. if A2007, i can't say.

hth

 

[Dennis]

tina,

Very nice solution. I'll give it a try. thanks again.

 

[Dennis]

Tina,

I got most of it working. However, I have some remaining questions.


QUESTION - "just keep in mind that you're filtering the subform's record
source, *not* the mainform's record source."

I understand that I want to filter the subform's record source. But I'm not
sure how to do this. Do I do this by Forms!frmMain!ChildList.Filter =
"selection criteria"? I've never done this before.



QUESTION - "still in frmMain's Design view, click in the RecordSource
property line, and click the Build (...) button at the right side. in the
builder, add the
fields you need for the form, "

In the main form's row source, I just have a query which is a logical view
of the inventory table joined to all of the FK tables. So, do I need to
still specify the individual fields?


QUESTION: making sure you include the primary key field, and set criteria
on the primary key field, as

Forms!frmMain!ChildList.Form!NameOfPrimaryKeyField"

I have no idea how to do this in the Row Source property. Are you refering
to linking the Child and Master fields?



QUESTION - apply your criteria to see the group of records you want
in the subform. as you move from record to record in the subform, the
mainform record (on the second tabpage) changes to match the subform record.

How are the records in first tab connnected to the records in the second
tab? I did not see where they got linked together. Is it because the second
tab is "linked" to the main form as those control are part of the main form
and when I link the subform to the mainform, then the second tab will
therefore also be linked?

 

[tina]

comments inline.

Tina,

I got most of it working. However, I have some remaining questions.


QUESTION - "just keep in mind that you're filtering the subform's record
source, *not* the mainform's record source."

I understand that I want to filter the subform's record source. But I'm not
sure how to do this. Do I do this by Forms!frmMain!ChildList.Filter =
"selection criteria"? I've never done this before.

probably, though you'll probably be running the "apply filter" code from a
command button on frmMain - right beside the criteria controls, perhaps? so
the syntax would a bit different; and i'd probably do a With statement so i
wouldn't have to write out the reference twice, as

With Me!ChildList.Form
.Filter = <your filter criteria string here>
.FilterOn = True
End With

i don't use filters much; instead, i usually write the criteria into a SQL
statement that is used as the RecordSource of the subform. if you're
interested in that approach, and would like details, post back.

QUESTION - "still in frmMain's Design view, click in the RecordSource
property line, and click the Build (...) button at the right side. in the
builder, add the
fields you need for the form, "

In the main form's row source, I just have a query which is a logical view
of the inventory table joined to all of the FK tables. So, do I need to
still specify the individual fields?

no. you can open the query in Design view, either from the Build button as
described above, or by itself, in the database window. in either case, make
sure that the inventory table's primary key field is included in the design
grid, and add the criteria as i explained in the previous post.

QUESTION: making sure you include the primary key field, and set criteria
on the primary key field, as

Forms!frmMain!ChildList.Form!NameOfPrimaryKeyField"

I have no idea how to do this in the Row Source property. Are you refering
to linking the Child and Master fields?

no. if frmMain's RecordSource had been a table, the steps i described in the
previous post would have changed the RecordSource to a SQL statement
instead. since you're already using a saved query as the RecordSource, just
open the query and add the criteria to the primary key field in the design
grid, as i described above.

sorry for the confusion; it's a sometimes unavoidable side effect of the
newsgroup venue, where i can't see your database, and you can't see me
demonstrate what i'm explaining.

QUESTION - apply your criteria to see the group of records you want
in the subform. as you move from record to record in the subform, the
mainform record (on the second tabpage) changes to match the subform record.

How are the records in first tab connnected to the records in the second
tab? I did not see where they got linked together. Is it because the second
tab is "linked" to the main form as those control are part of the main form
and when I link the subform to the mainform, then the second tab will
therefore also be linked?

no. re-read the previous post; i specifically said "do *NOT* set the
LinkChildFields and
LinkMasterFields properties - both should remain blank." setting those two
properties is how a mainform/subform are normally linked - but as i said
before, this is not a traditional mainform/subform setup.

the RecordSource of frmMain is linked to the RecordSource of frmSub by the
criteria set in frmMain's RecordSource. the requery code in frmSub's Current
event "activates" the link, so to speak, by forcing the mainform to display
only the record that is selected in the subform.

if you're familiar with the standard use of a mainform/subform setup, you
know that normally the subform records are always related to the current
mainform record, controlled by the LinkChildFields/LinkMasterFields
properties of the subform control within the mainform. what we're doing here
is essentially turning that standard setup "inside out", by forcing the
subform current record to control the selection of the mainform record.
doing it "backwards" means that we have to set everything up manually,
because the handy setup built into Access won't do the work for us.

hang in there. once you get it set up, and see it running correctly, i
believe it'll make a lot more sense to you, and then it'll seem "easy".
(really)

 

[Dennis]

comments inline.


Question: Sorry for being so slow, but I still don’t understand

“still in frmMain's Design view, click in the RecordSource property line,
and click the Build (...) button at the right side. in the builder, add the
fields you need for the form, making sure you include the primary key field,
and set criteria on the primary key field, as

Forms!frmMain!ChildList.Form!NameOfPrimaryKeyField

replace NameOfPrimaryKeyField with the actual name of the pk field in the
inventory table. “

The main form’s row source is the logical view of the inventory file call
qrytblInventoryHist. It contains ALL of the fields of the physical
tblInventoryHist plus the FK descriptions (via multiple joins) for the FK in
the tblInventoryHist. As a result, I really do not want to put all that SQL
in my row source – hence the use of the filter.

Anyhow, back to my question. The qrytblInventoryHist has all of the fields
I need for the form as well as the primary key field, which is called ChgNo
(auto assigned number). When I read your comment above, I interpret it as the
line

“Forms!frmMain!ChildList.Form!NameOfPrimaryKeyFieldâ€

should somehow be in the SQL statement. I don’t believe this is what your
are saying. However, I don’t understand how to the above line and “set
criteria on the primary key field†are connected or how to do what you are
saying.

What do I do with the line?

“Forms!frmInvDsp!frmsfInvHist.Form!ChgNoâ€

Where do I put it, how do I use, what do I do with it?


Also, I’m not familier with mainform / subform. I’m still learning Access –
doing a lot of reading and self-teaching. Sorry about that.

The form is coming together and I’m really exciting, it is just getting it
to work together. I really appreciate your help.

Thanks,

Dennis

--

 

[tina]

comments inline.

comments inline.

Question: Sorry for being so slow, but I still don't understand

"still in frmMain's Design view, click in the RecordSource property line,
and click the Build (...) button at the right side. in the builder, add the
fields you need for the form, making sure you include the primary key field,
and set criteria on the primary key field, as

Forms!frmMain!ChildList.Form!NameOfPrimaryKeyField

replace NameOfPrimaryKeyField with the actual name of the pk field in the
inventory table. "

The main form's row source is the logical view of the inventory file call
qrytblInventoryHist.

well, this is a bit off the subject, but let's stop here a moment. first of
all, by "main form's row source" i assume you mean the RecordSource. let's
use proper terminology to avoid confusion; "RowSource" is a property of a
combobox control, and that's not part of our discussion.

It contains ALL of the fields of the physical
tblInventoryHist plus the FK descriptions (via multiple joins) for the FK in
the tblInventoryHist. As a result, I really do not want to put all that SQL
in my row source - hence the use of the filter.

a filter is used to filter *records*, it does nothing to remove *fields*
from the form's RecordSource. if qrytblInventoryHist includes fields that
you don't need in frmMain, then remove them; or write a query to use
specifically for frmMain's RecordSource. and the filter we've been
discussing will be applied to frmSub, *not* frmMain, anyway. that filter
isn't going to affect frmMain's recordset at all.

Anyhow, back to my question. The qrytblInventoryHist has all of the fields
I need for the form as well as the primary key field, which is called ChgNo
(auto assigned number). When I read your comment above, I interpret it as the
line

"Forms!frmMain!ChildList.Form!NameOfPrimaryKeyField"

should somehow be in the SQL statement. I don't believe this is what your
are saying.

that's *exactly* what i'm saying.

< However, I don't understand how to the above line and "set

criteria on the primary key field" are connected or how to do what you are
saying.

What do I do with the line?

"Forms!frmInvDsp!frmsfInvHist.Form!ChgNo"

let's confirm: frmsfInvHist is the name of the *subform control within the
mainform*, correct? make sure, by opening the mainform in Design view,
clicking ONCE on the subform to select it, and looking at the Name property
in the Properties box. *that's* the name you need to use in the reference.
and ChgNo is the name of the primary key field of the inventory table,
correct?

Where do I put it, how do I use, what do I do with it?

ok, you know what a query is, obviously. do you know how to set criteria in
a query? if you don't, then you'll need to get a good book on basic Access
development (Microsoft Access <version> Bible is one good one), and read up
on building queries - or look for a good online tutorial. if you do know how
to set criteria in a query, then it should be easy. the expression is used
as criteria in the query. open the query in Design view, and add the
expression to the criteria line of the of the inventory's primary key field.

Also, I'm not familier with mainform / subform. I'm still learning Access -
doing a lot of reading and self-teaching. Sorry about that.

no problem, that's how you learn. if you haven't already, you should put
relational design principles at the top of your reading list. make sure you
have a solid grasp of the basics, because everything you do in Access is
based on those principles. here's a link for further reading:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Dennis]

Tina,

Sorry about the terminology, I’m still learning. I have 20 years in
programming midrange computers using relational db, so I understand the
programming and db concepts. I’m new to Access and its concepts and ways of
doing things.

Let me recap.

Form name: frmInvHist
Record Source: qrytblInvHist
This form consist of:
Header section where selection criteria is entered.
Two tab controls
1st tab ctl contains subform tblInvHist_sfList
2nd tabl ctl contains control items for:
Chg to existing data
Entry of a new history entry.

I have five data entry controls in the header section and one command button
(apply selection criteria). Each data entry control contains a data field
that I will use create a Select SQL that I will put in tblInvHist_sfList
Record Source. I also use “WHERE†section of that SQL (with the WHERE
stripped out) as the filter for the frmInvHist. The user can enter one of
more fields before they hit the Apply Selection Criteria button.

The tblInvHist_sfList subform’s detail section visible property is initially
set to No. The first time the user presses the Apply Criteria button, the
detail section’s visible property will be set to Yes.

On the subform tblInvHist_sfList I have transparent button laid over the
controls on the continuous form’s detail line. I will not have a unique
value for Forms!frmInvHist!frmInvHist_sfList.Form!ChgNo" until the user
double clicks on a row.

Here is my confusion. You say while "still in frmMain's Design view, click
in the Record Source property line, and click the Build (...) button at the
right side. in the builder, add the fields you need for the form, making sure
you include the primary key field, and set criteria on the primary key field,
as

Forms!frmMain!ChildList.Form!NameOfPrimaryKeyField


I guess I don’t understand how I can include
“Forms!frmInvHist!frmInvHist_sfList.Form!ChgNo" in fromInvHist Record Source
when it has a value of NULL when the form loads.

After the user presses the Apply Select Criteria button, it will have
multiple values. The frmInvHist_sfList.ChgNo field will not have a unique
value until the user pick a specific row from my subform’s list.

Are you saying that the value will be NULL when the form loads and when the
user picks a row, the On Current Even will run, which will execute
Me.Parent.Requery, which will then execute the row source SQL with a value in
the “Forms!....!ChgNo†and the appropriate row will be selected from the
table?

Let me give that a try.

 

[tina]

well, looks like you've taken my suggested solution and tried to integrate
it into the setup you were already working with - rather than doing a clean
setup as i described it. no wonder you're having problems with it.

i threw together a small demo db. it follows my post, except that i ended up
using two subforms, and leaving the mainform unbound. it's a somewhat
simpler solution than i would probably use myself, but should be easy to
follow. oh, and since you've been assigning your subform's RecordSource at
runtime, that tells me you don't want any records returned in the subform
until the user sets criteria. rather than build and assign a SQL string
everytime the user enters criteria, i just incorporated the criteria into
the continuous subform's RecordSource as part of the design. the setup uses
minimal code - just a couple requery commands, some code to clear the
criteria controls, and some code to set focus to the second tab page from
the continuous form.

i've loaded the demo to my website (at least i think it loaded okay, i'm
having some challenges there...) that you can download if you want, to see
the solution i've been describing. go to
http://home.att.net/~california.db/instructions.html; just scroll down to
the "demo" link at the bottom of the page. after downloading, be sure to
change the file extension from .bak to .mdb BEFORE you open the db. the file
is 176 KB, so if your download returns a file that's about 396 KB, that
means you got an older demo db that i built for somebody else several weeks
ago (did i mention i might have had a problem uploading? a webmaster i'm
not! <g>). that old demo db shows a completely different technique - tab
pages, i think...at any rate, it won't address your situation. so if that's
what you get, post back and i'll have another try at uploading.

hth

 

[Dennis]

Tina,

Sorry for taking so long to respond. I got pulled off to do other things.

I don't know why I did not get what your were telling me. I tried to follow
your directions, but I just did not understand. Sorry about that.

I've dowloaded your demo and it does exactly what I want it to do. I
GREATLY appreaciate you taking your time to assist me. I've learned a lot.

I looked at your code and I finally get it. I'll be implementing your code
in the next couple of days. I will let you know how it goes, but now that I
see an example I have not doubts I'll be able to do it.

Thanks again.

Dennis

 

[tina]

comments inline.

Tina,

Sorry for taking so long to respond. I got pulled off to do other things.

no problem. we all have "real lives" - jobs, families, etc.

I don't know why I did not get what your were telling me. I tried to follow
your directions, but I just did not understand. Sorry about that.

no worries. i've often thought that the easiest way to explain a concept in
Access is by pointing and waving your arms a lot. at least that's the
easiest way for me! <g> trying to communicate in a written forum with no
illustrations can be very frustrating for both the op and the respondents -
but it's still a lot better than being out there alone in the hard cold
world, with nobody to ask for help (i was in that situation for several
years, brrr!).

I've dowloaded your demo and it does exactly what I want it to do. I
GREATLY appreaciate you taking your time to assist me. I've learned a lot.

I looked at your code and I finally get it. I'll be implementing your code
in the next couple of days. I will let you know how it goes, but now that I
see an example I have not doubts I'll be able to do it.

i figured you wouldn't have any problem understanding and implementing the
setup if you could just see it. i'm glad i understood your intended goal
well enough to offer a viable solution. and the learning part is great,
isn't it? the more you understand how subforms work, especially within the
context of relational design, the better you'll be equiped to get the most
out of them in both traditional and non-traditional setups.

personally, i love subforms, and tabcontrols, and i use them A LOT. just
about every form i build has a subform on it, or a tab control, often both.
one technique you can "graduate" to, in using subforms, is to avoid using
multiple subforms in a form where only one subform at a time will be visible
to the user (subforms on tab controls generally fall into that category). a
form that incorporates a subform takes longer to load because of the
overhead, and that increases with each additional subform on the mainform.
where the user sees just one subform at a time, by moving from one tab page
to another, i usually have only one subform on the form, set *behind* the
tab control, and just change the subform control's SourceObject as the user
moves from tabpage to tabpage. in fact, in situations where there are no
objects on the tabpages, just the subform "showing through from the back", i
usually dispense with the tab control altogether and just use an option
group with toggle buttons, set above the subform, which controls the
subform's SourceObject as the user clicks from button to button.

Thanks again.

you're very welcome.