Validation criteria of Form-Table

[Colhem]

Hi!

I have a form used for data input (Frm1). The text boxes are bound to the
records in the table [TblCalibration], which contains several fields where
[Name] (text),[Recal] (true/false),[Date] (date) is of interest.

1) I don't want the user to be able to add a record (e.g. goto next record
(a blank one), which is the behaviour of my save-button.) where he/she inputs
the same combination of Name, Recal, Date already present in TblCalibration.
Any combination of these three is ok, as long as it is not already stored.

2) If it is not too complicated, I would also like to get a customized
duplicate record error message to pop up every time this happens.

Many thanks, Peter

 

[Frank Stone]

you could create a record set from a query that matches
all fields on the form.
if the recordset matches all fields i.e. has data then you
know it's a duplicate record.

Dim dbb As database
Dim rss As Recordset
Set dbb = CodeDb()
Set rss = db.OpenRecordset("put your SQL
here")", dbOpenDynaset)
if rss.eof then
add record
else
msgbox "duplicate record"
clear fields
exit sub
end if
I've used this technique several times, different ways but
never with a bound form. I don't use bound forms. You can
try it.

 

[Colhem]

Thank you for helping Frank!

I'm afraid I don't know any SQL at all, which makes your solution to complex
for me to follow.

Most of all I was hoping to solve this within the world of forms and
queries. Maybe that isn't possible?

Peter