469,272 Members | 1,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,272 developers. It's quick & easy.

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

194 100+
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.
Oct 8 '07 #1
5 2025
nico5038
3,080 Expert 2GB
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)
Oct 8 '07 #2
mfaisalwarraich
194 100+
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.
Oct 9 '07 #3
nico5038
3,080 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. Dlookup("Field1","tblX","Fieldtext='" & Me.Fieldtext & "' and Fieldnum=" & Me.Fieldnum)
  2.  
Pay attention to the single quotes around the Me.Fieldtext that should be present as a formcontrol.

Getting the idea ?

Nic;o)
Oct 9 '07 #4
mfaisalwarraich
194 100+
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.
Oct 10 '07 #5
nico5038
3,080 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. IF IsNull(Dlooup(... , ... , ...)) then
  2.    ' not found, you can add
  3. else
  4.    ' found, handle error e.g. with msgbox
  5. endif
  6.  
Getting the idea ?

Nic;o)
Oct 10 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Viorel | last post: by
10 posts views Thread by Toby Gallier | last post: by
reply views Thread by chandrasekhar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.