Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DoCmd.RunSQL problem

Status
Not open for further replies.

kwor

MIS
Dec 10, 2008
35
AU
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?
 
SELECT Max([AssetNo])+1 AS NextNumber FROM tblAssetNo"

This will give you the next number

PoppaSmurf
allanmurphy47@gmail.com
 

You could also use Dlookup.
Code:
intNextNumber = Dlookup("AssetNo", "tblAssetNo") + 1

Randy
 
How are ya kwor . . .
kwor said:
[blue]When I run the code, I get error 2342 ...[/blue]
Microsoft said:
[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] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Thanks everyone, the DLookup worked.

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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top