Microsoft Jet database cannot find a record ....

[Kiwi-Wombat]

Microsoft Jet database engine cannot find a record ....

Hi all

I hope I can describe my problem becasue the database is too large to upload

I have a database with two tables linked on one field.

The data source for the associated form is a query on both fields with an Inner Join. The field from the main table is a lookup combo box from the related table and used for data entry. The other is a text box from the related table and is used for viewing.

The fields are never visible at the same time.

If you fail to enter anything in the lookup combo (in this case assume that is an allowable option) and try to save the record, you get an error message as in the title of this thread.

I have worked out that it is because the Field ID is 0 if not entered and there isn't a 0 record in the related table. I guess I could create a blank record in the related table and make the default value for Field ID in the main database as 1, but is there any neater way of doing it?

 

[FoFa]

If you fail to enter anything in the lookup combo (in this case assume that is an allowable option) and try to save the record, you get an error message as in the title of this thread.

It sounds like this is requiring a record in the other table, and since you do not have one, you are getting an error. By placing a zero row in the other table, you are circumventing a designed business rule. Is that what you realy want to do? Either way sounds like a design change is required. You have to decide if requireing a record in the other table is correct, or check and force an entry in that table if one does not exist.
Just my thoughts.

 

[Kiwi-Wombat]

The lookup table is only designed to speed up data entry and the field is definitely optional.

Judging from the lack of answers either there is no alternative or no-one knows the solution (or they don't understand what I have asked, which is also probably likely lol)

Thanks anyway

 

[FoFa]

If it truly is an optional field, than it sounds like you have a required relationaship that is not required, hence the error. I would check your relationships and adjust per your requirements.