Running SQL to update field in a second table

[reidarT]

I want to update a table with the max-value from another table.
How do I combine the 2 sql-statements?

sqlMaxBookingID = _
"SELECT Max(tblBooking.BookingID) AS MaxOfBookingID " & _
"FROM tblBooking"

sqlLastBookingID = _
"UPDATE tblCompany SET tblCompany.LastBookingID = " & MaxOfBookingID+1

reidarT

 

[Arvin Meyer [MVP]]

Build a recordset using the first sql statement, then use that value in a
second recordset, something like (aircode):

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String

strSQL = _
"SELECT Max(tblBooking.BookingID) AS MaxOfBookingID " & _
"FROM tblBooking"

Set db = CurrentDB
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Set rst2 = db.OpenRecordset("tblCompany", dbOpenDynaSet)

With rst2
.Edit
.LastBookingID = rst.MaxOfBookingID + 1
.Update
End With

Then close everything and clean up your variables.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[reidarT]

Thanks, but the code stops compiling on LastBookingID = rst.MaxOfBookingID +
1
and select the MaxOfBookingID as an error.

reidarT

 

[Douglas J. Steele]

Are you sure that you typed MaxOfBookingID exactly the same in both places
it's used: in strSQL and in the line of code you cited as raising the error?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[reidarT]

Here is my code:

'-- LastBookingID
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String

strSQL = _
"SELECT Max(tblBooking.BookingID) AS MaxOfBookingID " & _
"FROM tblBooking"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
Set rst2 = db.OpenRecordset("tblCompany", dbOpenDynaset)

With rst2
.Edit
.LastBookingID = rst.MaxOfBookingID + 1
.Update
End With

rst.Close
rst2.Close
db.Close

reidarT

 

[Douglas J. Steele]

Try using Bangs (!), not Dots (.)

!LastBookingID = rst!MaxOfBookingID + 1


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[reidarT]

I don't know why it doesn't work, but it doesn't.
How do I code this using ADO?
reidarT

 

[Douglas J Steele]

Before changing from DAO, let's break the problem down a little.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim lngMaxValue As Long
Dim strSQL As String

strSQL = _
"SELECT Max(tblBooking.BookingID) AS MaxOfBookingID " & _
"FROM tblBooking"

Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

If rst.EOF = True Then
MsgBox "There are no records in rst"
Else
lngMaxValue = rst!MaxOfBookingsID
MsgBox "The highest used value is " & lngMaxValue
Set rst2 = db.OpenRecordset("tblCompany", dbOpenDynaset)

With rst2
.Edit
.LastBookingID = lngMaxValue + 1
.Update
End With
rst2.Close
End If

rst.Close
db.Close


What do the message boxes tell you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[reidarT]

Thanks for your help.
I have pasted your code and run it with following error

Compile Error

Method or data member not found.

The code stops on .LastBookingID =

I have double checked the field LastBookingID in the tblCompany table. It is
Long Integer and exists.

Both tables where tables in backend-db, but to make it easy for this example
I have imported the tables to use currentdb.

reidarT

 

[Douglas J Steele]

That was my fault: I forgot to use the !, as I'd advised earlier.

!LastBookingID = lngMaxValue + 1

If that still doesn't work, make sure that there aren't blanks in the table
name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Arvin Meyer [MVP]]

Sorry about the Bang, Doug was right on there. Now I think that the answer
may be something that is too simple: i.e. Have you set a reference to DAO?
If not, try doing so:

Tools ... References

Find Microsoft DAO 3.6 and check it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access