Validation

[lab18]

Ok when i enter a new record(RX numer) i want it to validate it by checking if there's anothere record(RX number) with the same date, if there is then get an error or msg stating that record exist with that date if not then record get entered.
thanks in advance

 

[naygl]

You could change the 'date' field's properties in the table. Set the field's 'Indexed' property to (Yes-no duplicates).

I haven't tried this so I am just guessing.

 

[Accessable]

You need to use DLookup. This built-in function goes through all the records in your table and checks the specified field. It looks for whatever you put in the criteria (in this case table field must equal the form control value). If it finds the search material, then it does whatever you tell it to do.

'--------------------------------------------

If (Not IsNull(DLookup("[TableField]", "tblYourTable", "[TableField] ='" & Me!FormControl & "'"))) Then

msgbox "This already exists."
end if
'--------------------------------------------

This is the function:
dlookup(Expr As String, Domain As String, [Criteria])

expr = what table field to search. This can be any field on the table.

Domain = which table/query to search

Criteria = What must be true. In my case, the table field must equal my form control value.

I hope this is helpful.
-Mark

[This message has been edited by Accessable (edited 02-21-2002).]

 

[David R]

Just for reference, my understanding is that with large recordsets, Dcount() is faster because you only want to know if the record exists, not what the value is.

HTH,
David R


[This message has been edited by David R (edited 02-21-2002).]

 

[lab18]

Thanks guys for the prompt reply. Maybe I didn't explain myself correctly.
I have two fields on the table
Date and Rxnumber
I made a form to input this info. What i need is that i can have duplicate dates and Rxnumbers but not in the same record.
no two records can have the same date and rxnumber but a record can have the same rxnumber as anotheras long as their dates are not the same.
thank u again

 

[David R]

Have you ever built a SQL WHERE clause? The format is just the same, except you leave off the WHERE part.
Use:

Dcount("[Field]","[Table]","[Field] = '" & Me.[FormControl] & "' AND [Field2] = " Me.[FormControl2])

Note that this assumes [Field] is a text field (note the single quotes), and the second is a number field (no quotes).

If you click on 'Edit Message' for my post you'll be able to see this more clearly.

HTH,
David R

 

[lab18]

Thank you, used the dcount, worked great. I'll be posting other questions as i keep working on this DB.

Thanks