By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,638 Members | 1,983 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,638 IT Pros & Developers. It's quick & easy.

Convert string from table into DoCmd.xxx Object

P: n/a
Hello (from Access novice),

I'm building a switchboard form (using a Treeview object). The treeview
is populated by two tables (tblSwitchboardParent and
tblSwitchboardChild). Within tblSwitchboardChild, I have a string field
called ChildArgument that contains string text of VBA code (e.g.,
DoCmd.OpenForm "myForm"). When users click on various portions of the
Treeview object I want the Tree to either expand or open the report /
form.

My question: I am unable to convert the ChildArgument string into a
command that VBA recognizes. My code currently sets strArgument to a
SQL string which accurately finds the appropriate string of code in the
table. However, I do not know if the variable set to the SQL string
results in the table value or simply the SQL string. Furthermore, I
have no idea how to convert the table value into a line of code which
Access will recognize as the DoCmd.xxx object.

======================Current state of Sub=======================
Private Sub tvxData_NodeClick(ByVal Node As Object)

Dim CurNode As Object
Dim strArgument As String
Set CurNode = Me!tvxData.SelectedItem 'Result is string of Item
Text (i.e., parent or child)
On Error GoTo ErrorHndlr

' Evaluate the Key value of the selected node.
' Note CurNode.Key results in string of Node Key (e.g., A100)
Select Case Len(CurNode.Key)
' Section node keys are 4 characters long (e.g., A100)
Case 4
ShowChild Node 'Sub procedure ShowChild, works fine
' Detail node keys are 5 characters long (e.g., A1000)
Case 5
' Extract argument from CurNode.Key
strArgument = _
"SELECT ChildArgument FROM tblSwitchboardChild " & _
"WHERE ChildNodeKey = '" & CurNode.Key & "'"
strArgument ' Compile error here; expect sub, function or
property
Case Else
ShowChild Node
End Select
Exit Sub

ErrorHndlr:
Select Case Err
' If error is because nothing is selected in TreeView.
Case 91
MsgBox "Please select an item in the TreeView control."
Case Else
MsgBox "Error: " & Err.Number & vbCr & Err.Description
End Select
Exit Sub

End Sub
======================End Code Segment=======================

