Add new record or select existing

[Taresa]

First Access 2013 form being created. (i may not use proper termonology but trying).

Tables:
tbl_Clients (PK=clientID auto#)-holds details of each client (Lname/Fname/MName/addr/preferences etc)
tbl_Encounters (PK=encounterID auto#)-could have many encounters for each client.
tbl_followup (PK=followupID auto#)-some encounters need followup, some don't, some encounters have many followups.

Have a form linking to tbl_Client, with subform to tbl_encounters (going to create another form for followups-haven't got that far yet). I would like to try to avoid duplicate clients in the tbl_Client, so when the form is opened they could look for client if not there add new. However for the existing client if say the address changed i want them to be able to do that. (i could get rid of dups in a query/report based on matching certain fields etc, however i'd like to avoid it in the first place)

How do i do this?
What is the correct terminology for what I'm trying to ask? (lol-bad question i know!) I think this is some kind of lookup but having a rough time finding info I must be using wrong words in my searches.

 

[maw230]

I would like to try to avoid duplicate clients in the tbl_Client, so when the form is opened they could look for client if not there add new.

Have a look at this even though it's for older versions of Access. The principle remains the same. Create a multi field index, so duplicate values can't be entered.

Set the "Allow Edits" Property of the form to "Yes" to allow users to edit values such as the address. You can lock fields that you don't want edited if needed.

 

[burrina]

Here is just on example:

If (DCount("*", "[tbl_Clients]", "[clientID]<>" & Nz([clientID], 0) & " And [FieldNameToValidateHere] = '" & Replace(Nz([Auto_Title0]), "'", "''") & "'") > 0) Then
Call MsgBox("Duplicate Client Was Found,Please Edit Or Cancel.", vbExclamation, Application.Name)
Me.Undo
End If

Auto_Title0 ' Control Source =IIf(Nz([FieldNameToValidateHere] & " " & [FieldNameToValidateHere])="","Blank Record",[FieldNameToValidateHere])

HTH

 

[Taresa]

@Maw230 and @burrina

thanks to you both-exactly what i was looking for-haven't tried anything yet but can tell this will do the trick!

This forum has been terrific, it's so nice that strangers are willing to reach out and give the time to assist others! Only hope i can one day return the favor!

 

[Taresa]

I'm finally getting back to this, i am about to my deadline and totally stumped. I tried the multifield indexing, isn't making a difference. I open the form and it provides the only record in the table, can't add any other client to the tbl_client from the form. and when i try time in any of the fields on the form it overwrites the data. :banghead:

I'm not sure where to really put the other quotes from Burrina, i did see the screen shot. I'm just not getting it, this is the last thing i need to get working to get some users in test mode to see if the other fields are to the staffs liking. (meeting is 10/20/14 2pm est time)

i can't seem to get my .accbd file small enough to upload. I am lost as to where to even start. Please if anyone has time, i could sure use some help.

 

[Taresa]

I found a post at another site that said to change Data Entry to Yes in Properties dialog that worked, form comes up blank. But now won't let me select a current client in table, only add new. I put a cmbFindExistingClient but when i click it, it errors saying "The command or action 'Find' isn't available now. I really don't know anything about commands.

Should i have made the main form from the encounters table and had the clients in the subform???
I am not as stupid as this is making me feel-really, lol, I just don't know where to put things in Access.

Anyone please any ideas? specifics would be most appreciated. If you can't help me, right now, can you give me a contact name that i could pay to assist me immediately???? I'm sure someone with real knowledge will figure this out in a few minutes urggggg