Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 2nd, 2007, 04:29 PM
Authorized User
 
Join Date: Feb 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default unbound field in subform

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


 
Old February 4th, 2007, 01:07 PM
Registered User
 
Join Date: Feb 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old February 5th, 2007, 08:42 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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

mmcdonal
 
Old February 5th, 2007, 11:22 PM
Authorized User
 
Join Date: Feb 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default


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



 
Old February 7th, 2007, 10:01 AM
Authorized User
 
Join Date: Feb 2004
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
filtering records on subform from an unbound combo jd_erd Access 3 February 12th, 2007 11:21 PM
Linking an unbound subform probitaille Access VBA 1 September 13th, 2006 12:27 PM
unbound field in a form zanza67 BOOK: Access 2003 VBA Programmer's Reference 0 September 3rd, 2005 01:46 PM
Unbound field lee27 VB Databases Basics 1 January 5th, 2005 07:53 AM
Filter Subform B Based on Field in Subform A SerranoG Access VBA 3 June 18th, 2004 12:23 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.