Hi,

I would like to have an unbound field in a continuous subform, but the value this field can be different from one record to the other. More specifically:

date Event checkbox price
date1 event1 yes $100
date2 event2 yes $125
date3 event3 no (null)
...

In this example the date, event and checkbox fields come from a query. The price (unbound field) changes depending on the event and other criteria from the main form and whether the check box is set to yes

Now, in this example, because the subform is continuous, this is what I get:

date Event checkbox price
date1 event1 yes $100
date2 event2 yes $100
date3 event3 no $100
...

The price shown is the one related to the last check box set to yes.

Do anyone of you have an idea how I can fix this ? Your help is very much appreciated.


Thanks

Chantal

Hello Chantal,

why don't you just set it up as a calculated field from a query or as part of a select statement in the form's data source?

Jesse

Regards,
Jesse

What is the code that is currently running? And on what event?

mmcdonal

If the checkbox is true, I run a query to get the price of the ticket and display it in the unbound field. The price changes depending on the event and the type of ticket (this value is in the main form).

Here is my code:

If AddTicket = True Then
Set db = CurrentDb()

strsql = "SELECT PriceChart.PriceCat " & _
"FROM PriceChart " & _
"WHERE PriceChart.IdEvent = " & [IdEvent] & _
" AND PriceChart.Category = " & Forms!Form_Tickets.[intCategory] & ";"

Set rec = db.OpenRecordset(strsql, dbOpenDynaset)
If Not rec.EOF Then
   parmSalePrice = rec!CatPrice
   rec.Close
Else
   parmSalePrice = Null
End If


This part works great...

If I add this field in the subform's data source (query), I will get a one-to-many relationship (event vs ticket price) which I want to avoid. The ticket information is in the main form and the price is only in the subform as a display and is different from one event to the other.

Can you see a way around this ?

Chantal

I forgot to mention that the type of ticket (or category) is a value entered by the user in the main form.