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 October 2nd, 2007, 04:54 AM
Registered User
 
Join Date: Oct 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subform problem

I have a subform that includes a text field. This text field does not correspond to any of the fields in the record source of the subform but its value would depend on one of the fields from the record source.

For example:

1) Let's say my query is "Select a,b,c from x"
2) My subform controls would have a textbox for a,b,c and another textbox d which has no control source
3) Let's say, c has values from 0-3
4) For each value of c, a corresponding value of d would be set

Can anyone help me on how to do this.

Thank you :)
 
Old October 2nd, 2007, 07:06 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You may need to do this in a couple of places.

On the SubForm's On Format event, add this code:

Dim iCValue As Integer
Dim sDValue As String

iCValue = Me.C

Select Case iCValue
   Case 0
     sDValue = "Case 0"
   Case 1
     sDValue = "Case 1"
   Case 2
     sDValue = "Case 2"
   Case 3
     sDValue = "Case 3"
End Select

Me.D = sDValue

You may also want to add this code to the Before Insert and Before Update event of the subform as well.

Did that help? Do a search for "Select Case" on http://wrox.books24x7.com and it will come up with 181 books that all discuss this. Use the Access 2003 VBA Programmer's Reference for a start.


mmcdonal
 
Old October 2nd, 2007, 08:21 PM
Registered User
 
Join Date: Oct 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your reply mmcdonal :)

I don't see any On Format event in the Events tab. I'm using 2003.
Regarding the code, I have a similar implementation. My real problem really right now is that I'm not sure where to put it so that it gets called for each record in the subform.

Additional information:
The subform is continuous, and I tried putting it in the OnLoad event. What happens is, the textfield only has the value from the first condition.

ex:
1 "one"
1 "one"
2 "one"
 
Old October 3rd, 2007, 07:07 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, that should be the On Current Event.

Continuous subforms are problematic. I would suggest creating a query to display the information. The problem then is if you want to allow th euser to update the subform, the query probably is not updateable.




mmcdonal
 
Old October 3rd, 2007, 07:33 PM
Registered User
 
Join Date: Oct 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks again :)

I tried that too after trying it on the OnLoad event. It had the same behavior.

Anyway, what I just did was to create a temp table to hold all the possible values for each type then included it in the query. The subform isn't supposed to be updated so I guess the solution will be okay.
 
Old October 4th, 2007, 11:04 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Oooh, if it doesn't need to be updated, you have a range of solutions, including the one you used there. Continuous forms are a real bear for doing any programmatic formatting.


mmcdonal
 
Old October 4th, 2007, 08:02 PM
Registered User
 
Join Date: Oct 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you again for your replies :) I appreciate it.






Similar Threads
Thread Thread Starter Forum Replies Last Post
form/subform problem chacquard Access VBA 1 November 21st, 2006 06:50 PM
Print subform problem J.K.W.Y. Access 4 April 25th, 2006 06:21 AM
Ids problem on subform ailyn Access VBA 1 December 9th, 2005 05:30 PM
Access SubForm Problem jscully Access 9 August 29th, 2005 03:03 PM
Filtering Subform Problem sherr8 Access 4 May 19th, 2004 10:22 AM





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