Matching Values of Two Fields in a Table using two fields on a form

[mfaisalwarraich]

Hi,

I have a table called mainTable. two fields of this table are SNO which is text and Relation which is also text.

there are more than 10 fields in this table.

i made a small form called frm_Check with only two fields SNO and Relation using mainTable.

now when i enter values in SNO and in Relation field and after i exit from relation field i want to run an event which should check for SNO and Relation field values in the mainTable. If the values SNO and Relation of frm_Check already exists in mainTable's fields SNO and Relation then an msgBox should appear and the values should not store in table mainTable. what should i do to get this? please tell me.

[nico5038]

When the combination of these two fields need to be unique, you need to specify an INDEX (See Index button) for these fields with the option "Duplicates (No)".

When the fields are always used as the unique key you can just select both fields and press the button with the yellow key. This makes both fields to be the unique identifier for the table and Access will alarm the user when (s)he tries to enter a duplicate combination.

Nic;o)

[mfaisalwarraich]

When the combination of these two fields need to be unique, you need to specify an INDEX (See Index button) for these fields with the option "Duplicates (No)".

When the fields are always used as the unique key you can just select both fields and press the button with the yellow key. This makes both fields to be the unique identifier for the table and Access will alarm the user when (s)he tries to enter a duplicate combination.

Nic;o)

Thank You Friend for your reply. but problem here is that these values can have only one duplicate field i.e. SNO. this field can be duplicated but the other field is Relation field which is if "Self" then i should get a message displaying "this entry exists".

i just want to check values before insertion into a table. this can be done by two unbound text boxes on a form. but problem here is this i dont know how to match two values of two fields in a table.

please tell me how i can check values from unbound text boxes in a form with a command button. if command button is clicked, the values entered in both text boxes should be checked for values in table. if values matched then msgbox should appear entry already exist if not then a form should open and these values should be entered in the fields of SNO and Relation in table.

thank u.

[nico5038]

For checking values in a table you can use the Dlookup() function.
The syntax is: Dlookup(<fieldname to return>,<tablename>,<WHERE clause>)
For checking two fields (assume fieldtext and fieldnum in table tblX) use:

Dlookup("Field1","tblX","Fieldtext='" & Me.Fieldtext & "' and Fieldnum=" & Me.Fieldnum)
 

Pay attention to the single quotes around the Me.Fieldtext that should be present as a formcontrol.

Getting the idea ?

Nic;o)

[mfaisalwarraich]

thank u for your reply. and i appologize to repeat question again and again.

well DLookup only looks for first occurrence of a value in a table or query. as i told u one of the field named SNO can have duplicate entries.

if query is giving values as under:

(SNO and Relation are two different fields, both are text data types)
1. SNO Relation
2. 5946 Mother
3. 1003 Self
4. 5946 Self
5. 5236 Wife
6. 5946 Wife

now in the above case, i want to check for 4th case. where SNO = 5946 and Relation=Self. similarly each SNO can't have duplicate Self entry.

please tell me how i can have this check. thank you Sir.

[nico5038]

No problem, glad you understand that concentrating the problem to one post gives advantages to both you and the experts :-)

Your requirement is to verify that SNO = 5946 and Relation=Self only occurs once. (Is unique).
To test this my Dlookup checks for both fields in the third parameter (the WHERE part):

Dlookup("SNO","mainTable","SNO='" & Me.txtSNO & "' and Relation=" & Me.txtRelation)

When the value returned equals "Null" nothing will be found, else the SNO and Relation will exist. So with the test:

IF IsNull(Dlooup(... , ... , ...)) then
   ' not found, you can add
else
   ' found, handle error e.g. with msgbox
endif
 

Getting the idea ?

Nic;o)