Can any one tell me how to do a sequential number in access?

[katie365]

Hi,

I'm new to this, so I need it as easy as possible....

Can any one tell me how to do a sequential number in access?



For my exam I have to learn how to do Sequential numbers in access but I do not know how to do them, i have been researching them on the internet but i still don't understand it, i have also attempted to add it to a database but still its not working!

Can somone help my please help me with this?

Ok so what I have is a Form and I need to generate a student ID (I already have this field by the way) for the students. The student ID must consist of the first three letters of their surname and a sequential number one higher than the last.So how can I do this please....help me!!!


Step by step instructions please!

Thanks!

[Robeen]

1. Do you have anything in your StudentID field right now or is it empty?

[orange]

Double posted/ cross posted
see https://www.accessforums.net/showthr...07-Auto-Number

[katie365]

Yes it already has student id's in the fields, its not empty.

[orange]

Katie365,

Google can be your friend.

Try this

http://www.599cd.com/tips/access/inc...r-own-counter/

Good luck with your project

[katie365]

Here is my database:

Database.zip

[orange]

Did you look at the tutorial/example? http://www.599cd.com/tips/access/inc...r-own-counter/

Do you have a specific question?

Did you get an error message?

What are we to do with your database?

[Robeen]

Hi Katie!

This is probably not the most elegant way of doing this - but since you've got your numeric values mixed in with the first 3 of your Student Surname in your StudentID field . . . this is what I came up with.
If you had your numeric ID in a separate field, you could use the DMAX() function in your After Update procedure and not have to mess with slightly more complex VBA . . .
Still . . . give this a shot.

1. Create a New Module.
2 Type in Option Explicit on top and then paste this function in there:

Function Highest_ID() As Integer
'Declare Variables:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL, strStudentID As String
Dim intHighID, intID As Integer

'If there is an error - go to the error handling routine at bottom.
On Error GoTo Error_Handle
   
'Set/initialize variables:
Set db = CurrentDb
strSQL = "Select * From [tbl_StudentDetails]"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
intHighID = 0

With rs

'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF

'Get the 4-digit value from current StudentID and convert it to an integer.
strStudentID = rs![StudentID]
intID = CInt(Right(strStudentID, 4))

'If the number in the current record is the higher than the previous one - then make this the 'High ID'.
If intID > intHighID Then
    intHighID = intID
End If
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
        
'This returns the highest existing numeric value to the calling routine.
Highest_ID = intHighID
        
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
    
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

3. Select the StudentSurname text box, click in the Property Sheet - Event - After Update row - [...].
4. When prompted - choose 'Code Builder'.
5. Paste this between the Private Sub StudentSurname_AfterUpdate() . . . and the End Sub lines:

Dim intNewID As Integer
intNewID = Highest_ID + 1
Me.StudentID = Left(StudentSurname, 3) & intNewID

Now . . . when you create a new record and tab off the StudentSurname field after typing in the new surname, you will see the StudentID for the new entry appear in the StudentID field on the Form.

Let us know if you have any problems.
All the best!!

[katie365]

Hi Robeen,

Here are all the data files, exam paper and mark scheme oh and a sample of what the database should look like, at the moment I am stuck on Activity 2 page 4 where I have to remove duplicates and inport the data etc...

I hope this helps you to be able to help me, thanks soooooooooooooooooooooooo much again for helping me!!!


zip.zip

[orange]

Just a small adjustment on the DIM statements
This

Dim strSQL, strStudentID As String
Dim intHighID, intID As Integer

does not do what you think. In fact it will Dim intHighId and strSQL as Variants.

I think this is what was intended

Dim strSQL As String, strStudentID As String
Dim intHighID As Integer, intID As Integer

Any variable not explicitly dimmed with a data type will be made a Variant.

[Robeen]

Hi Orange,

I have not run into any problems with any of my applications in which I commonly declare multiple variables of the same data type on the same line with the same Dim statement.
In fact, I've made it a practice to go through Modules, Functions, Subs etc and where I have variables of the same type on multiple lines - put them together on the same line [if possible] to reduce the clutter.
It just never made sense to me if I had 20 strings in my code to say 'Dim ... As String' 20 times.
But I had to make sure I wasn't harboring a false notion that what I've been doing has been correct.

I just checked here:
http://msdn.microsoft.com/en-us/libr...(v=vs.80).aspx
to see if I've misunderstood Microsoft [VB, VB.Net & MS Access] syntax rules for declaring variables all these years.

Here are some exerpts from the page:
1.

Multiple Variables.
You can declare several variables in the same declaration statement, specifying the variablename part for each one and following each array name with parentheses. Multiple variables are separated by commas.
Dim lastTime, nextTime, allTimes() As Date

2.

Different Types.
You can specify different data types for different variables by using a separate As clause for each variable you declare. Alternatively, you can declare several variables to be of the same type by using a common As clause. Each variable takes the data type specified in the first As clause encountered after its variablename part.
Dim a, b, c As Single, x, y As Double, i As Integer
'a, b, and c are all Single; x and y are both Double

Am I missing something?

[Robeen]

Katie!

What you should do for your next step is import the data into Access Tables from teh text files.

I looked at the Customer text file and after a quick glance at a few of the customers it seems like the entire rows are duplicated and the the entire row of data for the customer is a duplicate.

That being the case, after you have got Access to import the data into tables from your text files using the Import Text utility - you can try something like this:
1. Create a query for each table containing imported data.
2. Go to View - SQL View.
3. After the first word of the SQL [Select] - type in 'Distinct' so that your SQL looks like this:
Select Distinct Field1, field2, field3 . . . .
4. When you run the query - you should not see duplicates.
5. Now - make this query into a 'Make Table' query.
6. When you run the table next - it will create a new table for you in which there are no duplicates.

I hope this helps!

[orange]

Robeen, re Dim statement:

Where did you see that Ms Access (vba) was included in that syntax? I don't think so.

I have been warned and cautioned in the past re undimmed variables are Variants.

http://www.informit.com/articles/article.aspx?p=339929 This link is old and just touches the subject.

For my own test: I am using Acc2003 on Vista on this machine.

Create a very simple procedure

Sub TestDims()

Dim i as Integer
Dim k,l,m as Long
Dim x as string, p as string

Msgbox "I ran the sub"
End Sub

Turn View Locals on
Use F8 to step through the code
Look at the Locals

I've attached a jpg

[Robeen]

Thanks for the 'heads-up' Orange!
I will follow your instructions.
Appreciate the information!!
Thanks again!

[katie365]

I don't think it worked, this is what I have, help please! Is this right cos I still have duplicates!