Access 2002 CustomerInfoID Field

[Guest]

Next Question. How do I stop the CustomerInfoID Field from displaying "
(AutoNumber) when I open a new or blank form?

 

[Al Campagna]

Rick,
Set the Visible property of the control to No.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

I need the numbers to show as they are invoice numbers. I just don't want the
phrase " (AutoNumber) " to show up in the ID Field on new or blank forms.

 

[John Vinson]

I need the numbers to show as they are invoice numbers. I just don't want the
phrase " (AutoNumber) " to show up in the ID Field on new or blank forms.

I'd REALLY recommend *against* using an Autonumber field for invoice
numbers.

If you delete an invoice, you'll get a gap.

If you even hit <Esc> after the first keystroke of entering a new
record, and cancel the entry, you'll get a gap.

If you ever run an Append query into the invoices table, you'll get a
gap - possibly hundreds of numbers in size.

If you ever Replicate your database, they will - irreversibly - become
random; Invoice 352 might be followed by Invoice 1884234528 and then
by -2051332746.

Autonumbers are for one purpose, and one purpose ONLY: to provide a
unique key value. They're *not* guaranteed to be sequential or
gapless, and they're really not suitable for human consumption!

Try using a Long Integer rather than an Autonumber and use VBA code
(search this group for "Custom Counter") to populate it.

John W. Vinson[MVP]

 

[Guest]

Okay John, AutoNumber sounds like a bad Idea. However, I do need to assign a
unique invoices to each of these records. The "custom counter" discussion was
interesting but not quite the same situation.

 

[John Vinson]

Okay John, AutoNumber sounds like a bad Idea. However, I do need to assign a
unique invoices to each of these records. The "custom counter" discussion was
interesting but not quite the same situation.

Well, the Invoice Number is in fact a custom counter - you want it to
increment under your control so that each record in the Invoice table
gets a new value, right?

One way to do so works best for a single-user system: use the Invoice
Form's BeforeInsert event as:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
End Sub

It gets harder if you have multiple users (since two users might get a
new record at the same time and both get the same number). I use the
code in the Access Developer's Handbook, which has a "next number"
table which gets opened exclusively; the code extracts the next
number,increments it, updates the table, frees it up, and returns the
value to the form for insertion.

John W. Vinson[MVP]

 

[Al Campagna]

Rick,
John's code must be "interpreted" to match your specific object names. We often don't
know all the object names involved in a code solution, so most of the time, the
responder's code is an "example".

Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1

^ ^ ^
Use your own field names and table name in that context.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

For some reason, I can't seem to grt this to work. Someone else suggeswted a
similar expression as the control source in the Customer ID Field. That
doesn't seem to work either. It returns an error " #Name? ".

In the expression you suggest, you start out " Me.txtInvoiceNo". Is this
literal or a reprisentation of some value I need to substitute?

Okay John, AutoNumber sounds like a bad Idea. However, I do need to assign a
unique invoices to each of these records. The "custom counter" discussion was
interesting but not quite the same situation.

Well, the Invoice Number is in fact a custom counter - you want it to
increment under your control so that each record in the Invoice table
gets a new value, right?

One way to do so works best for a single-user system: use the Invoice
Form's BeforeInsert event as:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
End Sub

It gets harder if you have multiple users (since two users might get a
new record at the same time and both get the same number). I use the
code in the Access Developer's Handbook, which has a "next number"
table which gets opened exclusively; the code extracts the next
number,increments it, updates the table, frees it up, and returns the
value to the form for insertion.

John W. Vinson[MVP]

 

[Al Campagna]

Rick,
The Me. refers to the form object, and txtInvoiveNo referes to the control on your form
that will receive the value returned by the DLookup.
If the DLookup returned an InvoiceNo like 1742, then the statement is saying...
The txtInvoiceNo on the form = 1742

--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Yes. I realize they must be intereted. I'm just not sure which object the
"Me.txtInvoiceNo "represents or what the " Me." preface is supposed to mean.

Thanks
Rick

Rick,
John's code must be "interpreted" to match your specific object names. We often
don't
know all the object names involved in a code solution, so most of the time, the
responder's code is an "example".

Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1

^ ^ ^
Use your own field names and table name in that context.
--
hth
Al Campagna
Candia Computer Consulting
Microsoft MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

For some reason, I can't seem to grt this to work. Someone else suggeswted a
similar expression as the control source in the Customer ID Field. That
doesn't seem to work either. It returns an error " #Name? ".

In the expression you suggest, you start out " Me.txtInvoiceNo". Is this
literal or a reprisentation of some value I need to substitute?

:

On Mon, 1 Jan 2007 20:35:01 -0800, Rick

Okay John, AutoNumber sounds like a bad Idea. However, I do need to assign a
unique invoices to each of these records. The "custom counter" discussion was
interesting but not quite the same situation.


Well, the Invoice Number is in fact a custom counter - you want it to
increment under your control so that each record in the Invoice table
gets a new value, right?

One way to do so works best for a single-user system: use the Invoice
Form's BeforeInsert event as:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
End Sub

It gets harder if you have multiple users (since two users might get a
new record at the same time and both get the same number). I use the
code in the Access Developer's Handbook, which has a "next number"
table which gets opened exclusively; the code extracts the next
number,increments it, updates the table, frees it up, and returns the
value to the form for insertion.

John W. Vinson[MVP]