[RESOLVED] Updating tables?

[escudolm]

Got a question. I have a function getting the last number in a certain table's field. It gets the last number in the field and displays it. I then have a 1 added to it. It displays the last found number in the field, then I have it display what the next number would be, when having a 1 added to it. I need the number that has a 1 added to it, to be put in a table, or rather update it. How can I take the new number and update the field in the other table with the new number? I have the code in a module right now, if that matters.

[DKenny]

You will need to execute a SQL INSERT statement.

SQL Inserts have the following structure
INSERT INTO tablename (fieldname1,fieldname2,..fieldnamen) VALUES (value1, value2,...valuen)

[escudolm]

This is my code so far:


Sub GetLastRaceNumber()
Dim rs As ADODB.Recordset
Dim sSQL As String
Dim nextnum As Integer

Set rs = New ADODB.Recordset

sSQL = "SELECT RaceNumber FROM tblNumberGen"""

rs.Open Source:=sSQL, ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic

rs.MoveLast
MsgBox "last entrynumber is " & rs.Fields(0).Value
nextnum = rs.Fields(0).Value + 1
MsgBox "next entrynumber would be " & nextnum
Set rs = Nothing

'sSQL = "UPDATE tblNumberLog.Next INNER JOIN tblEntrants, tblNumberGen ON tblNumberGen.EntryNumber = tblEntrants.EntryNumber, tblEntrants.Category = tblNumberLog.Category SET tblNumberGen.RaceNumber + 1 = tblNumberLog.Next """
sSQL = "INSERT INTO tblNumberLog (Next) VALUES (nextnum)"""
'rs.Close
Set rs = Nothing
End Sub


The second sSql statement really doesn't do anything. Any ideas?

[DKenny]

This may be a dumb quextion, but why do you to store the next possible race number in a seperate table?
Wouldn't it be much easier to create a Query that tells you what the next race number should be?

The following SQl pasted into a Query in Access should give you this answer, without having to store the value in a seeperate table.

SELECT Max([tblNumberGen]![RaceNumber])+1 AS NextRace
FROM tblNumberGen

[escudolm]

Well, there is another table holding what the next number should be. I have to get the original number from tblNumberGen and then put it into the "Next" field on tblNumberLog. The "Next" field in tblNumberLog holds the number to be used next in a numbering sequence.

[DKenny]

Why do you need to physically store this value when you can always derive it programatically using the SQL statement above?

[escudolm]

Would I just create another query or can I stick it in another one? Im just doing what I got told to do, but if this is easier, then I guess I will try this.