DoCmd.RunSQL problem

[kwor]

I have two tables - tblAssets and tblAssetNo
tblAssets is the main db table
tblAssetNo has only one record that keeps track of the latest asset number.
I have a form to add a new asset with a bound control called AssetNo. I am trying to use the following code to get the next asset number from tblAssetNo and place it in the AssetNo control on the form:

Dim SQL As String
DoCmd.GoToRecord , , acNewRec
SQL = "SELECT AssetNo FROM tblAssetNo"
DoCmd.RunSQL SQL

When I run the code, I get error 2342
"A RunSQL action requires an argument consisting of an SQL statement". I can use the SQL string in a query and it retrieves the record.

What am I doing wrong?

 

[PoppaSmurf]

SELECT Max([AssetNo])+1 AS NextNumber FROM tblAssetNo"

This will give you the next number

PoppaSmurf
allanmurphy47@gmail.com

 

[randy700]

You could also use Dlookup.

intNextNumber = Dlookup("AssetNo", "tblAssetNo") + 1

Randy

 

[TheAceMan1]

How are ya kwor . . .

[blue]When I run the code, I get error 2342 ...[/blue]

[blue]A RunSQL [purple]action[/purple] requires an argument consisting of an SQL statement.For example, an [purple]action query[/purple] that appends records starts with INSERT INTO. A data-definition query that creates a table starts with CREATE TABLE.[/blue]

So ... DoCmd.RunSQL only handles [purple]action[/purple] queries, not select queries. You can use the select query if you stuff it in a recordset. However, randy700 has revealed a better way than recordset for your schema.

Don't forget you need to update [blue]tblAssetNo[/blue] with the new +1 value!

[blue]Your Thoughts! ...[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[kwor]

Thanks everyone, the DLookup worked.

I shall remember not to use DoCmd.RunSQL in this situation again.