Update Form Field from another Form

[dbarmer]

There are probably serveral ways this can be done, however I am having
trouble with them all.

I have a form with preloaded data that can be edited. However, if the data
needs to be changed, then the user should be able to pick the correct
[Employee] in this case.

The field itslef cannot be a lookup because it's tied to a table that is NOT
a lookup field - Nor should it be.

So, I have a command button called "Change Employee Name" where if clicked
will open another form, allowing the user to select an employee in a drop
down box.

Through VBA code, I was trying to update the main form with the new data on
the second open form. I cannot get this to work. There are two fileds on
the main form that need to be updated (an Employee and Employee No)

What is the BEST way to do this? I have come so close to geting it to work,
however it is not quite there. I need a fresh approach.

Can someone help?

 

[Mike Painter]

There are probably serveral ways this can be done, however I am having
trouble with them all.

I have a form with preloaded data that can be edited. However, if
the data needs to be changed, then the user should be able to pick
the correct [Employee] in this case.

The field itslef cannot be a lookup because it's tied to a table that
is NOT a lookup field - Nor should it be.

So, I have a command button called "Change Employee Name" where if
clicked will open another form, allowing the user to select an
employee in a drop down box.

Through VBA code, I was trying to update the main form with the new
data on the second open form. I cannot get this to work. There are
two fileds on the main form that need to be updated (an Employee and
Employee No)

What is the BEST way to do this? I have come so close to geting it
to work, however it is not quite there. I need a fresh approach.

Can someone help?

It sounds like you are keeping information in two places. This is almost
always a mistake.
If not then refreshing the form after making the changes will reload the new
information.
Using a modal form for the change makes this easier.

 

[Klatuu]

You are correct, it should not be a lookup field. Nothing should ever be.
It is really quite simple. Rather than a text box for the employee, change
it to a combo box and base the combo box's row source on the employee table.
When you select an employee, it will insert the employee's name in the
form's record source.

 

[dbarmer]

Thanks tkelley! It was the .Value that made the difference. NOW, I think
we are almost there. The problem now it, is changes the field to the last
time a user changed employee. So If I close everthing out and re-open, then
choose Change Employee, it is correct then - some kind of refresh problem.
I've tried Me.Refresh. Any ideas? These forms are offically on my last
nerve.

I assume the EmployeeNo is either in your dropdown as another column, or
somewhere on the [ChangeEmployeeName] form. If my assumption is correct, in
the on click event of your [Apply Changes] button on the [ChangeEmployeeName]
form:

Separate fields:
Forms!frmTargetForm![EmployeeName.value = me.EmployeeName
Forms!frmTargetForm![EmployeeNo.value = me.EmployeeNo

Columns in your dropdown:
'where EmployeeName is the first column
Forms!frmTargetForm![EmployeeName.value = me.cmbEmployee.column(0,0)

'where EmployeeNo is the second column
Forms!frmTargetForm![EmployeeNo.value = me.cmbEmployee.column(0,1)

There are probably serveral ways this can be done, however I am having
trouble with them all.

I have a form with preloaded data that can be edited. However, if the data
needs to be changed, then the user should be able to pick the correct
[Employee] in this case.

The field itslef cannot be a lookup because it's tied to a table that is NOT
a lookup field - Nor should it be.

So, I have a command button called "Change Employee Name" where if clicked
will open another form, allowing the user to select an employee in a drop
down box.

Through VBA code, I was trying to update the main form with the new data on
the second open form. I cannot get this to work. There are two fileds on
the main form that need to be updated (an Employee and Employee No)

What is the BEST way to do this? I have come so close to geting it to work,
however it is not quite there. I need a fresh approach.

Can someone help?

 

[dbarmer]

This is complicated - The data being in two places - The master tables are
from ODBC - using another program - I have no choice at this point. The
..Value Option worked - almost. There is a refresh problem. see my other
post. Any ideas?

I agree if the information is being kept in two different tables. I didn't
understand that to be the case.

There are probably serveral ways this can be done, however I am having
trouble with them all.

[quoted text clipped - 19 lines]

Can someone help?

It sounds like you are keeping information in two places. This is almost
always a mistake.
If not then refreshing the form after making the changes will reload the new
information.
Using a modal form for the change makes this easier.

 

[dbarmer]

Thanks - Klatuu! You were right, it was simple - I was trying to make it
really complicated. The Combox Box allowed me to create a sql querry to the
ODBC table with the needed field name!

GEEZ! Sometimes simple is the best, Thanks Again!!

You are correct, it should not be a lookup field. Nothing should ever be.
It is really quite simple. Rather than a text box for the employee, change
it to a combo box and base the combo box's row source on the employee table.
When you select an employee, it will insert the employee's name in the
form's record source.

There are probably serveral ways this can be done, however I am having
trouble with them all.

I have a form with preloaded data that can be edited. However, if the
data
needs to be changed, then the user should be able to pick the correct
[Employee] in this case.

The field itslef cannot be a lookup because it's tied to a table that is
NOT
a lookup field - Nor should it be.

So, I have a command button called "Change Employee Name" where if clicked
will open another form, allowing the user to select an employee in a drop
down box.

Through VBA code, I was trying to update the main form with the new data
on
the second open form. I cannot get this to work. There are two fileds on
the main form that need to be updated (an Employee and Employee No)

What is the BEST way to do this? I have come so close to geting it to
work,
however it is not quite there. I need a fresh approach.

Can someone help?

 

[Klatuu]

The .Value has nothing to do with it.
It is the default property returned when you refer to a control.

Thanks tkelley! It was the .Value that made the difference. NOW, I
think
we are almost there. The problem now it, is changes the field to the last
time a user changed employee. So If I close everthing out and re-open,
then
choose Change Employee, it is correct then - some kind of refresh problem.
I've tried Me.Refresh. Any ideas? These forms are offically on my last
nerve.

I assume the EmployeeNo is either in your dropdown as another column, or
somewhere on the [ChangeEmployeeName] form. If my assumption is correct,
in
the on click event of your [Apply Changes] button on the
[ChangeEmployeeName]
form:

Separate fields:
Forms!frmTargetForm![EmployeeName.value = me.EmployeeName
Forms!frmTargetForm![EmployeeNo.value = me.EmployeeNo

Columns in your dropdown:
'where EmployeeName is the first column
Forms!frmTargetForm![EmployeeName.value = me.cmbEmployee.column(0,0)

'where EmployeeNo is the second column
Forms!frmTargetForm![EmployeeNo.value = me.cmbEmployee.column(0,1)

There are probably serveral ways this can be done, however I am having
trouble with them all.

I have a form with preloaded data that can be edited. However, if the
data
needs to be changed, then the user should be able to pick the correct
[Employee] in this case.

The field itslef cannot be a lookup because it's tied to a table that is
NOT
a lookup field - Nor should it be.

So, I have a command button called "Change Employee Name" where if
clicked
will open another form, allowing the user to select an employee in a
drop
down box.

Through VBA code, I was trying to update the main form with the new data
on
the second open form. I cannot get this to work. There are two fileds
on
the main form that need to be updated (an Employee and Employee No)

What is the BEST way to do this? I have come so close to geting it to
work,
however it is not quite there. I need a fresh approach.

Can someone help?