D
Dennis
Hi,
I’m running Access via Office XP Pro on Windows 7.
General Question (specific question is at end of discussion):
I have a form with two sub-forms on it. Each sub-form is in a tab control.
There is no direct Parent / Child link to the second sub-form. How do I do
this?
Goal:
To create a screen that allows me to enter my parts orders by vendor
receipt.
Background:
I have a repair shop where most of the parts are ordered as we need them.
The repairs are so varied; it is hard to maintain an inventory of parts
because they are so specialized. We have a few parts that we keep in stock,
but most parts are ordered as needed. Also, each time we order a part, we
might get it from a different vendor and possibly a different manufacturer.
When I order my parts from a vendor, I enter the parts through a part
screen, but then I have to reconcile to the order receipt. What I want to do
is create a “Receive Order†form that is organized from the vendor receipt
point of view. That is, I will use the vendor’s receipt as my input document.
To do this I have a form that is comprised of three parts. The first part
is the vendor’s invoice information and is the main form.
The second part is a sub-form that resides in a tab control on the main
form. The second part is a continuous form that simply lists all of the
parts (that have been entered) for this receipt. This sub-form is a running
receipt and is updated as I add enter new parts. This running receipt allows
me to compare my data entry to the printed receipt and quickly find data
entry errors.
The third part is a “single entry†(as opposed to continuous) sub-form that
resides in a second tab control. This sub-form will enable me to enter the
detailed information associated with this part. This is where I am having an
issue. Since the key to the parts is an Access AutoNumber generated field,
there is nothing to link the Parent and Child forms (other than Vendor and
ReceiptNo and this will bring multiple entries).
My initial idea was to have the order’s individual parts listed on the first
sub-form. I would put an invisible command button over the entire line so
that if a user clicked anywhere on a record’s line on the continuous form,
they would be clicking on the command button. The comment button would take
the Item Number (which is displayed on the first-subform and is the key to
the individual part) and pass it to the second sub-form (which is the parts
form). The second sub-form would then read in the appropriate parts record.
Alternate Approach:
I’ve also thought about creating two forms. The first form would consist of
the Vendor Invoice record and the Parts record. The second form would be a
running receipt. Each time I updated a record in the Parts, I would have the
second form requery itself.
However, this approach still has the same issue in how do I link the Vendor
Receipt and Parts detail forms. Since each form has a different record
source, I figured I needed a form (for vendor invoice) and sub-form (for
parts). It seems like I would have to have a main form for the vendor
invoice and then a combo box where I could select a part, and then jump into
the parts form and somehow send the ItemNo (key to part screen) to the Parts
sub-form.
Database structure:
tblVenInvoice - one record per Vendor’s Invoice.
Key ----------------
VendorID - Foreign key to tblVendor table.
InvoiceNo - From invoice number of invoice paper.
Data ----------------------
Purchase / Invoice Date
Paid By
Paid Date
CheckNo
tblParts
key – ItemNo - Access Autonumber field
Data --------------------
VendorID
InvoiceNo
Part Description
ManufacturerID
Price
Tax
Shipping
WarrantyPeriod
WorkOrder
Question:
1. Does this approach make sense or is there a better approach?
2. If this is an appropriate approach, what is the best way to “send†or
“link†my ItemNo from my first sub-form to the second sub-form?
Thanks,
Dennis
I’m running Access via Office XP Pro on Windows 7.
General Question (specific question is at end of discussion):
I have a form with two sub-forms on it. Each sub-form is in a tab control.
There is no direct Parent / Child link to the second sub-form. How do I do
this?
Goal:
To create a screen that allows me to enter my parts orders by vendor
receipt.
Background:
I have a repair shop where most of the parts are ordered as we need them.
The repairs are so varied; it is hard to maintain an inventory of parts
because they are so specialized. We have a few parts that we keep in stock,
but most parts are ordered as needed. Also, each time we order a part, we
might get it from a different vendor and possibly a different manufacturer.
When I order my parts from a vendor, I enter the parts through a part
screen, but then I have to reconcile to the order receipt. What I want to do
is create a “Receive Order†form that is organized from the vendor receipt
point of view. That is, I will use the vendor’s receipt as my input document.
To do this I have a form that is comprised of three parts. The first part
is the vendor’s invoice information and is the main form.
The second part is a sub-form that resides in a tab control on the main
form. The second part is a continuous form that simply lists all of the
parts (that have been entered) for this receipt. This sub-form is a running
receipt and is updated as I add enter new parts. This running receipt allows
me to compare my data entry to the printed receipt and quickly find data
entry errors.
The third part is a “single entry†(as opposed to continuous) sub-form that
resides in a second tab control. This sub-form will enable me to enter the
detailed information associated with this part. This is where I am having an
issue. Since the key to the parts is an Access AutoNumber generated field,
there is nothing to link the Parent and Child forms (other than Vendor and
ReceiptNo and this will bring multiple entries).
My initial idea was to have the order’s individual parts listed on the first
sub-form. I would put an invisible command button over the entire line so
that if a user clicked anywhere on a record’s line on the continuous form,
they would be clicking on the command button. The comment button would take
the Item Number (which is displayed on the first-subform and is the key to
the individual part) and pass it to the second sub-form (which is the parts
form). The second sub-form would then read in the appropriate parts record.
Alternate Approach:
I’ve also thought about creating two forms. The first form would consist of
the Vendor Invoice record and the Parts record. The second form would be a
running receipt. Each time I updated a record in the Parts, I would have the
second form requery itself.
However, this approach still has the same issue in how do I link the Vendor
Receipt and Parts detail forms. Since each form has a different record
source, I figured I needed a form (for vendor invoice) and sub-form (for
parts). It seems like I would have to have a main form for the vendor
invoice and then a combo box where I could select a part, and then jump into
the parts form and somehow send the ItemNo (key to part screen) to the Parts
sub-form.
Database structure:
tblVenInvoice - one record per Vendor’s Invoice.
Key ----------------
VendorID - Foreign key to tblVendor table.
InvoiceNo - From invoice number of invoice paper.
Data ----------------------
Purchase / Invoice Date
Paid By
Paid Date
CheckNo
tblParts
key – ItemNo - Access Autonumber field
Data --------------------
VendorID
InvoiceNo
Part Description
ManufacturerID
Price
Tax
Shipping
WarrantyPeriod
WorkOrder
Question:
1. Does this approach make sense or is there a better approach?
2. If this is an appropriate approach, what is the best way to “send†or
“link†my ItemNo from my first sub-form to the second sub-form?
Thanks,
Dennis
I sounds okay, but I'm not your user...