Prevent further inserts in subform

[hughess7]

Hi all

I have a subform where the user creates Issues, if the code 11100 is entered
I want to prevent the user from creating any more issues just against the
current claim on the main form, unless 11100 is deleted. I have set a trap to
stop 11100 being entered if other issues exist, but what is the best way of
stopping users from entering further issues if 11100 is entered first against
the claim?

Would it involve a dlookup on entering a new code to check 11100 doesn't
exist or simply something like changing the allow additions to no if 11100
gets entered? Field in question is cboIssue (combo box based on a query -
also thought of hiding rest of choices in combo box if 11100 has been entered
against claim but not sure how to do this?).

Thanks in advance for any help.
Sue

 

[hughess7]

Thanks, not too sure how to do any of the options to be honest hence the
question. Also, don't want to waste time starting one way for there to be a
better way / easier way of doing it...

 

[hughess7]

I think I know how to do this in principle, I just struggle with getting the
sql string right! I have done something similar but I use DCount to check if
a record exists with the specific criteria.

This one has a complicated key as follows to check the match on:

Country - Text
Dealer - Text
ReviewDate - date
ClaimNo - Text
IssueCode - LongInteger (looking for 11100)

Table to check = [Dealer Claim Check Issues]

Thanks in advance for any help.
Sue

Please give some details about your table(s) and form. You can do a dlookup
on the field (need to filter on the current claim) and if you find 11100
popup a msgbox and set the value of the field to null.

Bonnie
http://www.dataplus-svc.com

Thanks, not too sure how to do any of the options to be honest hence the
question. Also, don't want to waste time starting one way for there to be a
better way / easier way of doing it...

Hi Sue,

[quoted text clipped - 23 lines]

Thanks in advance for any help.
Sue

 

[hughess7]

There is a very good reason for that - because we deal with small access
databases being emailed backwards and forwards between the office and all our
field staff (approx 20 people at mo) as 'transfer files'. So we can't have an
autonumber as it wouldn't be unique! I need to keep the key as it is...

That is complicated - why don't you apply an autonumber field to the Orders
table and then you can just look up the ID in that field.

Bonnie

I think I know how to do this in principle, I just struggle with getting the
sql string right! I have done something similar but I use DCount to check if
a record exists with the specific criteria.

This one has a complicated key as follows to check the match on:

Country - Text
Dealer - Text
ReviewDate - date
ClaimNo - Text
IssueCode - LongInteger (looking for 11100)

Table to check = [Dealer Claim Check Issues]

Thanks in advance for any help.
Sue

Please give some details about your table(s) and form. You can do a dlookup
on the field (need to filter on the current claim) and if you find 11100

[quoted text clipped - 12 lines]

Thanks in advance for any help.
Sue