Second question: Is this a unnecessarily convulted method for
accomplishing my objective (note: there are a significant number of
child nodes and I'm reticent to use macros).

Thanks in advance for any help you can provide, the NGs I looked
through had nothing on the subject that I could find.

Kelii

Sep 27 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a

ke****@yahoo.com wrote:
My question: I am unable to convert the ChildArgument string into a
command that VBA recognizes.
You can use the eval() function to execute code stored in a string:
http://msdn.microsoft.com/library/de...HV05186386.asp

Sep 27 '06 #2

P: n/a
Thanks for the response Robert.

Quick follow up: When I use the Eval(strArgument) function, I get
"Error 7956" Syntax of Subquery in this expression is incorrect. Any
thoughts?

Again, the strArgument is defined as strArgument = "SELECT
ChildArgument FROM tblSwitchboardChild WHERE ChildNodeKey = '" &
CurNode.Key & "'"

Resulting in a string such as SELECT ChildArgument FROM
tblSwitchboardChild WHERE ChildNodeKey = 'A1000'

Therefore, the Eval() function would look like:
Eval(SELECT ChildArgument FROM tblSwitchboardChild WHERE ChildNodeKey =
'A1000').

Any thoughts on why this is resulting in the above error would be
greatly appreciated.

Thanks again,

Kelii

Sep 27 '06 #3

P: n/a

ke****@yahoo.com wrote:
Thanks for the response Robert.

Quick follow up: When I use the Eval(strArgument) function, I get
"Error 7956" Syntax of Subquery in this expression is incorrect. Any
thoughts?

Again, the strArgument is defined as strArgument = "SELECT
ChildArgument FROM tblSwitchboardChild WHERE ChildNodeKey = '" &
CurNode.Key & "'"

Resulting in a string such as SELECT ChildArgument FROM
tblSwitchboardChild WHERE ChildNodeKey = 'A1000'

Therefore, the Eval() function would look like:
Eval(SELECT ChildArgument FROM tblSwitchboardChild WHERE ChildNodeKey =
'A1000').

Any thoughts on why this is resulting in the above error would be
greatly appreciated.

Thanks again,

Kelii
You can't just eval() a SQL statement; that is not a valid VB
expression.
What exactly are you trying to do with the data resulting from the SQL
statement?

Sep 27 '06 #4

P: n/a
Robert,

I wasn't sure if I could do Eval() a SQL statement or not. Thanks for
the clarification.

The SQL query results in a single record such as DoCmd.OpenForm
"frmMyForm". As such I'm trying to open the frmMyForm using this
particular piece of VBA code.

As you may notice from the postings above, the sub procedure is part of
NodeClick procedure on a Switchboard form. The Treeview object is built
from a table which I've filled with the Child Node Keys, Chile Node
Text, and an Argument field that contains the VBA command for opening a
form, report, or other such actions. The SQL statement identifies the
particular Node that has been clicked, then uses SQL to identify the
command (i.e., in the argument field) to execute.

I hope that makes sense. And thanks again for helping a VBA novice.

Best,

Kelii

Sep 28 '06 #5

P: n/a
Your code is inefficient and poorly organized. There is not much point
in using SELECT CASE when there are only two cases. Putting the line
that will create one of the errors you want to trap before the error
handling directive is unlikely to accomplish much. Having two Exit Subs
is confusing to the reader and perhaps to VB as well. So first we need
to clean up the code. This is an ATTEMPT but it's only that because I'm
not exactly sure what you are trying to do and I can't test my changes
against your situation:

Private Sub tvxData_NodeClick(ByVal Node As Object)
Dim CurNode As Object
Dim strArgument As String
On Error GoTo tvxData_NodeClickErr
Set CurNode = tvxData.SelectedItem
If Len(CurNode.Key) = 5 Then
strArgument = _
"SELECT ChildArgument FROM tblSwitchboardChild " & _
"WHERE ChildNodeKey = '" & CurNode.Key & "'"
Else
ShowChild Node
End If
tvxData_NodeClickExit:
Exit Sub
tvxData_NodeClickErr:
With Err
If .Number = 91 Then
MsgBox "Please select an item in the TreeView control."
Else
MsgBox "Error: " & Err.Number & vbNewLine & .Description
End If
End With
Resume tvxData_NodeClickExit
End Sub

The next thing we need to understand is what you want to do with
strArgument. That may seem totally clear to you but it's not to me.
What does "SELECT ChildArgument FROM tblSwitchboardChild WHERE
ChildNodeKey = 'A5000'" have to do with "DoCmd.OpenForm"?

Menus, of course, handle the show, don't show, roll up, drop down, do
action, without any intervention, flawlessly.

You may be unaware that some developers upon seeing a Switchboard
whisper "Oh God!", while others delete the whole file and tell their
client the application will have to be completely redone.

Sep 28 '06 #6

P: n/a

ke****@yahoo.com wrote:
The SQL query results in a single record such as DoCmd.OpenForm
"frmMyForm". As such I'm trying to open the frmMyForm using this
particular piece of VBA code.
If the form name if stored in your database and keyed to the detail
node key, why don't you just populate a recordset with the results of
your SQL query:

dim rcd as adodb.recordset
dim con as adodb.connection
dim myfrmname as string
set con = currentproject.connection
set rcd = con.execute "SELECT
ChildArgument FROM tblSwitchboardChild WHERE ChildNodeKey = " & """ &
CurNode.Key & "'"
rcd.movefirst
myfrmname = rcd.fields!ChildArgument.value
rcd.close
'(make sure frmname is a valid form name or docmd will choke)
docmd.openform myfrmname
set rcd=nothing
con.close
set con=nothing

There is probably an easier way to do this, but you get the idea.

Sep 28 '06 #7

P: n/a
You may be unaware that some developers upon seeing a Switchboard
whisper "Oh God!", while others delete the whole file and tell their
client the application will have to be completely redone.
Hah, good stuff; and all very much well deserved on my part :D. Yes,
I've read the posts and seen the plethora of KB articles on the
Treeview object. I've promised myself to keep things simple, and thus
avoid any and all pitfalls associated with this beast. The code that
I've shared represents probably 50% of all the code associated with
the Treeview, and in my novice assessment seems manageable.

The code you suggested works flawlessly, and as you suggest is probably
much better than what I started with. Thank you for that, I appreciate
it.

Now to the issue at hand: 1) what to do with the strArgument and 2)
what does DoCmd.OpenForm have to do with strArgument. Ok, so for
clarity sake, I'll start in reverse order.

