Getting data from a table not bound to a form

[sesling]

I created a form that contains a list box. The list box value comes from a table.
The list box is Titled XML_NODE. When the form is open, the list box displays the value from the table 255.255.255.0. I want to pass the value from the list box to a command button. The command button will run a batch file that will use the XML_NODE information. I can get the batch file to accept the XML_NODE value if the operator selects it on the form. Is there a way to get the list box value to pass to the command without having the operator select it?

below is the command I am trying to run

"c:\List_Clients.bat " & Me!XML_NODE

[Rabbit]

I created a form that contains a list box. The list box value comes from a table.
The list box is Titled XML_NODE. When the form is open, the list box displays the value from the table 255.255.255.0. I want to pass the value from the list box to a command button. The command button will run a batch file that will use the XML_NODE information. I can get the batch file to accept the XML_NODE value if the operator selects it on the form. Is there a way to get the list box value to pass to the command without having the operator select it?

below is the command I am trying to run

"c:\List_Clients.bat " & Me!XML_NODE

You don't even need the listbox, if you don't even want the operator to have to select it, then you can just pass the information directly from the table to what you want to run in the On Open event.

[sesling]

You don't even need the listbox, if you don't even want the operator to have to select it, then you can just pass the information directly from the table to what you want to run in the On Open event.

I assume this would be on the command button. I already have an OnClick event that will launch the batch file. Can the command button have more than one event? Can i add the table entry directly to the OnClick event? Is so, How would it be added?

[Rabbit]

If you don't need to let the operator decide when to run the code, then you don't even need a command button. You can just put all the code in the On Load or On Open event of the form.

[sesling]

If you don't need to let the operator decide when to run the code, then you don't even need a command button. You can just put all the code in the On Load or On Open event of the form.

I am sorry. I don't think my original post accurately outlined my issue. I created a form that has several different list boxes. The data contained in those boxes are used by the operator to select a sequence. I created a command button that will launch a Batch file using the selected sequence. What I am trying to do is create a table that will contain several XML_Nodes. I want to pass the node information along with some of the list box info into a batch file. Our company does not want the node information listed on the screen and my boss does not want it hard coded into the command line in case the Node information needs to be changed. I figured If I stored the values in a table I could get the value and pass it into the command line. This way I would just have to change the table values if necessary versus changing the command parameters. I know I could mask the value in the list box but again my hands are tied because I cannot change the form any more. Is there anything I could do to pass the table data to the form so it could be used on the command line?

[Rabbit]

Why can't you change the form?

Well, you can use a DLookup("[Field Name]", "[Table Name]", "WHERE clause") to look up what you want from another table.

There WHERE clause is built like the SQL WHERE except you leave out the word WHERE. You don't need it if you only have one record in the table.

[sesling]

Why can't you change the form?

Well, you can use a DLookup("[Field Name]", "[Table Name]", "WHERE clause") to look up what you want from another table.

There WHERE clause is built like the SQL WHERE except you leave out the word WHERE. You don't need it if you only have one record in the table.

In answer to your question Management does not want any additional changes to the screen. thx for the DLookup function. Is there a way to use DLookup to retreieve more than one item from the table?

[Rabbit]

No, it can only look up one thing at a time. You'll have to use multiple DLookup to look up multiple items.

[sesling]

No, it can only look up one thing at a time. You'll have to use multiple DLookup to look up multiple items.

Would I create a statement like this in the OnOpen Event for the form

Dim VarX
Dim VarY

VarX = DLookup("XML_Node",XML_TABLE,ID=1)
VarY = DLookup("XML_Node",XML_TABLE,ID=2)

And if so, how could I pass the VarX and VarY values into my command button on the form? I have tried with just VarX and the value for VarX is not being passed to the command line. Below is the statement I am using

stAppName = "c:\SubmitCommands.Bat " & "cmdfile=cfg/app/Reports.xml " & "PN1=" & VarX

[Rabbit]

Would I create a statement like this in the OnOpen Event for the form

Dim VarX
Dim VarY

VarX = DLookup("XML_Node",XML_TABLE,ID=1)
VarY = DLookup("XML_Node",XML_TABLE,ID=2)

