access tables field numbering

[Guest]

Often it is easier for me to do my database entries directly in one of my
tables rather than through a form. This (was in the past) because the table
would pick up number patterns automatically and all i had to do was enter the
first few in a number series and then keep hitting the arrow down button.
All of a sudden this automatic numbering stopped working at a certain point
in the table. It will automatically number till, say row 33255, and then it
stops. If I resort the table, it will always stop this behavior at row 33255
(in other words it's not data dependent, rather it's a certain row in the
table. Sorry I'm not very technical but I hope someone will understand what
I mean. It's extremely inconvenient for me now that it's no longer working
when I insert new records!

 

[Guest]

An autonumber is a long integer and should allow for + or - 2,147,483,647
rows of numbers. You can (and should) use a form and still get autonumbers to
work. If you have no dependent data, delete the number column and create a
new one. If you do have dependent tables, change the datatype to long in both
tables Add the new autonymber field to the main or primary table, and use an
update query to update an empty long integer field in your dependent table(s).
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[John Vinson]

Often it is easier for me to do my database entries directly in one of my
tables rather than through a form. This (was in the past) because the table
would pick up number patterns automatically and all i had to do was enter the
first few in a number series and then keep hitting the arrow down button.
All of a sudden this automatic numbering stopped working at a certain point
in the table. It will automatically number till, say row 33255, and then it
stops. If I resort the table, it will always stop this behavior at row 33255
(in other words it's not data dependent, rather it's a certain row in the
table. Sorry I'm not very technical but I hope someone will understand what
I mean. It's extremely inconvenient for me now that it's no longer working
when I insert new records!

Heh. You're one of the first people I've seen who actually *like* this
feature (which came in with A2000, if I recall correctly); I've seen
far more posts complaining about it and asking how to turn it off!
(There's no way to do so, FWIW).

What makes me suspicious here is that 33255 is pretty close to 32768 -
2^15. Perhaps the programmer had some binary number limit in mind!

I don't know how to solve your problem in table data entry, but I have
to wonder if an Append or Update query might not be able to fill this
field. What is its meaning? Are you trying to implement a "roll your
own" Autonumber, or what?

John W. Vinson[MVP]

 

[Guest]

Thanks Arvin, for your fast reply, but I must not have explained properly.
Sometimes but by far not always (kind of complicated to explain and I don't
know enough about Access at this point to change the data ently FORM to work
this out for me instead of writing directly to the table in question.) it is
way faster for me to fill in for example the data for field one and two on a
row of the table and then copy and paste append that row, say 20 times (so
field one and two in column one and two are repeated in each row). Then i
would go down the third and fourth columns of those newly pasted records and
type for example 4501 and then in the same columns but the row beneath, 4502.
Up to row 33888 or so I could then just hit arrow down through the third and
fourth columns of the table and it would number ascendingly. But if I typed
4501 and then 4503 it would know to skip by 2 digits. It didn't matter in
which column or row (text or integer), I could always type in the first few
numbers and this would work. Now no longer. It really puzzles me why it
just stops doing this at a certain point.

 

[Guest]

Ah... I'm glad you understood what I meant, I found it hard to explain! Yes
it's a kind of roll your own autonumbering. At times I have data to
enter that looks something like this:
field 1 field 2 "3 "4
XXXXX YYYYY 1 4501
XXXXX YYYYY 2 4502
XXXXX YYYYY 3 4503
etc., up to say 40 entries like that.
I know some ACCESS but not enough to make a form to do that. If I use the
form as I have it now I have to keep entering "duplicate" and then changing
the numbers each time. So I discovered by copying and "paste appending" 40
times the fields 1 and 2 directly in the table I could then do the "roll your
own" autonumbering thing in columns 3 and 4 of the table. If you think I can
accomplish this by an append or other query I will try to figure it out.
Unfortunately the person who initially set up this database for me has
disappeared so I have had to figure out things as I go along.
Many many thanks

 

[John Vinson]

Ah... I'm glad you understood what I meant, I found it hard to explain! Yes
it's a kind of roll your own autonumbering. At times I have data to
enter that looks something like this:
field 1 field 2 "3 "4
XXXXX YYYYY 1 4501
XXXXX YYYYY 2 4502
XXXXX YYYYY 3 4503

What you can do in a Form involves a VERY little bit of easy VBA code.
Let's say you have a form (single or continuous, doesn't make any
difference) based on this table, or on a Query selecting records from
this table; and you want the textbox txtFour to increment in this way.

Open the Form in design view; select txtFour; view its Properties. On
the Events tab select the AfterUpdate event and click the ... icon by
it; choose Code Builder. Access will give you the Sub and End Sub
lines for free; just add one more:

Private Sub txtFour_AfterUpdate()
Me.txtFour.DefaultValue = Chr(34) & Me.txtFour + 1 & Chr(34)
End Sub

This will set the default of the textbox to one more than its current
contents.


John W. Vinson[MVP]

 

[Guest]

Wow, you're great, I'm getting somewhere.. BUT... still need help. as follows.
The click button on the form in question that the developer called
"duplicate" is the one I would use for this data entry. Basically it
duplicates fields 1 and 2 as described below, so I don't have to keep typing
them. It was designed then for me to have blank fields to fill in for fields
3 through field whatever. On clicking "duplicate", therefore, in this "new"
form, is when I want the incremental numbering to happen. After I wrote in
the code you suggested, the incremental numbering happens only when I click
the button he called "next", which unless I'm scrolling via the "previous"
and "next" buttons through already entered records, will now give me in this
"new" form all the fields blank, except, indeed txtFour (as you call it) will
increment. Long story short: can you, would you help me to get this
incrementing Event to happen upon clicking "duplicate" ?? Forgive me if I am
asking a lot, but even though I have taken a very short course in Access and
taught myself some more, I just don't have the knowledge for this!! I would
be extremely grateful!! Susan

 

[John Vinson]

ong story short: can you, would you help me to get this
incrementing Event to happen upon clicking "duplicate" ?? Forgive me if I am
asking a lot, but even though I have taken a very short course in Access and
taught myself some more, I just don't have the knowledge for this!! I would
be extremely grateful!! Susan

Well... not without knowing more about your application. Could you
post the current event property of the button? Where would Access find
the data which you want duplicated? What table fields, and form
controls, are involved?

John W. Vinson[MVP]

 

[Guest]

Here is the event procedure below... from the "duplicate" button. Upon
click, the data that gets duplicated is the data for two fields that i have
just entered into the form.
What I'd love to do is have the incrementing procedure for which you gave
the coding to work for a few of the fields that remain blank upon clicking.
So for example if I have just typed 3405 in field 3 and then I click
"duplicate", instead of just having the field 3 blank again, I would like it
to be filled with "3406" when I hit this button. If it's possible to do this
then I would save it as a second form so I could choose either, the original
or the new, depending on the data I am entering. Here's the event:

Private Sub ButtonDuplicate_Click()
On Error GoTo Err_ButtonDuplicate_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Exit_ButtonDuplicate_Click:
Exit Sub

Err_ButtonDuplicate_Click:
MsgBox Err.Description
Resume Exit_ButtonDuplicate_Click

End Sub
Private Sub ButtonPrevious_Click()
On Error GoTo Err_ButtonPrevious_Click


DoCmd.GoToRecord , , acPrevious

Exit_ButtonPrevious_Click:
Exit Sub

Err_ButtonPrevious_Click:
MsgBox Err.Description
Resume Exit_ButtonPrevious_Click

End Sub
Private Sub ButtonNext_Click()
On Error GoTo Err_ButtonNext_Click


DoCmd.GoToRecord , , acNext

Exit_ButtonNext_Click:
Exit Sub

Err_ButtonNext_Click:
MsgBox Err.Description
Resume Exit_ButtonNext_Click

End Sub
Private Sub ButtonFormArtistTitle_Click()
On Error GoTo Err_ButtonFormArtistTitle_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "F-SelectTrackIDnr"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ButtonFormArtistTitle_Click:
Exit Sub

Err_ButtonFormArtistTitle_Click:
MsgBox Err.Description
Resume Exit_ButtonFormArtistTitle_Click

End Sub

 

[Guest]

HI I"M Sorry I gave you some wrong info (Don't know where my head was at).
The duplicate button doesn't just duplicate the first two fields, but all of
the fields that I have just entered. What I'd like is that for selected
fields it incremented the numbers.
thanks again for any assistance.

 

[John Vinson]

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

Yuck. For some reason, the Wizards are stuck several generations back,
using DoMenuItem code. There are MUCH better ways!

You'll probably do better to copy the values of controls on the form
into variables, move to the new record, and copy them back
(incrementing as needed). Since your code does not indicate anything
about the form's recordsource or control names, I'll just post an
example with bogus control names - adapt as needed:

Private Sub ButtonDuplicate_Click()
Dim vThisControl As Variant
Dim vThatControl As Variant
Dim vIncrementThisOne As Variant
Dim vIncrementThatOne As Variant

If Me.Dirty Then
Me.Dirty = False ' save the current record to disk
End If

' copy controls into variables, incrementing some
vThisControl = Me.ThisControl
vThatControl = Me.ThatControl
vIncrementThisOne = NZ(Me.IncrementThisOne) + 1
vIncrementThatOne = NZ(Me.IncrementThatOne) + 1
' move to the new record
DoCmd.GoToRecord acForm, Me.Name, acNewRecord
Me.ThisControl = vThisControl
Me.ThatControl = vThatControl
Me.IncrementThisOne = vIncrementThisOne
Me.IncrementThatOne = vIncrementThatOne

Exit_ButtonDuplicate_Click:
Exit Sub

Err_ButtonDuplicate_Click:
MsgBox "Error in ButtonDuplicate_Click: " & _
Err.Number & vbCrLf & Err.Description
Resume Exit_ButtonDuplicate_Click
End Sub


John W. Vinson[MVP]

 

[Guest]

Thanks again. This is a little advanced for me so it will take some time to
figure out -- so I need to set aside some time for it. I will definitely let
you know how it goes !!

 

[Guest]

Wow, it worked! It took me a little figuring out but I have my second form
now that works as desired. Thank you thank you!! As you can see I don't
know very much about Access programming. If you have any suggestions for the
best way for me to learn more would be great but you've really done enough
for now.

All the best to you. Susan