checking if field already exists

[sades1]

hi,

i got this off another thread somebody had put up and i want to do something similar.....i want to see if what the user enters already exists and tehn show an error message if it does...

well i dont really understand how this works.....can you explain what

rst.Open "SELECT anyusefulfield FROM tablename WHERE (keyfield = " & fieldvalue) & " AND (keyfield2 = " & field2value & &quot"

does? examples on the fieldnames you have entered would help!

im a beginner here and am really stuck!

SaDeS

 

[LittleSmudge]

The code you posted is intended to find out if a RECORD exists in the table where that record matches the two criteria of:-
FIELD keyfield contains the value in the variable fieldvalue
and
FIELD keyfield2 contains the value in the variable field2value

There is nothing about checking to see if the FIELD exists within the table.


If you are looking to check for a RECORD that exists containing matching data then in

rst.Open "SELECT anyusefulfield FROM tablename WHERE (keyfield = " & fieldvalue [b]& "[/b]) & " AND (keyfield2 = " & field2value & ")"

Note the & " that I've added in the line above - without it - it won't work
Replace tablename with the name of your table
Replace keyfield with the name of the first field that you want to check ( 'name of the FIELD' means the name that you typed in the table design view when you were creating the table )
Replace keyfield2 with the name of the second field that you want to check
Replace fieldvalue with the control name that hold the new data ( 'control name' means the name of the control on the FORM - this can often be the SAME as the field name that it is bound to, but doesn't have to be - so keep that in mind )
Replace field2value with the name of the second control that contains the other new data.
Replace the term anyusefulfield with the * star character ( then you get ALL fields - that will be fine for what you need. )

If, like many beginners you have spaces in the table name, field names or control names then you have problems.
To fix some of the problems them put square brackets around the names. ( that will be enough for now.)

Finally, if the values that you're checking are anything other than numbers then you'll need to 'delimit' them

Eg If the first is a date (add #s) and the second a string (add 's) then it becomes:-

rst.Open "SELECT anyusefulfield FROM tablename WHERE (keyfield = #" & fieldvalue & "#) & " AND (keyfield2 = '" & field2value & "')"


Report back on progress sades1


G LS

 

[BSman]

Another way to check for other records is to use the DLookup function:

Dim lngCount as Long
lngCount = DCount("anyusefulfield","tablename","keyfield = " & field1value & " and keyfield2 = " & fieldvalue2)
if lngcount > 0 then
' add code here to handle duplicate record situation
end if

The nice part about this is that it also gives you a way to allow multiple entries of duplicate information, but only allow the multiple entries up to a certain count. Or you could notify the user that an entry or entries already exist, in case the user decides not to create another similar entry.

Be sure you follow the rules for surrounding the values with # for dates and with ' for strings as discussed by LittleSmudge. That's important to remember whenever you create strings for filters and to set values in code.

 

[sades1]

tnx a lot for the help ppl!

 

[sades1]

uhm i kind of get this now littlesmudge.....what this does is checks if the record already exists in the table right? how do i add the code to carry out the action if it already does?...for example to run a macro "alreadyexists"?
im not familiar with these rst. statements and stuff.....i cant just say "THEN docmd.runmacro......." can i??

Sades1

 

[Bry12345]

Did the code in this thread not work for you?

thread705-253272

It looks like you are asking the same question in two threads. - - - -

Bry

 

[sades1]

uhm, not really.......i started thar thread before this one and then i realised that i could check it for all the fields......
neways, about my last question, how do i take any action after checking if it exists?i cant use a THEN statement can i? and since i havent figured this out i keep getting an "expected:end of statement" error.......

sades

 

[Bry12345]

To my thinking, using SELECT just complicates what you are trying to do . . .to follow a SELECT with an IF statement for what you are trying to do pretty much requires you to obtain a count of your select results and then branch your statements based upon whether or not the count is 0.

DCount does it with one statement.

My 2 cents . .. - - - -

Bry