Need help getting query data from subform

[Kenny A.]

I am working with Access 2003 SP3. I have a mainform (POLLTROUBLE) with a
subform (PTSUBFORM1). 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

 

[Marshall Barton]

I am working with Access 2003 SP3. I have a mainform (POLLTROUBLE) with a
subform (PTSUBFORM1). 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.

DLookup finds the first record that meets its criteria. For
a table the "first" record can be any record. So that's not
a good way to find a particular record unless the criteria
os on the table's primary key. In your case you did not
specify the field to compare to PollID. It would need to be
more like:
DLookup("POLLTROUBLE", "qrysf1PT", "PollID=" & Me.POLLID)

Because of filtering, there may(?) be different records in
the table that match the criteria from the records displayed
in the form. For cade in the main form to find the first
record in the subform, a better way is to use code like:

With Me.subformcontrol,RecordsetClone
If .Recount > 0 Then
.MoveFirst
mytable("PollTrouble") = !POLLTROUBLE
End If
End With

I have no idea what mytable("PollTrouble") is supposed to
represent, and I don't see how that syntax can be legal.
You said you want it in some table and I seriously doubt
that will do it.

Putting the result of that in a table can be a bad idea so
I'm not confident about your objective.

 

[Kenny A.]

Marshall,
Thanks for the reply. You question about "Mytable" comes from: Set
mytable = mydb.OpenRecordset("PTSUMHIST", DB_OPEN_TABLE) ' Open table.
mytable.AddNew ' Create new record. and the
"mytable("Polltrouble")" is one of the fields in the table that I just opened
and trying to write the value to.

Could you please explaine the purpose of the "With / End With" code and the
RecordsetClone. I have not used either of these in the past and would like to
understand better the use of these.

Thanks
Kenny

 

[Marshall Barton]

Ahhh, MyTable is a recordset, now it make sense, at least
the syntax does. I'm still questioning why you need to copy
it to that table.

With is a statement that allows you to specify an object
reference once and then refer to the properties/methods of
the object without having to repeat the full object
reference over and over.

Every form has a recordset (Me.Recordset) and a clone of the
recordset (Me.RecordsetClone). The clone can have a
different current record from the form's current record.
E.g. if you used MoveFirst/FindFirst/Bookmark/etc on the
form's recordset you could change the forms current record.
Doing those operations on the clone recordset does not
affect the Form's current record.

You may find my explations kind of confusing. If so, maybe
you'll get a better description in VBA Help (not Access
Help).

 

[Kenny A.]

Marshall,
I am writing the value of POLLTROUBLE (along with other values) to a
history table that we can report from over time. In the With statement, after
subformcontrol you have a comma, I am assuming it should have been a period??
Even when I try it with a period, I am getting a Compiler error: Method or
data member not found. this is the code I put in:

With Me.PTSUBFORM1.RecordsetClone
If .Recount > 0 Then
.MoveFirst
mytable("PollTrouble") = !POLLTROUBLE
End If
End With

PTSUBFORM1 is the subform and qrysf1PT is the query that is supplying the
data to the subform. What am I still doing wrong?

Thanks

Ahhh, MyTable is a recordset, now it make sense, at least
the syntax does. I'm still questioning why you need to copy
it to that table.

With is a statement that allows you to specify an object
reference once and then refer to the properties/methods of
the object without having to repeat the full object
reference over and over.

Every form has a recordset (Me.Recordset) and a clone of the
recordset (Me.RecordsetClone). The clone can have a
different current record from the form's current record.
E.g. if you used MoveFirst/FindFirst/Bookmark/etc on the
form's recordset you could change the forms current record.
Doing those operations on the clone recordset does not
affect the Form's current record.

You may find my explations kind of confusing. If so, maybe
you'll get a better description in VBA Help (not Access
Help).
--
Marsh
MVP [MS Access]

