Access VB question

[Guest]

I know this is a VERY BASIC question, but would appreciate help. I have a
form in Access2000 that is tied to a table and used for input. I want to
write a very simple SQL in VB to retrieve an ID number from a table within
the mdb
file and assign it to a variable. How would I do this? Essentially, the SQL I
want to write is: "select max(idea_number) from ideas;"
Thanks in advance for your help!!

 

[Dirk Goldgar]

I know this is a VERY BASIC question, but would appreciate help. I
have a form in Access2000 that is tied to a table and used for input.
I want to write a very simple SQL in VB to retrieve an ID number from
a table within the mdb
file and assign it to a variable. How would I do this? Essentially,
the SQL I want to write is: "select max(idea_number) from ideas;"
Thanks in advance for your help!!

You can use the DMax() function to return that value for you. For
example,

Dim MaxIdeaNo As Long

MaxIdeaNo = DMax("idea_number", "ideas")

You can do the same thing by opening a recordset on the SQL statement
you proposed, extracting the value from the recordset, and then closing
the recordset; however, for a quick lookup it's probably simplest just
to use DMax.

 

[Guest]

The DMAX idea should work. I very much appreciate the help!! Just for the
sake of learning something, if I did want to go the route of opening a
recordset on the SQL, how would I do that? (May come in handy in the
future....)

 

[Dirk Goldgar]

The DMAX idea should work. I very much appreciate the help!! Just for
the sake of learning something, if I did want to go the route of
opening a recordset on the SQL, how would I do that? (May come in
handy in the future....)

Dim rs As DAO.Recordset
Dim MaxIdeaNo As Long

Set rs = CurrentDb.OpenRecordset( _
"SELECT MAX(idea_number) FROM ideas", _
dbOpenSnapshot)

With rs
If .EOF Then
MaxIdeaNo = 0 ' empty table; aribitrarily use 0
Else
MaxIdeaNo = .Fields(0)
End If
.Close
End With

Set rs = Nothing

 

[Guest]

Thank you, sir!!!!

Dim rs As DAO.Recordset
Dim MaxIdeaNo As Long

Set rs = CurrentDb.OpenRecordset( _
"SELECT MAX(idea_number) FROM ideas", _
dbOpenSnapshot)

With rs
If .EOF Then
MaxIdeaNo = 0 ' empty table; aribitrarily use 0
Else
MaxIdeaNo = .Fields(0)
End If
.Close
End With

Set rs = Nothing

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)