No input allowed when connected (lookup) table is empty

M

micromoth

I have a form with a one to many relationship with one field in another
table this works fine when the table is full. It basically works as a
lookup table on the form which is what I want.
Problem:
Firstly when I want to leave this field on the form blank as it is not
relevant for an individual record input is blocked. Secondly is when
the table is empty and no number is available to be selected again
input is blocked. Any ideas welcomed, Thanks in advance
 
M

Michel Walsh

Hi,


"input being blocked" is not crystal clear. It is blocked because there is
an error?


Dim i As Long
i=DLookup("field", "table", false)



return an error, since DLookup returns NULL and an integer cannot hold such
a value. If this is your case, try:


Dim i As Variant
i=DLookup("field", "table", false)

or

Dim i As Long
i=Nz(DLookup("field", "table", false), -1)


if -1 is not a possible value, naturally.



Hoping it may help,
Vanderghast, Access MVP
 
M

micromoth

No it is beeing blocked because: You cannot add or change a record
because a related record is required in table "tblA"
 
M

Michel Walsh

Hi,


Sounds that you have a referential data integrity violation. Add the
required record in tblA before, or inside, the BeforeUpdate event of the
actual form (or somewhere else, if more appropriate).


Hoping it may help,
Vanderghast, Access MVP
 
Top