2) the result of strArgument (which as you notice is a SQL string) is a
dynaset with a single column, row and record. That record is of type
string, and reads "DoCmd.OpenForm "frmMyForm". Note: that this is
the text that I manually typed into the table.

1) now what to do with strArgument. Well as you can imagine at this
point, I would like strArgument to be evaluated (possibly through DAO?)
by Access and return the result of the dynaset (remember this is only
one record) as a String assigned to a variable (hopefully reassigned to
the original strArgument variable). Finally, I would like to then
execute the strArgument variable resulting in opening the specified
form (e.g., frmMyForm). It seems that the Eval() function could be
useful in this regard. For example, Eval ("str
Argument"), assuming that strArgument is now set to the value
"DoCmd.OpenForm 'frmMyForm'").

I hope that clarifies things. At the very least, it has clarified for
me the fact that I have a weak grasp on VBA terminology.

Re: menus, I have implemented menus in my home brew app, and you are
absolutely right, they perform flawlessly. However, I've tasked
myself with building something more visual for those who have an even
weaker grasp on technology than myself.

Thanks again for your help, I absolutely appreciate it.

Best,

Kelii

Sep 28 '06 #8

P: n/a
You may be unaware that some developers upon seeing a Switchboard
whisper "Oh God!", while others delete the whole file and tell their
client the application will have to be completely redone.
(Apologies if this posts twice.)

Hah, good stuff; and all very much well deserved on my part :D. Yes,
I've read the posts and seen the plethora of KB articles on the
Treeview object. I've promised myself to keep things simple, and thus
avoid any and all pitfalls associated with this beast. The code that
I've shared represents probably 50% of all the code associated with
the Treeview, and in my novice assessment seems manageable.

The code you suggested works flawlessly, and as you suggest is probably
much better than what I started with. Thank you for that, I appreciate
it.

Now to the issue at hand: 1) what to do with the strArgument and 2)
what does DoCmd.OpenForm have to do with strArgument. Ok, so for
clarity sake, I'll start in reverse order.

2) the result of strArgument (which as you notice is a SQL string) is a
dynaset with a single column, row and record. That record is of type
string, and reads "DoCmd.OpenForm "frmMyForm". Note: that this is
the text that I manually typed into the table.

1) now what to do with strArgument. Well as you can imagine at this
point, I would like strArgument to be evaluated (possibly through DAO?)
by Access and return the result of the dynaset (remember this is only
one record) as a String assigned to a variable (hopefully reassigned to
the original strArgument variable). Finally, I would like to then
execute the strArgument variable resulting in opening the specified
form (e.g., frmMyForm). It seems that the Eval() function could be
useful in this regard. For example, Eval ("str
Argument"), assuming that strArgument is now set to the value
"DoCmd.OpenForm "frmMyForm").

I hope that clarifies things. At the very least, it has clarified for
me the fact that I have a weak grasp on VBA terminology.

Re: menus, I have implemented menus in my home brew app, and you are
absolutely right, they perform flawlessly. However, I've tasked
myself with building something more visual for those who have an even
weaker grasp on technology than myself.

Thanks again for your help, I absolutely appreciate it.

Best,

Kelii

Sep 28 '06 #9

P: n/a
Robert,

Perfect, that's exactly what I was looking for :D. Sorry for the
convoluted post, but I'm glad we finally got there.

I'll test the code, but given your previous post on this topic, I'm
certain this will solve the isse.

Thanks again for your help.

Best,

Kelii

Sep 28 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.