Trying to get a value from a query in a subform

[Kenny]

I am working with Access 2003 SP3. I have a mainform (POLLTROUBLE)
with a
subform (PTSUBFORM1), don't know if makes a difference or not, but
subform is in detail section of mainform. The subform is linked to
the mainform by Link Child Fields and Link Master Fields = pollid. I
have a query (qrysf1PT) that returns a result set for the subform. The
subform displays 4 columns (POLLID, POLLTROUBLE, TDAT, and ACTION). As
I move through the records in the mainform, the associated records
(multiple) in the subform are displayed (as expected) The records in
the subform are listed in descending order based on
TDAT (date field). What I am trying to do is, retrieve the value in
the column POLLTROUBLE of the first record listed for the associated
record in the mainform, and pass this to another table via VBA code. I
am using the
DLookup function, but I do not seem to be retrieving the proper value
of the associated record. This is the code I am using: mytable
("PollTrouble") = DLookup("POLLTROUBLE", "qrysf1PT", POLLID). Mytable
is defined in earlier code. When I am stepping through the code,
POLLID has the proper value, but the associated value of POLLTROUBLE
is not correct. It has a different value, and I do not know which
record it is actually looking at when it pulls this wrong value.
What am I doing wrong? Please help point me in the right direction.

Thank you in advance

Kenny

 

[Jeff Boyce]

Kenny

Are you describing "forms" or "reports"? I don't recall a "detail section
of " a form...

Next, the 'value' of the record in the subform is the bound column of that
record, which is ?

"How" depends on "what", as in "what data are you working from?" You've
described the form and subform, but not the underlying table structure.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jeff Boyce]

Man?! I have to read the screen, too?!

(thanks!)

Jeff B.

(I don't recall ever seeing that before... was that part of the latest
service release?<g>)

I think you might kick yourself, Jeff. Its that part of the form which is
headed 'Detail' in design view.

Ken Sheridan
Stafford, England

Kenny

Are you describing "forms" or "reports"? I don't recall a "detail section
of " a form...

Next, the 'value' of the record in the subform is the bound column of that
record, which is ?

"How" depends on "what", as in "what data are you working from?" You've
described the form and subform, but not the underlying table structure.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

I am working with Access 2003 SP3. I have a mainform (POLLTROUBLE)
with a

[quoted text clipped - 22 lines]

Kenny

 

[Kenny]

Kenny:

The criterion for the DLookup function needs to be an expression which
evaluates to True or False; you are simply giving the POLLID column name.
True means Not False and as a Boolean False is implemented in Access as a
zero then any non-zero value will evaluate to True.  I'd imagine the return
value of the Dlookup function would therefore be that of the POLLTROUBLE
column in the first row in the query's sort order.  What I think you need is:

DLookup("POLLTROUBLE", "qrysf1PT", "POLLID = " & [POLLID])

However, I'm concerned that you want to "pass this to another table".  This
smacks of redundancy.  Normally derived data like this would be retrieved on
the fly by a query not stored in a table.  If for this example we call the
table underlying the subform SubTable the you can retrieve the latest value
of POLLTROUBLE by TDAT for each POLLID with:

SELECT pollid, polltrouble
FROM  SubTable AS ST1
WHERE tdat =
    (SELECT MAX(tdat)
      FROM SubTable As ST2
      WHERE ST2.pollid = ST1.pollid);

Ken Sheridan
Stafford, England

I am working with Access 2003 SP3. I have a mainform (POLLTROUBLE)
with a
subform (PTSUBFORM1), don't know if makes a difference or not, but
subform is in detail section of mainform.  The subform is linked to
the mainform by Link Child Fields and Link Master Fields = pollid. I
have a query (qrysf1PT) that returns a result set for the subform. The
subform displays 4 columns (POLLID, POLLTROUBLE, TDAT, and ACTION). As
I move through the records in the mainform, the associated records
(multiple) in the subform are displayed (as expected) The records in
the subform are listed in descending order based on
TDAT (date field).  What I am trying to do is, retrieve the value in
the column POLLTROUBLE of the first record listed for the associated
record in the mainform, and pass this to another table via VBA code. I
am using the
DLookup function, but I do not seem to be retrieving the proper value
of the associated record. This is the code I am using:   mytable
("PollTrouble") = DLookup("POLLTROUBLE", "qrysf1PT", POLLID).  Mytable
is defined in earlier code. When I am stepping through the code,
POLLID has the proper value, but the associated value of POLLTROUBLE
is not correct.  It has a different value, and I do not know which
record it is actually looking at when it pulls this wrong value.
What am I doing wrong? Please help point me in the right direction.

Thank you in advance

Kenny

Ken,
The reason why I am writing the value (along with others) to
another table is to keep a history that we can report off of. This
will not be creating redundancy. The subform (PTSUBFORM1) gets its
data via a query (qrysf1PT). I am trying to figure out how to get a
value from the the first record listed in the subform. How can I
access the value in the POLLTROUBLE column of the first record listed
in the subform for the current record selected in the mainform? What
should my code look like? I don't think that using DLookup works for
this situation. Since the records listed in the subform are just a
subset of the total dataset being returned to the subform via the
query, how can I properly address the piece of data I am after?

Thanks
Kenny

 

[Kenny]

Kenny:

If thesubformis returning the rows in whatever the sort order of qrysf1PT
is then the expression I posted should do it as it is restricted on the
PollID column:

DLookup("POLLTROUBLE", "qrysf1PT", "POLLID = " & [POLLID])

If thesubformis sorted independently of thequery'ssort order, i.e. by
means of the OrderBy property of thesubform, then it will depend on what the
sort order is.  In this case the expression would have to be extended so that
it looks up the first row in the relevant sort order, but without knowing
what that is I can't say more than that,

Another approach would be to examine the first row in thesubform's
underlying recordset by means of a function in the parent form's module, e.g.

Private Function GetPollTrouble()

    Dim frm As Form
    Dim rst As Object

    Set frm = Me.[YourSubformControl].Form
    Set rst = frm.Recordset.Clone

    With rst
        .MoveFirst
        GetPollTrouble = .Fields("PollTrouble")
    End With

End Function

Note that YourSubformControl is the name of the control in the parent form's
Controls collection which houses thesubform, not the name of the underlying
form object; unless both have the same name of course.

You can then reference the function in code or, for instance, set the
ControlSource property of a text box in the parent form to:

=GetPollTrouble()

That shouldgetthe first row currently in thesubform, even if you've
filtered thesubformor changed its sort order via the menu or toolbar.

Ken Sheridan
Stafford, England

[quoted text clipped - 54 lines]

- Show quoted text -

Ken,
   The reason why I am writing thevalue(along with others) to
another table is to keep a history that we can report off of. This
will not be creating redundancy. Thesubform(PTSUBFORM1) gets its
data via aquery(qrysf1PT). I amtryingto figure out how togeta
valuefrom the first record listed in thesubform. How can I
access thevaluein the POLLTROUBLE column of the first record listed
in thesubformfor the current record selected in the mainform? What
should my code look like? I don't think that using DLookup works for
this situation. Since the records listed in thesubformare just a
subset of the total dataset being returned to thesubformvia the
query, how can I properly address the piece of data I am after?

Thanks
Kenny

Ken,
Using the With / With End code was just the thing I needed. It's
working now. Thanks for your help.
Kenny