Demorganafari
New Member
- Joined
- Jun 19, 2016
- Messages
- 2
I am trying to use the code below to retrieve some data from Microsoft Access. However, the part of the code in italics below gets highlighted(when I debug) and I get the feedback :No value given for one or more required parameters. Can anyone help?
Sub GetUnsentTransfers()
' Page 481
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'FstNm As Variant, LstNm As Variant, HseNm As Variant)
'Build a SQL String to get all fields for unsent transfers
sSQL = "SELECT FstNm, LstNm, HseNm FROM Customer20"
sSQL = sSQL & " WHERE [FstNm] = " & Range("I2").Value & " And [LstNm]= " & Range("J2").Value
' Path to Transfers.mdb
MyConn = ThisWorkbook.Path & Application.PathSeparator & "Mydata1.mdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdText
Activeworksheet.Select
Range("N1:P1").EntireColumn.Clear
' Add Headings
Range("N1:P1").Value = Array("FstNm", "LstNm", "HseNm")
' Copy from the recordset to row 2
Range("N2").CopyFromRecordset rst
' Close the connection
rst.Close
cnn.Close
' Format the report
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' If there were no records, then stop
If FinalRow = 1 Then
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox "There are no transfers to confirm"
Exit Sub
End If
End Sub
Sub GetUnsentTransfers()
' Page 481
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'FstNm As Variant, LstNm As Variant, HseNm As Variant)
'Build a SQL String to get all fields for unsent transfers
sSQL = "SELECT FstNm, LstNm, HseNm FROM Customer20"
sSQL = sSQL & " WHERE [FstNm] = " & Range("I2").Value & " And [LstNm]= " & Range("J2").Value
' Path to Transfers.mdb
MyConn = ThisWorkbook.Path & Application.PathSeparator & "Mydata1.mdb"
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open MyConn
End With
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdText
Activeworksheet.Select
Range("N1:P1").EntireColumn.Clear
' Add Headings
Range("N1:P1").Value = Array("FstNm", "LstNm", "HseNm")
' Copy from the recordset to row 2
Range("N2").CopyFromRecordset rst
' Close the connection
rst.Close
cnn.Close
' Format the report
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
' If there were no records, then stop
If FinalRow = 1 Then
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox "There are no transfers to confirm"
Exit Sub
End If
End Sub