VBA Insert result from sql statement into a form of a field

Hi,
I was hoping to get some help with the following code. I have a field that I need to populate with the result of a sql select statement. Cant seem to work

So when I click a button I need the field to be populated with the max of a value

Me.provid = DoCmd.RunSQL "select max(id) from ser_faxes_file"

Open in new window


somehow it cant seem to work. I was reading a bit and noticed that docmd.runsql is not used for select statements. Not sure what to use.

thanks,
damixaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
What you want is this:

Me.provid = DMax("[id]","ser_faxes_file")

 This is using a Domain function, of which there are several (DMax(), Dlookup(), etc).   All encapsulate a SQL statement and are intended to be used where SQL statements are not directly.

Your other option when working in code is to open a recordset, read the record(s) returned, and grab your values.

DoCmd.RunSQL

 is a statement used by itself to execute "action queries"; those that don't return records (such as appends, make table, updates, etc) but only execute.

 and instead of that, you're better off with using:

CurrentDB().Execute   <SQL statement>, dbFailOnError

 Because with the last argument, dbFailOnError, you can add error trapping and tell if something goes wrong.  You can't do that with RunSQL.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damixaAuthor Commented:
HI Jim,
That Me.provid = DMax("[id]","ser_faxes_file") Worked perfect.
I'm a newbie, but if I wanted to add 1 to the max, what would the syntax be?

thanks,
Vinnie
0
damixaAuthor Commented:
I tried the Me.provid = DMax("[id]","ser_faxes_file")  +1 but got a type mismatch
0
damixaAuthor Commented:
Actually, I got it to work. Thanks a ton.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I tried the Me.provid = DMax("[id]","ser_faxes_file")  +1>>

If your doing this to generate a sequential number, be aware that DMax() can fail in a multi-user situation (it's possible for two users to end up with the same value.

Instead, you'd use a key generation procedure which locks a table allowing only one value to be generated at a time.

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.