[RESOLVED] I can't filter this query

[samer22]

hello everyone
I want to do a query on some criteria but I can't figure out how to do that.
This is my query.

 RS.Open "select PID,test1,test2,test3 from GradesTable where PID = '" & Text5.Text & "'", DB, adOpenStatic, adLockOptimistic
If not RS.BOF Then
RS!PID = Text5.Text
RS!test1 = Text1.Text
RS!test2 = Text2.Text
RS!test3 = Text3.Text
RS.update
Else
RS.AddNew
RS!PID = Text5.Text
RS!test1 = Text1.Text
RS!test2 = Text2.Text
RS!test3 = Text3.Text
RS.update

What I need to do is that if the same values in the texts are already existing in the table, then the values should not be added.
But if there is a new value in any of the texts, then they should be added.
Why am I doing that?
Perhaps a user, by mistake, clicks on the the cmd_button twice. So if the value are the same, then obviously no need to save the values even we click the save button again and again. But if there is a different value in any of the textboxes from those in database, then they are to be saved.
example:
Let's imagine These value are in the database
pid test 1 t2 t3
1 14 16 15
A user can't enter the same values as above as they are already in table.
But he can enter the following:
pid t1 t2 t3
1 14 15 16
1 14 15 13
1 15 12 16
I hope you understand my concern
Thanks a lot

[samer22]

I should note that this table is linked with a foreign key to a student table

[ColinE66]

Create a unique (composite) index on the columns that cannot be duplicated and the database will raise an error when you violate that constraint. Handle the error as you see fit. You should be able to do this on your own with those hints...

[DataMiser]

You can also add to the search criteria using the AND keyword

.... where field1=something AND field2=somethingelse AND field3=yetanotherthing

[IkkeEnGij]

samer22
please explain why there can not be 2 or more rows with the same values ????

[samer22]

Create a unique (composite) index on the columns that cannot be duplicated and the database will raise an error when you violate that constraint. Handle the error as you see fit. You should be able to do this on your own with those hints...

Thanks for replying
I think you mean a primary key. If so then as I said this table is a child table. This means I can't set the PID as primary key. Duplication of the PID is necessary.
thanks

[samer22]

You can also add to the search criteria using the AND keyword

.... where field1=something AND field2=somethingelse AND field3=yetanotherthing

Thanks DataMiser I shall try that.
But the prblem is that I have not only those fields, this is just a sample. I have Test1, 2 , 3, Exam1 ,2 ,3 ....
But If there is no other way I shall follow this approach, if it is efficient.
thanks

[samer22]

samer22
please explain why there can not be 2 or more rows with the same values ????

Thanks sir for interest
I will try to explain again though I think I have already explained in post1
Now a user may, by mistake, clicks the cmd-save button twice then of course I don't want to save the same values two times.
I need a code to prevent duplication of the same row with the same scores. (because practically it is impossible for a students to have the same scores in more than 6 examinanation unless it is a mistake of the user who may have clicked the cmd_save button twice)
I hope I have clarified the point.
Briefly, Is there a way to avoid duplication of the same row?
thank you

[IkkeEnGij]

Briefly, Is there a way to avoid duplication of the same row?

ColinE66 has already answered that, and so did DataMiser

because practically it is impossible for a students to have the same scores in more than 6 examinanation

and why is that impossible ?

[IkkeEnGij]

samer22
could it be you are trying to make a system to:
give marks to students for tests they have taken ?
do you know what kind of people usually give marks to students for tests they have taken ?
usually they are called 'teachers'
how many 'teachers' have you interviewed to ask how they do that ?
chances are they all do it the same easy and intelligent way
simply because it is so dead simple:
they give marks to students for tests

since you live in the boondocks, where there are no librarys
and hence you can not go to a library to lend books on database design
maybe,just maybe there is internet ?
and maybe,just maybe you can google for 'database design'
or maybe,just maybe you could go here:
http://www.vbforums.com/showthread.p...ner-join/page3
where some gentle person has given you a complete,very simple,very easy to use,foolproof system to do what you want to do

[samer22]

You can also add to the search criteria using the AND keyword

.... where field1=something AND field2=somethingelse AND field3=yetanotherthing

I did what you told me but the row is duplicated if a user clicks the cmd_twice
thanks

Edit:
I did a mistake in the query.
Now I think it is working
Thanks a lot

[samer22]

ColinE66 has already answered that, and so did DataMiser

and why is that impossible ?

And I think I have replied to them
Yes it is impossible for a student to get same marks for 8 or 10 examinations
I'm a teacher and I know that's impossible.

[samer22]

samer22
could it be you are trying to make a system to:
give marks to students for tests they have taken ?
do you know what kind of people usually give marks to students for tests they have taken ?
usually they are called 'teachers'
how many 'teachers' have you interviewed to ask how they do that ?
chances are they all do it the same easy and intelligent way
simply because it is so dead simple:
they give marks to students for tests

since you live in the boondocks, where there are no librarys
and hence you can not go to a library to lend books on database design
maybe,just maybe there is internet ?
and maybe,just maybe you can google for 'database design'
or maybe,just maybe you could go here:
http://www.vbforums.com/showthread.p...ner-join/page3
where some gentle person has given you a complete,very simple,very easy to use,foolproof system to do what you want to do

The sample you suggested in the other post doesn't meet my needs and I found it too complicated but I must thank you again for that help.
Now I'm asking for a method to avoid duplication of a row.I 'm so thankful to ColinE66 and DataMiser who suggested ideas but I have replied to them.
ColinE66 perhaps has suggested a primary key which doesn't fit my child table.
.

[samer22]

Thank you all I did what Mr.DataMiser suggested and it worked.

[IkkeEnGij]

Yes it is impossible for a student to get same marks for 8 or 10 examinations
I'm a teacher and I know that's impossible.

oh,silly me
i seriously thought it was possible for a student to have the same marks for several examinations
but, now i know that you are a teacher, i know how wrong i am

euh, maybe you could ask one of your fellow teachers
if it is impossible for a student to have the same marks for several examinations

but surely, since you are a teacher, you can explain to me just what it is that i see here: post #1

Let's imagine These value are in the database
pid test 1 t2 t3
1 14 16 15
A user can't enter the same values as above as they are already in table.
But he can enter the following:
pid t1 t2 t3
1 14 15 16
1 14 15 13
1 15 12 16

for as far as i can see, student #1 took the same 3 tests 3 times ?
or what do i see here ?

if you can have a table like so:
1 14 15 16
1 14 15 13
1 15 12 16
then why can you not have a table like so:
1 14 15 16
1 14 15 16
1 14 15 16

please excuse my stupidness, but i really can not understand why
student #1 can have the following marks :
1 14 15 16
1 14 15 13
1 15 12 16

and it is impossible for
student #1 to have the following marks
1 14 15 16
1 14 15 16
1 14 15 16

but,i am sure, since you are a teacher, you can explain that to stupid me

[samer22]

IkkeEnGij
Excuse me the internet disconnects every 5 minutes. And on the whole day, I can get connected 1 or two hours.
So many times I write a reply but I could not post it for problems of internet disconnection.
Now, what I meant is quite different from what you are explaining.
This problem is solved and the post is closed and I need help for another issue.
I hope I can post my concern before the internet disconnects.
Thanks

[ColinE66]

Why don't you type your replies into Notepad and then paste them into the thread when you are connected? Pretty obvious solution...