Continuous form - enter foreign key once?

[brodkat]

I have a continuous form that is designed for data entry. There are 60 records to be entered from one datasheet and there are hundreds of datasheets. One of the fields is a foreign key that is the same for all 60 records. I would like the data entry person to be able to enter the foreign key only once and have it copied into all 60 cells in the foreign key field.

In the past I have gotten past this problem by embedding the form as a subform in a form where there is a one-to-many relationship with properties set to 'enforce referential integrity' and 'cascade update related fields'. But I need this form to be opened separately and work on its own.

I have started to try using a control button that runs an update query at the end for all blank cells but I worry about inadvertently entering the wrong foreign key into records that for some reason are blank (can't think of why but ...what if?).

Any helpful suggestions are appreciated!!

 

[stopher]

It does sound a little scary. For instance, how does the user know which FK to enter?

Anyway, you could easily update the default value for the FK control. There are plenty of events that you could use to prompt the user for the FK value depending on when you want to change the value.

hth
Chris

 

[brodkat]

the foreign key is on the datasheet so the problem is just how to enter one FK for 60 records.

So I was thinking a command button the user clicks after they are done that updates all IsNull for the FK - but your suggestion is (I think) more along the lines of having the user click a button before entering the data, which prompts the user for the FK and then changes the default value.

I started to attempt just that but I'm stuck almost immediately. I added text box bound to the FK field - thinking the user will type the FK into the box. Then I found expression.DefaultValue in help and wondered if I could use that in an expression, I tried:

Forms![FormName]![FieldName].DefaultValue = [text box]

Not working, pretty sure I'm off on the text box idea and probably can't use .DefaultValue in an expression. Hoping for some more insight. Thanks!

 

[brodkat]

I worked around this without having to write any code. I went back to the idea of updating all Is Null values for the FK. I added a text box to the header where the user types in the FK. Then I inserted a command button for the user to click when they are done that fills in all the IsNull values with the text in the text box. In command button properties I set 'on click' event to run a macro. The actions of the macro are:

1. GoToRecord (next) because if the user didn't tab to a new record, the FK was left blank for the last record.
2. OpenQuery (an update query - see below)
3. Close the Form
4. OpenForm (reopen the same form - properties set for data entry mode so it opens a blank form and the user can start in on the new datasheet.)

The update query updated all 'Is Null' cells in the FK field with the text the user typed into the text box in the header of the form.