G
Guest
Hi all,
I am trying to get data for a field in a table from another table using
DLOOKUP function. In the DLOOKUP syntax, my domain field is a recordset that
is created in the coding using SQL. For some reason, I keep getting error
that the recordset which I created using SQL is not in the Jet Database. Do
any of you know why it's not working? I have given the whole code below: I
would really appreciate any help on this. Thanks!
Function update(current As Integer, strpart As String, strtype As String)
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim prev As Integer
Dim record As ADODB.Recordset
prev = current - 1
strSQL = "SELECT * FROM tblMaint WHERE week = " & prev & ""
Set rs = New ADODB.Recordset
With rs
..CursorType = adOpenStatic
..Open "tblmaint", CurrentProject.Connection
End With
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = strSQL
cmd1.CommandType = adCmdText
Set record = cmd1.Execute
Dim VT As Integer
VT = DLookup("[Percent]", "[" & record & "]", "[PartType]='" & strpart & "'
AND [Type]='" & strtype & "'")
update = VT
End Function
I am trying to get data for a field in a table from another table using
DLOOKUP function. In the DLOOKUP syntax, my domain field is a recordset that
is created in the coding using SQL. For some reason, I keep getting error
that the recordset which I created using SQL is not in the Jet Database. Do
any of you know why it's not working? I have given the whole code below: I
would really appreciate any help on this. Thanks!
Function update(current As Integer, strpart As String, strtype As String)
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim prev As Integer
Dim record As ADODB.Recordset
prev = current - 1
strSQL = "SELECT * FROM tblMaint WHERE week = " & prev & ""
Set rs = New ADODB.Recordset
With rs
..CursorType = adOpenStatic
..Open "tblmaint", CurrentProject.Connection
End With
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
cmd1.ActiveConnection = CurrentProject.Connection
cmd1.CommandText = strSQL
cmd1.CommandType = adCmdText
Set record = cmd1.Execute
Dim VT As Integer
VT = DLookup("[Percent]", "[" & record & "]", "[PartType]='" & strpart & "'
AND [Type]='" & strtype & "'")
update = VT
End Function