Passing results from Immediate Window

  • Thread starter David Mulholland
  • Start date
D

David Mulholland

I'm using the code to see who is in the LDB of a linked database. I have it
set up so that I select a linked table from a listbox from my Master Form and
then call this function...(original code)

Public Sub WhoIsInLDB()
' Written by Ken Snell (January 31, 2005)

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String
Dim strLinkedTableName As String
strLinkedTableName = Forms![Master Form]![LinkedTable]
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="
On Error GoTo Err_Msg
strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb
strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))
Debug.Print "File containing the data tables: " & strNewDataSource
cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)
cn.Open

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, "", rs.Fields(2).Name,
rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
Exit_Sub:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " & _
Err.Description
Resume Exit_Sub
End Sub


Modified code...
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name
While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] = &
_ rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend


What I want to do is send the debug.print comments to a textbox on my main
form, Forms![Master Form]![LDB_Results].

I've played around with it and can only get the first computername from the
Immediate Window to post to that textbox when there are actually multiples.
What am I missing?? It's got to be something in the While/Wend piece but I am
having brain cramps. Any ideas?
 
R

Ralph

Why not use a list box to display them:

Modified code...
dim strUser as string
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name

While Not rs.EOF
strUser=Forms![Master Form]![LDB_Results] = & _
rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)

Forms![Master Form]![LDB_Results].Listbox0.AddItem(strUser)

rs.MoveNext
Wend


David Mulholland said:
I'm using the code to see who is in the LDB of a linked database. I have it
set up so that I select a linked table from a listbox from my Master Form and
then call this function...(original code)

Public Sub WhoIsInLDB()
' Written by Ken Snell (January 31, 2005)

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String
Dim strLinkedTableName As String
strLinkedTableName = Forms![Master Form]![LinkedTable]
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="
On Error GoTo Err_Msg
strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb
strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))
Debug.Print "File containing the data tables: " & strNewDataSource
cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)
cn.Open

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, "", rs.Fields(2).Name,
rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
Exit_Sub:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " & _
Err.Description
Resume Exit_Sub
End Sub


Modified code...
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name
While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] = &
_ rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend


What I want to do is send the debug.print comments to a textbox on my main
form, Forms![Master Form]![LDB_Results].

I've played around with it and can only get the first computername from the
Immediate Window to post to that textbox when there are actually multiples.
What am I missing?? It's got to be something in the While/Wend piece but I am
having brain cramps. Any ideas?
Click to expand...
 
R

Ralph

After looking at this again I realized I did not answer your question. In
your loop you using and = where you should use &

While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] &
rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend




David Mulholland said:
I'm using the code to see who is in the LDB of a linked database. I have it
set up so that I select a linked table from a listbox from my Master Form and
then call this function...(original code)

Public Sub WhoIsInLDB()
' Written by Ken Snell (January 31, 2005)

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String
Dim strLinkedTableName As String
strLinkedTableName = Forms![Master Form]![LinkedTable]
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="
On Error GoTo Err_Msg
strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb
strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))
Debug.Print "File containing the data tables: " & strNewDataSource
cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)
cn.Open

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, "", rs.Fields(2).Name,
rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
Exit_Sub:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " & _
Err.Description
Resume Exit_Sub
End Sub


Modified code...
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name
While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] = &
_ rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend


What I want to do is send the debug.print comments to a textbox on my main
form, Forms![Master Form]![LDB_Results].

I've played around with it and can only get the first computername from the
Immediate Window to post to that textbox when there are actually multiples.
What am I missing?? It's got to be something in the While/Wend piece but I am
having brain cramps. Any ideas?
Click to expand...
 
D

David Mulholland

OMG!! Sweet, beautiful code. Sometimes I tend to overanalyze :)

I did tweak it just a little. I used only the rs.Fields(0) to list the
machine names and have that insert into my combobox. I can then use that
combobox to run my shelled cmd script to get actual network usernames for
anyone in that LDB.

Working great. Thanks for the tip.

Ralph said:
Why not use a list box to display them:

Modified code...
dim strUser as string
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name

While Not rs.EOF
strUser=Forms![Master Form]![LDB_Results] = & _
rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)

Forms![Master Form]![LDB_Results].Listbox0.AddItem(strUser)

rs.MoveNext
Wend


David Mulholland said:
I'm using the code to see who is in the LDB of a linked database. I have it
set up so that I select a linked table from a listbox from my Master Form and
then call this function...(original code)

