Subform trouble for New User

[E.Q.]

I'm working on my first Access database and have come accross a couple
problems. The first one I posted in "New Users" but have received no
response so I thought I'd post here, since it is ultimately a form problem.
The second one may be an example of a new user trying to force a bad design,
but I think what I want to do should be possible.
Problem No. 1: SQL doesn't work when form imported as a subform.
I created a form (fsubEventDetail) with the intent of using it as a subform.
One combo box control (cboObject) is populated by the user-selected value of
another control (cboArea). When I created the form, the source for cboObject
had the following SQL
Select Distinct chrObject
From tblAreas
Where chrArea = Forms!fsubEventDetail.cboArea;
That worked perfectly (along with a Me.Refresh) to get the desired drop-down
list for the user. However, when I placed this as a subform on a form called
frmShiftDetail I couldn't get it to work. I was hoping that the subform
wizard would make any appropriate changes, but that didn't happen. Next I
tried to edit the "where" clause as follows:

Where chrArea = Forms!frmShiftDetail!Form!fsubEventDetail.cboArea;

unfortunately I keep getting a parameter dialogue box.
I'm obviously missing something.

Problem No. 2: Linking tabbed sheets based on subforms.
My form frmShiftDetail has a tabbed control. One tab has the subform I
mentioned above. In addition, that tab holds text box controls displaying
date, shift and Operator name. The subform in this context displays the log
events entered by a given operator during one shift. I have created a
continuous form (fsubPP_Event_Overview) based on a query that displays an
abreviated version of each log event. I'd like to add the continuous form to
the second tab such that the operator could use the continuous form to review
previous log entries. From the continuous form, I'd like to have a button
control that will display the detailed listing for the shift the log was
entered. The problem is that the matching fields between the tabs are both
on subforms. If the details tab is current, I'd like to have the button
respond based on what has focus. If a record is selected within the subform,
I'd like to reference the corresponding record on the continuous form (both
contain the field PPLog_ID ), if something outside the subform has focus,
I'd like the button to simply move to the last record entered in the
continuous form.

I'm not sure if there's these two problems are directly related.
Any help will be appreciated.
Thanks,
E.Q.

 

[Dirk Goldgar]

I'm working on my first Access database and have come accross a couple
problems. The first one I posted in "New Users" but have received no
response so I thought I'd post here, since it is ultimately a form
problem. The second one may be an example of a new user trying to
force a bad design, but I think what I want to do should be possible.
Problem No. 1: SQL doesn't work when form imported as a subform.
I created a form (fsubEventDetail) with the intent of using it as a
subform. One combo box control (cboObject) is populated by the
user-selected value of another control (cboArea). When I created the
form, the source for cboObject had the following SQL
Select Distinct chrObject
From tblAreas
Where chrArea = Forms!fsubEventDetail.cboArea;
That worked perfectly (along with a Me.Refresh) to get the desired
drop-down list for the user.

Better would be

Me.cboObject.Requery

That just requeries the combo box.

However, when I placed this as a subform
on a form called frmShiftDetail I couldn't get it to work.

It wouldn't, because subforms aren't open in their own right, and aren't
members of the Forms collection.

I was
hoping that the subform wizard would make any appropriate changes,
but that didn't happen.

Nope, it's not that smart. And besides, it doesn't know that you only
want to use this form as a subform, or that you only want to use it as a
subform on one particular form.

Next I tried to edit the "where" clause as
follows:

Where chrArea = Forms!frmShiftDetail!Form!fsubEventDetail.cboArea;

unfortunately I keep getting a parameter dialogue box.
I'm obviously missing something.

You're close, but I think that ought to be ...

Where chrArea = Forms!frmShiftDetail!fsubEventDetail.Form!cboArea;

.... *provided* that "fsubEventDetail" is also the name of the subform
control (on the main form), and not just the name of the form object
displayed by that control. It's the name of the subform control you
have to use.

However, you might find it easier just to rewrite the rowsource of
cboObject in the AfterUpdate event of cboArea, embedding the value from
cboArea in the WHERE clause of the SQL statement as a literal. E.g,

