Question how to drag autonumber like on excel

[JamesMcS]

Well, if you have to use an ID consiting of ABC and a number, I think the easiest and quickest thing to do here is just to create an update query that updates the ABC ID field to the default value we've been talking about in this thread, i.e. "ABC/00" & [ListID]. That should put the correct ID value in your field, then it'll be permanently there with each record and you can do whatever you want with it.

But Brian's right - if you don't specifically require the ABC in the ID, i.e. it's not explicity used somewhere else as a unique ID, you might as well just use the autonumber as the unique ID. Then if you want to use ABCxxx in a report, just make that text box a concatenation like we've been talking about earlier in the thread.

 

[Brianwarnock]

Not sure what your continuing problem is.
The attached shows a Table with an alphaid field
the form shows how to show a totalid both using that field and just concatenating the text "abc/" to Listid.

The search form and query do not use the alphaid field as I don't believe it necessary to have the constant alpha id carried in the table, but as James said, if you wish to do that then create an update query to put it in a new field in the table having changed the table design.

Brian

 

[aadil7]

everything youve said is what i want to do but there is one main i dont know how to do any of the 2 options available!
i mean you (James) walked me through one of the options and nothing came up in the results! so im not sure if ive got something wrong.
and ive not tried the other one, infact ive not even made a query yet! just a form.

 

[gemma-the-husky]

aadil

I appreciate you think you arent getting anywhere

The trouble is, although Access looks like Excel, its a pretty different beast, and there's a series of leaiinng stages to overcome.

One reason for this is that the data in every row of each column in an access table HAS to be of a similar (and single type), which isnt the case in Excel.

Also every row in Access is independent of every other row, and you dont have calculations in Access fields, just values - which again makes using it completely different.

You will get a series of light-buld/eureka moments, as you realise how it all bolts together - and I am sure this is one of them.

 

[aadil7]

actually to be honest i think im getting there but there is just one screw which is not tight enough which is making it all fall through!
thats why i think if someone takes me through a little step by step it would be good and much much appreciated

 

[JamesMcS]

Perfection is always one step away....

How far have you got now then? Still no result at all? Honestly, I'd go back to posts number 19-21 and just do that. Unless you've got the ABC number in two tables that you need to link together, there's not much point storing it anywhere.

If the ABCxxx field is definitely one that you need to link one or more tables together, use the update method. Create your table, leaving the ABC-Id field blank. Create a button in your form that the user will click when finished editing the record. In the code of the button you need to put some SQL code. Create an update query based upon this table, make it update the ABC-ID field to "ABC00/" & [ListID] and switch to SQL view. Copy the SQL code.

Go back to your button on your form. In the on click event you need to select "code builder" which opens the VBa editor. In there, type the following:

Docmd.runsql " **then paste the SQL from earlier in here, so it's all on the one line** "

Then when you click the button it will run a query to update the ABC ID field to "ABC/00" & ListID as per the SQL code. Job done.

 

[aadil7]

umm thanks for that but im not linking no tables together i just want the ID to show ABC/ at the front of the ID number which has already been issued by the autonumber!
and thanks for all your time and effort james!

 

[JamesMcS]

You should be linking tables together in Relationships.... have a google

In the meantime, go with the update method. That will give you what you need.

And no worries, this forum has helped me enough in the past!

 

[aadil7]

i dont have no relationships actually going on its just one big table!
do you think thats the reason that it wont concatenate??

 

[JamesMcS]

No, that's not the problem with that... When we were talking about default values earlier, it was more to do with unbound text boxes on your form - so sorry about that. Really the answer is, if you need to link the field (which you don't) then go for the update method above, if not and you only want the ABC ID to appear in reports, use the concatenation method above, i.e. just put ="ABC/00" & [ListID] as the control source for your text box. That should definitely work.

 

[Brianwarnock]

Before i get involved again I want you to answer some questions.

1) Did you look at my attachment?
2) Did you look at the design view of the queries and forms and the properties of the forms' controls?

If the answer is no exit and do that.
If Yes

3) What did you not understand?
4) Do see now why you do not need to carry the constant ABC/ in your table as you can generate it easily when required, but hey you have been told how to do it if you want.

Brian

 

[aadil7]

ok thanks guys now ive got another little issue!
now that ive got that working it says "ABC/23" and the number at ListID
the list ID goes on and on into its hundreds!
is there a way of formatting or coding or validating this so it places 0's to not make it look odd. e.g: ABC/23001, ABC/23021 - this is so they all look consistant instead of what it looks like right now e.g: ABC/231, ABC/2321

is it possible to have 0's to fill the void?

kind regards

 

[JamesMcS]

Yep - use right("0000" & [listid],3) - this will take the right hand 3 characters from your listid with 4 zeros on the left. customise as necessary!

 

[aadil7]

thanks and where does this actually go? does it go in the control source after the "ABC/" & [ListID]? or in validation

 

[JamesMcS]

I'd forget validation for now bud - what you want is "ABC/" & right("0000" & [listid],3)

So if listid is 1 you'd get ABC/001 and so on.

For educational puropses you could look up left and mid functions in the help file too....

 

[aadil7]

will do mate & rep added

 

[aadil7]

hey umm it seems to be coming up with some query error is this because i havent made no queries or relationships?

 

[JamesMcS]

You should try and be a bit more specific... what's the error exactly as it appears?

 

[aadil7]

well it only showed the error once and i closed and ignored it thinking it was nothing and now when i try putting the coding into the box it just reverts back so:
i input "ABC/" & right("0000" &[ListID],3)
and it just keeps reverting straight back to "ABC/" & [ListID]

 

[JamesMcS]

Did you put a = in front of the statement?