Find Next Available Number and Set Value

[GL]

Hi,
Hope someone can solve this mystery for me! I have a numeric ID field
(NumID - not an autonumber because they need to type in). However, I need it
to go out and get the next sequential number and then set NumID to that
number when I click add. I have a field on my form (NewNumID) that finds the
MaxNumber+1 - I use a query to find the max value and add one - couldn't
think of any other way to do this, wondering if this is the problem. First I
tried a simple macro that will requery NewNumID (next available number) and
then set the value of NewNumId to NumID (field on form) - I get an error
message - "the search key was not found in any record" - doesn't make any
sense to me - I reference both fields from the form via the build command so
I know they are right.

Ideally I'd like to have it do it all when I click the add button, Requery
for the next NumID field (that doesn't work with what I have below) and then
set numid to newnumid (don't know how to do this with code). This is what I
typed but it doesn't work:
DoCmd.Save
DoCmd.Requery (NewNumID)
DoCmd.GoToRecord , , acNewRec

Hope this makes some sense!! It seems like such an easy thing to want to do
so I'm a bit frustrated that I can't figure it out! Thanks for any insight
you can provide!!
GL

 

[bhicks11 via AccessMonster.com]

Can you post your query SQL? Have you tried running the query outside the
form and verifying that it returns a value?

Bonnie
http://www.dataplus-svc.com

 

[GL]

Thanks for looking into this - the query does work, it returns the correct
number, but I've copied it below for your reference (I used NumId as and
example, but its really an IR#). The problem could be that I made this field
a lis box and this query is the row source - it was the only way I could get
it to show the value...
SELECT [MaxOfIr#]+1, Max(tblProject.[IR#]) AS [MaxOfIR#] FROM tblProject;

Is there some way I can do this via code?? Thanks SO much for your time.

 

[Arvin Meyer [MVP]]

Use the DefaultValue property of a textbox which is bound to your ID field,
set the value = to:

=DMax("[ID]","tblMyTable")+1

 

[bhicks11 via AccessMonster.com]

Listen to Arvin GL.

Thanks for looking into this - the query does work, it returns the correct
number, but I've copied it below for your reference (I used NumId as and
example, but its really an IR#). The problem could be that I made this field
a lis box and this query is the row source - it was the only way I could get
it to show the value...
SELECT [MaxOfIr#]+1, Max(tblProject.[IR#]) AS [MaxOfIR#] FROM tblProject;

Is there some way I can do this via code?? Thanks SO much for your time.

Can you post your query SQL? Have you tried running the query outside the
form and verifying that it returns a value?

[quoted text clipped - 27 lines]

 

[GL]

My form is actually based off a query that I build in code based on certain
criteria. Not sure if thats the problem but when I use the dmax function I
just get a #Name? returned - I tried the query name but got the same response
- it does put brackets around my table name . Sorry to be such a pest!!

Use the DefaultValue property of a textbox which is bound to your ID field,
set the value = to:

=DMax("[ID]","tblMyTable")+1
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,
Hope someone can solve this mystery for me! I have a numeric ID field
(NumID - not an autonumber because they need to type in). However, I need
it
to go out and get the next sequential number and then set NumID to that
number when I click add. I have a field on my form (NewNumID) that finds
the
MaxNumber+1 - I use a query to find the max value and add one - couldn't
think of any other way to do this, wondering if this is the problem.
First I
tried a simple macro that will requery NewNumID (next available number)
and
then set the value of NewNumId to NumID (field on form) - I get an error
message - "the search key was not found in any record" - doesn't make any
sense to me - I reference both fields from the form via the build command
so
I know they are right.

Ideally I'd like to have it do it all when I click the add button, Requery
for the next NumID field (that doesn't work with what I have below) and
then
set numid to newnumid (don't know how to do this with code). This is what
I
typed but it doesn't work:
DoCmd.Save
DoCmd.Requery (NewNumID)
DoCmd.GoToRecord , , acNewRec

Hope this makes some sense!! It seems like such an easy thing to want to
do
so I'm a bit frustrated that I can't figure it out! Thanks for any
insight
you can provide!!
GL

 

[Arvin Meyer [MVP]]

More than likely, you have a references problem. Open any code window and go
to Tools >>> References, then locate any marked as missing. Either find the
reference (if you need it) or uncheck it (if you don't).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

My form is actually based off a query that I build in code based on
certain
criteria. Not sure if thats the problem but when I use the dmax function
I
just get a #Name? returned - I tried the query name but got the same
response
- it does put brackets around my table name . Sorry to be such a pest!!

Use the DefaultValue property of a textbox which is bound to your ID
field,
set the value = to:

=DMax("[ID]","tblMyTable")+1
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,
Hope someone can solve this mystery for me! I have a numeric ID field
(NumID - not an autonumber because they need to type in). However, I
need
it
to go out and get the next sequential number and then set NumID to that
number when I click add. I have a field on my form (NewNumID) that
finds
the
MaxNumber+1 - I use a query to find the max value and add one -
couldn't
think of any other way to do this, wondering if this is the problem.
First I
tried a simple macro that will requery NewNumID (next available number)
and
then set the value of NewNumId to NumID (field on form) - I get an
error
message - "the search key was not found in any record" - doesn't make
any
sense to me - I reference both fields from the form via the build
command
so
I know they are right.

Ideally I'd like to have it do it all when I click the add button,
Requery
for the next NumID field (that doesn't work with what I have below) and
then
set numid to newnumid (don't know how to do this with code). This is
what
I
typed but it doesn't work:
DoCmd.Save
DoCmd.Requery (NewNumID)
DoCmd.GoToRecord , , acNewRec

Hope this makes some sense!! It seems like such an easy thing to want
to
do
so I'm a bit frustrated that I can't figure it out! Thanks for any
insight
you can provide!!
GL

 

[GL]

There aren't any reference problems, but I'm not understanding why that would
be a problem as I'm not trying to do this in code. Is there a code way of
doing it? I really appreciate all your time on this!! Thanks. GL

More than likely, you have a references problem. Open any code window and go
to Tools >>> References, then locate any marked as missing. Either find the
reference (if you need it) or uncheck it (if you don't).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

My form is actually based off a query that I build in code based on
certain
criteria. Not sure if thats the problem but when I use the dmax function
I
just get a #Name? returned - I tried the query name but got the same
response
- it does put brackets around my table name . Sorry to be such a pest!!

Use the DefaultValue property of a textbox which is bound to your ID
field,
set the value = to:

=DMax("[ID]","tblMyTable")+1
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,
Hope someone can solve this mystery for me! I have a numeric ID field
(NumID - not an autonumber because they need to type in). However, I
need
it
to go out and get the next sequential number and then set NumID to that
number when I click add. I have a field on my form (NewNumID) that
finds
the
MaxNumber+1 - I use a query to find the max value and add one -
couldn't
think of any other way to do this, wondering if this is the problem.
First I
tried a simple macro that will requery NewNumID (next available number)
and
then set the value of NewNumId to NumID (field on form) - I get an
error
message - "the search key was not found in any record" - doesn't make
any
sense to me - I reference both fields from the form via the build
command
so
I know they are right.

Ideally I'd like to have it do it all when I click the add button,
Requery
for the next NumID field (that doesn't work with what I have below) and
then
set numid to newnumid (don't know how to do this with code). This is
what
I
typed but it doesn't work:
DoCmd.Save
DoCmd.Requery (NewNumID)
DoCmd.GoToRecord , , acNewRec

Hope this makes some sense!! It seems like such an easy thing to want
to
do
so I'm a bit frustrated that I can't figure it out! Thanks for any
insight
you can provide!!
GL

 

[Arvin Meyer [MVP]]

Reference problems can break all code and expressions in VBA. Most notably,
use of the Date() function or some of the string functions, which seem to
break with any Reference problem. Have a look at:

http://www.accessmvp.com/DJSteele/AccessReferenceErrors.html
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

There aren't any reference problems, but I'm not understanding why that
would
be a problem as I'm not trying to do this in code. Is there a code way of
doing it? I really appreciate all your time on this!! Thanks. GL

More than likely, you have a references problem. Open any code window and
go
to Tools >>> References, then locate any marked as missing. Either find
the
reference (if you need it) or uncheck it (if you don't).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

My form is actually based off a query that I build in code based on
certain
criteria. Not sure if thats the problem but when I use the dmax
function
I
just get a #Name? returned - I tried the query name but got the same
response
- it does put brackets around my table name . Sorry to be such a
pest!!

:

Use the DefaultValue property of a textbox which is bound to your ID
field,
set the value = to:

=DMax("[ID]","tblMyTable")+1
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,
Hope someone can solve this mystery for me! I have a numeric ID
field
(NumID - not an autonumber because they need to type in). However,
I
need
it
to go out and get the next sequential number and then set NumID to
that
number when I click add. I have a field on my form (NewNumID) that
finds
the
MaxNumber+1 - I use a query to find the max value and add one -
couldn't
think of any other way to do this, wondering if this is the problem.
First I
tried a simple macro that will requery NewNumID (next available
number)
and
then set the value of NewNumId to NumID (field on form) - I get an
error
message - "the search key was not found in any record" - doesn't
make
any
sense to me - I reference both fields from the form via the build
command
so
I know they are right.

Ideally I'd like to have it do it all when I click the add button,
Requery
for the next NumID field (that doesn't work with what I have below)
and
then
set numid to newnumid (don't know how to do this with code). This
is
what
I
typed but it doesn't work:
DoCmd.Save
DoCmd.Requery (NewNumID)
DoCmd.GoToRecord , , acNewRec

Hope this makes some sense!! It seems like such an easy thing to
want
to
do
so I'm a bit frustrated that I can't figure it out! Thanks for any
insight
you can provide!!
GL