Need numbering help desperately

[Jelly''s]

I've spent hours reading all the posts and searching google for Custom
Autonumber and I can't get my code to work... so please help.

I have built code which imports an excel file into a master temporary table
on a monthly basis. I have another table "Text1" which I will populate using
an append query (pulling some of the information from the Master table). I
then add additional information to "Text 1" using an update query. So at
this point, all of the records are in the "Text1" table and I need a number
generated from 1 to n in the custom number field [BITEM]. I managed to get
all of the bugs out but no numbers get inserted into the field.

Sub SequentialNumber3()
Dim BITEM As String 'I've tried this as Integer as well and it doesn't
work
Dim Text1 As String
BITEM = Nz(DMax("[BITEM]", "Text1")) + 1
End Sub

I've also used the following code, but it also didn't work. In addition,
with this code, I have to manually insert a "1" in the first record or I get
a Null value error:

Sub SequentialNumber()
Dim BITEM As Integer
BITEM = DMax("[BITEM]", "Text1") + 1
End Sub

What am I doing wrong?

 

[Dirk Goldgar]

I've spent hours reading all the posts and searching google for Custom
Autonumber and I can't get my code to work... so please help.

I have built code which imports an excel file into a master temporary
table on a monthly basis. I have another table "Text1" which I will
populate using an append query (pulling some of the information from
the Master table). I then add additional information to "Text 1"
using an update query. So at this point, all of the records are in
the "Text1" table and I need a number generated from 1 to n in the
custom number field [BITEM]. I managed to get all of the bugs out
but no numbers get inserted into the field.

Sub SequentialNumber3()
Dim BITEM As String 'I've tried this as Integer as well and it
doesn't work
Dim Text1 As String
BITEM = Nz(DMax("[BITEM]", "Text1")) + 1
End Sub

I've also used the following code, but it also didn't work. In
addition, with this code, I have to manually insert a "1" in the
first record or I get a Null value error:

Sub SequentialNumber()
Dim BITEM As Integer
BITEM = DMax("[BITEM]", "Text1") + 1
End Sub

What am I doing wrong?

Neither of those routines is actually going to do anything with the
number you come up with. They're not functions, and even if they were,
they aren't returning a value; they don't assign a value to any
variable or control external to the routine itself. How are you using
them?

I'd expect you to have a function along these lines:

'----- start of suggested code -----
Function NextBITEM() As Long

NextBITEM = Nz(DMax("BITEM", "Text1"), 0) + 1

End Function
'----- end of suggested code -----

But exactly how you will use such a function to update your table isn't
clear to me. Normally, you'd use a function like this in the
BeforeUpdate event of a form, executing code like this:

'----- start of example form code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!BITEM = NextBITEM()

End Sub
'----- end of example form code -----

 

[Jelly''s]

I don't have a Form. I wouldn't know how to build one anyway. I'm too new
at this.

I was hoping that the code actually did populate the field with the numbers.
I guess I need to do something else, but what? I don't have any other codes
aside from my import and export codes and my two queries. I guess my
question should be, how do I populate my Custom Number field [BITEM]with
numbers from 1 to N?

Thanks for the quick response.

I've spent hours reading all the posts and searching google for Custom
Autonumber and I can't get my code to work... so please help.

I have built code which imports an excel file into a master temporary
table on a monthly basis. I have another table "Text1" which I will
populate using an append query (pulling some of the information from
the Master table). I then add additional information to "Text 1"
using an update query. So at this point, all of the records are in
the "Text1" table and I need a number generated from 1 to n in the
custom number field [BITEM]. I managed to get all of the bugs out
but no numbers get inserted into the field.

Sub SequentialNumber3()
Dim BITEM As String 'I've tried this as Integer as well and it
doesn't work
Dim Text1 As String
BITEM = Nz(DMax("[BITEM]", "Text1")) + 1
End Sub

I've also used the following code, but it also didn't work. In
addition, with this code, I have to manually insert a "1" in the
first record or I get a Null value error:

Sub SequentialNumber()
Dim BITEM As Integer
BITEM = DMax("[BITEM]", "Text1") + 1
End Sub

What am I doing wrong?

Neither of those routines is actually going to do anything with the
number you come up with. They're not functions, and even if they were,
they aren't returning a value; they don't assign a value to any
variable or control external to the routine itself. How are you using
them?

I'd expect you to have a function along these lines:

'----- start of suggested code -----
Function NextBITEM() As Long

NextBITEM = Nz(DMax("BITEM", "Text1"), 0) + 1

End Function
'----- end of suggested code -----

But exactly how you will use such a function to update your table isn't
clear to me. Normally, you'd use a function like this in the
BeforeUpdate event of a form, executing code like this:

'----- start of example form code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!BITEM = NextBITEM()

End Sub
'----- end of example form code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Zac Woodall [MSFT]]

I'm not sure you need code to do this. Is there a reason you can't just
make [BITEM] an autonumber field?

--
Zac Woodall | Program Manager | Microsoft Access

I don't have a Form. I wouldn't know how to build one anyway. I'm too new
at this.