Thanks for the reply. You question about "Mytable" comes from: Set
mytable = mydb.OpenRecordset("PTSUMHIST", DB_OPEN_TABLE) ' Open table.
mytable.AddNew ' Create new record. and the
"mytable("Polltrouble")" is one of the fields in the table that I just opened
and trying to write the value to.

Could you please explaine the purpose of the "With / End With" code and the
RecordsetClone. I have not used either of these in the past and would like to
understand better the use of these.

 

[Marshall Barton]

I am writing the value of POLLTROUBLE (along with other values) to a
history table that we can report from over time. In the With statement, after
subformcontrol you have a comma, I am assuming it should have been a period??
Even when I try it with a period, I am getting a Compiler error: Method or
data member not found. this is the code I put in:

With Me.PTSUBFORM1.RecordsetClone
If .Recount > 0 Then
.MoveFirst
mytable("PollTrouble") = !POLLTROUBLE
End If
End With

PTSUBFORM1 is the subform and qrysf1PT is the query that is supplying the
data to the subform. What am I still doing wrong?

Arrggghhhh. the comma wasn't the only thing I did wrong on
that line. I meant it to be:

With Me.PTSUBFORM1.Form.RecordsetClone

 

[Kenny A.]

Marshall,
I am writing the value (along with other values) to the new table to keep
a history that we can report on later.

In your With statement, after subformcontrol, you have a comma. I am
assuming it was supposed to be a period. Even with the change to the period,
I am getting a Compile error: Method or data member not found.

This is my code:

With Me.PTSUBFORM1.RecordsetClone
If .Recount > 0 Then
.MoveFirst
mytable("PollTrouble") = !POLLTROUBLE
End If
End With

What am I still doing wrong?

Kenny

Ahhh, MyTable is a recordset, now it make sense, at least
the syntax does. I'm still questioning why you need to copy
it to that table.

With is a statement that allows you to specify an object
reference once and then refer to the properties/methods of
the object without having to repeat the full object
reference over and over.

Every form has a recordset (Me.Recordset) and a clone of the
recordset (Me.RecordsetClone). The clone can have a
different current record from the form's current record.
E.g. if you used MoveFirst/FindFirst/Bookmark/etc on the
form's recordset you could change the forms current record.
Doing those operations on the clone recordset does not
affect the Form's current record.

You may find my explations kind of confusing. If so, maybe
you'll get a better description in VBA Help (not Access
Help).
--
Marsh
MVP [MS Access]

Thanks for the reply. You question about "Mytable" comes from: Set
mytable = mydb.OpenRecordset("PTSUMHIST", DB_OPEN_TABLE) ' Open table.
mytable.AddNew ' Create new record. and the
"mytable("Polltrouble")" is one of the fields in the table that I just opened
and trying to write the value to.

Could you please explaine the purpose of the "With / End With" code and the
RecordsetClone. I have not used either of these in the past and would like to
understand better the use of these.

 

[Kenny A.]

Marshall,

I am writing the value from the recordset (along with other values) to
the new table, to keep a history that we can later report against.

In your With statement, after the subformcontrol, you have a comma. I am
assuming it should have been a period. Even when I change it to a period, I
am getting a Compile error: Method or data member not found. Here is my
code:

With Me.PTSUBFORM1.RecordsetClone
If .Recount > 0 Then
.MoveFirst
mytable("PollTrouble") = !POLLTROUBLE
End If
End With

What am I still doing wrong?

Ahhh, MyTable is a recordset, now it make sense, at least
the syntax does. I'm still questioning why you need to copy
it to that table.

With is a statement that allows you to specify an object
reference once and then refer to the properties/methods of
the object without having to repeat the full object
reference over and over.

Every form has a recordset (Me.Recordset) and a clone of the
recordset (Me.RecordsetClone). The clone can have a
different current record from the form's current record.
E.g. if you used MoveFirst/FindFirst/Bookmark/etc on the
form's recordset you could change the forms current record.
Doing those operations on the clone recordset does not
affect the Form's current record.

