query populates form but 2 fields are not being saved...

  • Thread starter Thread starter justatad
  • Start date Start date
J

justatad

I have a query as follows....

SELECT tblAssignment.id, tblAssignment.projectSetupID,
tblAssignment.scenario, tblAssignment.targetPerson,
tblAssignment.assignmentDueDate, tblAssignment.certificate,
tblAssignment.comment, tblAssignment.mysteryType, tblFeeStructure.labor
AS feeLabor, tblFeeStructure.reimburseables AS feeReimburseables,
IIf(IsEmpty(tblAssignment.labor),tblAssignment.labor,tblFeeStructure.labor)
AS labor,
IIf(IsEmpty(tblAssignment.reimbursables),tblAssignment.reimbursables,tblFeeStructure.reimburseables)
AS reimbursables, tblAssignment.member
FROM ((tblClientProfile INNER JOIN (tblProjectNumbers INNER JOIN
tblProjectSetup ON tblProjectNumbers.id=tblProjectSetup.projectID) ON
tblClientProfile.id=tblProjectNumbers.clientID) INNER JOIN
tblFeeStructure ON tblClientProfile.defaultFeeID=tblFeeStructure.id)
INNER JOIN tblAssignment ON
tblProjectSetup.id=tblAssignment.projectSetupID;

The idea is if tblAssignment.labor and/or tblAssignment.reimbursables
(currency) is empty then replace it with tblFeeStructure.labor and/or
tblFeeStructure.reimburseables.

This information gets populated into a form which can: update existing
records or add new records. All the information from the query comes
into the form perfectly.

When the data is saved...all fields are saved except the labor field
and the reimbursables fields. On the form the field names are labor
and reimbursables and the control source is the same.

The tblAssignment also has fields for labor and reimbursables.

What am I missing....I need help. Thanks
 
justatad said:
I have a query as follows....

SELECT tblAssignment.id, tblAssignment.projectSetupID,
tblAssignment.scenario, tblAssignment.targetPerson,
tblAssignment.assignmentDueDate, tblAssignment.certificate,
tblAssignment.comment, tblAssignment.mysteryType,
tblFeeStructure.labor AS feeLabor, tblFeeStructure.reimburseables AS
feeReimburseables,
IIf(IsEmpty(tblAssignment.labor),tblAssignment.labor,tblFeeStructure.labor)
AS labor,
IIf(IsEmpty(tblAssignment.reimbursables),tblAssignment.reimbursables,tblFeeStructure.reimburseables)
AS reimbursables, tblAssignment.member
FROM ((tblClientProfile INNER JOIN (tblProjectNumbers INNER JOIN
tblProjectSetup ON tblProjectNumbers.id=tblProjectSetup.projectID) ON
tblClientProfile.id=tblProjectNumbers.clientID) INNER JOIN
tblFeeStructure ON tblClientProfile.defaultFeeID=tblFeeStructure.id)
INNER JOIN tblAssignment ON
tblProjectSetup.id=tblAssignment.projectSetupID;

The idea is if tblAssignment.labor and/or tblAssignment.reimbursables
(currency) is empty then replace it with tblFeeStructure.labor and/or
tblFeeStructure.reimburseables.

This information gets populated into a form which can: update existing
records or add new records. All the information from the query comes
into the form perfectly.

When the data is saved...all fields are saved except the labor field
and the reimbursables fields. On the form the field names are labor
and reimbursables and the control source is the same.

The tblAssignment also has fields for labor and reimbursables.

What am I missing....I need help. Thanks
Click to expand...

Calculated fields do not need to be saved (since they can be calculated). Just
remove those fields from your table. The fact that your table has fields named
the same as the derived fields in your query is irrelevant. Your form is bound
to the query, not the table.
 
OK....I agree...this is the kicker....

The fee is a default fee and can be changed after later by the user.
 
justatad said:
OK....I agree...this is the kicker....

The fee is a default fee and can be changed after later by the user.
Click to expand...

Okay, that is different. Then you need to have that expression "pushed" into
the bound control (perhaps on the push of a button) then the user will be able
to override the value afterwards or leave it and it will be saved to the table.

You need your query to include the raw field from the table into which you want
the value saved and the name of that field is what goes into the ControlSource
of the control on the form.
 
Back
Top