Append to a control from visual basic

[jgoodman00]

I wrote the code below in order to ascertain which value, either track or bldg is smaller. I need this to be appended to the control called RecmdedHeight, but I cannot make it do this. The values are retrieved correctly, & RecHeight eventually contains the correct result, but the code errors at line 13 (I have numbered the lines to make identification easier). Therefore my question is how do I make this product (RecHeight) populate the RecmdedHeight control on the form???

1 Private Sub Form_Current()

2 Dim track, bldg As Variant
3 Dim RecHeight As Single
4 Dim RecmdedHeight As Control
5 RecmdedHeight = Forms!frmTree!RecmdedHeight

6 track = DLookup("[Track]", "tblAdopt", "[RefNo] = Forms!frmTree!RefNo&quot
7 bldg = DLookup("[bldg]", "tblAdopt", "[RefNo] = Forms!frmTree!RefNo&quot

8 If track > bldg Then
9 RecHeight = bldg
10 ElseIf bldg > track Then
11 RecHeight = track
12 End If

13 RecHeight = RecmdedHeight

14 End Sub

 

[JerryDennison]

You've set RecmdedHeight as a Control, so you'll have to pass it's value.

RecHeight = RecmdedHeight.Value

What's happening is you're trying to assign a variable that's been dimmed as a number with a Control Object.

 

[jgoodman00]

So how should i best go about appending this value to the RecmdedHeight Field???

P.S. I tried the above code, & it returns the same error

 

[jgoodman00]

I think I have gotten slightly confused in declaring RecmdedHeight as a Control. Since this is a control on the form, it seems logical to declare it as a control. Therefore what should I declare it as, & how will I then go about displaying the result from the code in its corresponding control???

 

[JerryDennison]

Well, after looking more closely at your code I see several problems. One of which isn't related to your code at all. What are you trying to do?

Anyway, you could actually get away with naming a variable the same as a control on your form. I wouldn't recommend it. As it is you've dimmed your variable as a control. Therefore you must pass to it a control object. A control object is found in the controls collection of the form. So to set a variable as a control(this is usually done when you want to change property values for that control or you want to iterate through the controls). To simply assign a value in a control to a variable all you do is this:

Dim txtSomething As String
txtSomething = Me.ControlName

The other problems you have are your DLookUp() functions. They will not return valid values as written. If you want to pass a value in a control to the criteria property of the function it must be concatenated.

"[CriteriaField] = " & Me.ControlName

If the control has text then you'll need to also use a single quote:

"[CriteriaField] = " & Chr(39) & Me.ControlName & Chr(39)

Now to make this long story short. You don't need any of this code. Just set the controlsource property of an unbound textbox on your form to display the correct value based on the formula you're using. Use nested IIF() statements to get the correct value.

 

[jgoodman00]

That bit seems quite strange, because I agree that teh criteria part of the dlookup statement should be in the above format...However, it doesn't work. Thats why i changed it to the format it was already in, & now it works fine. As for sorting out the other problem, i changed the RecmdedHeight control to a string, & reversed the order in which they are written at the bottom of my code...now it works fine.

I thought about using the iif statement, but the value is going to be called from other fields, so it seemed logical to have it calculate in VB.