updating field data in one form, from another form 1

[visage]

I have two forms :

Order and Sent. Sent is a subform of Order, and is in datasheet format.

I have two fields in Order : Qty_Received and Free

and two fields in Sent: Qty_sent and qty_returned.

My problem is this - I want to use the calculation, Qty_Received - Qty_Sent + qty_returned, so me free value is always my current stock.

However, Qty_sent and qty_returned are in my Sent subform. The calculation works until I create a new record in my subform, then the calculation looks at the second record only and ignores the first records, thus making my free figure inaccurate.

Any assistance would be appreciated.

 

[Frink]

Hallo,

Does each of these forms show a single product type, or what?

You probably want Free to be a calculated control, something like:
=[Qty_Received]-DSum("Qty_Sent","tblSent","?&quot+DSum("Qty_Returned","tblSent","?&quot

The tricky bit is what "?" is, but that depends on the structure of your database. Basically "?" needs to be an sql where clause (without the where) identifying which records in tblSent apply to the record in your Order form.

- Frink

 

[visage]

The Form - Order holds the product information. The form - Sent - holds customer orders. Thus each customer order (record) must impact on the free stock value, as it must reflect the true free stock value.

SO how do I tell it to read each record and amend the free value where necessary??

thanks for your help.

 

[Frink]

Hallo,

It's like I said then. You'll need the calculated control with the DSum bits as above.

I shall try and guess your database structure.
You have a product id called lngProductId in both tables?

In which case paste the following into the control source of your Free text box on the order form:
=[Qty_Received]-DSum("Qty_Sent","tblSent","lngProductId=" & [lngProductId])+DSum("Qty_Returned","tblSent","lngProductId=" & [lngProductId])

That might work. If it doesn't then I'll need to know the important fields in your tables (and how they're linked)

- Frink

 

[visage]

Hello,

You were right. Both tables do have a product id, known a product_code. In Order table - product_code is the primary key, but in the Sent table, a field called Sent_no is the primary key. But product_code does appear in tblSent. The tables are linked through the product_code.

I pasted you formula into the control source, but when I run the form, it come back with an "#Error" in the free field. Also calculating.... appears in the bottom left hand corner. It looks like it is stuck in a neverending loop.

Cheers

 

[Frink]

Hallo,

What have you got as the control source for the Free field?

- Frink

 

[visage]

Hi,

I have this as the control source for the free field

=[Qty_Received]-DSum("Qty_Sent","Sent","Product_Code=" & [Product_Code])+DSum("Returned_qty","Sent","Product_code=" & [Product_Code])

Thanks

 

[Frink]

Hallo,

Is your Sent table called Sent?
Does your sent table have a Qty_Sent field?
Does your sent table have a Returned_qty field?
Does your sent table have a Product_Code field?
Is it a Number type?
Does your Orders form have a Qty_Received control (or field in its underlying recordset)?
Does your Orders form have a Product_Code control (or field in its underlying recordset)?

If any of these are not true then update the code accordingly.

If they're all ok, try the following as the control source to try to isolate the problem:
=[Qty_Received]

=DSum("Qty_Sent","Sent","Product_Code=" & [Product_Code])

=DSum("Returned_qty","Sent","Product_code=" & [Product_Code])


- Frink

 

[visage]

Hi Frink,

To answer your questions

Is your Sent table called Sent? - yes
Does your sent table have a Qty_Sent field? - yes
Does your sent table have a Returned_qty field? - yes
Does your sent table have a Product_Code field? - yes
Is it a Number type? - no
Does your Orders form have a Qty_Received control - yes
Does your Orders form have a Product_Code control - yes

If they're all ok, try the following as the control source to try to isolate the problem:
=[Qty_Received]

=DSum("Qty_Sent","Sent","Product_Code=" & [Product_Code])

=DSum("Returned_qty","Sent","Product_code=" & [Product_Code])

-> Tried this as the control source and it still never worked. I am pretty stuck, dont understand why it will not work.

Thanks again

 

[Frink]

Hallo,

So is the Product_Code field text?

If it is then we need to put some 's in. This should work:

=[Qty_Received]-DSum("Qty_Sent","Sent","Product_Code='" & [Product_Code] & "'&quot+DSum("Returned_qty","Sent","Product_code='" & [Product_Code] & "'&quot

Hopefully that's it,

- Frink

 

[visage]

Thanks Frink,

Thank worked fine.

Cheers for all your help.

 

[Frink]

Hallo,

Glad about that.
You might consider naming your fields using one of the prefix standards, strName for strings, intName for integers etc. It would have made sorting this one out quicker.

- Frink