Question how to drag autonumber like on excel

[aadil7]

hi
i am trying to get my database to autonumber the field like in excel.
in excel you just have to drag a little point on the end of the cell and it will auto number till wherever you want.
i dont know how to do this on access
another thing is that there are letters aswell as numbers included for example: ABC32001, ABC32002, ABC32003...
kind regards

 

[Galaxiom]

Access does not support this ability. Database tables are very different from a spreadsheet. Records in tables are not intrinsically ordered.

You need to increment the number in the query used to append new records or use an autonumber field. Note that there is no control over autonumbering so a full sequenceis not guaranteed. Where a number is seen by the users it is best to control its incrementing.

If all the values start with ABC then just record the numeric part in the table. For display simply concatenate the prefix to the stored number.

 

[aadil7]

Access does not support this ability. Database tables are very different from a spreadsheet. Records in tables are not intrinsically ordered.

You need to increment the number in the query used to append new records or use an autonumber field. Note that there is no control over autonumbering so a full sequenceis not guaranteed. Where a number is seen by the users it is best to control its incrementing.

If all the values start with ABC then just record the numeric part in the table. For display simply concatenate the prefix to the stored number.

sorry for not knowing much about access but how do you concatenate cells because if i could concatenate then i could just add what ever text i wanted to onto the start of the autonumber field i have.

and

if i concatenate then i go to search for a record, will i have to add just the number or would i have to add the full ID given to it (number and concatenated letters)
e.g.
i want to find a Mr Jones
Mr Jones is given the number 001
if i search for 001 i know Mr Jones will appear
i want to add extra because there is a perticular way i want their ID number to look like
so.. if i concatenate and add ABC/001
then i search for ABC/001 would it work?

kind regards and thank you

 

[Galaxiom]

"sometext" & [fieldname]

If you concatenate on the form then you just search for the number in the field.

Your search box could be made to display the full text but search on the number alone. However one would normally put the prefix section outside the textbox so people understood they only needed to enter the numeric part.

It could be made to recognise if the user had included the alpha characters and strip them for the search.

 

[gemma-the-husky]

one reason why you dont do this in access, is that you (effectively) have to paste rows, one at a time - and if you want some fields to be automatically set to specific values, you have to do this with some code.

although an access data sheet looks like an excel sheet, its rather a different animal.

Much better at many things, harder to manipulate for some things - because of the nature of what it is.

 

[aadil7]

thanks for the reply
i used -> "sometext" & [fieldname]
in the format box for the field name
and it returns some different format

am i actually entering it in the right box or is there a box that i am missing to concatemate?
kind regards & everything is much appreciated

 

[aadil7]

im starting to think it goes in the validation rule box am i right?

 

[JamesMcS]

Not really - the concatenation is only to stick two bits of data together. In the example, if the value of [fieldname] was "hello", "sometext" & [fieldname] would return "sometexthello" (note the lack of a space between the two.

The format property is to make Access disaply data in a particular way, so format being short date would make it display "1/1/1990" as "01/01/1990" etc.

Validation is used to force the user to enter data in a particular way, i.e. length being no more than 10 characters or whatever.

Your question about searching is right - if the ID was ABC/001, searching for 001 wouldn't work. However searching for *001 would.

 

[JamesMcS]

So, if you want to create a unique ID that isn't an autonumber, I think the best way would be to have the autonumber field anyway, then create your ID field, and set the default value to ="ABC/"&[Autonumber Field]... I think that's right anyway (you might need to take the equals off, I can't remember)

 

[aadil7]

ok so what ive just done is in the form i have made i have edited the control source of the field i want, and changed it to "ABC/" & [ListID]
access automatically added an = in front of everything so it turned out to be: ="ABC/" & [listID]

when i saved it it came up with a couple of errors: invalid control property: control source and circular reference

im not really sure how to get around this

regards

 

[JamesMcS]

Almost - not the control source, that's telling access what field in your table you want the text box to look at.

In your table, you've got your autonumber field and your ID number field set up as above yeah? So the default value property, for your ID number field, in your table's design view, is ="ABC/00"&[Autonumber field name].

Then, go back to your form, create a text box, and set its control source to the name of your ID number field. Then, as you create new records, the autonumber value will go up, and the text box should show "ABC/001", "ABC/002".

Does that work?

 

[aadil7]

adding to what ive wrote above
ive just made another field called StartID and only written ABC/ in the cells of that field
then i went back to the report ive created and tried concatenating StartID into ListID by using: [StartID] &[ListID]
again an = sign was added

this didnt work also

 

[JamesMcS]

That'll be the circular reference then. You can't tell a text box to be itself plus something else, that would just recur to infinity and blow your PC up.

So the default value stuff in my last pose didn't work?

 

[aadil7]

well im trying that right now but i dont understand what you mean by default value property

let me explain what ive got fully

i have a ListID which includes numbers, these numbers are auto numbers and this field is my primary key

i now want to add some text to the start of each cell in this field but there are too many records for me to do it 1 by 1

so now my next best option is to concatenate as youve said

this is as far as ive got now when i go into design view i get confused on what do actually do because im not really that good at access
could you guide me from here please now you know my situation
thanks a million

 

[JamesMcS]

OK you can't put anything in front of an autonumber field. They're uneditable to ensure that they're always unique.

Have a look at the screenshot, that's what I mean by default value.

So what's I'm saying is to leave your autonumber field as is. Create another field called IDNumber or something, data type text, and in the default value property box in table design, type ="ABC/00" &[ListID]

Then in your form, create a text box and set the controlsource property to IDNumber.

That should work....

 

[aadil7]

its saying this error:
the database engine does not recognise either the field 'ListID' in a validation expression, or the default value in the table 'client database'

do you know anything about this?

 

[JamesMcS]

Don't put anything in your validation rule box in table design.

If it still comes up with a problem specifically about default values, take it out of the table design and put it in the default value property of the text box in your form.

 

[aadil7]

well what ive done so far is ive created another text box on my form for the new field startID (this does not contain no info in any cell) and then i typed ="ABC/00" & [ListID] in default value in the form

when i did this and checked to see if it works, nothing actually came up in the box at all!
im not sure now how to get around this

 

[Brianwarnock]

Not sure what you are tryng to achieve here, if as appears the alpha part of the TotalID is fixed then you can just concatenate it at anytime that you wish to display it, but otherwise you do not need it.

If it will be used by people searching as it is carried on documents then that is still not a problem as the query will contain the alias field
Totalid:"ABC/" & [listid] for the criteria to search against.
I am sure that you need a space between the & and the field name but AWF wont show that

Brian

 

[aadil7]

how do you actually fix the alpha part of the totalID and how do you actually use this as for everything else your correct
im just not any good as access so a description of how to do it/make it work would be nice, thank you
ive been trying to get this done for a couple of days now and its fustrating!