And if so, how could I pass the VarX and VarY values into my command button on the form? I have tried with just VarX and the value for VarX is not being passed to the command line. Below is the statement I am using

stAppName = "c:\SubmitCommands.Bat " & "cmdfile=cfg/app/Reports.xml " & "PN1=" & VarX

Close, you need to put quotes around XML_Table and ID=1/2 as well.

VarX = DLookup("XML_Node","XML_TABLE","ID=1")

[sesling]

Close, you need to put quotes around XML_Table and ID=1/2 as well.

VarX = DLookup("XML_Node","XML_TABLE","ID=1")

I did that and I am still not seeing the VarX and VarY being passed into my command event. Do the VarX and VarY need to be public variables? I cannot understand why the value won't pass to the command line. There must be I am missing. Here are the steps I followed.

I opened the form is design view
I selected properties
I changed properties from Detail to Form
In the On Open event I placed the statements above with the correct "" added. See below

Private Sub Form_Open(Cancel As Integer)
Dim VarX
Dim VarY
Dim VarZ

VarX = DLookup("VNODE", "SSS_AOA_XML", "ID=1")
VarY = DLookup("PNODE", "SSS_AOA_XML", "ID=1")
VarZ = DLookup("SERVICE", "SSS_AOA_XML", "ID=1")

End Sub

I then went to the command button and added the variable to the statement contained in the OnClick event. See Below

stAppName = "c:\SubmitCommands.Bat " & "cmdfile=cfg/app/Report.xml " & "PN=" & VarY & " " & "VN=" & VarX & " " & "SVC=" & VarZ

When I run the command I have the batch file echo back what it receives before running and the I get the followed echo backed to me

cmdfile=cfg/app/Report.xml PN= VN= SVC=

[Rabbit]

I did that and I am still not seeing the VarX and VarY being passed into my command event. Do the VarX and VarY need to be public variables? I cannot understand why the value won't pass to the command line. There must be I am missing. Here are the steps I followed.

I opened the form is design view
I selected properties
I changed properties from Detail to Form
In the On Open event I placed the statements above with the correct "" added. See below

Private Sub Form_Open(Cancel As Integer)
Dim VarX
Dim VarY
Dim VarZ

VarX = DLookup("VNODE", "SSS_AOA_XML", "ID=1")
VarY = DLookup("PNODE", "SSS_AOA_XML", "ID=1")
VarZ = DLookup("SERVICE", "SSS_AOA_XML", "ID=1")

End Sub

I then went to the command button and added the variable to the statement contained in the OnClick event. See Below

stAppName = "c:\SubmitCommands.Bat " & "cmdfile=cfg/app/Report.xml " & "PN=" & VarY & " " & "VN=" & VarX & " " & "SVC=" & VarZ

When I run the command I have the batch file echo back what it receives before running and the I get the followed echo backed to me

cmdfile=cfg/app/Report.xml PN= VN= SVC=

As far as I can tell it should work assuming your fields are named id, vnode, pnode, and service. And that they're on the table named sss_aoa_xml. And that there is a record with id = 1 and the fields aren't empty.

If this is all true then it should work perfectly. Try setting a break point at the beginning of the code and step through the code (F8) step by step and see where it's going wrong.

[sesling]

As far as I can tell it should work assuming your fields are named id, vnode, pnode, and service. And that they're on the table named sss_aoa_xml. And that there is a record with id = 1 and the fields aren't empty.

If this is all true then it should work perfectly. Try setting a break point at the beginning of the code and step through the code (F8) step by step and see where it's going wrong.

I found the solution. I needed to remove the Dim VarX, Dim VarY etc from the On Open event and declare the variables outside any Private Sub step on the form. The variables were added below Option Compare Database

Dim VarX
Dim VarY
Dim VarZ

This worked for me. Thanks for your assistance

[Rabbit]

I found the solution. I needed to remove the Dim VarX, Dim VarY etc from the On Open event and declare the variables outside any Private Sub step on the form. The variables were added below Option Compare Database

Dim VarX
Dim VarY
Dim VarZ

This worked for me. Thanks for your assistance

Sure, no problem. Although I don't understand why it wouldn't work within the sub. There's no reason for it not to.