Problem with SQL and Recordset

G

Guest

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
 
G

Guest

May have misstated this, I think the correct termonology for what I am doing
is " Running an action query from VBA"
 
D

Douglas J. Steele

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

(no e-mails, please!)


Design by Sue said:
May have misstated this, I think the correct termonology for what I am
doing
is " Running an action query from VBA"

Design by Sue said:
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
Click to expand...
Click to expand...
 
J

John Spencer

An action query is one that adds, deletes or modifies records.

A SELECT query does none of those. It selects records for display.

Can you tell us what you are attempting to do?
You could create a query def and store it.
You could open a recordset and manipulate the data in it.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

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 said:
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

(no e-mails, please!)


Design by Sue said:
May have misstated this, I think the correct termonology for what I am
doing
is " Running an action query from VBA"

Design by Sue said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 datasheet 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
NextrSuffix field.


John Spencer said:
An action query is one that adds, deletes or modifies records.

A SELECT query does none of those. It selects records for display.

Can you tell us what you are attempting to do?
You could create a query def and store it.
You could open a recordset and manipulate the data in it.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Design by Sue said:
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
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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

(no e-mails, please!)


Design by Sue said:
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 said:
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

(no e-mails, please!)


Design by Sue said:
May have misstated this, I think the correct termonology for what I am
doing
is " Running an action query from VBA"

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 datasheet 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 said:
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

(no e-mails, please!)


Design by Sue said:
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 said:
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

(no e-mails, please!)


May have misstated this, I think the correct termonology for what I am
doing
is " Running an action query from VBA"

:

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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

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

(no e-mails, please!)


Design by Sue said:
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 datasheet 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 said:
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

(no e-mails, please!)


Design by Sue said:
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

:

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

(no e-mails, please!)


message
May have misstated this, I think the correct termonology for what I
am
doing
is " Running an action query from VBA"

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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

(no e-mails, please!)


Design by Sue said:
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 datasheet 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 said:
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

(no e-mails, please!)


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

:

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

(no e-mails, please!)


message
May have misstated this, I think the correct termonology for what I
am
doing
is " Running an action query from VBA"

:

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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

What's the current code in the module where you want to assign the value to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
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 said:
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

(no e-mails, please!)


Design by Sue said:
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 datasheet 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



:

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

(no e-mails, please!)


message
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

:

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

(no e-mails, please!)


message
May have misstated this, I think the correct termonology for what
I
am
doing
is " Running an action query from VBA"

:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

The subform control is called LocationSub2

The code I have, which is what you posted previously with the addition of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

Douglas J. Steele said:
What's the current code in the module where you want to assign the value to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
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 said:
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

(no e-mails, please!)


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 datasheet 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



:

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

(no e-mails, please!)


message
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

:

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

(no e-mails, please!)


message
May have misstated this, I think the correct termonology for what
I
am
doing
is " Running an action query from VBA"

:

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
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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] & "'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Design by Sue said:
The subform control is called LocationSub2

The code I have, which is what you posted previously with the addition of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

Douglas J. Steele said:
What's the current code in the module where you want to assign the value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)
Click to expand...
Click to expand...
 
G

Guest

I have to leave now, but thank you for such a complete reply - will try
tomorrow first thing

Have a good evening
Sue


Douglas J. Steele said:
If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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] & "'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Design by Sue said:
The subform control is called LocationSub2

The code I have, which is what you posted previously with the addition of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

Douglas J. Steele said:
What's the current code in the module where you want to assign the value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Doug, actually both worked (though I had to change
Me!LocationSub2.Form!Suffix to [Forms]![InPutFrm]![LocationSub2]![Suffix])
BUT - and there always seems to be a but - using either method, on either the
Got Focus event of the field or the On Current event of the subform, the
suffix number increases everytime you select the record. I need the number
to stay as assigned when the record is first created. Is there an event that
writes to the table only when the record is new, or can the coding be changed
to do this?

You have been a great help. (I hope to take a class in Access in April,
hopefully I will get a better understanding of this)




Douglas J. Steele said:
If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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] & "'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Design by Sue said:
The subform control is called LocationSub2

