ADO Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am new to witing VBA and am struggling with writing a function. The
following code does not work. What could be wrong. Thanks in advance for your
hrlp.

Function GetItemDesc(ItemNumber As String)

Set conn = AS400.CreateObject("ADODB.Connection")
strConn = "DSN=AS400"
conn.Open strConn
strQuery = "SELECT USIAJFL0.JFITDS FROM AS400.SCDBFP10.USIAJFL0 WHERE
USIAJFL0.JFITEM=ItemNumber"
conn.Execute (strQuery)

End Function
 
Dean Underwood said:
I am new to witing VBA and am struggling with writing a function. The
following code does not work. What could be wrong.
Click to expand...
strQuery = "SELECT USIAJFL0.JFITDS FROM AS400.SCDBFP10.USIAJFL0 WHERE
USIAJFL0.JFITEM=ItemNumber"
Click to expand...

Hi Dean

You are searching for the literal text "ItemNumber", not the variable value
of ItemNumber. Bringing us to

WHERE USIAJFL0.JFITEM=" & ItemNumber

This should work if the JFITEM table is numerical. If it's a text field
however then it needs to be enclosed by single quotes like this

WHERE USIAJFL0.JFITEM='" & ItemNumber & "'"

HTH. Best wishes Harald
 
First. Sorry about the double post. Now the code looks like the following and
it still returns a "#VALUE" in the cell. Thanks for the response. As you can
tell I am stumped.

Function GetItemDesc(ItemNumber As String)

Set conn = Server.CreateObject("ADODB.Connection")
strConn = "DSN=AS400"
conn.Open strConn
strQuery = "SELECT USIAJFL0.JFITDS FROM AS400.SCDBFP10.USIAJFL0 WHERE
USIAJFL0.JFITEM='" & ItemNumber & "'"
conn.Execute (strQuery)

End Function
 
#VALUE means there's an error in the code somewhere. I don't know anything
about interfacing with the AS400, but when fetching data from an Access
database, I have to set up a recordset object to hold the query results.

Does this query return a record set? I don't see any place where you define a
recordset and populate it, then assign the returned value to GetItemDesc.

Shouldn't you have something like

Dim rst As ADODB.Recordset
Set rst = conn.Execute(strQuery)
'some code here to look at what's returned

Here's a portion of some code I use (and it works). Maybe it will give you
some ideas.

Set rs = New ADODB.Recordset
With rs
.Source = SQLStmt 'an argument to the function
.ActiveConnection = ADO_Cnxn 'ADO_Cnxn set by another sub
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
If InStr(SQLStmt, " ") = 0 Then
Opt = adCmdTable
Else
Opt = adCmdText
End If
.Open , , , , Opt

If .RecordCount > 0 Then
v = .GetRows
Else
v(0, 0) = "No Records"
End If
.Close
End With
 
your function doesnt return a value.. so you get the #value! error.

try like:

GetItemDesc = conn.Execute(strQuery).Fields(0)

connExecute returns a recordset object..
normally you 's "catch that" like
set oRST = conn.Execute(str)

since your query retrieves 1 field only
i've "piped" it into the string above.
note that the recordset's fields collection is ZERO based!




http://www.devguru.com/Technologies/ado/quickref/ado_intro.html
has very good references for ADO and JetSql




keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >


"=?Utf-8?B?RGVhbiBVbmRlcndvb2Q=?="
 
Thanks to all. I have learned quite a bit today. The code that follows is the
code that works.

Function GetItemDesc(ItemNumber As String)

Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "DSN=AS400"
conn.Open
strQuery = "SELECT JFITDS FROM SCDBFP10.USIAJFPF WHERE JFITEM='" &
ItemNumber & "'"
GetItemDesc = conn.Execute(strQuery).Fields(0)

Set conn = Nothing
Set StrConn = Nothing

End Function
 
Back
Top