FindRecord (Macro) and then add one for a new record? (resend)

[Guest]

On my form I have buttons so someone can click on a button and it will find
the last number, in a group of numbers, assigned. What I would like to do is
build on that if possible so when the person clicks on the button, it will
take that last number assigned and add 1 to it so it makes a new record, is
this possible?Thanks for your help!Tammy

 

[Steve Schapel]

Tammy,

If I understand you correctly, you can achieve this by setting the
Default Value of this number control on the form to the equivalent of...
DMax("[YourNumberField]","YourTable")+1

 

[Guest]

I know this is a stupid question but I cant do this if the field is a text
field, or is there a way?
my number is a number but it has a dash 11111-111 so I cant make it a number
field.
Thanks for you help,
Tammy

Tammy,

If I understand you correctly, you can achieve this by setting the
Default Value of this number control on the form to the equivalent of...
DMax("[YourNumberField]","YourTable")+1

--
Steve Schapel, Microsoft Access MVP

On my form I have buttons so someone can click on a button and it will find
the last number, in a group of numbers, assigned. What I would like to do is
build on that if possible so when the person clicks on the button, it will
take that last number assigned and add 1 to it so it makes a new record, is
this possible?Thanks for your help!Tammy

 

[Steve Schapel]

Tammy

A few questions for you
What is the relationship between the numbers on either side of the
hyphen? Is it always 5 digits to the left and 3 digits to the right?
Do the numbers on both sides change? When your original message talked
about adding 1 to the number, is this to the number on the left of the
hyphen or the number to the right, or both? Is the design of your
database set in concrete, or could you consider a small change to make
it easier?

 

[Guest]

Hi Steve
The number to the left is always 5 digits and basically this is a family
number for different products. We have about 10 different products, the 3
numbers on the right are want need to be incremented, and I am not sure what
we do when we get to 999. I guess it will have to go to 4 numbers.
I tried splitting the number in to two fields and then joining them on the
form then adding one, but it doesn’t write the new number back to the table.
=[BASENUMBER] & "" & [EXTENTION]+1

Yes I can make changes to make it easier

Any help is greatly appreciated!! Thank you

 

[Guest]

I wanted to add that each family of numbers could have the same extention
number. the first extention number for each family is 001

Thanks again!

 

[Steve Schapel]

Tammy,

Ok, here's how I would do this. No matter what, I would definitely have
this stuff in 2 separate fields. It is 2 separate pieces of
information. I would have a simple ProductFamilies table with a field
with the 10 families listed. In my form, I would have my BaseNumber
field bound to a combobox, which uses the ProductFamilies table as its
Row Source. Then, I would put the Extension textbox next to it on the
form, and between the BaseNumber combobox and the Extension textbox I
would place a label with a hyphen as its only Caption. And then on the
AfterUpdate event of the BaseNumber combobox I would put code like this...
Dim NextNumber As Integer
NextNumber = DMax("[Extension]","YourTable","[BaseNumber]=" &
Me.BaseNumber) + 1
Me.Extension = NextNumber

Then, whenever you need, for appearance sake within your application,
such as in your reports etc, the combined number as if it is a single
entity, just generate it "on the fly", either as a calculated field in
the query that the report is based on, or in the Control Source of an
unbound textbox on the report, using syntax such as...
=[BaseNumber] & "-" & [Extension]

 

[Guest]

Hi Steve,
Thank you so much!! I will try this and let you know.

Tammy,

Ok, here's how I would do this. No matter what, I would definitely have
this stuff in 2 separate fields. It is 2 separate pieces of
information. I would have a simple ProductFamilies table with a field
with the 10 families listed. In my form, I would have my BaseNumber
field bound to a combobox, which uses the ProductFamilies table as its
Row Source. Then, I would put the Extension textbox next to it on the
form, and between the BaseNumber combobox and the Extension textbox I
would place a label with a hyphen as its only Caption. And then on the
AfterUpdate event of the BaseNumber combobox I would put code like this...
Dim NextNumber As Integer
NextNumber = DMax("[Extension]","YourTable","[BaseNumber]=" &
Me.BaseNumber) + 1
Me.Extension = NextNumber

Then, whenever you need, for appearance sake within your application,
such as in your reports etc, the combined number as if it is a single
entity, just generate it "on the fly", either as a calculated field in
the query that the report is based on, or in the Control Source of an
unbound textbox on the report, using syntax such as...
=[BaseNumber] & "-" & [Extension]

--
Steve Schapel, Microsoft Access MVP

