Re: Problem with SQL and Recordset

Thanks so much for your reply, but I guess I am way in over my head, as I
don't know what to do with the information you provided. I do know that I
tried the DLookup and though I can't remember the reason, it would not work
in this instance.

Thanks

"Douglas J. Steele" wrote:

To get the next suffix field from the query, you'd use

myRecordset!NextSuffix

To refer to the field on your subform from the parent form, you'd use

Me!LocationSub.Form!Suffix

This assumes that the name of the subform control on the parent form is the
same as the name of the form being used as the subform. Depending on how you
added the subform to the parent form, this might not be the case. If they're
different, you must use the name of the subform control.

Having said that, though, you don't really need to open a recordset: you
should be able to figure out the value of NextSuffix using DLookup:

Me!LocationSub.Form!Suffix = Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"), 0) + 1

If you do go the recordset route, make sure to "clean up" after yourself.
Once you're done with the recordset, you should put:

myRecordset.Close
Set myRecordset = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Design by Sue" <DesignbySue@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:121097B6-EE5C-441C-AFB6-ADAE46DD1EEB@xxxxxxxxxxxxxxxx
Doug - thank you -the error messages have gone away, but it in not
entering
the number in the Suffix field (really sorry to be so dumb) How do I get
the
NextSuffix field from the "query" to be entered into the Suffix field on
the
LocationSub? 9 I have restated by post below that expains better what I an
trying to do.


Quote from othe post: Please understand that you are talking to an
artist,
not a programmer, but
I'm doing my best here. I have a main form (InputFrm) which is tabbed. On
the Tab, InPutTab, I have a a field called part number. There subForm,
LocationSub ib this tab. LocationSub is in data*** view and has 3
fields,
Suffix, Location and Number. I wrote a query that finds the current
highest
(Max) number in the suffix field, based on part number, and then adds one
to
it. I need to enter this number in the suffix field when a new record is
created on the LocationSub.

The SQL code is from the query that does result in the number I want in
the
NextSuffix field. End Quote



"Douglas J. Steele" wrote:

Dim myRecordset As DAO.Recordset
Dim mySQL As String

mySQL = "SELECT Max(WhereIsItTbl.Suffix) AS MaxOfSuffix, " & _
"WhereIsItTbl.PartNumber, " & _
"Max([Suffix]+1) AS NextSuffix FROM WhereIsItTbl GROUP BY " & _
"WhereIsItTbl.PartNumber " & _
"HAVING WhereIsItTbl.PartNumber = " &
[Forms]![InPutFrm]![PartNumber]
& _
" ORDER BY Max(WhereIsItTbl.Suffix) DESC"
Set myRecordset = CurrentDb.OpenRecordset(mySQL)

Note that I've put the reference to the control on your form outside of
the
string: this is necessary. In the example, I assumed PartNumber was a
numeric field. If it's text, change that one line to

"HAVING WhereIsItTbl.PartNumber = '" &
[Forms]![InPutFrm]![PartNumber]
& "'" & _

Exagerated for clarity, that's

"HAVING WhereIsItTbl.PartNumber = ' " &
[Forms]![InPutFrm]![PartNumber]
& " ' " & _

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Design by Sue" <DesignbySue@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:453FA9F8-4EA2-4B10-8DBB-40FB9CFE4E8B@xxxxxxxxxxxxxxxx
I thought it was an action query because I have an added field to add
one
to
the Max number in the Suffix field. (By the way, I found the word Do
in
the
code which was resulting in the error - but obviously from your
comment -
it
still gets caught on DoCmd.RunSQL mySQL.) I have tried
myRecordSet.Open
mySQL and that doesn't work either - Can you help?

Sue

"Douglas J. Steele" wrote:

The problem is that you aren't running an Action query. Action queries
are
INSERT INTO, UPDATE or DELETE. You cannot execute a SELECT query using
RunSQL.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Design by Sue" <DesignbySue@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:25728366-F8D4-4038-AC5A-8DC9F249DFA8@xxxxxxxxxxxxxxxx
May have misstated this, I think the correct termonology for what I
am
doing
is " Running an action query from VBA"

"Design by Sue" wrote:

I am trying to convert an Action Query to SQL and using recordset
use
it
in a
Got Focus event. Below is the code. The debugger gives me a
message
that
there is a Sytax error (missing operator) in query expression. (I
am
using a
Dummies book on this and really don't quite understand, but I'm
sure I
followed the directions) Can someone tell me what is wrong with
this?

Dim mySQL As String
mySQL = "SELECT Max(WhereIsItTbl.Suffix) AS MaxOfSuffix,
WhereIsItTbl.PartNumber, " & _
"Max([Suffix]+1) AS NextSuffix FROM WhereIsItTbl GROUP BY
WhereIsItTbl.PartNumber " & _
"HAVING (((WhereIsItTbl.PartNumber)Do =
[Forms]![InPutFrm]![PartNumber]))
"
& _
"ORDER BY Max(WhereIsItTbl.Suffix) DESC"
DoCmd.RunSQL mySQL










.