Allocate a uniqueID on form load

[shaunacol]

I am trying to allocate the primary key/uniqueID of my tbl_Client when a form loads. The reason is that there are multiple locations the database will be in stand alone mode and it will be easier to pull data back together again if I don't have to worry about duplicate ID issues.

In the On Load event of my form I have the syntax below but it does not work (ClientID is just blank). I am not too good at coding so I have made a query that shows the current person logged on and their location and made a form based on this data. I have added that as a subform to the form I am loading with the event procedure in it. I have also created a field in tbl_Client called ClientID2 which is an autonumber. I hope this makes sense.



Private Sub Form_Load()
Me.ClientID = Forms!Frm_ClientID!StaffLocationOn & Me.ClientID
End Sub

Any help appreciated thanks

[shaunacol]

sorry mistype! code is
Private Sub Form_Load()
Me.ClientID = Forms!Frm_ClientID!StaffLocationOn & Me.ClientID2
End Sub

[June7]

Generating custom unique identifier is a common topic. Search forum for MANY threads.

Review https://www.accessforums.net/access/...ice-53750.html

[orange]

For uniqueness set up 2 fields

-a location field to identify each different location
-an autonumber field

The records in the table will be unique based on the combination of these 2 fields. This combination will be a composite Primary key.

If you need to identify multiple users at each location, then add a userId for each user and include that in the PK.

The uniqueness is a property of the table. The PK is unique.

You can set this all up before the user/client ever touches the database.
Hopefully you name these separate databases with unique names also.

Autonumber field alone could have duplicates based on inputs at different locations. The combination of LocationID and autonumber will prevent duplicates and allow for data reconciliation/combination later.

Good luck.

[shaunacol]

A composite primary key, of course! My only issue now is how to allocate the location into the composite key field. When a user logs on the database logs their location is logged in tbl_session. Can I somehow pull it out from there? I need something like this but of course it doesn't work,,,,
SELECT Tbl_Session.StaffLocationOn, Tbl_Session.TimeOut
FROM Tbl_Session
WHERE (((Tbl_Session.TimeOut) Is Null));

[orange]

If you provide a separate copy of the "stand alone database" to each location, you can predefine/default the locationID in each copy. Or have a small routine to do so.

Composite Primary Key

http://www.teach-ict.com/as_a2_ict_n...niweb/pg11.htm

[shaunacol]

Yeah that's an option. Just seems a shame that im collecting their location on log on but cant use it Just fiddling with it now but cant get it to work so maybe I have to go with your suggestion. Many thanks

[shaunacol]

I think I have solved it! There is only one form where a new customer can be created so I can set the default of the composite location PK to reference a query I set up for another reason. Default value looks like this and it works...
=DLookUp("[StaffLocationOn]","Qry_CurrentSession")
Thanks for everyones help

[orange]

You could do it at logon but there is no need.

If you have an admin utility that only you run, you could use it to set the LocationID (or name for that specific database). You wouldn't let users run this since, from experience, users don't necessarily follow instructions or follow instructions as you intended.

If you do collect something in a first time, once only logon, you could use SQL (or vba and SQL) to alter the default value of a field in the Table.

example:

ALTER TABLE Persons
ALTER COLUMN Location SET DEFAULT 'Chicago Office'

I recommend that you insert the Location value you want for the office at which this copy of the database will be used. It's the default value property of the field, you can set during Table design.