The code I have, which is what you posted previously with the addition of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

Douglas J. Steele said:
What's the current code in the module where you want to assign the value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Check the form's NewRecord property.

Private Sub Suffix_GotFocus()

If [Forms]![InPutFrm].NewRecord Then
[Forms]![InPutFrm]!LocationSub2.Form!Suffix = _
Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"), 0) + 1
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
Thanks Doug, actually both worked (though I had to change
Me!LocationSub2.Form!Suffix to [Forms]![InPutFrm]![LocationSub2]![Suffix])
BUT - and there always seems to be a but - using either method, on either
the
Got Focus event of the field or the On Current event of the subform, the
suffix number increases everytime you select the record. I need the
number
to stay as assigned when the record is first created. Is there an event
that
writes to the table only when the record is new, or can the coding be
changed
to do this?

You have been a great help. (I hope to take a class in Access in April,
hopefully I will get a better understanding of this)




Douglas J. Steele said:
If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a
single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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] &
"'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Design by Sue said:
The subform control is called LocationSub2

The code I have, which is what you posted previously with the addition
of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

:

What's the current code in the module where you want to assign the
value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That stopped the old records from updating, but also the new record - it
stays at 0



Douglas J. Steele said:
Check the form's NewRecord property.

Private Sub Suffix_GotFocus()

If [Forms]![InPutFrm].NewRecord Then
[Forms]![InPutFrm]!LocationSub2.Form!Suffix = _
Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"), 0) + 1
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
Thanks Doug, actually both worked (though I had to change
Me!LocationSub2.Form!Suffix to [Forms]![InPutFrm]![LocationSub2]![Suffix])
BUT - and there always seems to be a but - using either method, on either
the
Got Focus event of the field or the On Current event of the subform, the
suffix number increases everytime you select the record. I need the
number
to stay as assigned when the record is first created. Is there an event
that
writes to the table only when the record is new, or can the coding be
changed
to do this?

You have been a great help. (I hope to take a class in Access in April,
hopefully I will get a better understanding of this)




Douglas J. Steele said:
If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a
single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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] &
"'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


The subform control is called LocationSub2

The code I have, which is what you posted previously with the addition
of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

:

What's the current code in the module where you want to assign the
value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

That makes no sense at all, since it's running exactly what it was running
before!

Does PartNumber have a value when the code's running? (I'm guessing that
maybe it's looking up for PartNumber = '", and so finding nothing.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
That stopped the old records from updating, but also the new record - it
stays at 0



Douglas J. Steele said:
Check the form's NewRecord property.

Private Sub Suffix_GotFocus()

If [Forms]![InPutFrm].NewRecord Then
[Forms]![InPutFrm]!LocationSub2.Form!Suffix = _
Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"), 0) + 1
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
Thanks Doug, actually both worked (though I had to change
Me!LocationSub2.Form!Suffix to
[Forms]![InPutFrm]![LocationSub2]![Suffix])
BUT - and there always seems to be a but - using either method, on
either
the
Got Focus event of the field or the On Current event of the subform,
the
suffix number increases everytime you select the record. I need the
number
to stay as assigned when the record is first created. Is there an
event
that
writes to the table only when the record is new, or can the coding be
changed
to do this?

You have been a great help. (I hope to take a class in Access in
April,
hopefully I will get a better understanding of this)




:

If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a
single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If
it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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]
&
"'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
The subform control is called LocationSub2

The code I have, which is what you posted previously with the
addition
of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

:

What's the current code in the module where you want to assign the
value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub
or
something else?)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

That's my life!

Yes there is a value, nothing changes - If I use the code with NextRecord
the suffix is 0 and acts as described. Then if I remove the NextRecord and
the If references, the code adds 1 to the suffix but does it every time you
access the record.

I have tried this on the OnCurrent for the subform and on the GetFocus of
the Suffix field and get the same results. Should I be putting is somewhere
else?



Douglas J. Steele said:
That makes no sense at all, since it's running exactly what it was running
before!

