Custom Auto Number

[goju]

i have 10 names in a combo box, in a third box i would like an auto serial number nut with the initials of the names preceeding the serial number.

so say i have names kevin alder, i need the first serial number to be KA/1001

then if i need to select john Smith then the next number would JS/1002, and so on.

this is way out of my league (Newbie) any help would be appricated

searched the forums but dont understand how to do this.

 

[neileg]

Keep them separate. Hold your initials in one field and the number in another and concatenate them when you need to display the number.

 

[goju]

How do you concatenate them,

sorry newbie

 

[neileg]

In a form you would create a text box with a value of:

=[MyInitialsField] & "/" & [MyAutonumberField]

Or you might do it in a query as a calculated field:

NewField: [MyInitialsField] & "/" & [MyAutonumberField]

 

[goju]

sorry newbie still stuck

it returns #name?

 

[neileg]

I hope you are using the relevant field names, not the examples I used. On reflection, even if you want this in a form, it would be better to place the calculated field in the underlying query and bind your control on the form to this field. (You have based your form on a query and not direct on the table, I hope).

Like this

 

[goju]

ta i think we are close,

however, the number 1001, 1002 and so on is a rolling number the initials KA and JS etc are determind at the input stage so.

i input kevin alder and in the third box below KA/1001 automatically shows up.

then i enter on a second record John smith JS/1002 shows up then i enter Kevin Alder KA/1003 on a third record shows up, and so.

the initials are not linked to the serial number as it is a rolling number.

sorry very new.

thanks for any help

 

[neileg]

Yes, the design I gave you will produce this. Here's a more developed version that shows what you want, I think.

 

[goju]

i think you done it

ill let you knnow how i get on

many thanks

think i might need your knowledge later on

thanks very much.

can iget you on e mail

 

[Mile-O]

can iget you on e mail

What's wrong with the forum?

By elevating help to the level of private communication you are proposing consultancy. That usually comes at a price.

 

[goju]

See attached,

a little lost

any ideas

again thanks for your help

 

[neileg]

You're right, you're lost!

The reason you are getting a NAME# error is in your formula:
=[IKGA Alpha] & "/" & [IKGA Numeric]
Neither [IKGA Alpha] or [IKGA Numeric] are fields in your underlying table. Where is Access supposed to find these?

Few other points:
In naming your objects (tables, forms etc) it halps to adopt a naming convention that identifies the object type, eg tblMyTable, frmMyForm
Also you should not include either spaces or punctuation in object names

You should base your forms on a query, not a table, even if the data in the query is identical in your query to the table. There are lots of things you can do in a query that you can't do at table level (like concatenating fields!)

Your main table seems to include duplications in terms of fields. If you hold your instructors in a separate table, then you don't hold both the InstructorID and the Instructor name in the main table. By holding only the InstructorID, you can change the name (eg when a lady gets married or divorced) and the single change is then related to all records in your main table because the ID remains the same.

 

[goju]

taken your advise
created a new form based on a query

now totally lost any suggestions

 

[goju]

sorry cheated used your database

im hopefully going to build on that

many thanks

will have more questions shortly

 

[neileg]

sorry cheated used your database
im hopefully going to build on that
many thanks
will have more questions shortly

You can't steal what has been given willingly!

I look forward to the questions, I think