Get Text Box to add a "C"

[Bob V]

My text box has a control source to my table that is BillID , it shows the
Bill ID numbers, What I would like it to show is the letter "C" before each
number
Thanks for any help...........Bob

 

[pietlinden]

My text box has a control source to my table that is BillID , it shows the
Bill ID numbers, What I would like it to show is the letter "C" before each
number
Thanks for any help...........Bob

just *show* C...? You could create an unbound control with a
controlsource like

= "C" & me.Controls("MyCounterControl")

 

[John W. Vinson]

My text box has a control source to my table that is BillID , it shows the
Bill ID numbers, What I would like it to show is the letter "C" before each
number
Thanks for any help...........Bob

Is BillID an Autonumber? If so, you shouldn't be showing it to users AT ALL.

If it's a Number field and you just want to "see" a letter C, set the field's
Format property to

"C#"

to display a literal C followed by a number (C3, C841, C1002); or

"C000000"

to always show six digits (C000001, C000841, C001002).

John W. Vinson [MVP]

 

[Bob V]

Yes John , BiiID is a AutoNumber, Is that a problem!......Thanks Bob
The Format I was looking for was (C3, C841, C1002)

 

[John W. Vinson]

Yes John , BiiID is a AutoNumber, Is that a problem!......

It could be. Autonumbers have one purpose only: to provide a unique key. They
are NOT guaranteed to be sequential. Deleting a record will leave a gap;
hitting the <ESC> key while adding a record, to cancel the add, will leave a
gap; running an Append query will leave a gap, often a very large one.
Replicating your database will make the number random - bill C812 might be
followed by C1813552817 and then by C-2053117284.

At the very best you'll have sequences like C518, C519, C521, C522.
Accountants tend to get agitated when they see missing bill numbers: WAS there
a bill, and whose pocket did the money go into?

