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


L

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.
 
Ad

Advertisements

L

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 ....



June7 via AccessMonster.com said:
'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.
 
Ad

Advertisements

L

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.



laskowv said:
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 ....



June7 via AccessMonster.com said:
'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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top