Form size in Form of Invoice dependant on Invoice items

[hhfreddie]

Hullo guys, am developing a small database to print out invoices, Payment vouchers and Requisition vouchers. My challange is that the number of invoice items is not know at design level since it can have 1,2,3, - n items. So i want a way or vb code to create more data entry fields on the form depending on how many items to invoice the customers.

Thanks alot in advance

 

[CJ_London]

make sure the form allow additions is set to true and dataentry is set to false and the form is set to continuous

 

[hhfreddie]

Thanks JC but i tried that and it couldnt workout. I then read some where and its like i have to intergrate a subform on the main form in a datasheet format though i have not yet tried this one out.

 

[missinglinq]

...I then read some where and its like i have to intergrate a subform on the main form ...

This is correct. You don't add more Fields, for this kind of thing, you add related Records.

On the simplest level, you'll need two Tables for this kind of thing.

OrderTable
OrderID as the Primary Key
CustomerName
CustomerAddress
......

OrderDetailTable
DetailID as the Primary Key
OrderID as Foreign Key
ItemName
ItemQuantity
ItenUnitCost
......

1. You make an OrderForm based on the OrderTable.
2. You make a OrderDetailForm based on the OrderDetailTable.
3. On the OrderForm, which will be the Main Form, you place a Subform Control.
4. When the Subform Wizard comes up, select the OrderDetailForm as its source
5. Access will link the Subform to the Main Form by way of the common OrderID Fields

You can add as many Items to a given order as you need to. When you move from one order to another, on the Main Form, the Items will be updated appropriately.

Linq ;0)>

 

[hhfreddie]

First, sorry for taking so long to respond to post, i was a bit caught up.

Thanks missinglinq, your post was really helpful.

But then I got more other challanges on the way;

1. There is a field of discription on the sub-form which i want to grow (using can growth & can shrink properties) depending on the size of text typed by the user. It works out well in form view but it doesnt work out in datasheet view!

2. How to get the final invoice Amount derived from the total Amount field (quantity * unit price) of each invoice item.

3. I would also want to hide the orderId field on the sub-form as it is already on the main form, but whenever i try using the visible property, it doesnt dispear when in datashet view.

Thanks in advance for your time and knowledge

 

[gemma-the-husky]

just to clarify

if you have mutliple items on an invoice, you need two tables - an "invoice header" and an "invoice detail"

each line is included in the invoice detail.

now, your invoice needs to be based on a query that includes all the invoice lines. you can either have another expression to evaluate the invoice total as the sum of the line totals - or maybe you can include code to calculate the total as each line is processed and printed

can shrink/grow are better for report layout, i think. in a form the easiest way is to expand the control with shift-F2

 

[CJ_London]

but it doesnt work out in datasheet view

It won't - you need to use the controls columnwidth property

In the form on open or oncurrent event - benefit of using on current is that the form will resize if user populates a control with more data than can be shown. Don't worry if intellisense does not bring up ControlType and ColumnWidth - they are not documented ther

Private Sub Form_Current()
Dim crtl As Control
For Each Ctrl In Me.Controls
    If Ctrl.ControlType = acTextBox Then Ctrl.ColumnWidth = -2
Next
End Sub

 

[CJ_London]

I would also want to hide the orderId field on the sub-form as it is already on the main form, but whenever i try using the visible property, it doesnt dispear when in datashet view.

use
orderid.columnhidden = true
in your form open event

 

[CJ_London]

How to get the final invoice Amount derived from the total Amount field (quantity * unit price) of each invoice item.

If your field is called Amount then in the subform footer create a ctrl called TtlAmount and make it's control source

=sum(Amount)

And in your main form, creal another control called InvTotyal and make its control source

=subformctrlname.form.TtlAmount
'change the name of subformctrlname to the name of your subform contrl

 

[hhfreddie]

Private Sub Form_Current()
Dim crtl As Control
For Each Ctrl In Me.Controls
If Ctrl.ControlType = acTextBox Then Ctrl.ColumnWidth = -2
Next
End Sub

Thanks C.J
Your posts have really helped me.
That code above works fine but the text only expands holizontally yet i want the field to grow downwards. Also, the expansion doesn't show on the printed form, it only shows on the screen

Thanks

 

[CJ_London]

As far as expanding row height is concerned you would use

me.rowheight=200 (or whatever value you want) but since the columnwidth expands to the widest option, there is only one row of data so not saure why you need this.

As far as printing is concerned, I personally would use a report rather than a form and you would put the code previously provided in the onformat event of the report.

If you want to print the form then I would try saving the form before printing -use

me.save

I don't know if this would work, but worth a try.

 

[missinglinq]

Also note that the height you choose is going to be the height for all columns, which may look odd for the non-memo fields.

Using a Report, for printing, as CJ suggested, is really the way to go. With that, you can, in fact, use the Can Grow/Can Shrink Property.

Linq ;0)>

 

[hhfreddie]

Thanks guys for the knowledge, i really appreciate but sorry for the delay to respond to your posts.

My issues are now solved.