creating variables from a table

[techsupport3977]

I was wondering if it is possible to substitute folder / file locations with variables that do a lookup at a table. In the below code in bold red are C: and the text file (flux ). I have a table that contains roughly 30 records of IP addresses, host names and the file we are looking for. The table is named tWS_List. The field to replace C: is HOST and the field to replace flux is QMA FILE.

Also, within the code there are italicized quotes for host. I would like to replace the quotes with the variable doing the lookup.

Is it possible to continously do this code until the table is complete?

Private Sub Form_Open(Cancel As Integer)

         File = "[b][COLOR=red]C:[/color][/b]\Program Files\QM\QMD\[b][COLOR=red]flux[/color][/b].qma"
         
[COLOR=green]'Check to see if file is present[/color]
    If Dir$(File) = "" Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL " INSERT INTO Network ( HOST ,[DATETIME] ,RESULT )" & _
        "SELECT [b][i]'' AS HOST[/i][/b] ,now() AS [DATE],  'PASS'"
        DoCmd.SetWarnings True
        Close #1
        Exit Sub
    End If

[COLOR=green]'Take file and read second line[/color]
Dim strTextFromFile As String
Open File For Input As #1
     Line Input #1, strTextFromFile
     Line Input #1, strTextFromFile
     
    If strTextFromFile = "" Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL " INSERT INTO Network ( HOST ,[DATETIME] ,RESULT )" & _
        "SELECT [b][i]'' AS HOST[/i][/b] ,now() AS [DATE],  'PASS'"
        DoCmd.SetWarnings True
        Close #1
        Exit Sub
    End If

[COLOR=green]'Test will fail otherwise[/color]
DoCmd.SetWarnings False
DoCmd.RunSQL " INSERT INTO Network ( HOST ,[DATETIME] ,RESULT )" & _
"SELECT [b][i]'' AS HOST[/i][/b] ,now() AS [DATE],  'FAIL'"
DoCmd.SetWarnings True
     
Close #1
[COLOR=green]'MsgBox strTextFromFile[/color]
[COLOR=green]'MsgBox strBuff & FileLen("C:\Program Files\QM\QMD\flux.qma") & " B" [/color]



End Sub

 

[longhair]

techsupport3977,
i believe you can do this by just building your File var correctly.
instead of:

File = "C:\Program Files\QM\QMD\flux.qma"

try:

File = HOST&"Program Files\QM\QMD\"&QMA FILE"&".qma"

same theory would hold true for your querries.
from xl help
& Operator Example
This example uses the & operator to force string concatenation.

Dim MyStr
MyStr = "Hello" & " World" ' Returns "Hello World".
MyStr = "Check " & 123 & " Check" ' Returns "Check 123 Check".



regards,
longhair