I wanted to add that each family of numbers could have the same extention
number. the first extention number for each family is 001

Thanks again!
"Steve Schapel" wrote:

 

[Guest]

Hi Steve,
I am getting a compile error, expected: list separator ), this is what I put
in

Dim NextNumber As Integer
NextNumber = DMax("[Extension]","Products","[BaseNumber]=" &
Me.BaseNumber) + 1
Me.Extension = NextNumber

Hi Steve,
Thank you so much!! I will try this and let you know.

Tammy,

Ok, here's how I would do this. No matter what, I would definitely have
this stuff in 2 separate fields. It is 2 separate pieces of
information. I would have a simple ProductFamilies table with a field
with the 10 families listed. In my form, I would have my BaseNumber
field bound to a combobox, which uses the ProductFamilies table as its
Row Source. Then, I would put the Extension textbox next to it on the
form, and between the BaseNumber combobox and the Extension textbox I
would place a label with a hyphen as its only Caption. And then on the
AfterUpdate event of the BaseNumber combobox I would put code like this...
Dim NextNumber As Integer
NextNumber = DMax("[Extension]","YourTable","[BaseNumber]=" &
Me.BaseNumber) + 1
Me.Extension = NextNumber

Then, whenever you need, for appearance sake within your application,
such as in your reports etc, the combined number as if it is a single
entity, just generate it "on the fly", either as a calculated field in
the query that the report is based on, or in the Control Source of an
unbound textbox on the report, using syntax such as...
=[BaseNumber] & "-" & [Extension]

--
Steve Schapel, Microsoft Access MVP

I wanted to add that each family of numbers could have the same extention
number. the first extention number for each family is 001

Thanks again!
"Steve Schapel" wrote:

 

[Steve Schapel]

Tammy

I can't immediately see what's wrong. Did you copy/paste directly from
the code into your newsgroup post, or did you re-type? If re-type,
kindly check that it is precisely the same in your code.

I have assumed the BaseNumber field is a number data type. If it is
text, you will need...
NextNumber = DMax("[Extension]","Products","[BaseNumber]='" &
Me.BaseNumber & "'") + 1

In my newsreader, this line of the code wordwraps to a new line after
the & but in your actual code it should all be on one line.

 

[Guest]

Hi Steve,
sorry I havent gotten back, but when I couldn't get this to work I gave up
for awhile lol.
Will this accually write the new number (BaseNumber)-(Extention) to the
table? That is what I am going to need.

You told me to put this code in the afterupdate, but do I put it in the
after update for the basenumber or the extention.
When I click on my macro (findrecord) it is still pulling up the last number
assigned, not adding 1 to it.

Thanks again for your help!!

Tammy

I can't immediately see what's wrong. Did you copy/paste directly from
the code into your newsgroup post, or did you re-type? If re-type,
kindly check that it is precisely the same in your code.

I have assumed the BaseNumber field is a number data type. If it is
text, you will need...
NextNumber = DMax("[Extension]","Products","[BaseNumber]='" &
Me.BaseNumber & "'") + 1

In my newsreader, this line of the code wordwraps to a new line after
the & but in your actual code it should all be on one line.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
I am getting a compile error, expected: list separator ), this is what I put
in

Dim NextNumber As Integer
NextNumber = DMax("[Extension]","Products","[BaseNumber]=" &
Me.BaseNumber) + 1
Me.Extension = NextNumber

 

[Steve Schapel]

Hi Tammy

sorry I havent gotten back, but when I couldn't get this to work I gave up
for awhile lol.

I know the feeling.

Will this accually write the new number (BaseNumber)-(Extention) to the
table? That is what I am going to need.

No, this is not what you need. Definitely not. You need the BaseNumber
in one field (number data type) and the Extension in another separate
field (number data type), and you need to remove the existing text
BaseNumber-Extension field from the table altogether. You need controls
on the form bound to the BaseNumber and Extension fields. The whole
point here is that when you enter the BaseNumber for a new record, the
next sequential Extension number for that BaseNumber family will
automatically be entered... isn't that what you want.

You told me to put this code in the afterupdate, but do I put it in the
after update for the basenumber or the extention.

As mentioned previously, this goes in the After Update event of the
BaseNumber control (combobox?).

When I click on my macro (findrecord) it is still pulling up the last number
assigned, not adding 1 to it.

What macro? What do you need a macro for?

 

[Guest]

The whole
point here is that when you enter the BaseNumber for a new record, the
next sequential Extension number for that BaseNumber family will
automatically be entered... isn't that what you want.<<

