Fill field in datasheet view subform with value form form

[cdck]

I have a database set up to keep track of traveller information for our company.

I have a form that is used to enter and view traveller information.

On this form is a subform that shows all of the travel discount programs the viewed traveller participates in. This subform links to the main form using the field "userid". The fields on this subform are:
1. userid
2. programid
3. program (this fills in automatically, based on the programid)
4. account number
5. notes

On the subform is another form, linked through "programid", which shows what each program can be used for.

As the user views the form, the subform shows as a datasheet, with a + by each program's record. WHen the + is clicked on, it opens the sub-subform and shows what that program is good for.

MY PROBLEM:
When a user is entering a new traveller or trying to add a program to an existing traveller, I need each new record added through the subform to automatically fill in the userid based on the userid shown on the main form. I created a form once before that did this for me, but I have been unable to get this one to work, and cannot figure out what I am doing wrong.

I have been trying to insert an expression in the "default value" property of the "userid" field in the subform. Currently, when I view the entire form, everything looks good until I look at the "userid" field on the blank record at the end of the subform. There, it says "#Name?". What the expression says at this point is:
=[main form name]![subform name]![userid]

I have also tried [main form name]![userid], at which point i got "#Error".

Thanks for any help you can offer,

Cheryl dc Kern

 

[PHV]

Take a look at the LinkMasterFields and LinkChildFields properties of the subform.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cdck]

The fields are linking correctly, and I'm sure of this because all the information being displayed is the right information. The problem only comes up when I try to add a new record using the subform. (All existing records connect as they should)

Cheryl dc Kern

 

[PHV]

Have you tried to replace this:
=[main form name]![subform name]![userid]
By this:
=Forms![main form name]![subform name].Form![userid]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cdck]

I've tried this new formula, and I've also adapted it to refer to the main form instead of having the subform refer back to itself (i.e. : =Forms![main form name].Form![userid]), but continue to get the "#Name?" error in the userid field of the new record.

Am I trying to use the wrong property for the field?

Is there a different way to do this that I don't know?

Could the sub-subform or the field that autofills based on the program id be interfering?

Thanks again,

Cheryl dc Kern

 

[PHV]

Have just tried the Expression builder ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cdck]

I'm afraid that I don't know enough about how the expression builder works to get it to do anything for me. I tried finding the field I wanted it to refer to in the drop down and selecting that, but that was when I got the "#Error" error in the field. Any suggestions?

Cheryl dc Kern

 

[cdck]

I just thought of something else that might affect this situation. The subform's control record source is a query. Would this change the way that I have to call out the field?

Cheryl dc Kern

 

[cdck]

Can anyone give me a further suggestion? Following the help files, I have attempted the expression builder several times, and it always gives me a result similar to what was suggested above. I'm still getting the exact same error messages.

Is it possible that I am trying to use this expression in the wrong property?

Is it possible that the subform's control record source (which is a query in order to facilitate the field that auto-fills) is somehow blocking this effort?

Is there some other property of the field that I should investigate or change?

Thanks,
Cheryl

Cheryl dc Kern