Populate fields in one table from another tables using pop up form

[laskowv]

I have 3 tables: EMPLOYEE, COMPANY, and TRANSFERS

In EMPLOYEE, EmployeeID is PK with CompanyID as FK. In TRANSFERS,
TransferID is PK, EmployeeID is FK, and there are 2 fields named FromCompany
and ToCompany; which are FK's to COMPANY (CompanyID).

On my Employee Form, I have a command button called "Add Transfers"; which
activates a macro to display the "Transfers" form; after setting the
TRANSFERS.EmployeeID to EMPLOYEE.EmployeeID. The recordsource for this form
is TRANSFERS.

I have 2 combo boxes for the from & to company fields which are each bound,
respectively with the key.

OK -- HOW do I update/populate the EMPLOYEE.CompanyID with the "to-company"
field (Combo16) from the "Transfers" screen? I know it should be something
like:

Me.[EMPLOYEE.CompanyID] = Me.Combo16.Column(0)

I'm confused because this is a "pop up" form. I believe the "LEFT" side of
this statement is incorrect.

Please help this "dummy" out; it will be greatly appreciated.

Thanks.

 

[June7 via AccessMonster.com]

'Me.' only refers to the form/report currently on. You are referring to two
different forms so it can only be use for one side of the expression. I use
VBA not macros so in the AfterUpdate event of Combo16, include:
[Form_EMPLOYEE].CompanyID = Me.Combo16.Column(0)
Of course, must use the name of your employee form, I am just guessing that
this might be it.

I have 3 tables: EMPLOYEE, COMPANY, and TRANSFERS

In EMPLOYEE, EmployeeID is PK with CompanyID as FK. In TRANSFERS,
TransferID is PK, EmployeeID is FK, and there are 2 fields named FromCompany
and ToCompany; which are FK's to COMPANY (CompanyID).

On my Employee Form, I have a command button called "Add Transfers"; which
activates a macro to display the "Transfers" form; after setting the
TRANSFERS.EmployeeID to EMPLOYEE.EmployeeID. The recordsource for this form
is TRANSFERS.

I have 2 combo boxes for the from & to company fields which are each bound,
respectively with the key.

OK -- HOW do I update/populate the EMPLOYEE.CompanyID with the "to-company"
field (Combo16) from the "Transfers" screen? I know it should be something
like:

Me.[EMPLOYEE.CompanyID] = Me.Combo16.Column(0)

I'm confused because this is a "pop up" form. I believe the "LEFT" side of
this statement is incorrect.

Please help this "dummy" out; it will be greatly appreciated.

Thanks.

 

[June7 via AccessMonster.com]

Edit previous: 'Me.' refers to the form/report who's code module it is in. I
don't use macros so can't say if can be used in them or how.

'Me.' only refers to the form/report currently on. You are referring to two
different forms so it can only be use for one side of the expression. I use
VBA not macros so in the AfterUpdate event of Combo16, include:
[Form_EMPLOYEE].CompanyID = Me.Combo16.Column(0)
Of course, must use the name of your employee form, I am just guessing that
this might be it.

I have 3 tables: EMPLOYEE, COMPANY, and TRANSFERS

[quoted text clipped - 22 lines]

Thanks.

 

[laskowv]

OK, I left out a piece. I want to accomplish this in my "Save Record"
command button; just in case they change their mind or enter it for the wrong
person. Here is what I put in the VBA code, but it errored with "Can't find
the "|" expression you are referring to".

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
'Set Member's CompanyID to new CompanyID
[Form_EMPLOYEE].CompanyID = Me.Combo16.Column(0)

Do I need to add "Forms![Form_EMPLOYEE" ? When I look on the form control
source is CompanyID. The Record source for the Form_EMPLOYEE is a built
query from the ....

'Me.' only refers to the form/report currently on. You are referring to two
different forms so it can only be use for one side of the expression. I use
VBA not macros so in the AfterUpdate event of Combo16, include:
[Form_EMPLOYEE].CompanyID = Me.Combo16.Column(0)
Of course, must use the name of your employee form, I am just guessing that
this might be it.

I have 3 tables: EMPLOYEE, COMPANY, and TRANSFERS

In EMPLOYEE, EmployeeID is PK with CompanyID as FK. In TRANSFERS,
TransferID is PK, EmployeeID is FK, and there are 2 fields named FromCompany
and ToCompany; which are FK's to COMPANY (CompanyID).

On my Employee Form, I have a command button called "Add Transfers"; which
activates a macro to display the "Transfers" form; after setting the
TRANSFERS.EmployeeID to EMPLOYEE.EmployeeID. The recordsource for this form
is TRANSFERS.

