Inherit value from another table field

[zfind]

Hey guys,

My database is coming along nicely and I want to cover a few things that I havn't been able to yet.

I have a partners table and accompanying form. One of the fields here, called default % share asks for a percentage (for a revenue split). This is used in 90% of cases, but the other 10% of the time this needs to be a different percentage. In my records table I've added a field called manual % share. I want this to inherit the default % share value from the relevant partner in the partners table. How can I do this? In Excel it's a snap but I can't find equivalent functionality in Access.

The percentages are then going to be used to apply to revenues to give the aformentioned revenue split. I still havn't been brave enough to attempt that part yet, even though I'm very comfortable with VBA

Any help appreciated!!

 

[namliam]

Use the on got focus of your Manual or on exit event of the Default to set the value for the manual %

 

[zfind]

OK, i understand the events part, but what exactly should I put in there? I really don't know much about using the event builder, sorry...

 

[namliam]

Me.[Manual %] = Me.[Default %]

i will save the "dont use spaces and special characters"-speech for now

 

[zfind]

So this would be VBA code then? I'm having trouble finding where to put this

I am looking at the properties for the manual % textbox on the main form, on the events tab. Under " On Got Focus" I created a new macro (or event procedure). The Me.(textbox name) part I got but how do I make it look up the default field of the current partner record (being that the data entry area is a subform on my partners form). The default % is not present anywhere on this form or in this query so it needs to look on the table.

EDIT: Just saw the OnCurrent event, that looks like it would be perfect - each time the record is changed I could run the macro.

 

[zfind]

OK, I just realised a fundamental problem. If we are running this macro each time it we use the form, it will overwrite the manually changed entries. I think I'll just scrap it, thanks anyway for your help!

 

[namliam]

No, it doesnt overwrite if you are checking for excisting value...

if isnull(me.[Manual %]) then Me.[Manual %] = Me.[Default %]

This would prefent overwriting of previous values in [Manual %]

To get from the subform to the Main use Me.Parent, to get from the main form to the Child.... Me.[ChildName]