Hi all
Hoping you can help, i'm transferring data from access into to excel.
I want the information to show in a list box, I have got this to work witht he following code:
Public Sub please(ID As Integer)
Dim test As Variant
Set ActiveRecord = SQLQuery("SELEC T * FROM ............... .... WHERE ID= " + Trim(CStr(ID)))
test = ActiveRecord.Ge tRows
With Me.list
.Clear
.ColumnCount = 6
.list = Application.Tra nspose(test)
.ListIndex = -1
End With
End Sub
This works fine on its own, except that I need it to look at specifc data and look for all records which correspond to it. code I;m using for this is:
Public Function SearchRecord() As Boolean
Dim NI As String
test= InputBox("..... ............... .......", "Search")
If Len(NI) > 0 Then
Dim Query As ADODB.Recordset
Set Query = SQLQuery("SELEC T * FROM .... WHERE .......... = '" +....+ "'")
If Not Query.EOF Then
please Query.Fields(". ......").Value
SearchRecord = True
Else
MsgBox ".............. .... " + ......... + " could be located", vbExclamation
SearchRecord = False
End If
Query.Close
Else
SearchRecord = False
End If
End Function
This works fine, however it will only show one record in list box, I need it to show all related records. I have highlighted the section I believe is wrong, but could do with some help to rectify it. I tried to include a loop but that seem to break excel
If Not Query.EOF Then
please Query.Fields(". .............") .Value
SearchRecord = True
Hoping you can help, i'm transferring data from access into to excel.
I want the information to show in a list box, I have got this to work witht he following code:
Public Sub please(ID As Integer)
Dim test As Variant
Set ActiveRecord = SQLQuery("SELEC T * FROM ............... .... WHERE ID= " + Trim(CStr(ID)))
test = ActiveRecord.Ge tRows
With Me.list
.Clear
.ColumnCount = 6
.list = Application.Tra nspose(test)
.ListIndex = -1
End With
End Sub
This works fine on its own, except that I need it to look at specifc data and look for all records which correspond to it. code I;m using for this is:
Public Function SearchRecord() As Boolean
Dim NI As String
test= InputBox("..... ............... .......", "Search")
If Len(NI) > 0 Then
Dim Query As ADODB.Recordset
Set Query = SQLQuery("SELEC T * FROM .... WHERE .......... = '" +....+ "'")
If Not Query.EOF Then
please Query.Fields(". ......").Value
SearchRecord = True
Else
MsgBox ".............. .... " + ......... + " could be located", vbExclamation
SearchRecord = False
End If
Query.Close
Else
SearchRecord = False
End If
End Function
This works fine, however it will only show one record in list box, I need it to show all related records. I have highlighted the section I believe is wrong, but could do with some help to rectify it. I tried to include a loop but that seem to break excel
If Not Query.EOF Then
please Query.Fields(". .............") .Value
SearchRecord = True