Beginner Question

[SDragon9]

Well, I'm a beginner to SQL but I have a good amount of experience with Visual Basic. Anyway, I would like to know this:
When I query a database in Visual Basic, and it returns a single, numeric value, is there any way that I can save that value into a integer variable that I have declared in Visual Basic?
I have heard that this is very hard, but it is crucial a program I'm working on. If anyone has a better way, please let me know, and if anyone knows how to do this, I would really appreciate it if you replied.

Thanks

[Clunietp]

do you mean it "returns" a value by returning a recordset with a single record?

or do you have a stored procedure that has a return value (NOT a recordset) that you need to retrieve?

[SDragon9]

I have a recordset which returns one number, and I want to be able to use that number in my program, which means that I have to save it to an integer, I assume...

I would appreciate it if you could tell me the answer to this, but if it would be easier to explain how to do it with a stored procedure, please don't hesitate to explain that instead.

Thanks again

[SDragon9]

I'm trying to select a value in a field, ID_Count, from the last row in a table, General_Info and save that into an integer. Using the above code, this is what I do, however I get an error saying "No current record". Here's my code:

Dim db As Database
Dim rs As Recordset
Dim dbPath As String
Dim SQL As String
Dim numRecords As Integer
Dim theID_Count As Integer
dbPath = App.Path
dbPath = dbPath & "/DatabaseNameHere.mdb"
Set db = OpenDatabase(dbPath)
Set rs = db.OpenRecordset("General_Info")
rs.MoveLast
numRecords = rs.RecordCount
SQL = "SELECT ID_Count FROM General_Info WHERE ID_Count=numRecords"
Set rs = db.OpenRecordset(SQL)
theID_Count = CInt(rs.Fields(0))
'********************************************************
'Doing the following to ensure that ID_Count was actually
'selected and placed into the integer theID_Count
MsgBox theID_Count

If I had a little more background in programming SQL with VB, I would probably be able to fix this problem. However, I would appreciate any other feedback. Thank you.

[Clunietp]

The problem appears to be in your SQL statement

Change this:
SQL = "SELECT ID_Count FROM General_Info WHERE ID_Count=numRecords"

To this:
SQL = "SELECT ID_Count FROM General_Info WHERE ID_Count=" & numRecords

[SDragon9]

Argh, of course, I should have seen that. Thanks! BTW, does the rest of my code look right? Because I always get the No current Records error where I say "Records.MoveLast". Anyway, thanks, and I'll get back to you.

[SDragon9]

Actually, you know what, I don't even need to do rs.MoveLast, but I'll go try out that code now.

[SDragon9]

Never mind that last post.

[SDragon9]

Thanks for all your help Clunetip. It's working fine, atleast for now...