Data Validation - Stopping duplicate data

[Gerimc]

I have a maintain financial year table – when a user enters a new year I want my form to check if this year has been added before. If it has an error message should come up telling the user this year already exists else the user should be able to input the year, How would I go about doing this?

 

[vbajock]

Use the dlookup function. It returns null if the value does not exist. Your solution would look something like

if isnull(dlookup("yrval","fintable","yrval = "+str$(forms!myform!txtYrBox) then

do something, yr is not in table
else
do something else, it is
end if

Your code would go in the command button that starts your processing or in the AfterUpdate event of your year-entry box if you need to user to know immediately

 

[Gerimc]

The value yrval would that come from the financial year table and if so would it be the financial year primary key field or the description field ?

 

[vbajock]

primary key.
dlookup("TheFieldToReturn","TheTableToSearch","TheFieldtoCompare ="+SometextboxOnMyformWhereIgotTheSearchCriteriaFrom)

 

[Gerimc]

I have entered the following:
If IsNull(DLookup("FinYearDescriptionFld", "FinancialYearTbl", "FinYearDescriptionFld = " + str$(Forms!MaintainFinancialYearFrm!FinYearDescriptionFld))) Then
I am getting a type mismatch error.
Do you know what the matter might be ?

 

[RoyVidar]

Hi!

I see some possible reasons (might be others, a combination...);

1 - the str function often returns a leading space (trim?)
2 - using + for concationation might sometimes perform an addition in stead of concatination
3 - datatype is text, not numeric (se bottom)

Try:

[tt]If IsNull(DLookup("FinYearDescriptionFld", "FinancialYearTbl", "FinYearDescriptionFld = " & trim$(str$(Forms!MaintainFinancialYearFrm!FinYearDescriptionFld)))) Then[/tt]

(or drop the str, but try the "&")

Now this should be OK if the year is a numeric field, if text, one would need text qualifiers, apostrophe (')

[tt]If IsNull(DLookup("FinYearDescriptionFld", "FinancialYearTbl", "FinYearDescriptionFld = '" & trim$(str$(Forms!MaintainFinancialYearFrm!FinYearDescriptionFld & "'")))) Then[/tt]

Roy-Vidar

 

[Gerimc]

The year is a text field - but i am still getting that type mismatch error. I am wondering do I maybe need to reference the Primary Key field of the Financial Year tbl ?

 

[vbajock]

Let's look at the code:

I have entered the following:
If IsNull(DLookup("FinYearDescriptionFld", "FinancialYearTbl", "FinYearDescriptionFld = " + str$(Forms!MaintainFinancialYearFrm!FinYearDescriptionFld))) Then

str$(Forms!MaintainFinancialYearFrm!FinYearDescriptionFld) would return a mismatch if the data is already a string, remove str$ function

"FinYearDescriptionFld = " + str$(Forms!MaintainFinancialYearFrm!FinYearDescriptionFld)))
would return a mismatch if your FinYearDescriptionFld is a text type-field, if it is, you have to encapsulate the criteria in single quotes:
"FinYearDescriptionFld = ' " + str$(Forms!MaintainFinancialYearFrm!FinYearDescriptionFld)+"'")

Or a combination of both:

DLookup("FinYearDescriptionFld", "FinancialYearTbl", "FinYearDescriptionFld = ' " + Forms!MaintainFinancialYearFrm!FinYearDescriptionFld+" '")