looking for a solution to update a table.

[lccrews]

God help me to explain this... I have a table with the following fields:

bill

description

raw material

claim

overage

input

quantity

uom

batch size

revision level

I'd like my users to enter all but quantity and batch size field. I'd like a button to do 4 things:
1. add the records
2. fill series of current records with value in 'text1'
3. calculate quantity with this expression: Quantity1: [Input]*[batch size]/1000
4. refresh the form so the user sees the quantity field updated


I'm considering a form with a qry-subform to enter the data.

I am working on the query and I cannot get it to update. Am I doing this properly?: I bring all relevant fields in, add one field with the expression from item 3 above, and select update query. Every time I try to view the results I'm met with error: .... is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.



I'm not looking for a specific answer because this is a really complex idea. I'm just looking for brainstorming ideas. Thank you for your time and patience. I've attached my database if you want to look around at what I'm doing.

[June7]

1. add how many records with what data?

2. either loop through form RecordsetClone or run UPDATE sql

3. calculate in query or textbox

4. would require code to save calculated value to field but should not save this calculated value, calculate when needed

[lccrews]

Hey June7,
1. the user would input data by hand into a subform(update query?)
2. I'll read up on update SQL. I don't know anything as of now.
3. I tried using a query to do this calculation / update the record, but I'm met with an error every time.
4. I want the calculated values to be saved to the table. This table will be used in a report.

Thank you for the suggestion.

[June7]

1. bind form to table and inputs will save directly to table

2. okay

3. assuming form is bound to table, expression in textbox ControlSource would be: =[Input]*[batch size]/1000, record first has to be committed to table before could run a UPDATE sql action

4. as stated, requires code (macro or VBA behind form) to save calculated value to field, calculation can be done in query used as report RecordSource