Linked form question

[John]

I have an Invoice form and a Jobcard form. They're bound to tblInvoices and
tblJobcard. These in turn have a many to many relationship through a
junction table. The Jobcard form is based on a query using tblJobcard and
tblInvoiceJobcard (junction table).
I currently have to select the correct invoice from a combo box on the
Jobcard form. What I want to do is open the Jobcard form from the Invoice
form and automatically write the appropriate keys to the junction table. I
don't want the jobcard as a subform as I sometimes need to write parts to
the invoice without any jobcard being involved.

What's the easiest way to do this. I may need to write several jobcards for
the one invoice, so would have to write the same invoice key to the juction
table for each jobcard called from that particular invoice on the invoice
form.

Any suggestions greatly appreciated

John

Table structure is as follows

tblInvoices
pkInvoiceID
InvoiceNumber
InvDate
etc

tblInvoiceJobcard
pkInvoiceJobcardID
fkInvoiceID
fkJobcardID
fkCustomerID

tblJobcard
pkJobcardID
JobNumber
JobDate
etc

tblPartsDetails
pkPartDetailId
fkInvoiceJobcardID
PartNumber
Description
etc

[Bob Quintal]

"John" <br****@gofree.indigo.ie> wrote in
news:dd**********@reader01.news.esat.net:

I have an Invoice form and a Jobcard form. They're bound to
tblInvoices and tblJobcard. These in turn have a many to many
relationship through a junction table. The Jobcard form is
based on a query using tblJobcard and tblInvoiceJobcard
(junction table). I currently have to select the correct
invoice from a combo box on the Jobcard form. What I want to
do is open the Jobcard form from the Invoice form and
automatically write the appropriate keys to the junction
table. I don't want the jobcard as a subform as I sometimes
need to write parts to the invoice without any jobcard being
involved.

What's the easiest way to do this. I may need to write several
jobcards for the one invoice, so would have to write the same
invoice key to the juction table for each jobcard called from
that particular invoice on the invoice form.

Any suggestions greatly appreciated
I have to make some assumptions here:
1) You want a command box that opens the jobcard form,
2) The on the JobCard form you want to find a particular job
number and click on a button to add the invoiceID and the
JobcardID to the InvoiceJobcard table.

(if those are wrong, the following will not be valid.)

Create a button on the invoice card, that opens the Jobcard
form.
In the on click event for the button, add the command to open
the form and pass the invoiceID value in the openargs position
of the docmd.openform statement (see the help.

In the Jobcard form process the openargs value to display it as
the value in a textbox or your combobox.

That saves you from having to reselect the Invoice number each
time you open the form.

As an alternative, if you never open the Jobcard form unless the
Invoice form is open you could simply add a textbox that refers
to the textbox that contains the invoice number on the invoice
form.

It may be easier, but you'll get #name errors if your invoice
form isn't open.
John

--
Bob Quintal

PA is y I've altered my email address.

[Mondrogan]

>What I want to do is open the Jobcard form from the Invoice form and

automatically write the appropriate keys to the junction table. I don't
want the jobcard as a subform as I sometimes need to write parts to the
invoice without any jobcard being involved.

Maybe you are asking one form to do too much. What about having one form
with the jobcard as a subform in order to show the jobcards related to
invoices, and another form used to write parts to the invoice?

[John]

For a clean user interface, I want to be able to write up all invoices from
one form. It's a bit messy having "Invoices with jobcards" and "Invoices
without jobcards"
My Invoice report will group jobcards and the parts and labour used on each
job separtely and if no jobcards exist it will just list parts. I want to be
able to open the invoice form and enter all data for a particular invoice
from the one place.
"Mondrogan" <mo*******@comcast.net> wrote in message
news:S7********************@comcast.com...

What I want to do is open the Jobcard form from the Invoice form and
automatically write the appropriate keys to the junction table. I don't
want the jobcard as a subform as I sometimes need to write parts to the
invoice without any jobcard being involved.

Maybe you are asking one form to do too much. What about having one form
with the jobcard as a subform in order to show the jobcards related to
invoices, and another form used to write parts to the invoice?

[John]

Thanks for that. The Jobcard from is regularly open separately from the
invoice and writes a new record to the junction table leaving a blank field
for fkInvoiceID.
Two things I need to do.
1. Open new invoice which writes to invoice table, then select existing
jobcards and write new invoiceID to appropriate record in junction table for
each existing jobcard.

2. Open new invoice and write new jobcard or jobcards for this invoice and
write data to all three tables.

Can I do this using the openargs method. There is a hidden textbox in the
jobcard form bound to pkInvoiceJobcardID.
John

"Bob Quintal" <rq******@sPAmpatico.ca> wrote in message
news:1124234182.cd2e6372c7991150fc3238ab63c5a192@t eranews...

"John" <br****@gofree.indigo.ie> wrote in
news:dd**********@reader01.news.esat.net:

I have an Invoice form and a Jobcard form. They're bound to
tblInvoices and tblJobcard. These in turn have a many to many
relationship through a junction table. The Jobcard form is
based on a query using tblJobcard and tblInvoiceJobcard
(junction table). I currently have to select the correct
invoice from a combo box on the Jobcard form. What I want to
do is open the Jobcard form from the Invoice form and
automatically write the appropriate keys to the junction
table. I don't want the jobcard as a subform as I sometimes
need to write parts to the invoice without any jobcard being
involved.

What's the easiest way to do this. I may need to write several
jobcards for the one invoice, so would have to write the same
invoice key to the juction table for each jobcard called from
that particular invoice on the invoice form.

Any suggestions greatly appreciated

I have to make some assumptions here:
1) You want a command box that opens the jobcard form,
2) The on the JobCard form you want to find a particular job
number and click on a button to add the invoiceID and the
JobcardID to the InvoiceJobcard table.

(if those are wrong, the following will not be valid.)

Create a button on the invoice card, that opens the Jobcard
form.
In the on click event for the button, add the command to open
the form and pass the invoiceID value in the openargs position
of the docmd.openform statement (see the help.

In the Jobcard form process the openargs value to display it as
the value in a textbox or your combobox.

That saves you from having to reselect the Invoice number each
time you open the form.

As an alternative, if you never open the Jobcard form unless the
Invoice form is open you could simply add a textbox that refers
to the textbox that contains the invoice number on the invoice
form.

It may be easier, but you'll get #name errors if your invoice
form isn't open.

John

--
Bob Quintal

PA is y I've altered my email address.