Subform not quite right

[Jaybird]

I have a form (frmInvoice) based on table Order Entry5. The primary key for
this is RecordID. These are order records for parts that are to be
processed. Each order has an order number and can have several parts on it.
Each line of the order is uniquely identified by the primary key (RecordID).
On this form I would like to include a subform that is based on the table,
tblInvoice. The records on this table have a relationship that is one to
one with the records on the Order Entry table and the table has the same
primary key (RecordID). However, I would like to group the records based on
the Order Number field, which is a foreign key on both tables. Doing so
allows me to marry up the order details like part number and quantity
(included on the Order Entry table) with the pricing for those parts (from
the table tblInvoice). The problem is that where data doesn't exist for
records in the Order Entry table, I haven't been able to come up with a way
for smoothly creating new records in the tblInvoice table. I toyed with the
idea of using the subform's OnCurrent event to use the same RecordID when the
data doesn't exist, but this produces errors. (BTW, I had to modify the
subform's record source to include the table Order Entry5 in order to make
this work - sortof...) My reasoning was that if the record source query for
the subform produced results for each record on Order Entry and those
matching records from tblInvoice, that I could use the OnCurrent event to
uniquely identify the results of the query. The difficulty of doing this
seemingly simple task has led me to conclude that creating another table for
the pricing information makes no sense. If it is a one to one relationship
with the Order Entry table, then perhaps I should simply add pricing fields
to the Order Entry table? I feel incredibly stupid about this. Can anybody
show me the way around this mess?
Why are you asking me? I dont know what Im doing!

Jaybird

 

[Allen Browne]

Okay, I think you have these tables:
- Product table with ProductID and current price.
- Orders table, with OrderID, CustomerID, OrderDate, etc.
- OrderEntry table: the line items of the order, so fields:
o OrderEntryID primary key
o OrderID relates to Orders.OrderID
o Quantity Number
o ProductID relates to Product.ProductID.

Now your goal is, at the end of the period (e.g. month), to create an
invoice for each customer who has orders that have not been invoiced yet. Is
that the idea?

Clearly you need an Invoice table where you create a header record for each
customer who has uninvoiced orders, so:
- Invoice, with fields:
o InvoiceID primary key
o CustomerID relates to Customer.CustomerID
o InvoiceDate date/time
o BatchID the batch number this invoice belongs to.
Now you need to be able to add the line items for each invoice.

As you say, the invoice line itmes have a one-to-one correspondence to the
records in the OrderEntry table. To me, it makes no sense to have 2 tables.
What I personally do is to add 2 more fields to the OrderEntry table:
o InvoiceID relates to Invoice.InvoiceID
o PriceEach the unit price for the product on this row.
These fields are blank (Null) until you create the invoice.
Then you fill in the invoice number and the price, and this record is a
child of 2 parents (the Orders table, and the Invoice table.)

So, the code that creates the invoices works like this:
a) OpenRecordset on all the OrderEntry records,
joined to the Product table (so we can get the current price),
where the InvoiceID is Null, sorted by CustomerID.

b) OpenRecordset on the Invoice table (so we can add new records.)

c) Get a new batch number.

d) Loop through the OrderEntry records.

e) If this is a new customer, AddNew to the Invoice recordset, and remember
the InvoiceID.

f) Assign this InvoiceID to the InvoiceID foreign key of the OrderEntry
table, and assign the price.

If you need to undo the creation of invoices, you just execute an Update
query to set OrderEntry.InvoiceID to Null and OrderEntry.PriceEach to Null,
for all records with that BatchID.

It does involve some code. But having the single OrderEntry record as a
child of both the order and the invoice certainly simplifies things. It also
makes it dead easy to block changes to the order once the InvoiceID field is
not null.

 

[Jaybird]

Allen, thank you for your response... I'm a big fan Your assessment of
the tables and their relationships is not quite correct, but I'm not sure
that it matters. I didn't tell you, so how could you know? The Order Entry5
table contains just about everything. We don't have a product table. We are
a process shop, and the prices we use are highly negotiated and specialized.
We have a guy whose job it is to quote prices. Anyhow, the inclusion of the
invoice records onto the Order Entry table, is what I figured to be the
essential piece of information here. I've got everything else working.
However, since you've never been anything but helpful, I will see if you have
laid out a better way. I'm a little confused at what you have in mind,
though. You say at first that adding the invoice records to the order entry
table makes sense, but then you tell me how to create and synch records in an
invoice table. I think you may be answering my original question (which may
have been more implied than stated) which is: how do you create invoice
records and keep them in synch with the current records? I appreciate the
instructions. This has been driving me crazy for a while now. Can you
please tell me the advantage of having the invoice records in a separate
table when their relationship to the Order Entry records is one to one? If
the invoice records are included in the Order Entry table, could I not simply
create a specialized form form invoicing and be done with it?

 

[Allen Browne]

Say you invoice monthly. In a month, a good customer may have many orders,
but I am assuming that you want all the line items from those orders showing
on a single invoice for the customer for the month.

 

[Jaybird]

In that case, could I not simply filter the records by the customer and the
month?

 

[Jaybird]

Allen,

I must now admit that I don't know what I'm doing with the openrecordset
method. I have always used queries. This is something new to me. I've
tried messing around with it and got nowhere. It's probably simpler than I
think, but I don't have a point of reference.

 

[Allen Browne]

Perhaps I haven't understood what you are doing.

I assumed you already have orders (consisting of 2 tables - the order
header, and the line items, as in Northwind), and you later want to create
invoices. If that's not what you are doing, my suggestions are irrelevant.

 

[Jaybird]

Allen,

No, you are correct. I already have records in the Order Entry table. It's
just that I have never used the openrecord method, so I don't know what I'm
doing. I have successfully avoided all this ADO, DAO gobbledygook up to now.
The concept seems to make sense to me. It's the HOW that eludes me. Can
you point me to some tutorials?

 

[Allen Browne]

You open and close a recordset like this:
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT ...
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
'Do some useful stuff here
rs.Close
Set rs = Nothing

To get the SQL statement, you can design a query with some dummy criteria,
and switch to SQL View to see what the SQL string should look like.

Once you get the recordset open, you can add a new value and then execute an
update on the related records. It's similar to this example:
http://allenbrowne.com/ser-57.html

Here's another example of a recordset:
http://allenbrowne.com/func-DAO.html#DAORecordsetExample

 

[Jaybird]

Thanks, Allen. I'll have to study up on this!

 

[Jaybird]

Why doesn't the subform wizard work in cases like this?