Variables not recognized

[Wiz47]

I have a private sub that is somewhat repetitive and want to add variables to lighten the code. The original code looks like this:

If Me.Cbo_Mult_B1 = "2" Then Me.Txt_B_Fat_1 = Nz(Me.Txt_B_Fat_1, 0) * 2

This works fine on the Afterupdate Event

But when I attempt the code below, it doesn't execute. I think I might be declaring the variables incorrectly. Cbo_Mult_B1 is a Combobox and Txt_B_Fat_1 is a textbox that accepts an integer

Private Sub Cbo_Mult_B1_AfterUpdate()

Dim First_Box as String
Dim Second_Box as integer

First_box = Me.Cbo_Mult_B1
Second_Box = Me.Txt_B_Fat_1

If First_box = "2" Then Second_Box = Nz(Second_Box, 0) * 2

End Sub

Any help would be appreciated.

 

[pbaldy]

You're setting the value of a variable, not a textbox. It's probably working but you haven't done anything with the result.

 

[Wiz47]

Thanks for the response. I'm not quite following. I'm trying to set the DIM to define the combobox and textbox, then use the = to identify the fields in the form to use.

PBaldy, what am I missing here?

 

[pbaldy]

This:

Second_Box = Nz(Second_Box, 0) * 2

sets the value of the variable, it does not set the value of a textbox. In other words, Second_Box is a variable that holds a value, it is not connected to a textbox on the form. It's no different than what you did here:

Second_Box = Me.Txt_B_Fat_1

 

[pbaldy]

Try declaring the variables as Control.

 

[Wiz47]

Try declaring the variables as Control.

I did that and got the error:
"Object variable or With Variable not set"

I plan to expand the code as follows:

If First_Box = "1" Then Second_Box = Nz(Second_Box, 0) * 1 Else
If First_Box = "2" Then Second_Box = Nz(Second_Box, 0) * 2 Else
If First_Box = "3" Then Second_Box = Nz(Second_Box, 0) * 3 Else

And so forth. I'll have about six options and don't want have to type in code for each. Second_Box = Me.Txt_B_Fat_2, Second_Box = Me.Txt_B_Fat_3, Second_Box = Me.Txt_B_Fat_4, Etc.

So you can see why I need to have the variables (Controls) declared properly for the code to work.

Point: Me.Txt_B_Fat_1 *is* the name of a textbox on the form.

 

[pbaldy]

My bad, you'd have to set the controls differently. Can you attach the db here?

 

[Wiz47]

My bad, you'd have to set the controls differently. Can you attach the db here?

I'll try to strip it down to the area of concern and send it.

 

[Wiz47]

My bad, you'd have to set the controls differently. Can you attach the db here?

Yes, here is the pared down version. Hope it is sufficient for your use. The full blown Db does a lot of things, but this is the particular form I need help with.

 

[Galaxiom]

I did that and got the error:
"Object variable or With Variable not set"

Dim First_Box As Control
Dim Second_Box As Control

[B]Set[/B] First_box = Me.Cbo_Mult_B1
[B]Set[/B] Second_Box = Me.Txt_B_Fat_1

 

[Galaxiom]

I'll have about six options and don't want have to type in code for each. Second_Box = Me.Txt_B_Fat_2, Second_Box = Me.Txt_B_Fat_3, Second_Box = Me.Txt_B_Fat_4, Etc.

If you are doing the same thing to multiple pairs of control you might consider using a Sub with the controls as arguments.

Private Sub MySub(FirstBox As Control, SecondBox As Control)

whatever you do in here with FirstBox and SecondBox arguments will act upon the controls that are passed to it.

End Sub

Use the sub in code like this:

MySub Me.Cbo_Mult_B1, Me.Txt_B_Fat_2

Do not put parentheses around the parameters.

BTW Using underscores as object names isn't such a good idea. Access already uses underscores to separate the object name from the event sub.

eg objectname_AfterUpdate

 

[Wiz47]

If you are doing the same thing to multiple pairs of control you might consider using a Sub with the controls as arguments.

Private Sub MySub(FirstBox As Control, SecondBox As Control)

whatever you do in here with FirstBox and SecondBox arguments will act upon the controls that are passed to it.

End Sub

Use the sub in code like this:

MySub Me.Cbo_Mult_B1, Me.Txt_B_Fat_2

Do not put parentheses around the parameters.

BTW Using underscores as object names isn't such a good idea. Access already uses underscores to separate the object name from the event sub.

eg objectname_AfterUpdate

Worked like a charm, thanks. I was told when I first started programming to separate out terms with an underscore. Old habits are hard to die, but I will make adjustments in the future so the program doesn't get confused.

 

[Wiz47]

Try declaring the variables as Control.

That was it. Galaxiom provided a bit of code that solved the problem. I used it to create a subroutine that accepted both as arguments. Now, I'm going to look through my code and see what else is repetitive and use that method on them as well.