Maybe entered is the wrong word? more like created, but yes this is what I
want, I need to use the number that is created for reports, dosnt it have to
write to the table?
I use this number in another form that has a subform, where I list the
products that are affected by a certain change. so I can't just change the
number (record), because I have to keep the history.
BaseNumber control (combobox?).<<
I might not have read that part sorry, but thanks for the clarity
I use the macro to find the last number assigned.

Thanks again!
Tammy

 

[Guest]

I created a web page of the hard copy form we use, maybe it will help to
explain what I am trying to do with the database
http://www.angelsads.com/mypage/eco.htm

We want to be able to keep track of what eco changed what part and so on.
Which my database is doing now
I was just trying to automate it more because some users dont understand
that they have to create the new number, and they are changing existing
numbers and messing up the history, so I thought it would be easier if I
could get the database to assign it for them

 

[Steve Schapel]

Tammy,

Maybe entered is the wrong word? more like created,

Yep, created is a fair description

but yes this is what I

want, I need to use the number that is created for reports, dosnt it have to
write to the table?

Yep, it will be stored in the table, in the BaseNumber and Extension fields.

The key point is the differnence between the data as strored and the
data as displayed. When you need to have is displayed on your reports
and other forms in a layout that complies with the user's "business
rules", that's when you use a calculated expression, in the query that
the report is based on, or in the control source of a textbox on the
report, to show the data as:
Format([BaseNumber],"0000") & "-" & Format([Extension],"000")

I use this number in another form that has a subform, where I list the
products that are affected by a certain change. so I can't just change the
number (record), because I have to keep the history.

I'm afraid I don't understand this.

I use the macro to find the last number assigned.

I guess this applies to your "old" way of trying to get the next number?
I guess this is no longer relevant?

 

[Steve Schapel]

Tammy,

I will have a look at your example shortly. But I think I already
understand the basis of what you are trying to do. It is *very* easy,
but somehow I am not conveying the method/concept to you clearly enough.

If you want to stop the users from changing the number, set the Enabled
property of the control on the form to No, and its Locked property to No.

 

[Steve Schapel]

Oops, sorry, I mean Locked = Yes

 

[Guest]

Steve thank you so much for all your help, hopefully this will be the last
problem I am having

I am getting a run-time error "2471"

The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object Extension."

here is the code I have

Dim NextNumber As Integer
NextNumber = DMax("[Extension]", "Product", "[BaseNumber]='" &
Me.BASENUMBER & "'") + 1

I cant figure out why it is putting the BaseNumber all in caps??

My basenumber is set as text, and is a combo box on my form with the control
to my table products.basenumber
same with the extention except it is a number.

Tammy,

Maybe entered is the wrong word? more like created,

Yep, created is a fair description

but yes this is what I

want, I need to use the number that is created for reports, dosnt it have to
write to the table?

Yep, it will be stored in the table, in the BaseNumber and Extension fields.

The key point is the differnence between the data as strored and the
data as displayed. When you need to have is displayed on your reports
and other forms in a layout that complies with the user's "business
rules", that's when you use a calculated expression, in the query that
the report is based on, or in the control source of a textbox on the
report, to show the data as:
Format([BaseNumber],"0000") & "-" & Format([Extension],"000")

I use this number in another form that has a subform, where I list the
products that are affected by a certain change. so I can't just change the
number (record), because I have to keep the history.

I'm afraid I don't understand this.

I use the macro to find the last number assigned.

I guess this applies to your "old" way of trying to get the next number?
I guess this is no longer relevant?

 

[Steve Schapel]

Tammy,

I notice the expression in my example uses [Extension] whereas you are
spelling it as "extention", must be American spelling Could this be
the reason?

 

[Guest]

urr, no its just tammy spelling lol.
okay I changed that now I am getting this:
run-time error "94"
Invalid use of Null

Tammy,

I notice the expression in my example uses [Extension] whereas you are
spelling it as "extention", must be American spelling Could this be
the reason?

--
Steve Schapel, Microsoft Access MVP

Steve thank you so much for all your help, hopefully this will be the last
problem I am having

I am getting a run-time error "2471"

The expression you entered as a query parameter produced this error:
'The object doesn't contain the Automation object Extension."

here is the code I have

Dim NextNumber As Integer
NextNumber = DMax("[Extension]", "Product", "[BaseNumber]='" &
Me.BASENUMBER & "'") + 1

I cant figure out why it is putting the BaseNumber all in caps??

My basenumber is set as text, and is a combo box on my form with the control
to my table products.basenumber
same with the extention except it is a number.