Random Sub-form Linking Error

[canuck_1]

Hello All,


DB Background:


I have created a cost tracking database that uses timesheet entries to track hours and costs for different tasks.


I have a Lbr_ts_tbl that has a field for the date the work was completed. the Lbr_ts_tbl has an autonumber primary key named Lbr_ts_id. I also have a Lbr_ts_dtl table with an autonumber primary key for all the timesheet info and such.


I have linked Lbr_Ts_Tbl to Lbr_Ts_Dtl with a one to many relationship using Lbr_ts_tbl!Lbr_ts_ID (one) and Lbr_ts_dtl!Lbr_ts_tbl_ID# (many).


I created a form for Lbr_Ts_Tbl with a datasheet style subform for Lbr_ts_dtl. To link them I used "Link Master Fields: lbr_Ts_ID" and "Link Child Fields: Lbr_Ts_tbl_ID#"


Lastly, and maybe most importantly, I have split my database into a front end/back end. The front end is linked to the back end using a UNC path.


My Problem:

This form works fine most of the time but every week or two I get the error: "You must enter a value in the 'Lbr_ts_dtl.Lbr_ts_tbl_ID#' field when I am entering timesheet data in my sub-form datasheet. It won't let me add any new records without that value as I have made it a required field (as it should be I believe).


I have tried un-hiding the 'Lbr_ts_dtl.Lbr_ts_tbl_ID#' field in the datasheet subform but for whatever reason it won't stay un-hidden.


Sometimes the errror can be fixed by closing the front end and re-opening it but most of the time I need to delete that file and use one of my backup files.


I cannot figure out why this is happening or how to fix it.


If anyone could please provide some input it would be greatly appreciated!!!!!


Thank you in advance!


Canuck_1

 

[PaulO]

I wonder, is the blank required field being using as a Parent field on the Main Form to link to a Child field on the Sunform? This might explain the symptom at least ...

 

[canuck_1]

Hello Paul0,


You are correct, the field that is not populating is the field that links the subform datasheet records to the parent table's records. Therefore, you can click through the Parent records on the main form (which are the different days that the crews worked) and it automatically filters the subform datasheet records based on what day you are looking at. When you add records in the subform datasheet it automatically populates that field. However, every once and a while it stops doing that.


A temporary fix would be to key that field in manually however, I cannot unhide that field and save it un-hidden. It just re-hides itself as soon as you exit the form.


Is there some simple VBA code that could unhide the Lbr_ts_dtl!Lbr_ts_tbl_ID# column? The people using the database have the runtime edition and can't do it.

 

[canuck_1]

After dealing with this issue for a number of weeks I have narrowed it down a little bit.

-This error generally only occurs when you sort the subform table.
-When the error occurs it only affects the file that you are currently using.
-If you delete the linked tables and re-link them, the error is fixed.

I am looking to abandon this form and create a new form that uses a query to populate the data table with a combo-box to filter on the date. I just haven't figured out how to use that to add new entries and edit existing ones....

 

[boblarson]

Make sure that the main form's record source doesn't have the table that the subform is using as its record source and the subform doesn't have the table that the main form is using in its record source.

 

[canuck_1]

Hello Bob,
Thank you very much for your response! It is an encouraging thing to come back to after my vacation.
Currently, the main form has:
Record Source: Lbr_TS_Tbl
The Subform has:
Source Object: Table.Lbr_TS_Dtl
Link Master Fields: [Forms]![Lbr_Ts_Tbl_Form]![Lbr_TS_ID_FormLink]
Link Child Fields: Lbr_TS_Tbl_ID#
The [Forms]![Lbr_Ts_Tbl_Form]![Lbr_TS_ID_FormLink] field is the text box on the main form that is the primary key of the table. I have also tried using the normal name [Lbr_Ts_Tbl]![Lbr_Ts_Tbl_ID] but I get the same error.
If I understand what you are saying, and I am not sure I do, the issue could be caused by having the main form and the subform using the same record source. However, the way it is set up right now the main form and subform have different record source’s. Therefore, I am not sure where to go from here.
Do you think you could possibly clarify for me?
Thank you very much!
Canuck_1