Frustrated with Value

[Afrosheen via AccessMonster.com]

First of all, Thanks for reading this post.

Here's my problem I have a form that is displayed like a table. I have a
field called [ratingcyc]. If there are no records in the form a person enters
a number from a combo box, like 20 in the field then goes to a new record.

When I go to the next new record I want to increment the number 20 which is
in the previous record by 1 and not have to use the combo box unless there is
no value to use.

Thanks for your help. Any help would be appreciated.

 

[Jack Leach]

That's kind of a tough thing to do. Access doesn't let you see any values
from a previous record, so you have to store that somehow (generally in a
variable public to the form's module). Then it becomes an issue of making
access store the before each record change, and you can never really count on
the record that the user was last on being the one that you want to
increment. The BeforeUpdate event would work for some, but not if the user
goes back to edit some random record. You may be able to utilize some
"MoveBack, GetValue, MoveForward, AddValue" type scheme, but thats not very
easy either.

On a different note, you may be able to include this as a calculated field
in the underlying query. You need to make sure the sort order is set
correctly, an underlying table (or any table) will not have a reliable order.
There's a few twists and turns doing it like this as well, depending on what
the end use of the data is.

What exactly do you need this type of incrementation through records for,
and why is the user allowed to change it? Usually something like this is
handled more by the underlying structure than in the form itself.


hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Ken Snell MVP]

Use the AfterUpdate event of the combo box to set the Default Value of the
control that holds the 1+ number so that the new record will get the right
value:

Private Sub ComboBoxName_AfterUpdate()
Me.NameOfControl.DefaultValue = """" & Me.ComboBoxName.Value + 1 & """"
End Sub

Then the new record will get the value from the Default Value.

 

[Afrosheen via AccessMonster.com]

Good Morning and thanks for your reply.

Here's what's happening. I took Ken's idea and put it where it should go and
it works as it should. Now to add to this. Unless I change the value of the
combo box then the value stays the same no matter how many records I put in.

I guess what I'm trying to do is, have the user input in a number on the
first record from the combo box then on the next new record take the number
from the previous combo box entry and add 1 to that number and put it in the
combo box location and not have the user use the combo box for number entry.

I thought about using the autonumber feature but this is on a one-to-many
relationship. The many may have the same starting number or they may be
different.

I'm just trying to save some steps.

Thanks.

Use the AfterUpdate event of the combo box to set the Default Value of the
control that holds the 1+ number so that the new record will get the right
value:

Private Sub ComboBoxName_AfterUpdate()
Me.NameOfControl.DefaultValue = """" & Me.ComboBoxName.Value + 1 & """"
End Sub

Then the new record will get the value from the Default Value.

First of all, Thanks for reading this post.

[quoted text clipped - 10 lines]

Thanks for your help. Any help would be appreciated.

 

[Ken Snell MVP]

