Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 2nd, 2007, 04:29 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
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


Reply With Quote
  #2 (permalink)  
Old February 4th, 2007, 01:07 PM
Registered User
 
Join Date: Feb 2007
Location: Cooper City, fl, USA.
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
Reply With Quote
  #3 (permalink)  
Old February 5th, 2007, 08:42 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #4 (permalink)  
Old February 5th, 2007, 11:22 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
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



Reply With Quote
  #5 (permalink)  
Old February 7th, 2007, 10:01 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:40 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.