If you want sequential numbers, you may want instead to use VBA code to "roll
your own", using a Long Integer field. Search the newsgroups
(http://groups.google.com advanced search) for "custom counter" - the question
comes up often.

John W. Vinson [MVP]

 

[Bob V]

Thanks John, Would it be to late to change my database as I am up to 380
..............Thanks Bob

 

[John W. Vinson]

Thanks John, Would it be to late to change my database as I am up to 380
.............Thanks Bob

Not really. You would need to do several steps - remove any relationships on
this field; remove this field's Primary Key index; change the datatype from
Autonumber to Long Integer; reestablish the Primary Key and any relationships;
and finally add the custom counter VBA code to the form that you use for data
entry (and yes, you must use a form, tables have no usable events).

For a one user application - or one where it is very unlikely that there will
be two users adding records concurrently - the custom counter code is simple.
Use the Form's BeforeInsert event with code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtBillID = DMax("[BillID]", "[Bills]") + 1
End Sub

where txtBillID is the name of the form textbox bound to BillID, and Bills is
the name of your table. If you have a multiuser (split, I hope!!) application,
post back, it's doable with a bit more work.

John W. Vinson [MVP]

 

[suzanne verhaert]

My text box has a control source to my table that is BillID , it shows the
Bill ID numbers, What I would like it to show is the letter "C" before
each number
Thanks for any help...........Bob

 

[Bob V]

Thanks John. What if I added a field (Number) [PaymentNo] and a Check Box
[ckbPayNumber] is this possible
I dont mind re-entering all other previous payment numbers by hand, would
something like this make sence?

Function NextPaymentNo() As Long
NextPaymentNo = Nz(DMax("PaymentNo", "tblAccountStatus"), 0) + 1
End Function
----------------------------------
If ckbPayNumber.value = True Then
recInvoice.Fields("PaymentNo") = NextPaymentNo
Else
recInvoice.Fields("PaymentNo") = 0
End If
---------------------------------------
Example:
388
0
389
0
390

Thanks for your help ..............Bob

Thanks John, Would it be to late to change my database as I am up to 380
.............Thanks Bob

Not really. You would need to do several steps - remove any relationships
on
this field; remove this field's Primary Key index; change the datatype
from
Autonumber to Long Integer; reestablish the Primary Key and any
relationships;
and finally add the custom counter VBA code to the form that you use for
data
entry (and yes, you must use a form, tables have no usable events).

For a one user application - or one where it is very unlikely that there
will
be two users adding records concurrently - the custom counter code is
simple.
Use the Form's BeforeInsert event with code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtBillID = DMax("[BillID]", "[Bills]") + 1
End Sub

where txtBillID is the name of the form textbox bound to BillID, and Bills
is
the name of your table. If you have a multiuser (split, I hope!!)
application,
post back, it's doable with a bit more work.

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks John. What if I added a field (Number) [PaymentNo] and a Check Box
[ckbPayNumber] is this possible
I dont mind re-entering all other previous payment numbers by hand, would
something like this make sence?

Function NextPaymentNo() As Long
NextPaymentNo = Nz(DMax("PaymentNo", "tblAccountStatus"), 0) + 1
End Function
----------------------------------
If ckbPayNumber.value = True Then
recInvoice.Fields("PaymentNo") = NextPaymentNo
Else
recInvoice.Fields("PaymentNo") = 0
End If
---------------------------------------
Example:
388
0
389
0
390

I'd put it all in one subroutine, rather than pulling it out as a separate
function. Since the incremented value depends on the value of another control
(ckbPayNumber) you'll need to move the code from the form's BeforeInsert event
to either the form's BeforeUpdate event, or the afterupdate event of
ckbPayNumber; otherwise there won't be a value there!

Note that if you're entering 0 for every record where ckbPayNumber is False,
then PaymentNo cannot be the primary key of your table; it would need to be
unique. Is there some other field that distinguishes the records with 0
values?

John W. Vinson [MVP]

 

[Bob V]

Thanks John ,do I have to create another field so as any Payments with a "0"
are separate from the others so as you can get the increase of numbers
[PaymentNo]for the other payments???..........Regards Bob

Thanks John. What if I added a field (Number) [PaymentNo] and a Check Box
[ckbPayNumber] is this possible
I dont mind re-entering all other previous payment numbers by hand, would
something like this make sence?

Function NextPaymentNo() As Long
NextPaymentNo = Nz(DMax("PaymentNo", "tblAccountStatus"), 0) + 1
End Function
----------------------------------
If ckbPayNumber.value = True Then
recInvoice.Fields("PaymentNo") = NextPaymentNo
Else
recInvoice.Fields("PaymentNo") = 0
End If
---------------------------------------
Example:
388
0
389
0
390

I'd put it all in one subroutine, rather than pulling it out as a separate
function. Since the incremented value depends on the value of another
control
(ckbPayNumber) you'll need to move the code from the form's BeforeInsert
event
to either the form's BeforeUpdate event, or the afterupdate event of
ckbPayNumber; otherwise there won't be a value there!

Note that if you're entering 0 for every record where ckbPayNumber is
False,
then PaymentNo cannot be the primary key of your table; it would need to
be
unique. Is there some other field that distinguishes the records with 0
values?

John W. Vinson [MVP]

 

[John W. Vinson]

Thanks John ,do I have to create another field so as any Payments with a "0"
are separate from the others so as you can get the increase of numbers
[PaymentNo]for the other payments???..........Regards Bob

I really don't know, Bob! You know your business rules, what these payments
mean, what the checkbox means, the structure of your tables - I don't.

Step back a bit and describe the nature of the information you're storing.
What are the Entities - real-life persons, things or events - modeled by your
database? How does each record uniquely identify which individual entity is
represented by that record? How are the tables related?

John W. Vinson [MVP]

 

[Bob V]

Thanks John , The idea of the zero numbered payments where if you where paid
Cash and you didn't want your payment numbers to start missing numbers as
your accountant would start asking questions
When you enter a payment there is a drop down combo box that you select your
owner
SELECT tblOwnerInfo.OwnerID,
IIf(isnull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ')
& IIf(isnull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName & '
') & IIf(isnull(tblOwnerInfo.OwnerTitle),'',tblOwnerInfo.OwnerTitle) AS
OwnerName FROM tblOwnerInfo;
And from there you enter the payment made , but the field/Text Box [BillID]
gives you your Payment Number, BillID is a AutoNumber/Increment
I am just looking for an option to enter a zero so as when I do a print out
for my accountant I can do a search with No "0"
Thanks for helping out...............Bob

Thanks John ,do I have to create another field so as any Payments with a
"0"
are separate from the others so as you can get the increase of numbers
[PaymentNo]for the other payments???..........Regards Bob

I really don't know, Bob! You know your business rules, what these
payments
mean, what the checkbox means, the structure of your tables - I don't.

Step back a bit and describe the nature of the information you're storing.
What are the Entities - real-life persons, things or events - modeled by
your
database? How does each record uniquely identify which individual entity
is
represented by that record? How are the tables related?

John W. Vinson [MVP]