No input allowed when connected (lookup) table is empty

  • Thread starter Thread starter micromoth
  • Start date Start date
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
 
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
 
No it is beeing blocked because: You cannot add or change a record
because a related record is required in table "tblA"
 
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
 
Back
Top