I have 2 combo boxes for the from & to company fields which are each bound,
respectively with the key.

OK -- HOW do I update/populate the EMPLOYEE.CompanyID with the "to-company"
field (Combo16) from the "Transfers" screen? I know it should be something
like:

Me.[EMPLOYEE.CompanyID] = Me.Combo16.Column(0)

I'm confused because this is a "pop up" form. I believe the "LEFT" side of
this statement is incorrect.

Please help this "dummy" out; it will be greatly appreciated.

Thanks.

 

[laskowv]

Thanks for all the help, but I found the fix in the AfterUpdate event:

Forms![Form_EMPLOYEE]!CompanyID = Me.Combo16.Column(0)

I swear, I am really confused on when and where to use the " ! " or the " .
" when referring to the different objects.

OK, I left out a piece. I want to accomplish this in my "Save Record"
command button; just in case they change their mind or enter it for the wrong
person. Here is what I put in the VBA code, but it errored with "Can't find
the "|" expression you are referring to".

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
'Set Member's CompanyID to new CompanyID
[Form_EMPLOYEE].CompanyID = Me.Combo16.Column(0)

Do I need to add "Forms![Form_EMPLOYEE" ? When I look on the form control
source is CompanyID. The Record source for the Form_EMPLOYEE is a built
query from the ....

'Me.' only refers to the form/report currently on. You are referring to two
different forms so it can only be use for one side of the expression. I use
VBA not macros so in the AfterUpdate event of Combo16, include:
[Form_EMPLOYEE].CompanyID = Me.Combo16.Column(0)
Of course, must use the name of your employee form, I am just guessing that
this might be it.

I have 3 tables: EMPLOYEE, COMPANY, and TRANSFERS

In EMPLOYEE, EmployeeID is PK with CompanyID as FK. In TRANSFERS,
TransferID is PK, EmployeeID is FK, and there are 2 fields named FromCompany
and ToCompany; which are FK's to COMPANY (CompanyID).

On my Employee Form, I have a command button called "Add Transfers"; which
activates a macro to display the "Transfers" form; after setting the
TRANSFERS.EmployeeID to EMPLOYEE.EmployeeID. The recordsource for this form
is TRANSFERS.

I have 2 combo boxes for the from & to company fields which are each bound,
respectively with the key.

OK -- HOW do I update/populate the EMPLOYEE.CompanyID with the "to-company"
field (Combo16) from the "Transfers" screen? I know it should be something
like:

Me.[EMPLOYEE.CompanyID] = Me.Combo16.Column(0)

I'm confused because this is a "pop up" form. I believe the "LEFT" side of
this statement is incorrect.

Please help this "dummy" out; it will be greatly appreciated.

Thanks.

 

[June7 via AccessMonster.com]

As I understand it, generally, use the '.' when writing code so you can get
intellisense which will list the form/report controls and control
properties/methods, use '!' (bang) with recordset fields and also in Access
objects and when you need to refer to a collection (Tables, Forms, Reports -
note the plural for a collection). Both work in VBA code but the bang is
needed in Access objects and saved queries. For instance, this expression is
in a textbox ControlSource:
=IIf([Forms]![DataSoilsAgg]![tbxSet] Like "1*",IIf(nz([Forms]![DataSoilsAgg]!
[ctr1]![tbxLabOneHalf],100)=100,Null,IIf([SScond]="BA" Or [SScond]="CA",100-
[Forms]![DataSoilsAgg]![ctr1]![tbxLabOneHalf])),Null)

If you typed your code
Forms![Form_EMPLOYEE]!CompanyID = Me.Combo16.Column(0)
as
[Form_EMPLOYEE].CompanyID = Me.Combo16.Column(0)
then you should see intellisense popup after you type each dot. And as you
type CompanyID it should autoexpand the text as it finds a match, same with
Combo16 and Column. This aids in making sure the control or property actually
exists with the name you are typing. This assumes you have the correct
Library Reference selected from the VBA code editor menu.

In VBA, this is a reference to a field of a form's recordsource: If Me!Metric
= True Then

Again in VBA, this references a subform (ctrTests is the container control
holding the subform):
Forms!Sample!ctrTests.Form.Requery

Thanks for all the help, but I found the fix in the AfterUpdate event:

Forms![Form_EMPLOYEE]!CompanyID = Me.Combo16.Column(0)

I swear, I am really confused on when and where to use the " ! " or the " .
" when referring to the different objects.

OK, I left out a piece. I want to accomplish this in my "Save Record"
command button; just in case they change their mind or enter it for the wrong

[quoted text clipped - 43 lines]