Updating Many Rows From Unbound Form

  • Thread starter Thread starter BK
  • Start date Start date
B

BK

I'm trying to do something which I would think would be easy, but can't
seem to figure out how to do it.

In a table I am tracking invoices. All the invoices are input with
payment date and check number left out for obvious reasons, they haven't
been paid yet. My problem is that we'll get a check that pays for
hundreds of invoices at a time. One check, but broken out in a detailed
report. How could I possibly construct something so that I could have a
nice neat form where I could input the Check Number and Date Paid once
and then just type in every invoice it applies to, thus updating the
invoices table? there's no real relation between the main and subforms.
ANd I don't want to list every invoice and have the user search
through and enter that info. It's like data entry in a way, but I don't
want to add records, I just want to update the records that I type in.
It doesn't seem like unbound datasheets work. It only gives one row and
doesn't let me add anything possibly into an array or anything which
would be ideal since I could then just iterate through the array and
issue an update statement. Any ideas?

TIA,
Bill
 
You have to think about how to model this in the data tables, and only then
how to present it for data entry.

Sounds like you need a table for invoices, and a table for payments. Then a
join table containing just the invoice ID numbers and the payment ID numbers
will give you what you want. As each payment comes in, create one record in
the payment table, and many records in the join table, one for each invoice
the payment relates to.

A form/subform arrangement will do this easily. Main form for the payment,
subform for the join table. The invoice ID field can be a dropdown combo
set to list all unpaid invoices - ie all invoices for which there is not yet
an entry in the join table. And of course you can show more details about
the invoice in other fields in the subform, as soon as the correct invoice
is selected.

You can even handle the case of part payments, add a field to the join table
for the actual amount paid off each invoice from the payment. Then you can
easily track completed and part paid invoices just by doing some sums
filtered on invoice ID numbers.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Yeah, you're right about the table structure. Unfortunately this is a
fairly well established db and changing the table structure like that
probably wouldn't really work (politically speaking). I probably would
have gone that route myself had I designed it originally, but alas, I
did not. So it looks like I'll have to fall back to a hack. This info
is already recorded, I'm just trying to ease data entry. Currently they
have to open up each job enter check number and date. I guess I just
need a way to display a long list of values that a user can change, add
or delete before they "commit" the transaction. I've already come up
with a bit of a hack by using a listbox and iterating through those
values, but I have a lot of work to do on it to make sure I'm not
updating rows willy-nilly. Was just wondering if there is another
option I might not be thinking about. Thanks for the input Adrian.

Bill
 
Back
Top