Public Sub WhoIsInLDB()
' Written by Ken Snell (January 31, 2005)

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String
Dim strLinkedTableName As String
strLinkedTableName = Forms![Master Form]![LinkedTable]
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="
On Error GoTo Err_Msg
strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb
strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))
Debug.Print "File containing the data tables: " & strNewDataSource
cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)
cn.Open

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, "", rs.Fields(2).Name,
rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
Exit_Sub:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " & _
Err.Description
Resume Exit_Sub
End Sub


Modified code...
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name
While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] = &
_ rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend


What I want to do is send the debug.print comments to a textbox on my main
form, Forms![Master Form]![LDB_Results].

I've played around with it and can only get the first computername from the
Immediate Window to post to that textbox when there are actually multiples.
What am I missing?? It's got to be something in the While/Wend piece but I am
having brain cramps. Any ideas?
Click to expand...
Click to expand...
 
D

David Mulholland

Like I said in my earlier post, I did get it working and it is posting only
the rs.Fields(0) (machinenames) to a combobox using that AddItem.

Here's another question. How do I remove these Items from that combobox so
if i run this again using a different linked table, it does NOT append the
machinenames to the already populated list. I need to clear all the entries
in the Forms![Master Form]![tempMachineNames] combobox before the function is
run.

Ralph said:
After looking at this again I realized I did not answer your question. In
your loop you using and = where you should use &

While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] &
rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend




David Mulholland said:
I'm using the code to see who is in the LDB of a linked database. I have it
set up so that I select a linked table from a listbox from my Master Form and
then call this function...(original code)

Public Sub WhoIsInLDB()
' Written by Ken Snell (January 31, 2005)

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String
Dim strLinkedTableName As String
strLinkedTableName = Forms![Master Form]![LinkedTable]
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="
On Error GoTo Err_Msg
strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb
strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))
Debug.Print "File containing the data tables: " & strNewDataSource
cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)
cn.Open

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, "", rs.Fields(2).Name,
rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
Exit_Sub:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " & _
Err.Description
Resume Exit_Sub
End Sub


Modified code...
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name
While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] = &
_ rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend


What I want to do is send the debug.print comments to a textbox on my main
form, Forms![Master Form]![LDB_Results].

I've played around with it and can only get the first computername from the
Immediate Window to post to that textbox when there are actually multiples.
What am I missing?? It's got to be something in the While/Wend piece but I am
having brain cramps. Any ideas?
Click to expand...
Click to expand...
 
D

David Mulholland

nevermind...I guess I need to search better before posting...

Found this

Me.tempMachineName.RowSource = ""

and it works..

Thanks again, Ralph.

Ralph said:
After looking at this again I realized I did not answer your question. In
your loop you using and = where you should use &

While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] &
rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend




David Mulholland said:
I'm using the code to see who is in the LDB of a linked database. I have it
set up so that I select a linked table from a listbox from my Master Form and
then call this function...(original code)

Public Sub WhoIsInLDB()
' Written by Ken Snell (January 31, 2005)

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String
Dim strLinkedTableName As String
strLinkedTableName = Forms![Master Form]![LinkedTable]
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="
On Error GoTo Err_Msg
strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb
strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))
Debug.Print "File containing the data tables: " & strNewDataSource
cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)
cn.Open

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, "", rs.Fields(2).Name,
rs.Fields(3).Name
While Not rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3)
rs.MoveNext
Wend
Exit_Sub:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub
Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " & _
Err.Description
Resume Exit_Sub
End Sub


Modified code...
Forms![Master Form]![LDB_Results] = rs.Fields(0).Name & " " & _
rs.Fields(1).Name & " " & rs.Fields(2).Name & " " & rs.Fields(3).Name
While Not rs.EOF
Forms![Master Form]![LDB_Results] = Forms![Master Form]![LDB_Results] = &
_ rs.Fields(0) & " " & rs.Fields(1) & " " & rs.Fields(2) & " " &
rs.Fields(3)
rs.MoveNext
Wend


What I want to do is send the debug.print comments to a textbox on my main
form, Forms![Master Form]![LDB_Results].

I've played around with it and can only get the first computername from the
Immediate Window to post to that textbox when there are actually multiples.
What am I missing?? It's got to be something in the While/Wend piece but I am
having brain cramps. Any ideas?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What Code and where do I put it to Nest Loop 1
Export Data to MS Word Table and Format 1
traversing a large recordset 4
Help with found code 23
display the result 1
link subform within subform 0
Convert PowerPoint refrence to late binding in Access 5
Ranking Records 4

Top