Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 486
  • Last Modified:

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,
0
damixa
Asked:
damixa
  • 3
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
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)PresidentCommented:
<<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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now