Subform: record select form other subform

[Guest]

I have two subforms, on a master subform of a form. While viewing one of the
lowest level subforms (continuous form) I use a button to setfocus to a
subform (single form) on the same level. I want to retrieve the record on
the continuous that the button is associated with.

My code so far:

Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus

With Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm]
.RecordsetClone.FindFirst "InvNum = """ & Me!txtInvNum & """"
If .NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

It sets the focus to the other subform, but it fails on the ".Recordset..."
line. Error #438,"Object doesn't support this property or method.

On the continuous form, the control for [InvNum] is named "txtInvNum", as it
is on the single form.

Is this the correct method and, if so, what am I doing wrong?

 

[Marshall Barton]

I have two subforms, on a master subform of a form. While viewing one of the
lowest level subforms (continuous form) I use a button to setfocus to a
subform (single form) on the same level. I want to retrieve the record on
the continuous that the button is associated with.

My code so far:

Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus

With Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm]
.RecordsetClone.FindFirst "InvNum = """ & Me!txtInvNum & """"
If .NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

It sets the focus to the other subform, but it fails on the ".Recordset..."
line. Error #438,"Object doesn't support this property or method.

On the continuous form, the control for [InvNum] is named "txtInvNum", as it
is on the single form.

Is this the correct method and, if so, what am I doing wrong?

You forgot the .Form property at the end of the With
statement.

 

[Guest]

Thanks. Did I understand correctly? It should read "With Forms![frm1
Client].Form![Reservationfrm].Form![Jobfrm].Form"?

If so, that returns Error 3464 data type mismatch....on the .Recordset line.


--
Thanks for your help,
Chris

I have two subforms, on a master subform of a form. While viewing one of the
lowest level subforms (continuous form) I use a button to setfocus to a
subform (single form) on the same level. I want to retrieve the record on
the continuous that the button is associated with.

My code so far:

Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus

With Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm]
.RecordsetClone.FindFirst "InvNum = """ & Me!txtInvNum & """"
If .NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

It sets the focus to the other subform, but it fails on the ".Recordset..."
line. Error #438,"Object doesn't support this property or method.

On the continuous form, the control for [InvNum] is named "txtInvNum", as it
is on the single form.

Is this the correct method and, if so, what am I doing wrong?

You forgot the .Form property at the end of the With
statement.

 

[Marshall Barton]

Thanks. Did I understand correctly? It should read "With Forms![frm1
Client].Form![Reservationfrm].Form![Jobfrm].Form"?

If so, that returns Error 3464 data type mismatch....on the .Recordset line.

Yes, that's what I meant. Note that you have a redundant
..Form after Forms![frm1 Client]

The type mismatch implies that InvNum is a numeric type
field in it's table. If so, then don't enclose it in
quotes:

.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum

 

[Guest]

Thanks again, Marsh.

I don't follow the redundant "..form" comment.

The previous error went away, now I fail on "If .NoMatch...", #2465
application-defined or object-defined error.

Code now:
Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus

With Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].Form
.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum
If .NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

FYI, I my two forms are:

Continuous:
Forms![frm1 Client].Form![Reservationfrm].Form![ReservationViewfrm]...

Single form:
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm]...

 

[Marshall Barton]

I don't follow the redundant "..form" comment.

The previous error went away, now I fail on "If .NoMatch...", #2465
application-defined or object-defined error.

Code now:
Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus

With Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].Form
.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum
If .NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

FYI, I my two forms are:

Continuous:
Forms![frm1 Client].Form![Reservationfrm].Form![ReservationViewfrm]...

Single form:
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm]...

A member of the Forms collection is a form object so it is
redundant to use the .Form property of what is already a
form object. I.e.
With Forms![frm1 Client]![Reservationfrm].Form![Jobfrm].Form
will work the same as what you have.

NoMatch is a property of the recordset object, not the form
object (from the With). Use this instead:
If .RecordsetClone.NoMatch Then

 

[Guest]

Thanks once again. The code works! At least it doesn't fail for code.

However, I am only successful in retrieving the first record. Each client
can have multiple records and, other than the first, returns the message "no
record."

Why would that be?

--
Thanks for your help,
Chris

I don't follow the redundant "..form" comment.

The previous error went away, now I fail on "If .NoMatch...", #2465
application-defined or object-defined error.

Code now:
Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus

With Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].Form
.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum
If .NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

FYI, I my two forms are:

Continuous:
Forms![frm1 Client].Form![Reservationfrm].Form![ReservationViewfrm]...

Single form:
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm]...

A member of the Forms collection is a form object so it is
redundant to use the .Form property of what is already a
form object. I.e.
With Forms![frm1 Client]![Reservationfrm].Form![Jobfrm].Form
will work the same as what you have.

NoMatch is a property of the recordset object, not the form
object (from the With). Use this instead:
If .RecordsetClone.NoMatch Then

 

[Marshall Barton]

Thanks once again. The code works! At least it doesn't fail for code.

However, I am only successful in retrieving the first record. Each client
can have multiple records and, other than the first, returns the message "no
record."

Can't tell without seeing what you are doing to "retrieve"
other records.

It should be fairly obvious that FindFirst only finds the
first matching record. You haven't explained what you are
trying to accomplish, but maybe you want to use FindNext???

 

[Guest]

Thanks, Marsh.

Please remember remember my learning-lightweight-amateur status. I could
reason that FindFirst would find the first iteration of InvNum = InvNum,
which is the key field, thus there would be only one. Trouble is, it only
retrieves the first. How would I know that?

What is comparable to FindFirst that would find only the matching invoice
numbers?

I truly thought I had explained my intent. But here goes again!

On the continuous subform the user selects a record of interest by clicking
that record's associated "get job" button. The button would navigate the
user to another tab/subform which contains a single form that would display
the record as was selected on the continuous form (by selecting the desired
record's associated button).

Please let me know if I am not clear enough. It is simple from here...but I
recognize you need to know what I want before you can help me.

 

[Guest]

FindNext results in "record not found".

 

[Marshall Barton]

Please remember remember my learning-lightweight-amateur status. I could
reason that FindFirst would find the first iteration of InvNum = InvNum,
which is the key field, thus there would be only one. Trouble is, it only
retrieves the first. How would I know that?

What is comparable to FindFirst that would find only the matching invoice
numbers?

I truly thought I had explained my intent. But here goes again!

On the continuous subform the user selects a record of interest by clicking
that record's associated "get job" button. The button would navigate the
user to another tab/subform which contains a single form that would display
the record as was selected on the continuous form (by selecting the desired
record's associated button).

Please let me know if I am not clear enough. It is simple from here...but I
recognize you need to know what I want before you can help me.

What I am confused about is this "first one" you are talking
about. The first of what? I'm pretty sure you said that
the InvNum field is the primary key, so the FindFirst will
find the one and only record.

Pease help me understand what you mean by:
"... Trouble is, it only retrieves the first. ...
"What is comparable to FindFirst that would
"find only the matching invoice numbers?"

Maybe the problem is that your continuous subform's records
are not invoice records?? If the continuous subform has
records from the many side table in a one to many
relationship with the invoice table, then we are searching
for the wrong field. Instead of the InvNum field, we should
be using the primary key field in the continuous subform's
records.

If that is off base, then please explain more about the
tables that are involved in your arrangement of forms,
subforms and subsubforms.

 

[Guest]

The continuous form is based on a query that sorts by date descending -- that
is the "first one" I referred to. Any selection other than the first returns
the no record message. That could have been clearer, sorry.

For clarification...
Continous form: for the client as selected on its parent form, lists all of
their invoice (InvNum) history as one line summary, with button to navigate
to details.

Single form: for the invoice selected by the button on the continous form,
shows that selected invoice's details.

Both forms and their record source queries are based on the same table -
invoices, where InvNum is the key field.

One client to many invoices, RefInt, both cascades.

Does this shed light?

Thanks for your help,
Chris

 

[Guest]

The continuous form is based on a query that sorts by date descending -- that
is the "first one" I referred to. Any selection other than the first returns
the no record message. That could have been clearer, sorry.

For clarification...
Continous form: for the client as selected on its parent form, lists all of
their invoice (InvNum) history as one line summary, with button to navigate
to details.

Single form: for the invoice selected by the button on the continous form,
shows that selected invoice's details.

Both forms and their record source queries are based on the same table -
invoices, where InvNum is the key field.

One client to many invoices, RefInt, both cascades.

Does this shed light?

Thanks for your help,
Chris

 

[Marshall Barton]

The continuous form is based on a query that sorts by date descending -- that
is the "first one" I referred to. Any selection other than the first returns
the no record message. That could have been clearer, sorry.

For clarification...
Continous form: for the client as selected on its parent form, lists all of
their invoice (InvNum) history as one line summary, with button to navigate
to details.

Single form: for the invoice selected by the button on the continous form,
shows that selected invoice's details.

Both forms and their record source queries are based on the same table -
invoices, where InvNum is the key field.

One client to many invoices, RefInt, both cascades.

Ok, that's what I originally thought you were describing.

So, the situation is that when you click on the first record
in the continuous subform, everything works the way you want
it to. But, when you click on any other record in the
continuous subform, the code produces the message "No
Record".

If that's a correct summary, then I don't see how the code I
think you have can cause a problem. I am unfamiliar with
that error message, are you sure that's an exact copy of the
message?

Maybe there's something else getting in the way?? Please
post the single form's Link Master/Child properties so I can
see if they are filtering the form's recordset too much. I
would also like to see the continuous subform's record
source query's SQL view to see if it has a criteria that's
filtering too much.

Also post a Copy/Paste of the code as you currently have it.

 

[Guest]

The "no record" message I get is from my code (see below), not a system
message.

Continuous form's Master/Child: ClientID/ClientID
Single form's Master/Child: ClientID/InvNum

Continuous form SQL:
SELECT [tbl 2 Job].ClientID, [tbl 2 Job].InvNum, [tbl 2 Job].Service, [tbl 2
Job].StartDate, [tbl 2 Job].EndDate, [tbl 2 Job].PastEndDate,
Nz([QuotedAmount])-Nz([JobDiscountAmount])-Nz([Coupon])+Nz([PriceAdjustment])-Nz([CrAmtJob])
AS TotalDue, [tbl 2 Job].PaidInFull, Nz([TotalDue])-Nz([AmountReceived]) AS
NetDue, [tbl 2 Job].PmtType, [tbl 2 Job].QuotedAmount, [tbl 2 Job].Cancelled,
[tbl 2 Job].CRAmtJob, [tbl 2 Job].AmountReceived
FROM [tbl 1 CLIENT] RIGHT JOIN [tbl 2 Job] ON [tbl 1 CLIENT].ClientID = [tbl
2 Job].ClientID
ORDER BY [tbl 2 Job].StartDate DESC;

Continuous form navigation button code:
Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus
With Forms![frm1 Client]![Reservationfrm].Form![Jobfrm].Form
.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum
If .RecordsetClone.NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

 

[Marshall Barton]

The "no record" message I get is from my code (see below), not a system
message.

Continuous form's Master/Child: ClientID/ClientID
Single form's Master/Child: ClientID/InvNum

Continuous form SQL:
SELECT [tbl 2 Job].ClientID, [tbl 2 Job].InvNum, [tbl 2 Job].Service, [tbl 2
Job].StartDate, [tbl 2 Job].EndDate, [tbl 2 Job].PastEndDate,
Nz([QuotedAmount])-Nz([JobDiscountAmount])-Nz([Coupon])+Nz([PriceAdjustment])-Nz([CrAmtJob])
AS TotalDue, [tbl 2 Job].PaidInFull, Nz([TotalDue])-Nz([AmountReceived]) AS
NetDue, [tbl 2 Job].PmtType, [tbl 2 Job].QuotedAmount, [tbl 2 Job].Cancelled,
[tbl 2 Job].CRAmtJob, [tbl 2 Job].AmountReceived
FROM [tbl 1 CLIENT] RIGHT JOIN [tbl 2 Job] ON [tbl 1 CLIENT].ClientID = [tbl
2 Job].ClientID
ORDER BY [tbl 2 Job].StartDate DESC;

Continuous form navigation button code:
Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus
With Forms![frm1 Client]![Reservationfrm].Form![Jobfrm].Form
.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum
If .RecordsetClone.NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

I see two glaring errors here.

The single form's Master/Child: ClientID/InvNum
It doesn't make sense to me that invoice numbers must be the
same as the clientID, please double check this.

It may not make a difference if the tables hace the right
properties, but the RIGHT JOIN in the query could
conceivably produce some strange effects in your situation.

 

[Guest]

Does the following change your previous response?

Main form: clients

Subform level 1:
Reservations; form is container for tabs with subforms of InvNum and
ClientID related information. Master/Client=ClientID/ClientID

Subform level2:

Contiuous: Selected client's invoices summary. Master/Child:ClientID/ClientID

Single form:
Selected (at continuous form) client invoice details. Master/Child:
ClientID/InvNum.

I was advised a while back here at this NG to build this container so that I
could limit the invoices to the selected client. I also have the related
tabs/subforms which we have not discussed that are either InvNum or ClientID
dependent. This structure allows those all to work fine -- except for this
navigation button to select the desired record on the continous form.

If there is a better way that does the same and warrants changing, I'm
prepared to try it. I need to show the invoice summary of the client upon
viewing client and allow for the user to go to a details tab/subform.


--
Thanks for your help,
Chris

The "no record" message I get is from my code (see below), not a system
message.

Continuous form's Master/Child: ClientID/ClientID
Single form's Master/Child: ClientID/InvNum

Continuous form SQL:
SELECT [tbl 2 Job].ClientID, [tbl 2 Job].InvNum, [tbl 2 Job].Service, [tbl 2
Job].StartDate, [tbl 2 Job].EndDate, [tbl 2 Job].PastEndDate,
Nz([QuotedAmount])-Nz([JobDiscountAmount])-Nz([Coupon])+Nz([PriceAdjustment])-Nz([CrAmtJob])
AS TotalDue, [tbl 2 Job].PaidInFull, Nz([TotalDue])-Nz([AmountReceived]) AS
NetDue, [tbl 2 Job].PmtType, [tbl 2 Job].QuotedAmount, [tbl 2 Job].Cancelled,
[tbl 2 Job].CRAmtJob, [tbl 2 Job].AmountReceived
FROM [tbl 1 CLIENT] RIGHT JOIN [tbl 2 Job] ON [tbl 1 CLIENT].ClientID = [tbl
2 Job].ClientID
ORDER BY [tbl 2 Job].StartDate DESC;

Continuous form navigation button code:
Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus
With Forms![frm1 Client]![Reservationfrm].Form![Jobfrm].Form
.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum
If .RecordsetClone.NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

I see two glaring errors here.

The single form's Master/Child: ClientID/InvNum
It doesn't make sense to me that invoice numbers must be the
same as the clientID, please double check this.

It may not make a difference if the tables hace the right
properties, but the RIGHT JOIN in the query could
conceivably produce some strange effects in your situation.

 

[Marshall Barton]

Does the following change your previous response?

Main form: clients

Subform level 1:
Reservations; form is container for tabs with subforms of InvNum and
ClientID related information. Master/Client=ClientID/ClientID

Subform level2:

Contiuous: Selected client's invoices summary. Master/Child:ClientID/ClientID

Single form:
Selected (at continuous form) client invoice details. Master/Child:
ClientID/InvNum.

I was advised a while back here at this NG to build this container so that I
could limit the invoices to the selected client. I also have the related
tabs/subforms which we have not discussed that are either InvNum or ClientID
dependent. This structure allows those all to work fine -- except for this
navigation button to select the desired record on the continous form.

If there is a better way that does the same and warrants changing, I'm
prepared to try it. I need to show the invoice summary of the client upon
viewing client and allow for the user to go to a details tab/subform.

This additional information about the main form and subform
is about what I suspected. It does confirm that I hadn't
gone down the wrong track, but it does not change what I
think the problem probably is. The single form's Link
Master/Child should be the same as for the single form:
ClientID/ClientID
Otherwise the single form can not contain the same set of
records as the continuous form.

With the Link Child property set to InvNum, the only records
that will show up in the single form are the unlikely ones
where the InvNum just happens, by coincidence, to be the
same as the ClientID, probably the first client and first
invoice entered into the tables. You can check my
hypothesis by looking in the table at the one record the
code does find to see if the two fields are the same.

I really think that RIGHT JOIN should be an INNER JOIN even
if you do have RI set on the relationship.

 

[Guest]

Marsh...I got an email notification of a new response here...but there is no
unread response. Since I haven't heard from you regards my last posting as
yet, I want to ensure you didn't respond and it somehow got lost. I doubt
it...but checking.
--
Thanks for your help,
Chris

The "no record" message I get is from my code (see below), not a system
message.

Continuous form's Master/Child: ClientID/ClientID
Single form's Master/Child: ClientID/InvNum

Continuous form SQL:
SELECT [tbl 2 Job].ClientID, [tbl 2 Job].InvNum, [tbl 2 Job].Service, [tbl 2
Job].StartDate, [tbl 2 Job].EndDate, [tbl 2 Job].PastEndDate,
Nz([QuotedAmount])-Nz([JobDiscountAmount])-Nz([Coupon])+Nz([PriceAdjustment])-Nz([CrAmtJob])
AS TotalDue, [tbl 2 Job].PaidInFull, Nz([TotalDue])-Nz([AmountReceived]) AS
NetDue, [tbl 2 Job].PmtType, [tbl 2 Job].QuotedAmount, [tbl 2 Job].Cancelled,
[tbl 2 Job].CRAmtJob, [tbl 2 Job].AmountReceived
FROM [tbl 1 CLIENT] RIGHT JOIN [tbl 2 Job] ON [tbl 1 CLIENT].ClientID = [tbl
2 Job].ClientID
ORDER BY [tbl 2 Job].StartDate DESC;

Continuous form navigation button code:
Private Sub btnGetJob_Click()
Forms![frm1 Client].Form![Reservationfrm].Form![Jobfrm].SetFocus
With Forms![frm1 Client]![Reservationfrm].Form![Jobfrm].Form
.RecordsetClone.FindFirst "InvNum = " & Me!txtInvNum
If .RecordsetClone.NoMatch Then
MsgBox "Record not found!"
Else
.Bookmark = .RecordsetClone.Bookmark
End If
End With
End Sub

I see two glaring errors here.

The single form's Master/Child: ClientID/InvNum
It doesn't make sense to me that invoice numbers must be the
same as the clientID, please double check this.

It may not make a difference if the tables hace the right
properties, but the RIGHT JOIN in the query could
conceivably produce some strange effects in your situation.

 

[Marshall Barton]

Marsh...I got an email notification of a new response here...but there is no
unread response. Since I haven't heard from you regards my last posting as
yet, I want to ensure you didn't respond and it somehow got lost. I doubt
it...but checking.

Here's a copy of my last response:
----------------------------------------------------------------
This additional information about the main form and subform
is about what I suspected. It does confirm that I hadn't
gone down the wrong track, but it does not change what I
think the problem probably is. The single form's Link
Master/Child should be the same as for the single form:
ClientID/ClientID
Otherwise the single form can not contain the same set of
records as the continuous form.

With the Link Child property set to InvNum, the only records
that will show up in the single form are the unlikely ones
where the InvNum just happens, by coincidence, to be the
same as the ClientID, probably the first client and first
invoice entered into the tables. You can check my
hypothesis by looking in the table at the one record the
code does find to see if the two fields are the same.

I really think that RIGHT JOIN should be an INNER JOIN even
if you do have RI set on the relationship.