Set a field/value equal to a SQL statement 3

[kjv1611]

Is there a particular context to set the value of a table or field equal to a SQL statement within VBA?

For instance, I want to have a query return one value, and have that value entered into a table. I would like to do this via SQL within my VBA procedure.

I may be just totally forgetting or overlooking something, but I just can't seem to get this accomplished at the moment.

Any thoughts/ideas?

Here's the data:
I have an ID field in one table. In updating another table's new records, I want to include the "MAX" value of the ID field in the original table.

So, I would like to do something like this:

Private Sub Test()
  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  Dim strSQL as String
  Set db = CurrentDb
  Set rs = db.OpenRecordset("TableDetail")

  strSQL = "SELECT Max([ImportID]) AS ImpID FROM tblHist;

  rs.Fields("OtherID") = DoCmd.RunSQL (strSQL)
  
  rs.Close
  db.Close
  Set rs = Nothing
  Set db = nothing
End Sub

I'm sure that won't work, but I am hoping that someone can straiten me out here, or show me a keyword that would make since on further searching this one out.

Thanks in advance for any advice/info.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

Perhaps you are thinking of DMax?

rs.Fields("OtherID") = DMax("ImportID","tblHist")

 

[SoCalAccessPro]

You've gotta have group by for the max aggregate function to work:

strSQL =
SELECT Max([ImportID])
AS ImpID
FROM tblHist
GROUP BY [ImportID]




Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Remou]

SoCalAccessPro

No matter how Max is done in the above code, it cannot work, as this line:


rs.Fields("OtherID") = DoCmd.RunSQL (strSQL)

Cannot work. This is a textbox case for a domain aggregate function.

 

[SoCalAccessPro]

My example simply returns the max value of the importID in tblHist.

The OP can use the return value as he sees fit.

But, yes, a domain aggregate function is another way of doing it.

If the OP wants to update every record in TableDetail with the Max contained in tblHist, then the following will work.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String
Dim intMax as integer
Set db = CurrentDb
Set rs = db.OpenRecordset("TableDetail")

intMax = DMax("ImportID","tblHist") ‘Note use double or other field type if intMax will be larger than about 32,000
  Rst.movefirst
  Rst.movelast
  Do while not rs.eof
    rs.Fields("OtherID") = intMax
    .rs.movenext
 Loop
 rs.Close
 db.Close
 Set rs = Nothing
 Set db = nothing

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[SoCalAccessPro]

Sorry,

Should be

Rst.movelast
Rst.movefirst



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[Golom]

You've gotta have group by for the max aggregate function to work:

I don't think that's true.

Select MAX(SomeField) As MX From SomeTable

works just fine. It reports the maximum value for SomeField in the entire table.

The query you suggest would return the distinct values of "importID" because each group has only one value for InvoiceID.

 

[PWise]

i See no need for the recordset

Dim db as DAO.Database
Set db = CurrentDb

db.execute "update TableDetail set order =(SELECT Max([ImportID]) AS ImpID FROM tblHist)"

set db=nothing

 

[SoCalAccessPro]

Golom. I always thought MAX had to be used as an aggregate function. Thanks for pointing that out.



Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[SoCalAccessPro]

pwise, very nice !

(BTW, pwise as in PennyWise, the clown ?)

However, when I test that on a test DB, Access complains that I must use an updatable query.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[PHV]

db.Execute "update TableDetail set [order]=DMax('ImportID','tblHist') WHERE [order] Is Null"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks for all the replies! Wow, I just checked this for the first time since posted.

I'll look it all over, and post my results.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

Or just skip to PHV if you are in a hurry

 

[kjv1611]

[ROFL2] @ Remou!

Yeah, I suppose that usually does the trick.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

SoCalAccessPro,

On the Max() and Group By comments, hopefully this'll help in clearing that up. At first, I thought - "oops, I did it again!"

But then I remembered why I did not use a GROUP By clause.

The Group By clause would be required for other items in the list, IF there were other items.

For example:

SELECT  Max(a.Weight) ,Color ,Type
FROM    tblApples a
GROUP BY Color ,Type

'Course, I didn't test this, but it's just based on the multiple times I remember leaving off the Group By, and then having to go back and correct. [WINK]



--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Thanks to Remou for the original mention of using DMax - duh, I should've remembered that one!

And Thanks to PHV for the SQL correction. I was thinking it would be something like the "execute" statement, but I hadn't tried it yet.

And actually, that was only one small portion of what I was doing. I DO want to update the entire table with the same "Max" value, so I can do that flawlessly now. And besides that, I'm pretty confident that I can now write the necessary SQL and run it instead of looping through a recordset - much more efficient!
[SMILE][SMILE][SMILE][SMILE][SMILE][SMILE][SMILE]

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Thanks for the other attempts as well. It seems that everyone was pretty much on the right track. PHV just nailed it down to a short, concise, working statement!
[WINK]

Now I'll have to go back sometime and edit the SQL statement to take care of the whole recordset thing. I'll just let it be for the time being, however (everything but the ID mentioned).

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Another follow-up. I noticed, but didnt' try to fix until just now. The "IS NULL" statement would work great for a text field, but I'm actually using a numeric field for the ImportID field. So, I changed it to "=0", and NOW the PHV statement works perfectly.

I'm not saying anything was wrong with the statement. Based on the info I provided, that was probably the best guess.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Golom]

Numeric fields can be NULL ... just as can Text fields.

It depends on the "Required" setting for the field in the table definition. If a field is required then a value must be supplied ... even if only by default. If it is not required then NULL is permitted for that field.

 

[kjv1611]

Hmm, it looks, then, like the issue is that the "default value" portion is set to "0" by default. So if I remove that, then I could go back to using IS NULL?

--

"If to err is human, then I must be some kind of human!" -Me