Calculations from combo boxes and lists

[Eason67]

Hi, I am trying to carry out calculations which involve getting values from combo boxes and lists.
The usual method of for e.g. [numberoftickets]*[ticket price] doesn't work and returns #name?

Obviously, I am doing something wrong....

Can anyone help?

P.S. Will I be able to append the calculation to a table as well? I am not sure this is possible.

[ItsMe]

values from combo boxes and lists.

Where are you getting these values from? Are you using a Query Object or, maybe, code behind a form? Can you provide an example of one of the Combos RowSource? Are you using a Value List?

[June7]

Saving calculated data is usually a bad idea. Calculate when needed. Requires code to save calculated data. However, because prices can change over time, there is justification for saving the price. I recommend you save the unit price and calculate the total.

[Eason67]

Where are you getting these values from? Are you using a Query Object or, maybe, code behind a form? Can you provide an example of one of the Combos RowSource? Are you using a Value List?

The values come from a list box, where I created the values myself. i.e. Tickets sold. The other values are generated based on a selected value in a combo box. Basically different bands concert tickets cost different amounts. select a band from the combo box and their ticket price is displayed in another text box. The data for the combo box comes from a table called bands and the price charged is acquired from there too.

So Total cost is the tickets sold * price per ticket.

Thanks

[June7]

Provide list/combo box RowSource SQL statement.

What column is the price in?

Save quantity to NumberOfTickets. Save price to TicketPrice. Do calc in query or textbox.

[ItsMe]

What do you want to do with the result and where do you need to do the calculation? Maybe you can do the calculation behind a form and display it in an unbound control. If you are going to use code behind a form, I suggest you start by adding a Command Button Control to your form while in Design View. Then, in the button's click event, you could use code like ...

msgbox Me.ListControlName.Value * Me.ComboName.Value

While in form view, you could select an option from the List Box control and also select a value from the Combo Box control. Then, click the button you added to execute the VBA code.

[Eason67]

What do you want to do with the result and where do you need to do the calculation? Maybe you can do the calculation behind a form and display it in an unbound control. If you are going to use code behind a form, I suggest you start by adding a Command Button Control to your form while in Design View. Then, in the button's click event, you could use code like ...

msgbox Me.ListControlName.Value * Me.ComboName.Value

While in form view, you could select an option from the List Box control and also select a value from the Combo Box control. Then, click the button you added to execute the VBA code.

I will give it a go. To be honest, I did suspect there would be a bit of code involved.

Thanks for the input.

[ItsMe]

As an alternative to using VBA code behind a form, you can use an expression within an Unbound Text Box Control.
= ListControlName * ComboName

However, it is probably better if you use code. Code will allow for better control. You can execute specific statements based on various events. For instance, the example I provided suggests to start with a button control click event. After you successfully test your code, you can look for other events that should trigger the execution of your code. For instance, the AfterUpdate event of a Combo might be a place where you can validate data and then display an answer.

[Eason67]

I will give that a go too.
The calculation works now. All I need to do is get it transferred to a table.
I know thats storing calculations is frowned upon but this is a calculated bill.
It needs storing.

[Eason67]

I got the calculation to work. I can also append the total calculation to my bookings table. I cant add the append with the other data which goes in the table though.
I added the append query to a macro which adds the form data. No joy.
Then I added it to the after update section in properties of the total cost box. Nothing.
Assigning it to a macro on its own doesn't seem to work. You only seem to be able to add the append query by using the opening query command.

Am I missing something?

I want the data being added from the form and append query to be in the same record. i.e. record contains client details (added-easy bit) and also total cost (appended).

Any thought or suggestion greatly appreciated.

[ItsMe]

I want the data being added from the form and append query to be in the same record. i.e. record contains client details (added-easy bit) and also total cost (appended).

This is confusing to me because if you append the calculated value to a table, you are adding a new record. If you do not want to append a new record use a bound control and assign the result of the calculation to the Value property of the bound control.
Me.BoundControlName.Value = ListControlName * ComboName

If a form is bound to a table, any control you add to the form, while in design view and from the Field List (Add Existing Fields tool), will be a bound control.

[Eason67]

Cheers. I will give it a go. I don't want a new record. I want the calculated value assigning to the same record which is being added.
I've done a lot of experimenting so this is something new. Steep learning curve.

[ItsMe]

...
I've done a lot of experimenting so this is something new. Steep learning curve.

For your situation, I do not see a way to avoid using VBA and multiple events associated with the various controls and the form itself. However, you can start by using a Click Event for a new Command Button. You can get your hands dirty using a button and clicking the button while in Form View to execute your code. Later, you can explore additional events to help provide a more 'Automated' experience for the User.

[June7]

Select band from combobox - this combobox can have a column with the price info then code in the combobox AfterUpdate event can save the price to field:

Me!Price = Me.cboBands.Column(1)

This code references combobox column index. Index begins with 0 so if the price is in second column it is index 1.