Trying to use 3 fields on a form to see if record already exists or not

[Yorksdummy]

Haven't used Access and VBA since 2001 so having problems doing the simplest things. I know what I want to do but can't!!
I have a table with a 3-part primary key. Have a form with 3 unbound controls corresponding to the 3-part table key. After the 3 form fields have been entered I want to search the table to see if a record with the same 3 fields on the table exists. If it does I want to go to another form to enter data for a related table carrying forward the 3 key fields. If it doesn't I want to go to a different form to keep the 3 fields already entered from form 1 and add additional fields to complete the record.Can anyone tell me how to do this please - I'm assuming I need event procedures and VBA

:banghead:

 

[plog]

Yes, you will use VBA. Honestly, you spelled out exactly what you want to occur pretty well, so I don't know exactly what you are asking. It's a matter of turning that english into code. Line by line, do the things in VBA that you wrote out.

If you need specific help, post a specific question and we will be more than happy to guide. Here's some functions you will need:

DCount - to check for duplicates
Docmd.OpenForm - to open a form

 

[Privateer]

As I read this I am wondering what type of data would justify this request? What are you trying to do? You are kind of describing cascading combo boxes, which implies the three fields are primary keys in their own tables but you have them all in the same table. If you want an answer, you won't like it because it is complicated.
Grab the values from the three fields and assign them to variables in that form. Create a criteria string where the field one equals variable one and concatenate that with fields two and three using "AND" You setup a recordset on the table in question and do a findfirst using the criteria string. If nomatch = true, open the FormA else FormB and pass the three variables in the openargs. Personally, I would use global variables, but I wanted to give you the big picture without complications. Perhaps you could let us know a little more about the data because I suspect an easier way of doing this. Good luck.

 

[Yorksdummy]

Hi Plog

It's turning it into code that's the problem!! Can't work out how to find if the record exists or not. i understand the DCount stuff but I don't know how to access the table with the 3 fields in VBA

 

[plog]

DCount is VBA. You access the table with it, using the 3 fields in the criteria argument.

http://www.techonthenet.com/access/functions/domain/dcount.php

 

[Yorksdummy]

Hi Privateer
The table has a composite primary key of Surname/First Name/Postcode which are the 3 fields being input on the form. If the record exists I need to display the details on a form. If not I need an input form with the rest of the input to create the record. Hope this helps

 

[Privateer]

Hey Yorksdummy
Thanks for the feedback; it helps.

My concern was the restriction of hard coding a restriction that does not allow a duplicate. I am a Junior, my Dad and I lived in the same house while I was growing up, so we had the same zip code; would your design allow that? What I do in these circumstances is create a linker table with the primary key from the people table and the PK for the post office, or the zip code in your case. This allows a many to many connection for people with the same name, like me and my Dad, but who are really two different people.

 

[Yorksdummy]

Hi Privateer
The situation you mention doesn't happen in my case - we're not big in the UK on naming sons after fathers etc but that aside my situation is an over-50s club so it's highly unlikely 2 people with same full name at same address - so will go ahead with your initial suggestion and thanks