Required field problems in a subform!

[SteveC24]

Hello

I have been trying to get round this problem for weeks now, and just can't do it!

I have a fish database, which records all sorts of details about people's fish that they have in their fish tanks.

Basically, the main form (called "frm Fish enter") holds info like name, tank located in etc. There is then a subform (called "frm Purchases enter"), where the user can enter each purchase they make for that fish.
That subform contains fields for:

Date - called "fDate"
Quantity Purchased - "Quantity"
Shop Purchased From - "ShopID"
and
Price Each - "PriceEach"

My problem is, when you have put a fish name in the main form, you move along to enter a purchase...but, for example, say you know when you bought it, but can't remember where from, you might enter the date, and quantity, but want to leave the shop field.

The problem is, when you try to leave the subform again (like by clicking on the main form, or trying to move records or something), an error pops up telling me that a "related record is required in table "tbl shop", which is where the shop dropdown gets its data from.

I have had a look and there is no sign of anything being a required field in any of the relevant tables.

How can I stop it from doing this...it is remarkably annoying!

Thanks very much....and sorry for the long post!

 

[ehdoh]

Not sure if this is part of your problem, but when I've received similar messages it has been because the field I am trying to leave empty is one of the link child/master fields that creates the relationship between the form and subform. Might this be your problem, too?

 

[SteveC24]

No, I just had a look, and the master and child fields are one called "FishID".

So, no luck

Any more suggestions...PLEASE?!?!

 

[Idjit]

This won't directly address the cause of your problem, but it may be a way around it. I would add a record to tbl shop for "unknown" or something. I've found it's a good idea to force entry for every field - that way you don't have to wonder later whether the omission was deliberate or accidental.

If you're using tbl shop only as a lookup table for a dropdown field, I'm not sure why you would get the error message you're seeing. How do you have the relationships between tbl shop and your other tables set up? I get the message you're seeing when I try to violate referential integrity.

 

[SteveC24]

Yep that solves the problem partially...but say the user just wants to click somewhere else, like they notice a spelling error in the name or something whilst entering a purchase? It will still come up with the error....grrr! This is very annoying!

Not sure about the referential integrity, I will have a look, and get back!

Thanks!

 

[jfgambit]

In the SubForm set the FishID Default Value to be:
[Forms]![MainFormName]![FishID]

HTH

 

[SteveC24]

Nope, jfgambit, that makes no difference.

There is no problem with the subform linking to the main form...that all works fine, and if you put a shop in, it works fine, it is only if you don't enter a shop that you get the error.

Thanks anyway

 

[SteveC24]

OK, I have been fiddling with referential integrity...I have disabled it all together on that relationship, and I no longer get the error, so that is great! Thanks!

As far as I can see, I do not need referential integrity on that relationship, so...all being well with further testing...that should be great!

Thanks!