Me.cboObject.RowSource = _
"SELECT DISTINCT chrObject FROM tblAreas " & _
"WHERE chrArea = " & Chr(34) & Me.cboArea & Chr(34)

That serves the dual purpose of applying the criterion and forcing the
combo to be requeried (because the rowsource has changed).

Problem No. 2: Linking tabbed sheets based on subforms.
My form frmShiftDetail has a tabbed control. One tab has the subform
I mentioned above. In addition, that tab holds text box controls
displaying date, shift and Operator name. The subform in this
context displays the log events entered by a given operator during
one shift. I have created a continuous form (fsubPP_Event_Overview)
based on a query that displays an abreviated version of each log
event. I'd like to add the continuous form to the second tab such
that the operator could use the continuous form to review previous
log entries. From the continuous form, I'd like to have a button
control that will display the detailed listing for the shift the log
was entered. The problem is that the matching fields between the
tabs are both on subforms. If the details tab is current, I'd like
to have the button respond based on what has focus. If a record is
selected within the subform, I'd like to reference the corresponding
record on the continuous form (both contain the field PPLog_ID ), if
something outside the subform has focus, I'd like the button to
simply move to the last record entered in the continuous form.

I don't quite follow what you're saying here. However, it may help to
know that you can link two subforms ("SubformA and "SubformB", for
argument's sake) by way of an extra control on the main form, which
serves as the Link Master Field for one of the subforms. For example,
you can have a hidden text box named "txtSubformLink", with it's
ControlSource property set to the expression,

=[SubformA].[Form]![PPLog_ID]

That is, it picks up the value of PPLog_ID from SubformA.

Then you use this text box, txtSubformLink, as the Link Master Field for
SubformB. Thus, SubformB always shows the records that are related to
the current record on SubformA. It's tricky, but it works.

 

[E.Q.]

Thanks for the input. The SQL you suggested worked perfectly, but I liked
using the VBA better. I also included code to set the second combo box to
null if the first one was changed. (And avoiding some records that would
make no sense that could be entered by selecting an object from one area then
changing the area.)

I'm still struggling with my second problem. I added the txt control as
suggested. Access displays a message saying "You must add tblLogIn_ID to your
record source if you want to use this link." I added that field to the query
used to generate the subform, but the result was that instead of getting a
continuous form with all operator log entries, it was limited to the log
entries corresponding to the specific shift identified by the tblLog_ID field
(even though I didn't use that field in the Subform Field Linker).

I'm not sure if my design is the best structure here. My though is to have
two tabs; one tab will provide the place for data entry as well as provide a
space for detail. The structure of this tab is three fields that identify
the date, shift, and operator name along with a continuous subform that uses
multiple lines to provide detail for a full log entry. The other tab has a
subform set up as a continuous form.I have limited the fields in the
continuous form to fit on one line. I'd like to be able to switch between
the two tabs smoothly (from the users perspective).

I'm wondering if I might need to give up on the continuous form in the
"details" tab. I'd like to have it since it would be a way to review all log
entries an operator made on one particular shift. (I showed it to a couple
operators who liked that structure as opposed to one record per form). But
they would have the continous form to scan the last few entries, they would
just need to click the mouse a couple more times to see all the fields on
multiple records.

But the VBA for the first problem has been a great help. Thanks.
E.Q.

I'm working on my first Access database and have come accross a couple
problems. The first one I posted in "New Users" but have received no
response so I thought I'd post here, since it is ultimately a form
problem. The second one may be an example of a new user trying to
force a bad design, but I think what I want to do should be possible.
Problem No. 1: SQL doesn't work when form imported as a subform.
I created a form (fsubEventDetail) with the intent of using it as a
subform. One combo box control (cboObject) is populated by the
user-selected value of another control (cboArea). When I created the
form, the source for cboObject had the following SQL
Select Distinct chrObject
From tblAreas
Where chrArea = Forms!fsubEventDetail.cboArea;
That worked perfectly (along with a Me.Refresh) to get the desired
drop-down list for the user.

Better would be

Me.cboObject.Requery

That just requeries the combo box.

However, when I placed this as a subform
on a form called frmShiftDetail I couldn't get it to work.

It wouldn't, because subforms aren't open in their own right, and aren't
members of the Forms collection.

I was
hoping that the subform wizard would make any appropriate changes,
but that didn't happen.

Nope, it's not that smart. And besides, it doesn't know that you only
want to use this form as a subform, or that you only want to use it as a
subform on one particular form.

Next I tried to edit the "where" clause as
follows:

Where chrArea = Forms!frmShiftDetail!Form!fsubEventDetail.cboArea;

unfortunately I keep getting a parameter dialogue box.
I'm obviously missing something.

You're close, but I think that ought to be ...

Where chrArea = Forms!frmShiftDetail!fsubEventDetail.Form!cboArea;

.... *provided* that "fsubEventDetail" is also the name of the subform
control (on the main form), and not just the name of the form object
displayed by that control. It's the name of the subform control you
have to use.

However, you might find it easier just to rewrite the rowsource of
cboObject in the AfterUpdate event of cboArea, embedding the value from
cboArea in the WHERE clause of the SQL statement as a literal. E.g,

Me.cboObject.RowSource = _
"SELECT DISTINCT chrObject FROM tblAreas " & _
"WHERE chrArea = " & Chr(34) & Me.cboArea & Chr(34)

That serves the dual purpose of applying the criterion and forcing the
combo to be requeried (because the rowsource has changed).

Problem No. 2: Linking tabbed sheets based on subforms.
My form frmShiftDetail has a tabbed control. One tab has the subform
I mentioned above. In addition, that tab holds text box controls
displaying date, shift and Operator name. The subform in this
context displays the log events entered by a given operator during
one shift. I have created a continuous form (fsubPP_Event_Overview)
based on a query that displays an abreviated version of each log
event. I'd like to add the continuous form to the second tab such
that the operator could use the continuous form to review previous
log entries. From the continuous form, I'd like to have a button
control that will display the detailed listing for the shift the log
was entered. The problem is that the matching fields between the
tabs are both on subforms. If the details tab is current, I'd like
to have the button respond based on what has focus. If a record is
selected within the subform, I'd like to reference the corresponding
record on the continuous form (both contain the field PPLog_ID ), if
something outside the subform has focus, I'd like the button to
simply move to the last record entered in the continuous form.

I don't quite follow what you're saying here. However, it may help to
know that you can link two subforms ("SubformA and "SubformB", for
argument's sake) by way of an extra control on the main form, which
serves as the Link Master Field for one of the subforms. For example,
you can have a hidden text box named "txtSubformLink", with it's
ControlSource property set to the expression,

=[SubformA].[Form]![PPLog_ID]

That is, it picks up the value of PPLog_ID from SubformA.

Then you use this text box, txtSubformLink, as the Link Master Field for
SubformB. Thus, SubformB always shows the records that are related to
the current record on SubformA. It's tricky, but it works.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I'm still struggling with my second problem. I added the txt
control as suggested. Access displays a message saying "You must add
tblLogIn_ID to your record source if you want to use this link." I
added that field to the query used to generate the subform, but the
result was that instead of getting a continuous form with all
operator log entries, it was limited to the log entries corresponding
to the specific shift identified by the tblLog_ID field (even though
I didn't use that field in the Subform Field Linker).

No, the technique I proposed would not have forced you to add anything
to your recordsource. I think you made a mistake in implementing it.
It's also possible I didn't understand what you're trying to accomplish,
but the technique does work.

Was that the exact error message you got? I've never seen one phrased
like that. What version of Access are you using? When did you get the
error message? What exactly did you put as the controlsource for the
linking text box, and what did you set as the Link Master and Child
Fields properties of the subforms?

I'm not sure if my design is the best structure here. My though is
to have two tabs; one tab will provide the place for data entry as
well as provide a space for detail. The structure of this tab is
three fields that identify the date, shift, and operator name along
with a continuous subform that uses multiple lines to provide detail
for a full log entry. The other tab has a subform set up as a
continuous form.I have limited the fields in the continuous form to
fit on one line. I'd like to be able to switch between the two tabs
smoothly (from the users perspective).

So the other subform is just a condensed version of the first? Or have
I missed something?

I'm wondering if I might need to give up on the continuous form in the
"details" tab. I'd like to have it since it would be a way to review
all log entries an operator made on one particular shift. (I showed
it to a couple operators who liked that structure as opposed to one
record per form). But they would have the continous form to scan the
last few entries, they would just need to click the mouse a couple
more times to see all the fields on multiple records.

I don't know enough to say whether your concept is a good one or a bad
one. I still don't have a very clear picture of what this is intended
to look like, and of the structure of the underlying data. What are the
tables involved, and what are the recordsources of the main form and
each subform?

 

[E.Q.]

No, the technique I proposed would not have forced you to add anything
to your recordsource. I think you made a mistake in implementing it.
It's also possible I didn't understand what you're trying to accomplish,
but the technique does work.

Was that the exact error message you got? I've never seen one phrased
like that. What version of Access are you using? When did you get the
error message? What exactly did you put as the controlsource for the
linking text box, and what did you set as the Link Master and Child
Fields properties of the subforms?

That is the exact message of the error. I work on a couple different
machines. The old one in the field has Access 2000, the newer one in my
office has Access 2003. I think I initially started working on the project
while out in the field, but the error came up on the newer machine. The
control source for the linking textbox is: =fsubEventDetail.Form!txtPPLogID
I've tried to enter "txtSubformLink" as the master and idsPPLogID as the
child links, but I get a different error message: "The text you entered isn't
an item on the list. Select an item on the list or enter text that matches
on of the listed items."

So the other subform is just a condensed version of the first? Or have
I missed something?

In general yes one subform is a condensed version of the other. The
"detail" subform contains a timestamp, a boolean (though I may add more
later), but the big thing is a memo field. (Most log entries are short, but
I wanted to add a memo field for the more loquacious notes that some of my
operators make on occassion. I want to accommodate both the occassional need
for a long log entry with the daily need to review all log entries since the
last time the operator worked a particular work area.)

I don't know enough to say whether your concept is a good one or a bad
one. I still don't have a very clear picture of what this is intended
to look like, and of the structure of the underlying data. What are the
tables involved, and what are the recordsources of the main form and
each subform?

The database consist of six tables, two are to fill in combo boxes, one was
generated by Access by the import wizard, though that one isn't used.
Another was imported from another application and is queried to provide the
operator ID. That leaves tblLogIn, which has fields idsID, chrShift,
chrOperator, and dtmDate. And tblEventLog with fields idsPPLogID, dtmTime,
chrArea, chrObject, blnW/O, chrEventHeadline, and memEventDetail.

The main form is called frmShiftDetail. It has a tab control with two tabs.
On the tab labled "Detail" are the controls: txtDate (from
tblLogIn.dtmDate), cboShift(from tblLogIn.chrShift), cboOperator (from
tblLogIn.chrOperator), subform fsubEventDetail, and now txtSubformLink. That
subform has controls dtmTime, cboArea, cboObject, blnW/O, txtEvent Headline,
txtEventDetail all from tblEventLog. This is set up as a continuous form.
Its Master LInk is iidsID, and child link is intLogIn_Id It's a nice set-up
because one screen can usually hold all entries from the shift defined by
Date and shift.
The other tab, labled "Overview" only holds fsubEvent_Overview. This
continuous subform has only six controls txtDate, cboShift, cboOperator, all
from tblLogIn and cboArea, cboArea, cboObject, and txtEvent Headline, all
from tbl_Event Log.
When I created the form, I toggled off the data entry, deleting, and
addition capabilites.
Individually, each tab look OK and seems to function as I would like. The
problem is that the tabs aren't linked. (I did try to link through
tblLogIn.idsID but that ended up filtering the continuous form to only those
events that occurred on the shift specified on the "details" tab.