prevent users from adding new record when there is one already.

[sunset1215]

Hi all,

I have 2 tables, tblCustomers and tblTest.

tblCustomers
CusID - pk
Customer name

tblTest
TestID - pk
CusID - fk
Test status

I want to prevent users from adding a new test status for a customer when he already has a test status. i'm using a command button to create new test statuses, but i want to disable it when there is a current test status already.

I've tried to use dlookup to check if my pkCusID is already in tblTest as fk, but it's not working.

any help or advice is appreciated.

 

[MStef]

It is not clear enough what you want.
Do you want only one record in the "tblTest" (for "CusID"),
or you want more records in the "tblTest" (for "CusID"),
every record with the different "Test status".

 

[sunset1215]

i only want one record in tblTest for one CusID.

i put it as a one-to-many relation because customers may not have a test status. but if they have, there's only one.

 

[MStef]

Ok, in that case put INDEX on the field "CusID" (in tblTest),
with UNIQUE = yes (properties).
Look at attacment, zip.

 

[sunset1215]

it works, but not in the way i want it to.

can i put this as an onclick event in a command button?

for example:

If (Customer already has a Test status) Then
cmdbutton.enabled=false
Else
cmdbuttom.enabled=true

is this possible?

 

[MStef]

All is possible, (what work), try it and see.

 

[mane_uk]

sunset1215,

I would create under the loading form a look up in tblTest using your Customer ID, storing the status against a variable; Something like:

strStatus = [B]DLookup([/B]"[Test status]", "tblTest", "[CusID] = " & CusID[B])[/B]

Then you can do the if clause you mentiond above using the strStatus.

If (strStatus ="Customer already has a Test status") Then
   cmdbutton.enabled=false
Else
   cmdbuttom.enabled=true
End if

If you create on the command button you can do the look up, show a message to the user saying it has alread a test status, move the focus to another field and then do the enable/disable.
Cheers

 

[sunset1215]

I'm currently at the library posting with my iPod. Though I haven't tried out the code, but I have an idea how it works now. Apparently, I was using the dlookup the wrong way.

But I was sitting here thinking for a while, and I thought that these 2 tables should be in a one to one relationship isn't it? One customer can only have one test status or none. Please correct me if I'm wrong.

Appreciate the help,
Vivian

 

[sunset1215]

ok. i have tried the code, but i'm still getting an error in my dlookup.

my code is:

'declare variable
Dim x As String
x = DLookup("strStatus", tblTest, "pkCusID = " & fkCusID)

i have tried switch the fkCusID and pkCusID around, but it's telling me i gave an invalid argument.