Auto Populating Field in Subform

[sparlaman]

I am having a hard time thinking how to explain this so I apologize if I leave out important info. I have a form with subform. My main form has the primary key EmployeeID (autonumber field). my subform has foreign key EmpID (it's a lookup field on the related table)

What I want to do is when I create a new record in the subform by entering a date (I have a date field) I want the empID field to get the empID from the related main form.

I'd attach a screen shot if I could figure out how to do that.

I've tried adding the following code (or forms of this code) to the OnDirty event in the subform:

 
Forms![sbfTimeSheets]![EmpID] = Me.EmpID

but alas I get an error that "access can't find the form 'sbfTimeSheets' referred to... " and I know that's the right name of that form

[RuralGuy]

Start by removing the Lookup Fields from your tables.
http://www.mvps.org/access/lookupfields.htm
http://www.btabdevelopment.com/ts/removelookups

[sparlaman]

OK, I've removed all my lookup fields. Can, I ask a quick question about that? I can understand the reasoning behind removing lookups from tables. But is it correct that the lookups are reasonable to be used in forms? And that is really the only place they are necessary?

[RuralGuy]

OK, I've removed all my lookup fields. Can, I ask a quick question about that? I can understand the reasoning behind removing lookups from tables. But is it correct that the lookups are reasonable to be used in forms? And that is really the only place they are necessary?

You are *exactly* correct with your statement!

[sparlaman]

OKEY DOKEY what's next?

[RuralGuy]

You may also wish to review this link: http://www.mvps.org/access/forms/frm0031.htm

[RuralGuy]

I should also say that the LinkChildField as defined by the SubFormControl is automatically populated by Access when you add a new record to the SubForm.

[sparlaman]

OK, I think I see where you are going with this and I figured my syntax was problematic, but can you translate for me as it applies to my current layout: I'm not new to Access but it's been a long time since I've done any work in it and my memory is bad.

My main form = frmHRDB based on fqEmployees (is a query with EmpID field)
my subform1 = sbfTimeSheets based on fqTimeSheets (is a query with EmpID field)
my subform2 = sbfTSEntrybased on fqShopTimeSheets (is a query with TimeSheetID field)

[sparlaman]

and yes, when I look at the SubFormControl the master & child field links appear to be correct. Master = EmpID and Child = Employee. and where I already have 'child' records created the links are working perfectly, but what I am aiming to do is create a new child record which auto populates the empID from the primary form. Does that make sense? Am I going in the wrong direction? I feel like I did this in the past but I cannot remember those days.

[RuralGuy]

You described two SubForms. Are we just talking about the subform1 SubForm? The LinkChildField should be a ForeignKey field in the SubForm RecordSource. Is it? It is *very* useful if the names of the fields are the same. It removes some confusion.

[sparlaman]

You are correct on all accounts. I should change the name of the field. I'm sort of coming from an oldschool practice (of my own I think) where I name the child field slightly different so I can differentiate between the primary key and the foreign key. But if it makes more sense these days to use identical names I like to keep current.

Anywho... yes I am just working on subform1 at present. And Employee is a foreign key in the fqTimeSheets.

[sparlaman]

side note: in most of my access experience. I was/am the sole user of the system I created so in some cases I didn't follow proper naming conventions and/or design systems. Then when I left those positions the people who came behind me would inevitably use Excel instead of Access anyway because they couldn't figure out Access and I had not created any kind of interface to work with. Plus, I could laugh at them knowing that my system was easier. Yes - I am evil and should be destroyed. So I developed some bad habits. BUT I am going back to school to get a degree in database admin/design right now and welcome all your helpful input in regards to accepted standards and practices. BIG THANK YOU!

[RuralGuy]

Having coded for many decades I now code so anyone (including me) can maintain it years later. It is never too late to start developing good habits. What DataType are the PK and FK fields? Hopefully AutoNumber and LongInteger respectively but you tell me?

[sparlaman]

Yes, Autonumber and LongInteger

[RuralGuy]

And you are saying the FK field (LinkChildField) does *not* populate when you Dirty a new record in the SubForm?