Linking two independent forms via event

[beeky]

I have a main form called frmWorkMain that has a record source based on an SQL statement from three tables -("tblClients, tblWorkRecord and tblWorkDetails". There is a subfrm that shows (one-to-many) the details of work carried out for a specific job. So for example a Job record may have a number of works undertaken for the same job but on different days. The record source for the sub form is simply the work details table tblWorkDetails. the master/child links are based on the InvoiceNo field. Some jobs may require a visit to the site so I have a seconf form with sub form that allows records for mileage to be input. This form is accessed via a link on a label: the underlying code at present in the OnClick event is:

Private Sub lblOpenMileageForm_Click()
DoCmd.OpenForm "frmMileageMain", acNormal, , , acAdd, acDialog, NewData
End Sub

frmMileageMain has a subfrm that lists all the journeys taken for that invoice.

What I want is that when the label to open the mileage form is clicked it opens to the invoice record from the frmWorkMain. I have attached a couple of screen shots that hopefully makes this clearer

How do I achieve this please?

 

[pbaldy]

This type of thing?

http://www.baldyweb.com/wherecondition.htm

 

[beeky]

Thanks tried this:

Private Sub lblOpenMileageForm_Click()
DoCmd.OpenForm "frmMileageMain", , , "InvoiceNo = " & Me.InvoiceNo
End Sub

But does not work the frmMileageMain is the seconf form (with a subfrm that doesn't show at all). I want to link with InvoiceNo but no joy.

Have a look at picture attached which is what I am trying to achieve.

 

[pbaldy]

The picture did not make it. I assume InvoiceNo is also a field in the second form's source?

 

[beeky]

Sorry I will try again with smaller files.

For completeness I show the SQL and record sources I am using

The SQL for the frmWorkMain is:

SELECT tblWorkRecord.InvoiceNo, tblWorkRecord.ClientCode, tblClients.ClientName, tblClients.ClientAddress, tblClients.ClientTel, tblClients.ClientEmail, tblClients.ClientWeb, tblWorkRecord.InvoiceSent, tblWorkRecord.Daterecieved, tblWorkDetails.JobReference
FROM (tblClients INNER JOIN tblWorkRecord ON tblClients.ClientCode = tblWorkRecord.ClientCode) INNER JOIN tblWorkDetails ON tblWorkRecord.InvoiceNo = tblWorkDetails.InvoiceNo;


The Record source for the subfrmWorkDetails is:

tblWorkDetails

The SQL for the frmMileageMain is:

SELECT tblWorkRecord.InvoiceNo, tblClients.ClientName, tblClients.ClientAddress, tblClients.ClientTel
FROM tblClients INNER JOIN tblWorkRecord ON tblClients.ClientCode = tblWorkRecord.ClientCode
GROUP BY tblWorkRecord.InvoiceNo, tblClients.ClientName, tblClients.ClientAddress, tblClients.ClientTel;

And the record source for the subfrmMileage is tblMileage

Hope this helps but please let me know if there is anything else I can do to make it clearer.

 

[pbaldy]

Since your InvoiceNo is text, the syntax is a little different, as noted on that link. Try:

DoCmd.OpenForm "frmMileageMain", , , "InvoiceNo = '" & Me.InvoiceNo & "'"

 

[beeky]

Thanks pBaldy. I had made the same mistake and forgot that text acted differently. Appreciate your input thank you.

 

[pbaldy]

No problem, glad we got it sorted out.