Try To Execute String as VBA

[kyprogrammer]

Hey All,

I know I have read that this may not be possible but I wanted to submit this anyways. I have a module that detects a count of records in a table fitting a criteria. It then concatenates a string including the number so that it adds this string to a variable. FYI I am using this in MS Access

The variable is then sent to the procedure to be "evaluated" as vba code, but I am stuck there.

Here is my First procedure.

Public Sub checkNumFields()
Dim myPartID, myCount, i As Integer
Dim myPartNum As String
Dim mytext As Variant

myPartNum = Form_QC_ASSY_Check_Main_frm.cbo_Part_Number

myPartID = Int(DLookup("Part_ID", "Part_Number_Table", "[ASSY_Part_Number]='" & myPartNum & "'"))

myCount = Int(DCount("Check_Point", "qry_ASSY_QC_Check_Info", "[Part_ID]=" & myPartID))


If (myccount = 0) Then

i = myCount + 1
For i = 1 To myCount

mytext = "QC_ASSY_Check_Main_frm.tab_QC_Attribute_Checks.txt_P" & i & ".visible = True"
'sends the variable mytext to the form procedure
Call Form_QC_ASSY_Check_Main_frm.ShowMyFields(mytext, i)
Next i

Else

For i = 1 To myCount

mytext = "Form_QC_ASSY_Check_Main_frm.txt_p' & i & '.visible = True"

Next i


End If



The form procedure is this

Public Function ShowMyFields(mytext, i)

Eval (mytext)

End Function

In theory the first record would have mytext = QC_ASSY_Check_Main_frm.tab_QC_Attribute_Checks.txt_P1.visible = True

This would unhide that first field and be sent back to the original procedure and goto the next count. However, when the string is trying to be evaluated it says it can't find "QC_ASSY_Check_Main_frm" even though it's spelled correctly.

Any help?:banghead:

 

[Ranman256]

A simple counting query can do this without any code.

 

[kyprogrammer]

I don't think it's that easy. I want my vba code to be dynamic. Where it changes the text field's number by using i and cycling through the record count I have. so in essence if there are 17 records it will unhide the first 17 fields.

 

[Gasman]

Wouldn't you need to reference the controls in the format show here?

http://www.fmsinc.com/tpapers/primer/

 

[kyprogrammer]

I name my forms differently than the list you have. If I did it that way it would still say it couldn't find the "name".

 

[MarkK]

Do this loop...

For i = 1 To myCount
   mytext = "QC_ASSY_Check_Main_frm.tab_QC_Attribute_Checks.tx t_P" & i & ".visible = True"
   'sends the variable mytext to the form procedure
   Call Form_QC_ASSY_Check_Main_frm.ShowMyFields(mytext, i)
Next i

...on the form that hosts the controls. Expose a public method, and just pass over myCount as a parameter, and then your control reference is simply....

Me.Controls("txtP" & i).visible = True

You don't need to pass over some string representation of the code to do the work, just write a routine that does the work, and call it with parameters.

Public Function ShowMyFields(ControlCount as Integer)
   dim i as integer
   For i = 1 To ControlCount
      me.controls("txt_P" & i).visible = True
   Next
End Function

 

[Galaxiom]

Dim myPartID, myCount, i As Integer

BTW Only i will be an Integer with that declaration.

The other two will be Variant.

 

[kyprogrammer]

Do this loop...

For i = 1 To myCount
   mytext = "QC_ASSY_Check_Main_frm.tab_QC_Attribute_Checks.tx t_P" & i & ".visible = True"
   'sends the variable mytext to the form procedure
   Call Form_QC_ASSY_Check_Main_frm.ShowMyFields(mytext, i)
Next i

...on the form that hosts the controls. Expose a public method, and just pass over myCount as a parameter, and then your control reference is simply....

Me.Controls("txtP" & i).visible = True

You don't need to pass over some string representation of the code to do the work, just write a routine that does the work, and call it with parameters.

Public Function ShowMyFields(ControlCount as Integer)
   dim i as integer
   For i = 1 To ControlCount
      me.controls("txt_P" & i).visible = True
   Next
End Function

This worked! Thank you!