I was hoping that the code actually did populate the field with the
numbers.
I guess I need to do something else, but what? I don't have any other
codes
aside from my import and export codes and my two queries. I guess my
question should be, how do I populate my Custom Number field [BITEM]with
numbers from 1 to N?

Thanks for the quick response.

I've spent hours reading all the posts and searching google for Custom
Autonumber and I can't get my code to work... so please help.

I have built code which imports an excel file into a master temporary
table on a monthly basis. I have another table "Text1" which I will
populate using an append query (pulling some of the information from
the Master table). I then add additional information to "Text 1"
using an update query. So at this point, all of the records are in
the "Text1" table and I need a number generated from 1 to n in the
custom number field [BITEM]. I managed to get all of the bugs out
but no numbers get inserted into the field.

Sub SequentialNumber3()
Dim BITEM As String 'I've tried this as Integer as well and it
doesn't work
Dim Text1 As String
BITEM = Nz(DMax("[BITEM]", "Text1")) + 1
End Sub

I've also used the following code, but it also didn't work. In
addition, with this code, I have to manually insert a "1" in the
first record or I get a Null value error:

Sub SequentialNumber()
Dim BITEM As Integer
BITEM = DMax("[BITEM]", "Text1") + 1
End Sub

What am I doing wrong?

Neither of those routines is actually going to do anything with the
number you come up with. They're not functions, and even if they were,
they aren't returning a value; they don't assign a value to any
variable or control external to the routine itself. How are you using
them?

I'd expect you to have a function along these lines:

'----- start of suggested code -----
Function NextBITEM() As Long

NextBITEM = Nz(DMax("BITEM", "Text1"), 0) + 1

End Function
'----- end of suggested code -----

But exactly how you will use such a function to update your table isn't
clear to me. Normally, you'd use a function like this in the
BeforeUpdate event of a form, executing code like this:

'----- start of example form code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!BITEM = NextBITEM()

End Sub
'----- end of example form code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Jelly''s]

I need the number to be begin at 1 every month, which means I have to clear
and compact every month. And as I understand it, this would have to be done
manually as there is no way to write vba for compacting. Also, I need the
format to have 10 digits in total, with zeroes before the number, which I
don't think can be done with autonumber (I could be wrong about this).
Finally, everyone in the community really discourages the use of autonumber
because of its peculiarities.

Can I (should I) use autonumber instead?

I'm not sure you need code to do this. Is there a reason you can't just
make [BITEM] an autonumber field?

--
Zac Woodall | Program Manager | Microsoft Access

I don't have a Form. I wouldn't know how to build one anyway. I'm too new
at this.

I was hoping that the code actually did populate the field with the
numbers.
I guess I need to do something else, but what? I don't have any other
codes
aside from my import and export codes and my two queries. I guess my
question should be, how do I populate my Custom Number field [BITEM]with
numbers from 1 to N?

Thanks for the quick response.

I've spent hours reading all the posts and searching google for Custom
Autonumber and I can't get my code to work... so please help.

I have built code which imports an excel file into a master temporary
table on a monthly basis. I have another table "Text1" which I will
populate using an append query (pulling some of the information from
the Master table). I then add additional information to "Text 1"
using an update query. So at this point, all of the records are in
the "Text1" table and I need a number generated from 1 to n in the
custom number field [BITEM]. I managed to get all of the bugs out
but no numbers get inserted into the field.

Sub SequentialNumber3()
Dim BITEM As String 'I've tried this as Integer as well and it
doesn't work
Dim Text1 As String
BITEM = Nz(DMax("[BITEM]", "Text1")) + 1
End Sub

I've also used the following code, but it also didn't work. In
addition, with this code, I have to manually insert a "1" in the
first record or I get a Null value error:

Sub SequentialNumber()
Dim BITEM As Integer
BITEM = DMax("[BITEM]", "Text1") + 1
End Sub

What am I doing wrong?

Neither of those routines is actually going to do anything with the
number you come up with. They're not functions, and even if they were,
they aren't returning a value; they don't assign a value to any
variable or control external to the routine itself. How are you using
them?

I'd expect you to have a function along these lines:

'----- start of suggested code -----
Function NextBITEM() As Long

NextBITEM = Nz(DMax("BITEM", "Text1"), 0) + 1

End Function
'----- end of suggested code -----

But exactly how you will use such a function to update your table isn't
clear to me. Normally, you'd use a function like this in the
BeforeUpdate event of a form, executing code like this:

'----- start of example form code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Me!BITEM = NextBITEM()

End Sub
'----- end of example form code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tony Toews]

I need the number to be begin at 1 every month, which means I have to clear
and compact every month.

If you were to use an autonumber key yes. However, like Dirk stated
you'll have to put the code in behind a form. Oh, you want to put
this in on the importing query.

See How to create a running totals query in Microsoft Access
http://support.microsoft.com/kb/290136/en-us but based on a field
which contains a 1.

Also, I need the
format to have 10 digits in total, with zeroes before the number, which I
don't think can be done with autonumber

You could do this using a an appropriate display format on the table.
Once it's there and working it would percolate

(I could be wrong about this).
Finally, everyone in the community really discourages the use of autonumber
because of its peculiarities.

Autonumbers are very useful and highly encouraged. However in some
circumstances they aren't appropriate.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm