Linking two forms that both have sub forms

[mondeoman]

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.

How do I achieve this please?

 

[kjv1611]

There may be a better way, but it seems to me that you could just feed the correct InvoiceID over to the new form's field for that field, and then refresh or requery the new form after the ID is entered in the form field.

I hope that doesn't sound like a dog chasing his tail...

--

"If to err is human, then I must be some kind of human!" -Me

 

[TheAceMan1]

How are ya mondeoman . . .

Things are a little vague here. Espcially since your putting [blue]frmMileageMain[/blue] in data entry mode with [blue]acAdd[/blue] ... which by the way should be [green]acFormAdd[/green].

We could use some assemblence of your table structure ... primary keys, fields of interest, table relationships. Also, what are the recordsources of the [blue]frmMileageMain[/blue]?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mondeoman]

I have attached the two pictures on box.net. These urls are:

http://www.box.net/shared/5f77xolajd

and

http://www.box.net/shared/nins3gmtam

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.

 

[MajP]

I think everyone is reading too much into this. This is a standard "open to a selected record". This is clearly explained in the help file and examples given.

expression.OpenForm(FormName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs)

FormName: Required Variant A string expression that's the valid name of a form in the current database. If you execute Visual Basic code containing the OpenForm method in a library database , Microsoft Access looks for the form with this name first in the library database, then in the current database.
View Optional AcFormView A AcFormView constant that specifies the view in which the form will open. The default value is acNormal.

FilterName: Optional Variant A string expression that's the valid name of a query in the current database.

WhereCondition Optional Variant A string expression that's a valid SQL WHERE clause without the word WHERE.

DataMode: Optional AcFormOpenDataMode A AcFormOpenDataMode constant that specifies the data entry mode for the form. This applies only to forms opened in Form view or Datasheet view. The default value is acFormPropertySettings.

WindowMode: Optional AcWindowMode A AcWindowMode constant that specifies the window mode in which the form opens. The default value is acWindowNormal.
OpenArgs Optional Variant A string expression. This expression is used to set the form's OpenArgs property. This setting can then be used by code in a form module , such as the Open event procedure . The OpenArgs property can also be referred to in macros and expressions . For example, suppose that the form you open is a continuous-form list of clients. If you want the focus to move to a specific client record when the form opens, you can specify the client name with the openargs argument, and then use the FindRecord method to move the focus to the record for the client with the specified name.

The 4th parameter is a where condition

"[invoice#] = " & me.[invoice#]

Where the left side is the name of the invoice # field in the form you are opening, and the right side is the invoice field on the opening form. If not numeric then
"[invoice#] = '" & me.[invoice#]& "'"

 

[MajP]

However, have you looked a a Tab control? You could have a main form with a tab control. The subforms are on the Tabs.

Main form
Invoice Title, name
Tab 1
Invoice details
phone, address, etc
Tab 2
Work details
Tab 3
Mileage

 

[TheAceMan1]

MajP . . .

[blue]DoCmd.OpenForm "frmMileageMain", acNormal, , , [red]acAdd[/red], acDialog, NewData[/blue]

In A2K the [blue]Data Mode[/blue] constant is [blue]acFormAdd[/blue], which puts the opened form in [blue]Data Entry Mode[/blue] ... aka add new records. I can't see going to a record in this mode! ... Can You?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[mondeoman]

Thanks Aceman1 and MajP. Appreciate your help but now realise that my links weren't working because of the data type which was text and not numerical. That said I hadn't thought of using the tab option which I think would be a good one so thank you for that I may redesign this part of the database.

 

[MajP]

Ace Man,
Concur. A lot did not make sense to me. The acAdd, the open arg, and the suggestion to pass the ID to the new form and then requery all made no sense.