Subform in a continuous form (1 Viewer)

Any advice on the following form query would be much appreciated.

My database contains the following:

TBLsupplierorders
QRYsupplierorders
FRMsupplierorder (continuous form)

TBLsupplierorders contains details of orders we place on behalf of our client (supplier quote date and value, invoice date and value etc). At the end of each record is the 'Invoice/Journal to finance' field. in this field we currently input the amount to be invoiced to the client, usually the same as the supplier invoice value. However sometimes on large invoices, we need to issue a part-invoice to the client (e.g. £50k of a £200k supplier order).

The way we record this is by using copious notes in the comments field to record amounts and dates. Far from ideal.

I would like to be able to record multiple 'invoice/journal to finance' records for each supplier order. That way I could accurately record the amount, date and any comments related to that particular part-invoice as well as seeing how much is left to invoice to the client and producing an accurate invoice report.

I would normally do this with a subform but Access 2010 will not allow me to add a subform to a continuous form. FRMsupplierorders needs to be a continuous form for ease of updating.

I've attached the part of the database I'm referring to, in the hope that someone can advise me because I'm stumped.

I have no knowlege of VB and stumple along blindly with SQL.

Many thanks in advance

Allison

It's not just A2010. No version of Access allows this specifically but they do allow you to use subdatasheets. Here's a picture of a form that uses a subform on a subform. The innermost subform is a subdatasheet. You expand it by pressing the + at the left.

Thank you Pat.

Its amazing what a fresh pair of eyes can do. I have seen that done before but just couldn't get it yesterday.

I've just tried it and it works a treat. You're a gem thank you.

Now on to the next job...

Allison

if you actually want a continuous form, rather than a datasheet - then also note that although you cannot get access to automatically manage the links, it is still easy with a couple of lines of code

just base the subform on a query that uses some data from the main form.

then in the main form current event, just do mysubform.requery.

I tend to use a dereferenced variable, rather than a form reference in the subform query, as it makes the subquery more re-useable, but it is mainly a matter of taste.

I

gemma-the-husky said:

I tend to use a dereferenced variable, rather than a form reference in the subform query, as it makes the subquery more re-useable, but it is mainly a matter of taste.

Click to expand...

Could you please elaborate on how you would use a dereferenced variable, rather than a form reference? How does the subform know where to get the value for the variable?

Thanks.

Wayne