You may find my explations kind of confusing. If so, maybe
you'll get a better description in VBA Help (not Access
Help).
--
Marsh
MVP [MS Access]

Thanks for the reply. You question about "Mytable" comes from: Set
mytable = mydb.OpenRecordset("PTSUMHIST", DB_OPEN_TABLE) ' Open table.
mytable.AddNew ' Create new record. and the
"mytable("Polltrouble")" is one of the fields in the table that I just opened
and trying to write the value to.

Could you please explaine the purpose of the "With / End With" code and the
RecordsetClone. I have not used either of these in the past and would like to
understand better the use of these.

 

[Kenny A.]

Marshall,

In your With statement, after the subformcontrol, you have a comma. I am
assuming it should be a period. Even with that change, I am getting a Compile
error: Method or data member not found.

This is my code:

With Me.PTSUBFORM1.RecordsetClone
If .Recount > 0 Then
.MoveFirst
mytable("PollTrouble") = !POLLTROUBLE
End If
End With

What am I still doing wrong? The name of the subform is PTSUBFORM1 and the
Source Object is also PTSUBFORM1.

Thanks
Kenny

Ahhh, MyTable is a recordset, now it make sense, at least
the syntax does. I'm still questioning why you need to copy
it to that table.

With is a statement that allows you to specify an object
reference once and then refer to the properties/methods of
the object without having to repeat the full object
reference over and over.

Every form has a recordset (Me.Recordset) and a clone of the
recordset (Me.RecordsetClone). The clone can have a
different current record from the form's current record.
E.g. if you used MoveFirst/FindFirst/Bookmark/etc on the
form's recordset you could change the forms current record.
Doing those operations on the clone recordset does not
affect the Form's current record.

You may find my explations kind of confusing. If so, maybe
you'll get a better description in VBA Help (not Access
Help).
--
Marsh
MVP [MS Access]

Thanks for the reply. You question about "Mytable" comes from: Set
mytable = mydb.OpenRecordset("PTSUMHIST", DB_OPEN_TABLE) ' Open table.
mytable.AddNew ' Create new record. and the
"mytable("Polltrouble")" is one of the fields in the table that I just opened
and trying to write the value to.

Could you please explaine the purpose of the "With / End With" code and the
RecordsetClone. I have not used either of these in the past and would like to
understand better the use of these.

 

[Kenny A.]

Marshall,

Sorry about the multiple posting with the same thing. I thought there was
a problem either on my side or this discussion boards side, and the postings
each time were not making it up to here. I was not able to see them until
the following day. Then I realized all four made it up. Again sorry for the
multiple repostings.

I still do need the help though.

Kenny

Ahhh, MyTable is a recordset, now it make sense, at least
the syntax does. I'm still questioning why you need to copy
it to that table.

With is a statement that allows you to specify an object
reference once and then refer to the properties/methods of
the object without having to repeat the full object
reference over and over.

Every form has a recordset (Me.Recordset) and a clone of the
recordset (Me.RecordsetClone). The clone can have a
different current record from the form's current record.
E.g. if you used MoveFirst/FindFirst/Bookmark/etc on the
form's recordset you could change the forms current record.
Doing those operations on the clone recordset does not
affect the Form's current record.

You may find my explations kind of confusing. If so, maybe
you'll get a better description in VBA Help (not Access
Help).
--
Marsh
MVP [MS Access]

Thanks for the reply. You question about "Mytable" comes from: Set
mytable = mydb.OpenRecordset("PTSUMHIST", DB_OPEN_TABLE) ' Open table.
mytable.AddNew ' Create new record. and the
"mytable("Polltrouble")" is one of the fields in the table that I just opened
and trying to write the value to.

Could you please explaine the purpose of the "With / End With" code and the
RecordsetClone. I have not used either of these in the past and would like to
understand better the use of these.