registration database

[hodagh]

Hi,
I have a database which has my student information(name,family,field of study,...)
to take exam I have to assign a unique number to each of them based on their field of study, for example chemistry students between 1000 and 2000, physics student between 2000 and 3000 , and so on
(and these IDs should be between 1000 and 999999 )
anyone knows how should I do it? (I don't know any programming in access!!)

[June7]

You want to programatically create unique ID. This will require VBA. Why is this necessary? Why not just allow autonumber and another field for the field of study?

Common topic. Search forum or Google

Review https://www.accessforums.net/access/...ers-21361.html

[hodagh]

I was told that each field of study should have its own student number
so it's just possible with VBA??
I wanted to do with nested if like:
IIf([field of study]="chemistry", =Int ((2000 - 1000 + 1) * Rnd + 1000),IIf([field of study]="math",= Int ((3000 - 2000 + 1) * Rnd + 2000))) and so on
but it doesn't accept my formula! have any idea??

[SteveH2508]

What happens when more than 1000 students are taking Chemistry?

Is this a real system or a school project?

If it is a real system then you need to redesign your table structure - if it is a school project your exam board or teacher needs to do some research. (I am a teacher who has suffered exam board stupidity with incorrect data models and specifications).

[hodagh]

What happens when more than 1000 students are taking Chemistry?

Is this a real system or a school project?

If it is a real system then you need to redesign your table structure - if it is a school project your exam board or teacher needs to do some research. (I am a teacher who has suffered exam board stupidity with incorrect data models and specifications).

yeah this is the real system and they want to use it in lower scale systems
so is the nested if in this way correct?

to have a validation rule for student ID, I'm trying to say [student ID] Between 1000 And 2000 but it doesnt accept . what is the correct way for "Between And"??
Thanks in Advance

[June7]

Code is required to save generated ID. I use only VBA, don't know if macro can do this.

Remove the = sign from in front of each Int.

Why the 2000-1000, 3000-2000 terms. They just evaluate to 1000.

How many fields of study? Nested IIf is supposed to have a limit of 7 levels.

BETWEEN AND won't work in IIf expression.

Use <= and >=, like:

x >= And x<=

You are trying to use Rnd() to generate a unique identifer? I would not trust Rnd() to not duplicate.

Did you review the referenced link? And did you consider Steve's question about hitting ceiling of each study field number set?

[hodagh]

Code is required to save genered ID. I use only VBA, don't know if macro can do this.

Remove the = sign from in front of each Int.

Why the 2000-1000, 3000-2000 terms. They just evaluate to 1000.

How many fields of study? Nested IIf is supposed to have a limit of 7 levels.

BETWEEN AND won't work in IIf expression.

Use <= and >=, like:

x >= And x<=

You are trying to use Rnd() to generate a unique identifer? I would not trust Rnd() to not duplicate.

Did you review the referenced link? And did you consider Steve's question about hitting ceiling of each study field number set?

yes I read it but I don't think that's gonna work for me . so I found this VBA code and works fine but I don't know how to connect it to my student ID field in the database . how do I do it ??


Sub GenerateRandomUnique() Dim lng As Long
Const lngMax As Long = 100
Const lngMin As Long = 1
With CreateObject("Scripting.Dictionary")
Do While .Count <= lngMax - lngMin 'removed the greater than symbol
lng = Rnd * (lngMax - lngMin) + lngMin 'removed the +1
.Item(lng) = Empty
Loop
MsgBox Join(.Keys, " ")
End With
End Sub

http://www.excelfox.com/forum/f13/ra...excel-vba-608/

[June7]

Here is how I manage generating unique ID: http://forums.aspfree.com/microsoft-...ta-403208.html