Copy text from query and calculate form

[Rob Brookbanks]

Help!!

I run a query to get information on a purchase order from an external
database via a one way ODBC driver.

This gathers all the text relating to items we purchase. I get the textual
description of the item, the part code, the purchase order number and the
number of items on each order etc. I can then store this in my serial number
table.

To get this, I use a QBF to get the unique items associated with each
purchase order and make a temporary table to store the items and quantities
etc. I drop the table when I am done ( I know there are better ways all you
purists, but I am just getting the hang of this!)

I want to take this information and place it on a form where I can enter the
serial numbers of all the received items into my own table. This is then a
snapshot of the received items in an access table.

Each item received has a unique primary key in the access table.

What I can't figure out is how to get the text from the query and create the
form, ie. if the order has three widgets on it, seven thingumybobs and one
whodjamaflip, how do I create a form that knows it has eleven records to
fill in an underlying table, fills in the text with the right number of
thingumybobs, widgets and whodjamaflips and have the form display an
additional text box that I can use a barcode reader on to input the serial
numbers through??? (I can do the barcode stuff!) This data then all goes in
my serial number table.

I can't figure out conceptually how to do this from the documentation!

Sorry to ramble, but I am as confused as this posting!!

 

[Arvin Meyer]

How about this:

Instead of dropping the Temp table, just clear out all the data in it. Make
sure you have all the fields you need, including the serial number field.
Build a continuous form based upon the Temp table. You can put a button in
the form's header (or footer) to write the data to the permanent table, then
delete the data in the Temp table. Further, with a little coding skill, you
can do it all in a Transaction, then Commit, or Rollback if there is an
error. Check the help files for "Transactions" and post back to this thread
if you need more help.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Rob Brookbanks]

Thanks for that Arvin.

I will look into transactions, as it does look neat, but the bit I am really
stuck on is how do I get a form, based on the following?

query results

ItemDesc ItemNo PartNo Qty
Widgets 1 PT-123 3
Thingumybobs 2 HP-076 7
Whodjamaflips 3 HW-394 1


When I click on my nExt button or whatever control I use, I want to produce
a form that has 11 lines (Or however many, but in this case, based on the
maths of above) and has somehow populated the text boxes with three lines
filled with the text "widgets", seven lines of "thingumybobs" and one line
of "whodjamaflips". The next blank column is where I enter my serial
numbers. The continuous form may run to seventy or eighty items in some
cases.

Each order can have different numbers of items and different quantities of
those items.

I can't really use any data from the ODBC query dynamically and back
reference it, as the text returned is free form (Part numbers don't
necessarily always have the same description, rubbish, but not my
choice!!!!!) hence my snapshot approach.

I know how to bind controls to my underlying permanent table, but producing
the form with the right number of lines, each with the right text in has
really got me stumped. (I'm a newbie I'm afraid)

I hope this makes some sense!

Thanks again,
Rob

 

[Arvin Meyer]

What I am "getting" from you is that you want an editable aggregate. The
form will show the totals (which must come from an aggregate or totals query
.... the Sigma button on the toolbar), but you also want a column where you
can edit/add all the serial numbers in one row.

There is no direct way to do that, nor should there be as it is a complete
violation of relational design. What you could do is to wite the results of
the aggregate to another table (hopefully as a temp table.) then add the
serial numbers to another column in that table
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
..