Issue with DLOOKUP coding

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
 
G

Guest

Hi,

It's my understanding that dlookup requires a table or query name to be
passed to it as the domain, not a recordset... perhaps this is the problem?

Damian.
 
G

Guest

Hi,

Thanks for the response. But is there a way I can convert recordset to a
domanin so that I can use it in DLOOKUP function? This is the recent coding I
tried where I passed the recordset to a function that process "DLOOKUP"...I
checked the recordset got passed into the function but when I used it in the
DLOOKUP, it's throwing me the same error that Jet is not able to fine the
table. Can someone please help me with it? Thanks in advance!

Function updateVTpercent(record As Recordset, strparttype As String,
strvendortype As String)
Dim VTPercent As Integer
VTPercent = DLookup("[VendorTypePercent]", "record", "[PartType]='" &
strparttype & "' AND [VendorType]='" & strvendortype & "'")
updateVTpercent = VTPercent
MsgBox VTPercent
End Function


Damian S said:
Hi,

It's my understanding that dlookup requires a table or query name to be
passed to it as the domain, not a recordset... perhaps this is the problem?

Damian.

vcsphx said:
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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Try a FindFirst on the recordset instead.

Function updateVTpercent(record As Recordset, _
strparttype As String, strvendortype As String)
Dim VTPercent As Integer

record.MoveFirst
record.FindFirst "[PartType]='" & strparttype & _
"' AND [VendorType]='" & strvendortype & "'")
If record.NoMatch Then
updateVTpercent = 0
Else
updateVTpercent = record![VendorTypePercent]
End If
End Function


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


vcsphx said:
Hi,

Thanks for the response. But is there a way I can convert recordset to a
domanin so that I can use it in DLOOKUP function? This is the recent
coding I
tried where I passed the recordset to a function that process
"DLOOKUP"...I
checked the recordset got passed into the function but when I used it in
the
DLOOKUP, it's throwing me the same error that Jet is not able to fine the
table. Can someone please help me with it? Thanks in advance!

Function updateVTpercent(record As Recordset, strparttype As String,
strvendortype As String)
Dim VTPercent As Integer
VTPercent = DLookup("[VendorTypePercent]", "record", "[PartType]='" &
strparttype & "' AND [VendorType]='" & strvendortype & "'")
updateVTpercent = VTPercent
MsgBox VTPercent
End Function


Damian S said:
Hi,

It's my understanding that dlookup requires a table or query name to be
passed to it as the domain, not a recordset... perhaps this is the
problem?

Damian.

vcsphx said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

Jason Lepack

You can't use a recordset in a dlookup because it's not a table. You
can use the findfirst function of the recordset though.

Function updateVTpercent(record As Recordset, strparttype As String,
strvendortype As String) As Integer
record.FindFirst ("[PartType]='" & strparttype & "' AND
[VendorType]='" & strvendortype & "'")
If Not record.NoMatch Then
updateVTpercent = rs!VendorTypePercent
Else
MsgBox "Bad!"
updateVTpercent = -1
End If
End Function

This should work.

Cheers,
Jason Lepack
Hi,

Thanks for the response. But is there a way I can convert recordset to a
domanin so that I can use it in DLOOKUP function? This is the recent coding I
tried where I passed the recordset to a function that process "DLOOKUP"...I
checked the recordset got passed into the function but when I used it in the
DLOOKUP, it's throwing me the same error that Jet is not able to fine the
table. Can someone please help me with it? Thanks in advance!

Function updateVTpercent(record As Recordset, strparttype As String,
strvendortype As String)
Dim VTPercent As Integer
VTPercent = DLookup("[VendorTypePercent]", "record", "[PartType]='" &
strparttype & "' AND [VendorType]='" & strvendortype & "'")
updateVTpercent = VTPercent
MsgBox VTPercent
End Function



Damian S said:
Hi,
Click to expand...
It's my understanding that dlookup requires a table or query name to be
passed to it as the domain, not a recordset... perhaps this is the problem?
Damian.
Click to expand...

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

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

Guest

Thanks a lot, Jason and Douglas. It works!

Jason Lepack said:
You can't use a recordset in a dlookup because it's not a table. You
can use the findfirst function of the recordset though.

Function updateVTpercent(record As Recordset, strparttype As String,
strvendortype As String) As Integer
record.FindFirst ("[PartType]='" & strparttype & "' AND
[VendorType]='" & strvendortype & "'")
If Not record.NoMatch Then
updateVTpercent = rs!VendorTypePercent
Else
MsgBox "Bad!"
updateVTpercent = -1
End If
End Function

This should work.

Cheers,
Jason Lepack
Hi,

Thanks for the response. But is there a way I can convert recordset to a
domanin so that I can use it in DLOOKUP function? This is the recent coding I
tried where I passed the recordset to a function that process "DLOOKUP"...I
checked the recordset got passed into the function but when I used it in the
DLOOKUP, it's throwing me the same error that Jet is not able to fine the
table. Can someone please help me with it? Thanks in advance!

Function updateVTpercent(record As Recordset, strparttype As String,
strvendortype As String)
Dim VTPercent As Integer
VTPercent = DLookup("[VendorTypePercent]", "record", "[PartType]='" &
strparttype & "' AND [VendorType]='" & strvendortype & "'")
updateVTpercent = VTPercent
MsgBox VTPercent
End Function



Damian S said:
Hi,
Click to expand...
It's my understanding that dlookup requires a table or query name to be
passed to it as the domain, not a recordset... perhaps this is the problem?

"vcsphx" wrote:
Click to expand...
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 coding 3
Function not returning a recordset 5
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
Help with Code Updating Oracle table from an Access table! 3

Top