April 30, 2009 at 5:26 am
Hello,
All I am trying to do is return a single number into a variable in order to check if there are records in a table that match certain criteria. My query works fine in SQL Server, but it's not filling the variable in access. Can you help me? - Rob
April 30, 2009 at 5:50 am
Can you post the query?
April 30, 2009 at 6:02 am
Dim cmdSQL As ADODB.Command
Set cmdSQL = New ADODB.Command
cmdSQL.CommandText = "select count(OrderQty) from RFQOrderQtys where RFQNumber = " & Me.RFQNumber
cmdSQL.CommandType = adCmdText
Dim rsSQL As ADODB.Recordset
Set rsSQL = New ADODB.Recordset
rsSQL.Open cmdSQL
Dim intCount as int
intCount = rsSQL.RecordCount
MsgBox (intCount & " Records.")
April 30, 2009 at 6:39 am
What value do you get in variable intCount? If it returns -1 then it means the recordset object is opened in dynamic mode. Change the CursorType property of recordset object to adOpenStatic.
--Ramesh
April 30, 2009 at 7:05 am
I think I am going about this all wrong. I am developing in a data project(.adp). Do I still need to create a connection in order to return a recordset, or is the connection already established via the properties of the project? This is beyond the scope of my normal responsibilities and I am very new to this. I just want to check if there is data in a table based on an ID number and return a true/false. It sounds easy to me, but I have no idea how to do it. Is this even the right place to ask for this help? - Rob
I changed to this and it worked. Sorry if this post was not in the right forum. - Rob
Dim rsSQL As New ADODB.Recordset
Dim strSQL As String
strSQL = "select OrderQty from RFQOrderQtys where RFQNumber = " & Me.RFQNumber
rsSQL.Open strSQL, CurrentProject.Connection
If rsSQL.BOF And rsSQL.EOF Then
MsgBox "You Need to enter an order Qty!", vbCritical, "ERROR"
Exit Sub
Else
End If
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply