Solved Uses Random numbers to generate custom id

[Nonnylizing]

Hello fam, Please I need help with something...
I've been designing this form for biometric capture....
How do I use a serial/random numbers in one textbox to autogenerate a custom ID in another textbox
E.g.
If I enter 345 in serial textbox, I'll get MOT/T/345 in fleet textbox
Please help

 

[The_Doc_Man]

The first question why complicate a perfectly good simple table with a simple autonumber when in fact you can use a "plain" autonumber and return that more complex custom identifier using a query. Don't confuse a table's prime key with an ID that one might use on a printed label.

Tell us a little more about the requirement because otherwise, what you are trying to do is gilding a lily.

 

[Ranman256]

AutoNum fields create a non repeating 'random' #.
No programming involved.

 

[jdraw]

I agree with the other responders.. What exactly will this "Custom ID" mean to you and the application?
You can use an autonumber to uniquely identify records in your table. The database system (Access/ACE) understands it and can use it efficiently -no effort on your part. If you need some other identifier for your (human) use, then tell readers about that requirement. Good luck with your project.

 

[Nonnylizing]

@jdraw , @Ranman256 and @The_Doc_Man
Let's forget the terminologies that I used... may be confusing but this is what I want...

I want to reduced the time of inputs and the possibility of mistakes in my fleet number column which is an alphanumeric value

The serial number corresponds to the fleet number in this manner
S/N. FleetNo
0215. MOT/T/0215
0330. MOT/T/0330
etc

Is there a way I can input the SerialNo so the fleetNo will be automatically generated...that's my question
Thanks guys

 

[jdraw]

Not enough info for me, but maybe the others understand. I suggest you tell us about your proposed application and database in simple, plain English in order that we have some context to help with your requirement. Tell us why 0215. MOT/T/0215 is required rather than a simple number or two.

 

[theDBguy]

@jdraw , @Ranman256 and @The_Doc_Man
Let's forget the terminologies that I used... may be confusing but this is what I want...

I want to reduced the time of inputs and the possibility of mistakes in my fleet number column which is an alphanumeric value

The serial number corresponds to the fleet number in this manner
S/N. FleetNo
0215. MOT/T/0215
0330. MOT/T/0330
etc

Is there a way I can input the SerialNo so the fleetNo will be automatically generated...that's my question
Thanks guys

Hi. Probably not the exact answer to your question, but with the limited info we got, try the following steps:

1. Create a new table called tblSerials
2. Add one Short Text field to this table and call it SN
3. Enter some serial numbers into this table, e.g. 0215, 0330, etc.
4. Create a new query based on table tblSerials using the following SQL statement:

SELECT [SN], "MOT/T/" & [SN] AS FleetNo FROM tblSerials

Open the query in datasheet view and let us know if it does what you want.

 

[Gasman]

Yes.
Is it always going to be MOT/T or is it going to change, perhaps year to you.?

 

[Nonnylizing]

Yes.
Is it always going to be MOT/T or is it going to change, perhaps year to you.?

No year...just MOT/T/SerialNo

 

[theDBguy]

No year...just MOT/T/SerialNo

Hi. Did you try the steps I posted. I was just curious...

 

[Nonnylizing]

Hi. Probably not the exact answer to your question, but with the limited info we got, try the following steps:

1. Create a new table called tblSerials
2. Add one Short Text field to this table and call it SN
3. Enter some serial numbers into this table, e.g. 0215, 0330, etc.
4. Create a new query based on table tblSerials using the following SQL statement:

SELECT [SN], "MOT/T/" & [SN] AS FleetNo FROM tblSerials

Open the query in datasheet view and let us know if it does what you want.

I'm a little bit confused cos my main database table already contained serialNo field and FleetNo field
And my Form also contain the SerialNo textbox and fleetNo textbox bound to theirs respective fields in the database table
Help me relate the two textboxes so that if I enter values in textbox SerialNo, textbox fleetNo will automatically be displayed

I know my explanation is poor but please help me understand

 

[theDBguy]

I'm a little bit confused cos my main database table already contained serialNo field and FleetNo field
And my Form also contain the SerialNo textbox and fleetNo textbox bound to theirs respective fields in the database table
Help me relate the two textboxes so that if I enter values in textbox SerialNo, textbox fleetNo will automatically be displayed

I know my explanation is poor but please help me understand

Hi. Please disregard, for the moment, your current/existing database setup/design. All I'm asking is please try the steps I have outlined above and let us know if this is anywhere close to what you were thinking about. If it is, I can then tell you how to apply it to your existing database design. Just looking for some confirmation from you at the moment.

 

[Nonnylizing]

It generated the fleetNo field

 

[theDBguy]

It generated the fleetNo field

So, the question I asked was: "Was that what you were thinking about?" As in, you only enter a serial number and the fleetno is automatically generated from it.

 

[Nonnylizing]

So, the question I asked was: "Was that what you were thinking about?" As in, you only enter a serial number and the fleetno is automatically generated from it.

Yes but through a form

 

[theDBguy]

Yes but through a form

Understood. Just wanted to understand the requirement first. So, now, while you still have the new query I asked you to create, try creating a new form based on it. Then, in a new record, enter a new serial number. What happened?

 

[Nonnylizing]

Understood. Just wanted to understand the requirement first. So, now, while you still have the new query I asked you to create, try creating a new form based on it. Then, in a new record, enter a new serial number. What happened?

It auto-generated but when I save, record is not seen anywhere

 

[Gasman]

Why not just set the default value in the table and be done with it?

If it is *always* MOT/T, I see little point in storing it?

 

[Nonnylizing]

Why not just set the default value in the table and be done with it?

If it is *always* MOT/T, I see little point in storing it?

Tell me how please

 

[Gasman]

Look at the Default Value property for the control on the form?, or if you insist on storing it, the table.
If it is just for visual purposes, I'd be doing what theDBguy has shown you and just concatenate it when needed.?

In fact you could just have an unbound control with a control source of ="MOT/T" & [SerialNo]