Code to reference a subform ??

  • Thread starter Thread starter Peter Smith
  • Start date Start date
P

Peter Smith

Hi

I have an order form that I fill in, and then I have a button on the order
form to create an invoice.

The invoice has a 'Purchases' subform which I want to fill with data from
the order form. (I know this is not good wrt normalisation, but it seems
the best way for the job.)

The following code fills line one of the subform which is in 'Continuous
Forms' view. How do I tell Access to move to line 2 of the form to add the
other products?

tia

- Peter
------ SAMPLE CODE ---------------------------------------
[Forms]![invoices]![Purchases]![ProductID] = ProdID
' The above line brings product details into a combo box field
[Forms]![invoices]![Purchases].[Form]!Quantity = [Forms]!frmorder!RBoxes
[Forms]![invoices]![Purchases].[Form]!Price = RFCost / RBoxes
 
Just to point you in the correct direction, you should
learn how to loop through data.
Sorry I cant address your actual question.
 
Hi

I have an order form that I fill in, and then I have a button on the order
form to create an invoice.

The invoice has a 'Purchases' subform which I want to fill with data from
the order form. (I know this is not good wrt normalisation, but it seems
the best way for the job.)

The following code fills line one of the subform which is in 'Continuous
Forms' view. How do I tell Access to move to line 2 of the form to add the
other products?

tia

- Peter
------ SAMPLE CODE ---------------------------------------
[Forms]![invoices]![Purchases]![ProductID] = ProdID
' The above line brings product details into a combo box field
[Forms]![invoices]![Purchases].[Form]!Quantity = [Forms]!frmorder!RBoxes
[Forms]![invoices]![Purchases].[Form]!Price = RFCost / RBoxes
Click to expand...

Well... you're doing it THE HARD WAY.

A Form is not a data repository. It's a window. The data is stored in
Tables.

If you have a purchases subform, it should be based on a purchases
Table (and if you have an Invoice report, that Report should be based
on the same table).

Put the data into the Table - using an Append query - not into the
Form. If you're going directly from the Orders form to the printed
invoice, you don't need to open a Purchases form at all (though you
can certainly do so if the user needs to interact with it).
 
Peter said:
Hi

I have an order form that I fill in, and then I have a button on the order
form to create an invoice.

The invoice has a 'Purchases' subform which I want to fill with data from
the order form. (I know this is not good wrt normalisation, but it seems
the best way for the job.)

The following code fills line one of the subform which is in 'Continuous
Forms' view. How do I tell Access to move to line 2 of the form to add the
other products?

tia

- Peter
------ SAMPLE CODE ---------------------------------------
[Forms]![invoices]![Purchases]![ProductID] = ProdID
' The above line brings product details into a combo box field
[Forms]![invoices]![Purchases].[Form]!Quantity = [Forms]!frmorder!RBoxes
[Forms]![invoices]![Purchases].[Form]!Price = RFCost / RBoxes
Click to expand...


Try somthing like this:

With Me.Purchases.Form.RecordsetClone
.AddNew
!ProductID = ProdID
!Quantity = Me.RBoxes
!Price = RFCost / RBoxes
.Update
End With
 
John Vinson said:
Hi

I have an order form that I fill in, and then I have a button on the order
form to create an invoice.

The invoice has a 'Purchases' subform which I want to fill with data from
the order form. (I know this is not good wrt normalisation, but it seems
the best way for the job.)

The following code fills line one of the subform which is in 'Continuous
Forms' view. How do I tell Access to move to line 2 of the form to add the
other products?

tia

- Peter
------ SAMPLE CODE ---------------------------------------
[Forms]![invoices]![Purchases]![ProductID] = ProdID
' The above line brings product details into a combo box field
[Forms]![invoices]![Purchases].[Form]!Quantity = [Forms]!frmorder!RBoxes
[Forms]![invoices]![Purchases].[Form]!Price = RFCost / RBoxes
Click to expand...

Well... you're doing it THE HARD WAY.

A Form is not a data repository. It's a window. The data is stored in
Tables.

If you have a purchases subform, it should be based on a purchases
Table (and if you have an Invoice report, that Report should be based
on the same table).

Put the data into the Table - using an Append query - not into the
Form. If you're going directly from the Orders form to the printed
invoice, you don't need to open a Purchases form at all (though you
can certainly do so if the user needs to interact with it).
Click to expand...

I have an Invoices table (InvID, CustID, InvDate, Status, Note) also a
Purchases table(InvID, PurchID, ProdID, SalePrice, Quantity) as well as a
Payments table (InvID, PayID, PayDate, Method, Amount, Note). I also have a
Products table where each product detail is stored (ProdID, Description,
ListPrice, Tax%, SalesGroup). The DB has other tables including Customer
etc.

The append query to the Invoice and Purchase tables sounds interesting. I
hadn't thought of doing it that way. The user could still interact with
the invoice form when it is presented. Now I just have to be sure the
CustID is appended to the Invoice, and the Purchases are appended with the
correct (newly created) InvID. That's not a question... yet :)

I'll have another good look at it all and a think. Thanks for the reply!

- Peter
 
There are usually only 2 or 3 products per order, so the looping is not the
issue - anyway, I know how to loop. The problem is how to address that
second row in the subform Continuous Form layout.

But thanks for the reply, much appreciated.

- Peter

Just to point you in the correct direction, you should
learn how to loop through data.
Sorry I cant address your actual question.
-----Original Message-----
Hi

I have an order form that I fill in, and then I have a button on the order
form to create an invoice.

The invoice has a 'Purchases' subform which I want to fill with data from
the order form. (I know this is not good wrt normalisation, but it seems
the best way for the job.)

The following code fills line one of the subform which is in 'Continuous
Forms' view. How do I tell Access to move to line 2 of the form to add the
other products?

tia

- Peter
------ SAMPLE CODE ---------------------------------------
[Forms]![invoices]![Purchases]![ProductID] = ProdID
' The above line brings product details into a combo box field
[Forms]![invoices]![Purchases].[Form]!Quantity = [Forms]! frmorder!RBoxes
[Forms]![invoices]![Purchases].[Form]!Price = RFCost / RBoxes


.
Click to expand...
Click to expand...
 
Marshall Barton wrote...
Peter said:
Hi

I have an order form that I fill in, and then I have a button on the order
form to create an invoice.

The invoice has a 'Purchases' subform which I want to fill with data from
the order form. (I know this is not good wrt normalisation, but it seems
the best way for the job.)

The following code fills line one of the subform which is in 'Continuous
Forms' view. How do I tell Access to move to line 2 of the form to add the
other products?

tia

- Peter
------ SAMPLE CODE ---------------------------------------
[Forms]![invoices]![Purchases]![ProductID] = ProdID
' The above line brings product details into a combo box field
[Forms]![invoices]![Purchases].[Form]!Quantity = [Forms]!frmorder!RBoxes
[Forms]![invoices]![Purchases].[Form]!Price = RFCost / RBoxes
Click to expand...


Try somthing like this:

With Me.Purchases.Form.RecordsetClone
.AddNew
!ProductID = ProdID
!Quantity = Me.RBoxes
!Price = RFCost / RBoxes
.Update
End With
Click to expand...

Ahhh perfect. I was after a technique to automate what I was previously
doing manually. I only needed a slight ajustment to make it work in my app:
With Forms!invoices!Purchases.Form.RecordsetClone
.AddNew
!InvoiceID = Forms!invoices!InvID
!ProductID = ProdID
!Quantity = Me.RBoxes
!Price = RFCost / RBoxes
.Update
End With

A thousand thanks.

- Peter
 
Back
Top