Go Back   Access World Forums > Apps and Windows > Visual Basic

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-05-2016, 10:43 AM   #1
kyprogrammer
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 1
Thanked 0 Times in 0 Posts
kyprogrammer is on a distinguished road
Try To Execute String as VBA

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.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

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?


Last edited by kyprogrammer; 10-05-2016 at 10:47 AM. Reason: took a msgbox line out that wasn't needed
kyprogrammer is offline   Reply With Quote
Old 10-05-2016, 12:22 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,923
Thanks: 0
Thanked 641 Times in 626 Posts
Ranman256 will become famous soon enough
Re: Try To Execute String as VBA

A simple counting query can do this without any code.
Ranman256 is offline   Reply With Quote
Old 10-05-2016, 12:29 PM   #3
kyprogrammer
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 1
Thanked 0 Times in 0 Posts
kyprogrammer is on a distinguished road
Re: Try To Execute String as VBA

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.

kyprogrammer is offline   Reply With Quote
Old 10-05-2016, 12:42 PM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,046
Thanks: 259
Thanked 307 Times in 292 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: Try To Execute String as VBA

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

http://www.fmsinc.com/tpapers/primer/
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 10-05-2016, 12:46 PM   #5
kyprogrammer
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 1
Thanked 0 Times in 0 Posts
kyprogrammer is on a distinguished road
Re: Try To Execute String as VBA

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".
kyprogrammer is offline   Reply With Quote
Old 10-05-2016, 04:54 PM   #6
MarkK
Super Moderator
 
MarkK's Avatar
 
Join Date: Mar 2004
Location: Vancouver BC
Posts: 7,614
Thanks: 10
Thanked 1,260 Times in 1,199 Posts
MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all MarkK is a name known to all
Do this loop...
Code:
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....
Code:
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.
Code:
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
__________________
formerly known as lagbolt | Windows 10 | Access 2010 | Visual Studio 2013 | "Institutions have a vested interest in perpetuating the problems to which they are the solution." - Clay Shirky
MarkK is offline   Reply With Quote
The Following User Says Thank You to MarkK For This Useful Post:
kyprogrammer (10-05-2016)
Old 10-05-2016, 05:36 PM   #7
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,132
Thanks: 68
Thanked 1,377 Times in 1,298 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Try To Execute String as VBA

Quote:
Originally Posted by kyprogrammer View Post
Code:
Dim myPartID, myCount, i As Integer
BTW Only i will be an Integer with that declaration.

The other two will be Variant.

Galaxiom is offline   Reply With Quote
Old 10-05-2016, 09:34 PM   #8
kyprogrammer
Newly Registered User
 
Join Date: Oct 2016
Posts: 14
Thanks: 1
Thanked 0 Times in 0 Posts
kyprogrammer is on a distinguished road
Re: Try To Execute String as VBA

Quote:
Originally Posted by MarkK View Post
Do this loop...
Code:
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....
Code:
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.
Code:
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!

kyprogrammer is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Split string, execute function, delete split. Dranoweb Modules & VBA 30 11-03-2010 09:56 PM
ADO Execute SQL string length mikeyanagita Modules & VBA 3 11-03-2010 02:35 PM
Execute a string Pyro Modules & VBA 3 01-22-2010 10:44 AM
How Can I Execute a Formula stored as a string KevinM SQL Server 4 06-27-2007 02:34 AM
Update Recordset with String Calculation result, not string itself cclambie Modules & VBA 4 05-16-2006 05:37 PM




All times are GMT -8. The time now is 12:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World