Adding a Auto increment Text/Number

[elliotth123]

I have a daabase that I am importing via excel. I want the entries to be numbered
ex: MT0001
MT0002
etc....

I would like it to promt the user for the last number(or next number in sequence) entered, then fill in the blank records with the next increment number.
The prefix will also change to so eventually the user would enter into the prompt RD0001. then autofill the 140 imported records with RD0002, rd0003... etc....

I can't really make seperate fields because the MT0001 number will become a barcode and putting them seperate causes many issues.

can this be done? Any help would be great I am still a beginner but slowly learning!
Thanks!

 

[FLabrecque]

This could be possible, through VBA. I'm not sure this would be the fastest or more normalize, but it should get the job done.

1. Import the Excel spreadsheet into Access with an autoincrement ID number (supposing your Excel file doesn't contain the MT001, MT002).
2. Add a column to the imported table.
3. Update the column, set NEWCOL = 'MT' & format(ID,'0000').

Et voila!

 

[FLabrecque]

Another possiblity could be to simply format it in the queries. For example:

SELECT 'MT' & Format(ID, '0000') as MyID
FROM ...

 

[elliotth123]

Help!

I kind of understand what you are saying but, this won't allow it to prompt the user for the prefix.
What about an update query that will take the first blank record and insert an id (it will get this via a txt box on a form). Then increment the number portion until the null boxes are gone. Then for the next section that gets imported it will ask again for the first number to input tehn do the same increment the number that has been input by the user till all null vales are full.
Is this possible? ANy ideas how?

 

[elliotth123]

So far,....

Here is what I have done with it so far:
I created 2 more fields IDSub and IDNum.
Then I created an update querey and form that will prompt the user for the prefix (IDSub) and fill in the newly imported records IDSub field with the prefix.
I have also created an update query to go through an make the ID number equal IDSub & IDNum.
My only question is now....how can I prompt the user to input a number, go to the first blank field under IDNum and insert that num. Then increment that number for the rest of the records that are alreday in existance.

Any ideas? I am trying!!!! Thanks everyone!

 

[elliotth123]

Anyone?

Anyone on this last post?

 

[elliotth123]

Help!

Nope.... nothin there helpful unfortunatly... anyone?

 

[elliotth123]

Help!

Yes I did download it and go through the queries but it was not was I was looking for, I still need a way of fiinding the first blank field of a column , insert a number (taken from an input form) and then increment that number by one till all blank fields (1 for each record) is complete.

 

[mjain]

Adding a Auto increment Text/Number in word

 

[GraceGrady]

I had a similar situation.

I created a table to hold the number (or next number), when I grab the number, I increment the table.

Public Function GetNextNumber() As Variant
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyNumbers")

With rs
rs.MoveFirst
GetNextNumber = "RS-" & rs!MyNextNumb1
rs.Edit
rs!MyNextNumb1 = rs!MyNextNumb1 + 1
rs.Update
End With
Set db = Nothing
Set rs = Nothing

End Function