J
Jaybird
I'm attempting to create an invoice form. Up to now we've been using a third
party product. It's tricky because we need to output two tables to the
corporate ERP in .dbf files. These must me in a particular format, or the
corporate system won't understand the data.
The invoices are based on work orders. This is a one to one relationship.
We have a table called Order Entry that has part number, quantity and weight
information on it that I wish to include in the invoice form.
The information that corporate wants is on two tables (to be output to a
particular location so that it can be uploaded into the corporate system).
These tables are called HDRPLAT and LINPLAT. Their structure is like this:
HDRPLAT table
CUSTNO
SHIPNO
INVNUM - PK
INVDTMD
INVDTY
SHIPDTMD
SHIPDTY
JOBNO
CUSTPO
CRMEM
LINPLAT table
INVNUM
ACCTLAB
ACCTPRE
ACCTSUF
KEYNUM
LINETOTL
ORDQTY
SHIPQTY
These two tables have a one to many relationship. INVNUM is the field they
are joined by. Think of HDRPLAT as a table of records, and LINPLAT as a
table of details of those records. Who knows why they do it this way? It
doesn't matter. I'm stuck with it.
I also have a fourth table called tblInvoice that gives me a little more
detail about the order. It's structured like this:
tblInvoice table
Invoice Number - PK
Invoice Date
Shipped Date
Batch Number
ChrgPartNumber1
ChrgPartNumber2
ChrgPartNumber3
ChrgPartNumber4
Anyhow, I'm having a problem making the resulting form updatable. I
figured that I should be able to use Order Entry as the Record Source for my
form, then use subforms to link to the other tables. Since LINPLAT can be
viewed as a sort of detail for HDRPLAT, I figured that I should be able to
imbed a LINPLAT subform within the HDRPLAT subform and insert the HDRPLAT
subform (with the LINPLAT subform embedded within it) to my mainform using
INVNUM as my linking property.
Still, no luck. I wonder if the fact that my LINPLAT table has no primary
key is causing me problems. I'm guessing so, but I can't think of a way to
uniquely identify the records.
QUESTIONS:
Is the fact that I have no Primary key for my LINPLAT table the source of
some of my recordsource problems? If so, how can I create a primary key for
this table?
Can anybody give me a clue about how I can make the resulting form updatable?
I think I can figure out the rest. Thanks for your help.
party product. It's tricky because we need to output two tables to the
corporate ERP in .dbf files. These must me in a particular format, or the
corporate system won't understand the data.
The invoices are based on work orders. This is a one to one relationship.
We have a table called Order Entry that has part number, quantity and weight
information on it that I wish to include in the invoice form.
The information that corporate wants is on two tables (to be output to a
particular location so that it can be uploaded into the corporate system).
These tables are called HDRPLAT and LINPLAT. Their structure is like this:
HDRPLAT table
CUSTNO
SHIPNO
INVNUM - PK
INVDTMD
INVDTY
SHIPDTMD
SHIPDTY
JOBNO
CUSTPO
CRMEM
LINPLAT table
INVNUM
ACCTLAB
ACCTPRE
ACCTSUF
KEYNUM
LINETOTL
ORDQTY
SHIPQTY
These two tables have a one to many relationship. INVNUM is the field they
are joined by. Think of HDRPLAT as a table of records, and LINPLAT as a
table of details of those records. Who knows why they do it this way? It
doesn't matter. I'm stuck with it.
I also have a fourth table called tblInvoice that gives me a little more
detail about the order. It's structured like this:
tblInvoice table
Invoice Number - PK
Invoice Date
Shipped Date
Batch Number
ChrgPartNumber1
ChrgPartNumber2
ChrgPartNumber3
ChrgPartNumber4
Anyhow, I'm having a problem making the resulting form updatable. I
figured that I should be able to use Order Entry as the Record Source for my
form, then use subforms to link to the other tables. Since LINPLAT can be
viewed as a sort of detail for HDRPLAT, I figured that I should be able to
imbed a LINPLAT subform within the HDRPLAT subform and insert the HDRPLAT
subform (with the LINPLAT subform embedded within it) to my mainform using
INVNUM as my linking property.
Still, no luck. I wonder if the fact that my LINPLAT table has no primary
key is causing me problems. I'm guessing so, but I can't think of a way to
uniquely identify the records.
QUESTIONS:
Is the fact that I have no Primary key for my LINPLAT table the source of
some of my recordsource problems? If so, how can I create a primary key for
this table?
Can anybody give me a clue about how I can make the resulting form updatable?
I think I can figure out the rest. Thanks for your help.