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?

 

[Pat Hartman]

You'll have to do the insert with VBA rather than an append query in order to prompt and generate the appropriate numbers.

I recommend two columns also. You can concatenate the two fields in a query whenever you need them together for a barcode.

 

[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?

 

[Pat Hartman]

You can prompt the user for input with an InputBox. Search for posts regarding autonumbering in queries or ranking. I haven't looked at the sample database recently so I'm not sure that the ranking samples can be "seeded" but take a look. Microsoft has a number of sample databases available for download. Search for the one for queries. This Post by me may have the link you need.

 

[elliotth123]

Help!

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

 

[Pat Hartman]

I see the link to the queries sample db there. Did you download it and look at it?

 

[Pat Hartman]

In fact, here's the list of query examples.

Database of Access 2000 sample queries
The sample queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form, report, or data access page.) in this database demonstrate a number of querying techniques, including how to rank records within a query, how to group one set of records and sort on another, and how to use custom functions in queries.

Download the sample query database

Want to make sure this download is right for you? Here's a list of the sample queries.

Create a make-table query with a union query 
Change column headings in a crosstab query using code 
Create a crosstab query with multiple fields 
Group column headings in a crosstab query 
Use IIF() in crosstab queries to limit column headings 
Create a join on a range of values 
Create a parameter query that accepts a list of values 
Filter a query using an option group 
Return all records when a query parameter is blank 
Sort a query using an option group 
Sort and group in a query using a portion of a field's value 
Use a custom function in a query 
Use a query to filter for unique data 
Use a subquery as a criterion in a Select query 
Use the DLookup() function inside a query 
Query by form using Dynamic QueryDef 
Refer to a field in the previous or next record 
Create an SQL pass-through query using code 
Simulate parameters in an SQL pass-through query 
Create a union query 
Create running totals in a query 
Group on one field and sort on another in a query 
Extract the second-highest value from a table using a query 
Rank records within a query 
How to use the sample queries database

When you open the sample queries database, a switchboard appears. You can view all the sample queries or sort them by category. When you select a query, the switchboard displays a list of the tables and other database objects that the query uses, plus a link to a corresponding knowledge base article, if one exists. To use a query, select it and click Show Example.

 

[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