Re: Subform: record select form other subform

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


"Marshall Barton" wrote:

Chris wrote:

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.

--
Marsh
MVP [MS Access]

.

Quantcast