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 :)

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

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"

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

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.

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

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