Issue with coding

V

vinochand

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
 
V

vinochand

Hi, Thanks for your response. But the reason why I had a separate
record opened is because I needed to run a query inside my coding
(which is "SELECT * FROM tblMaint WHERE week = " & prev & "") and I
don't have a clue how to refer this query in the DLookup syntax. Can
you please help me with it?

Thanks!


Hi,
you don't need to open recordset in order to use dlookup:
VT = DLookup("[Percent]", "[tblMaint ]", "[PartType]='" & strpart
& "'
AND [Type]='" & strtype & "'")

--
Best regards,
___________
Alex Dybenko (MVP)http://alexdyb.blogspot.comhttp://www.PointLtd.com




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!
Click to expand...
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- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...
 
G

Guest

Hi Alex,

Please don't mind my previous reply. I got it working. I used .Findnext
option instead of the Dlookup as I had a recordset and not a table. And it
did the magic!
Thanks!

[email protected] said:
Hi, Thanks for your response. But the reason why I had a separate
record opened is because I needed to run a query inside my coding
(which is "SELECT * FROM tblMaint WHERE week = " & prev & "") and I
don't have a clue how to refer this query in the DLookup syntax. Can
you please help me with it?

Thanks!


Hi,
you don't need to open recordset in order to use dlookup:
VT = DLookup("[Percent]", "[tblMaint ]", "[PartType]='" & strpart
& "'
AND [Type]='" & strtype & "'")

--
Best regards,
___________
Alex Dybenko (MVP)http://alexdyb.blogspot.comhttp://www.PointLtd.com




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!
Click to expand...
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- Hide quoted text -
Click to expand...

- Show quoted text -
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

Issue with DLOOKUP coding 5
Function not returning a recordset 5
Connection and Create New Table 2
Return Values from SQL Server to MS Access 1
INSERT Query problem with Quotes & Apostrophes 9
return recordset 3
Multiple-variable Functions 2
Access ADO parameter query 1

Top