Re: Sequential Record numbering
- From: "Rick Brandt" <rickbrandt2@xxxxxxxxxxx>
- Date: Sun, 14 Jan 2007 01:28:45 GMT
Rick wrote:
Okay. I'm still having a problem seting up a sequential record
numbering system for my database. I've bought the book and although I
have'nt read all 1105 pages, I did manage to make it through the
glossary with only a slight head ache.
AutoNumber leaves gaps and is not preferred.
I need a form that presets a record number before I enter any data.
I've tried a couple of suggestions :
" Use this as a text box's default value if you want sequential
numbering: ..... Nz(DMax("MyField", "tblMyTable")) +1 "
This returns an error no matter how I try and rearange it.
And John, ( Thanks for all your useful advice ), I can't seem to get
your suggestion to work either:
Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
End Sub .
I'm not a code writer, ( Or at least I haven't been one since Basic
and C+ were in fashion ), but I do need a working database with
sequential record numbering system.
I have a table named "Customer Info" with a field named "Customer
InfoID" that , until now has had it's data type set as " AutoNumber "
, which I'm told is a bad idea.
On the form I'm using, I have a field for Invoive #'s with it's
control set to "Customer InfoID". I need a definative formula, in
the right place, that will achive the results I'm looking for.
I don't ask for much, just a releif from my ignorance.
Thank You,
Rick
Both of those suggested methods would do what you are asking IF you replace the
posted field and table names with your actual field and table names. Based on
what you posted I would think that...
Nz(DMax("[Customer Info ID]", "[Customer Info]"),0) +1
If you use that as the default value property you need an = at the front. If in
code then...
Me!ControlName = Nz(DMax("[Customer Info ID]", "[Customer Info]"),0) +1
I used a sample control name because the terminology you used in your second
paragraph was incorrect and/or garbled. Forms don't have fields (they have
controls) and controls have ControlSource properties. The sentence...
I have a field for Invoive #'s with it's control set to "Customer InfoID".
....does not make clear to me what you really have.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
.
- Prev by Date: Re: DoCmd.Send Obkect Help
- Next by Date: Re: Sequential Record numbering
- Previous by thread: Auto add . after Dr on Form
- Next by thread: Re: Sequential Record numbering
- Index(es):
Relevant Pages
|