You could use the Current event of the form to also reset the DefaultValue
when you're on a new record. One problem with this is that it would occur
regardless of whether you want to increment or not for the next new record.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.NameOfControl.DefaultValue = """" & _
CLng(Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Good Morning and thanks for your reply.

Here's what's happening. I took Ken's idea and put it where it should go
and
it works as it should. Now to add to this. Unless I change the value of
the
combo box then the value stays the same no matter how many records I put
in.

I guess what I'm trying to do is, have the user input in a number on the
first record from the combo box then on the next new record take the
number
from the previous combo box entry and add 1 to that number and put it in
the
combo box location and not have the user use the combo box for number
entry.

I thought about using the autonumber feature but this is on a one-to-many
relationship. The many may have the same starting number or they may be
different.

I'm just trying to save some steps.

Thanks.

Use the AfterUpdate event of the combo box to set the Default Value of the
control that holds the 1+ number so that the new record will get the right
value:

Private Sub ComboBoxName_AfterUpdate()
Me.NameOfControl.DefaultValue = """" & Me.ComboBoxName.Value + 1 & """"
End Sub

Then the new record will get the value from the Default Value.

First of all, Thanks for reading this post.

[quoted text clipped - 10 lines]

Thanks for your help. Any help would be appreciated.

 

[Afrosheen via AccessMonster.com]

Thanks for your reply Ken.
I cut and pasted the routine in. I changed the ComboBoxName and the
NameOfControl to combo22. I got a:

Runtime error 13; Type Mismatch.

Isn't the NameOfControl and the ComboBoxName supposed to be the same? If not
then that maybe where the problem is.

You could use the Current event of the form to also reset the DefaultValue
when you're on a new record. One problem with this is that it would occur
regardless of whether you want to increment or not for the next new record.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.NameOfControl.DefaultValue = """" & _
CLng(Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

Good Morning and thanks for your reply.

[quoted text clipped - 36 lines]

 

[Ken Snell MVP]

Yes, NameOfControl and ComboboxName would be the same. Sorry, brain freeze
when I typed the code snippet.

The error that you're getting suggests that there might not be a value for
DefaultValue initially. Try this to see if the error disappears.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.ComboBoxName.DefaultValue = """" & _
CLng(Nz(Me.ComboBoxName.DefaultValue, 0) + 1 & """"
End Sub

If the error does disappear, then you'll need to replace the 0 in the fifth
line above with a DMax function that returns the current maximum value so
that the code can add 1 to it.

If the error continues, then put a breakpoint on the If Me.NewRecord line,
and let it run. When the code breaks, and opens VBE, hover the cursor over
the Me.ComboBoxName.DefaultValue and wait for popup box to show. What value
do you see in the box?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks for your reply Ken.
I cut and pasted the routine in. I changed the ComboBoxName and the
NameOfControl to combo22. I got a:

Runtime error 13; Type Mismatch.

Isn't the NameOfControl and the ComboBoxName supposed to be the same? If
not
then that maybe where the problem is.

You could use the Current event of the form to also reset the DefaultValue
when you're on a new record. One problem with this is that it would occur
regardless of whether you want to increment or not for the next new
record.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.NameOfControl.DefaultValue = """" & _
CLng(Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

Good Morning and thanks for your reply.

[quoted text clipped - 36 lines]

Thanks for your help. Any help would be appreciated.

 

[Ken Snell MVP]

If we determine that the Current event won't work, we can use the form's
BeforeInsert event to set the default value .. the code essentially will be
the same, just put it in a different event procedure.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Yes, NameOfControl and ComboboxName would be the same. Sorry, brain freeze
when I typed the code snippet.

The error that you're getting suggests that there might not be a value for
DefaultValue initially. Try this to see if the error disappears.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.ComboBoxName.DefaultValue = """" & _
CLng(Nz(Me.ComboBoxName.DefaultValue, 0) + 1 & """"
End Sub

If the error does disappear, then you'll need to replace the 0 in the
fifth line above with a DMax function that returns the current maximum
value so that the code can add 1 to it.

If the error continues, then put a breakpoint on the If Me.NewRecord line,
and let it run. When the code breaks, and opens VBE, hover the cursor over
the Me.ComboBoxName.DefaultValue and wait for popup box to show. What
value do you see in the box?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks for your reply Ken.
I cut and pasted the routine in. I changed the ComboBoxName and the
NameOfControl to combo22. I got a:

Runtime error 13; Type Mismatch.

Isn't the NameOfControl and the ComboBoxName supposed to be the same? If
not
then that maybe where the problem is.

You could use the Current event of the form to also reset the
DefaultValue
when you're on a new record. One problem with this is that it would occur
regardless of whether you want to increment or not for the next new
record.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.NameOfControl.DefaultValue = """" & _
CLng(Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

Good Morning and thanks for your reply.

[quoted text clipped - 36 lines]

Thanks for your help. Any help would be appreciated.

 

[Afrosheen via AccessMonster.com]

Good morning Ken,
I still get the error. It says type miss match.

When I hovered over the break point it shows line 3 of your code with nothing
in it.

Thanks for your help again.

What are your thoughts on putting a field on the main table that will store
the number, then when it comes to a New Record it will take that number and
add 1 to it and place it in the secondary table?

Thanks for your help.

Yes, NameOfControl and ComboboxName would be the same. Sorry, brain freeze
when I typed the code snippet.

The error that you're getting suggests that there might not be a value for
DefaultValue initially. Try this to see if the error disappears.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.ComboBoxName.DefaultValue = """" & _
CLng(Nz(Me.ComboBoxName.DefaultValue, 0) + 1 & """"
End Sub

If the error does disappear, then you'll need to replace the 0 in the fifth
line above with a DMax function that returns the current maximum value so
that the code can add 1 to it.

If the error continues, then put a breakpoint on the If Me.NewRecord line,
and let it run. When the code breaks, and opens VBE, hover the cursor over
the Me.ComboBoxName.DefaultValue and wait for popup box to show. What value
do you see in the box?

Thanks for your reply Ken.
I cut and pasted the routine in. I changed the ComboBoxName and the

[quoted text clipped - 22 lines]

 

[Ken Snell MVP]

Don't hover over the breakpoint. Hover over the text
Me.ComboBoxName.DefaultValue in that line.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Good morning Ken,
I still get the error. It says type miss match.

When I hovered over the break point it shows line 3 of your code with
nothing
in it.

Thanks for your help again.

What are your thoughts on putting a field on the main table that will
store
the number, then when it comes to a New Record it will take that number
and
add 1 to it and place it in the secondary table?

Thanks for your help.

Yes, NameOfControl and ComboboxName would be the same. Sorry, brain freeze
when I typed the code snippet.

The error that you're getting suggests that there might not be a value for
DefaultValue initially. Try this to see if the error disappears.

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.ComboBoxName.DefaultValue = """" & _
CLng(Nz(Me.ComboBoxName.DefaultValue, 0) + 1 & """"
End Sub

If the error does disappear, then you'll need to replace the 0 in the
fifth
line above with a DMax function that returns the current maximum value so
that the code can add 1 to it.

If the error continues, then put a breakpoint on the If Me.NewRecord line,
and let it run. When the code breaks, and opens VBE, hover the cursor over
the Me.ComboBoxName.DefaultValue and wait for popup box to show. What
value
do you see in the box?

Thanks for your reply Ken.
I cut and pasted the routine in. I changed the ComboBoxName and the

[quoted text clipped - 22 lines]

Thanks for your help. Any help would be appreciated.

 

[Afrosheen via AccessMonster.com]

Sorry Ken that I didn't explain my self better. When I did hover over the Me.
Combo22.DefaultValue, all it said was Me.Combo22.DefaultValue=. There was
nothing else.

Thanks again for your help.

Don't hover over the breakpoint. Hover over the text
Me.ComboBoxName.DefaultValue in that line.

Good morning Ken,
I still get the error. It says type miss match.

[quoted text clipped - 41 lines]

 

[Ken Snell MVP]

OK, try this:

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.ComboBoxName.DefaultValue = """" & _
CLng(Iif(Me.ComboBoxName.DefaultValue = "", 0, _
Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Sorry Ken that I didn't explain my self better. When I did hover over the
Me.
Combo22.DefaultValue, all it said was Me.Combo22.DefaultValue=. There was
nothing else.

Thanks again for your help.

Don't hover over the breakpoint. Hover over the text
Me.ComboBoxName.DefaultValue in that line.

Good morning Ken,
I still get the error. It says type miss match.

[quoted text clipped - 41 lines]

Thanks for your help. Any help would be appreciated.

 

[Afrosheen via AccessMonster.com]

Sorry Ken I'm getting back so late. My computer crashed and I had to
reinstall everything.

It still gives me a type mismatch and when I hover over the Me.Combo22.
DefaultValue it still shows up as nothing. I was wondering if the Me.Combo22.
DefaultValue is a string or a number. The control source for the combo box is
a number.

Thanks again.

OK, try this:

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.ComboBoxName.DefaultValue = """" & _
CLng(Iif(Me.ComboBoxName.DefaultValue = "", 0, _
Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

Sorry Ken that I didn't explain my self better. When I did hover over the
Me.

[quoted text clipped - 11 lines]

 

[Ken Snell MVP]

Sorry, I mistakenly repasted the same code in my reply. Try this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ComboBoxName.DefaultValue = """" & _
CLng(Iif(Me.ComboBoxName.DefaultValue = "", 0, _
Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Sorry Ken I'm getting back so late. My computer crashed and I had to
reinstall everything.

It still gives me a type mismatch and when I hover over the Me.Combo22.
DefaultValue it still shows up as nothing. I was wondering if the
Me.Combo22.
DefaultValue is a string or a number. The control source for the combo box
is
a number.

Thanks again.

OK, try this:

Private Sub Form_Current()
If Me.NewRecord = True Then _
Me.ComboBoxName.DefaultValue = """" & _
CLng(Iif(Me.ComboBoxName.DefaultValue = "", 0, _
Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

Sorry Ken that I didn't explain my self better. When I did hover over
the
Me.

[quoted text clipped - 11 lines]

Thanks for your help. Any help would be appreciated.

 

[Afrosheen via AccessMonster.com]

Still coming up with the same error. It may be that there is no solution.
Thanks for the help. They do have a dropdown box to choose the numbers. I
guess I'll have to use that.

Thanks again Ken.

Sorry, I mistakenly repasted the same code in my reply. Try this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ComboBoxName.DefaultValue = """" & _
CLng(Iif(Me.ComboBoxName.DefaultValue = "", 0, _
Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

Sorry Ken I'm getting back so late. My computer crashed and I had to
reinstall everything.

[quoted text clipped - 23 lines]

 

[Ken Snell MVP]

I am puzzled by this result. DefaultValue is a string datatype and cannot
contain a Null value. So the code should work to get a default value
established. I don't have any other suggestion at this time. Sorry.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Still coming up with the same error. It may be that there is no solution.
Thanks for the help. They do have a dropdown box to choose the numbers. I
guess I'll have to use that.

Thanks again Ken.

Sorry, I mistakenly repasted the same code in my reply. Try this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.ComboBoxName.DefaultValue = """" & _
CLng(Iif(Me.ComboBoxName.DefaultValue = "", 0, _
Me.ComboBoxName.DefaultValue) + 1 & """"
End Sub

Sorry Ken I'm getting back so late. My computer crashed and I had to
reinstall everything.

[quoted text clipped - 23 lines]

Thanks for your help. Any help would be appreciated.

 

[Afrosheen via AccessMonster.com]

HI Ken
The field is a number. Maybe I was not too clear. Having those senior moments.

I am puzzled by this result. DefaultValue is a string datatype and cannot
contain a Null value. So the code should work to get a default value
established. I don't have any other suggestion at this time. Sorry.

Still coming up with the same error. It may be that there is no solution.
Thanks for the help. They do have a dropdown box to choose the numbers. I

[quoted text clipped - 15 lines]