Add new record if record does not exist

[leehi82]

Hi there,



I have designed an activity log which logs all activities done by my team, when they enter a users UUID (unique number) that number will then pull up the users details including all the relevent information i.e places of work, job roles etc.

What i would like is that if a UUID is entered but the user does not already exist on the database then a popup to create a new user appears creating a new record? I hope i'm making sense!

Could someone please offem me a bit of help as it's driving me nuts!

Attached is screenshot, the 'registered user' form is a subform which shows you the details of a user when the UUID is entered. P.s that subform will only appear when the end user selects the relevent job type such as 'add position to user'

Any help would be greatly appreciated. Thank you!

[orange]

What exactly is UUID - purpose, data type, how does a new value get entered, etc?

You could use an autonumber field -provided by Access and unique.
If there is a user managed field(s), you could check for DCount() >0.
If 0 create a new record; if > 0 then that record/values already exist.

[leehi82]

The UUID is a users smartcard number, i have around 30,000 users in my area and We get smartcard users coming from different areas of the country that are not already in our database so they need to be added as new user.

[orange]

 If DCount("*", "yourTableName", "UUID= '" &UUIDEntered & "'") > 0 Then
  msgBox "Record exists with UUID:  '" & UUIDEntered & "'"
else
  msgBox "Does Not exist - so add new record to your table for this UUID " 
              'Routine to validate and add new record would go here
End if

This is the general approach.

[leehi82]

Thank you so much for your reply! At the risk of sounding a bit daft, i would like a message to appear that says 'user not found, would you like to create a new user?' and when the click yes the create new user form appears. Does that sound right?

attached is a test copy of the database if anyone could have a play around with it and give me a helping hand. All data has been removed apart from a test user with the UUID of 883918982017, the form in question is the activity log and the form to create a new user is called frmCreateUsers.

The tables are usually hosted on an SQL server not that that should make any difference here.

RA Database local.zip

[orange]

I looked at you relationships?? No relationships.
Most people will use vba and/or built in events and avoid embedded macros.

[leehi82]

I looked at you relationships?? No relationships.
Most people will use vba and/or built in events and avoid embedded macros.

When I originally built the database I did set up the relationships but then the relationships disappeared when I migrated them to the sql server.

[Micron]

i would like a message to appear that says 'user not found, would you like to create a new user?

Then alter the msgbox part of what you were given. Have you researched the message box function? Could spoon feed you I suppose, but it's very basic stuff that you should learn.
Only suggestions I'd add to the above is to test (in the control BeforeUpdate event) whether or not the count = 0 then present the message and if >0 do nothing (Exit Sub). If 0 then present the option to add or not. If not, cancel the update.

[orange]

I agree with micron's comments. I was answering stepwise to avoid confusion.
Validation goes in the BeforeUpdate event.

OK. Relationships only relevant in back end.
You should look at some examples of vba and learn a few basics. I wouldn't recommend embedded macros.

Generally speaking, you should avoid names with embedded spaces when using Access.
Perhaps you could tell us more about the application so we understand the "relationships" and purpose of this application.
Do you have list of business rules/facts that you can share?
Where do organizations and positions fit?
Good luck.

[davegri]

Which has to exist first? The User or the Smartcard? That will determine which is parent and child.
Can a user have more than one smartcard? If not, user table and smartcard table can be combined and relationships simplified.

Currently the smartcard table contains almost 24000 records with no duplicates in the UUID field (although allowed via the design?). This could become the combined table (by adding user details).
Currently the Users table contains one record. This makes referential integrity with the smartcard table impossible if Users is the parent table.
Why is the UUID such a large number? 12 digits short text.

[Micron]

I've provided some guidance as a response to a pm, which included an invitation to post the results of implementing said guidance along with the code and any issues it might raise.
In the meantime, I'll say that maybe the UUID control should be a combo from which the smart card number is chosen and the combo Not In List event be used to add the new user. However, it's not clear how many db user records there are - just how many are "in the area". 30K individuals would be too much for a combo.

All that is about the immediate posted issue. However, I think some review of normalization is warranted if there is any interest of fixing design issues.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/