Does PartNumber have a value when the code's running? (I'm guessing that
maybe it's looking up for PartNumber = '", and so finding nothing.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
That stopped the old records from updating, but also the new record - it
stays at 0



Douglas J. Steele said:
Check the form's NewRecord property.

Private Sub Suffix_GotFocus()

If [Forms]![InPutFrm].NewRecord Then
[Forms]![InPutFrm]!LocationSub2.Form!Suffix = _
Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"), 0) + 1
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug, actually both worked (though I had to change
Me!LocationSub2.Form!Suffix to
[Forms]![InPutFrm]![LocationSub2]![Suffix])
BUT - and there always seems to be a but - using either method, on
either
the
Got Focus event of the field or the On Current event of the subform,
the
suffix number increases everytime you select the record. I need the
number
to stay as assigned when the record is first created. Is there an
event
that
writes to the table only when the record is new, or can the coding be
changed
to do this?

You have been a great help. (I hope to take a class in Access in
April,
hopefully I will get a better understanding of this)




:

If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a
single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If
it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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]
&
"'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
The subform control is called LocationSub2

The code I have, which is what you posted previously with the
addition
of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

:

What's the current code in the module where you want to assign the
value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub
or
something else?)
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Oops - I mean NewRecord, not NextRecord (I cut and pasted your code)



Design by Sue said:
That's my life!

Yes there is a value, nothing changes - If I use the code with NextRecord
the suffix is 0 and acts as described. Then if I remove the NextRecord and
the If references, the code adds 1 to the suffix but does it every time you
access the record.

I have tried this on the OnCurrent for the subform and on the GetFocus of
the Suffix field and get the same results. Should I be putting is somewhere
else?



Douglas J. Steele said:
That makes no sense at all, since it's running exactly what it was running
before!

Does PartNumber have a value when the code's running? (I'm guessing that
maybe it's looking up for PartNumber = '", and so finding nothing.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Design by Sue said:
That stopped the old records from updating, but also the new record - it
stays at 0



:

Check the form's NewRecord property.

Private Sub Suffix_GotFocus()

If [Forms]![InPutFrm].NewRecord Then
[Forms]![InPutFrm]!LocationSub2.Form!Suffix = _
Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"), 0) + 1
End If

End Sub


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Doug, actually both worked (though I had to change
Me!LocationSub2.Form!Suffix to
[Forms]![InPutFrm]![LocationSub2]![Suffix])
BUT - and there always seems to be a but - using either method, on
either
the
Got Focus event of the field or the On Current event of the subform,
the
suffix number increases everytime you select the record. I need the
number
to stay as assigned when the record is first created. Is there an
event
that
writes to the table only when the record is new, or can the coding be
changed
to do this?

You have been a great help. (I hope to take a class in Access in
April,
hopefully I will get a better understanding of this)




:

If you want to go the recordset route, try:

Private Sub Suffix_GotFocus()
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]

' There's no point having an ORDER BY when there's only going to be a
single
record!

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

That having been said, the following should work just as well:

Private Sub Suffix_GotFocus()

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

End Sub

You never did let me know wheter PartNumber is numeric or text. If
it's
text, then the recordset solution needs to be changed to

Private Sub Suffix_GotFocus()
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]
&
"'"

Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If

myRecordset.Close
Set myRecordset = Nothing

End Sub

and the non-recordset approach to

Private Sub Suffix_GotFocus()

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

End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
The subform control is called LocationSub2

The code I have, which is what you posted previously with the
addition
of
the closure of the record set you posted.

Sorry that I just can't see this.

Thanks for your help


Private Sub Suffix_GotFocus()
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)


myRecordset.Close
Set myRecordset = Nothing



End Sub

:

What's the current code in the module where you want to assign the
value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub
or
something else?)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

query programming noobie question 1
Windows XP weird problem with dates in query! 0
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value als 3
Problem with SQL CE, select with sum and SqlCeResultSet 1
Problem with Search Button and SQL Statement 5
Download the JAVA , .NET and SQL Server interview with answers 2
Attn Sprinks- Not duplicate insert records 1
Attn Sprinks - clarification on VB code 6

Top