473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 2316
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

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

Similar topics

2
by: John Wilson | last post by:
Hello friends, I have this dynamic array(shown below) that I need to match to values (1 - 10) that I am returning from the database via DSN connection object. The values I need to match are on...
5
by: MX1 | last post by:
Oh boy, this is kind of a big one. I have a form with several calculated fields on spanning 5 rows. I somehow need to get the field values into a table. Challenge is I don't know how to do this....
6
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join"...
2
by: Viorel | last post by:
Adding new row with default values. In order to insert programmatically a new row into a database table, without direct "INSERT INTO" SQL statement, I use the well-known DataTable.NewRow,...
8
by: mike11d11 | last post by:
I have a dataset with a account table and another table that contains transactions for accounts in the account table. I create a crystal report based off an inner join query of the two tables but...
18
by: Drayno241 | last post by:
I'm working in access 2002. I have three tables : 1- District Data (Student ID, name, grade, etc) 2- Rosters (RRec ID,Campus, Teacher ID) 3- Students on Roster(SRec ID, RRec ID, Student ID) ...
10
by: Toby Gallier | last post by:
Hello! I have a form that is calculating averages as follows: " =(NZ()+Nz()+Nz())/3 " However I need to now adjust for null values , so for example if value2 is null I would then need to...
0
by: chandrasekhar | last post by:
Hi I have developed a web page with some controls. I put all of them in session variables. I passed this values in the page. When an user enter some data in form fields and click the buttton ,...
9
Catalyst159
by: Catalyst159 | last post by:
I have a form which is used to calculate residential Floor Area Ratio (FAR). The form is structured into seven parts as follows: Part A: Maximum FAR and Floor Area: Part B: Gross Floor Area of...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.