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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy