Copy rates to a job table.

[Frank]

I have a program that has a table that contains company rates
(HoldingCoRates) and I want to copy the rates into the job table (Job) so
that when the rates change the data for jobs with the old rates stays
intact. I don't want to use an append query, I have already been through
that and for my purposes this won't work. I just want to copy the rate (SEP)
from (HoldingCoRates) to (SEP1) in the (Job) table. Can anyone help me with
this?

 

[John Vinson]

I have a program that has a table that contains company rates
(HoldingCoRates) and I want to copy the rates into the job table (Job) so
that when the rates change the data for jobs with the old rates stays
intact. I don't want to use an append query, I have already been through
that and for my purposes this won't work. I just want to copy the rate (SEP)
from (HoldingCoRates) to (SEP1) in the (Job) table. Can anyone help me with
this?

How are you entering the new job? What's the Recordsource of the form?
How do you identify which record in HoldingCoRates contains the
desired value of SEP? You should be able to use the AfterUpdate event
of whatever control identifies the current rate to "push" that value
into the Jobs table. Let's say you have a combo box cboHoldingCo;
include SEP as one of the columns in that combo's row source, and use
code like

Private Sub cboHoldingCo_AfterUpdate()
Me!txtSEP1 = Me!cboHoldingCo.Column(2)
End Sub

This assumes that the third field in the combo's rowsource contains
the desired SEP value (Column is zero based).

John W. Vinson[MVP]

 

[Guest]

This should work for you just adjust the tables and fields as required. It
will fill the info for you and delete it and refill each time you open the
form to ensure all data is current. Try it out if it works here it is.

Private Sub Form_Activate()
On Error GoTo Form_Activate_Err

Dim MyDB As Database
Dim Choose As Recordset 'identify source
Dim Chosen As Recordset 'identify source
Dim ChooseFrom As Recordset

Set MyDB = CurrentDb()
Set ChooseFrom = MyDB.OpenRecordset("qryFillChooseList", B_OPEN_DYNASET)
Set Chosen = MyDB.OpenRecordset("qryChosen", DB_OPEN_DYNASET)
Set Choose = MyDB.OpenRecordset("qryChoose", DB_OPEN_DYNASET)

If Not Choose.BOF Then
Choose.MoveFirst
Do
Choose.Delete
Choose.MoveNext
Loop Until Choose.EOF
End If

If Not Chosen.BOF Then
Chosen.MoveFirst
Do
Chosen.Delete
Chosen.MoveNext
Loop Until Chosen.EOF
End If

ChooseFrom.MoveFirst
Do
Choose.AddNew
Choose!id = ChooseFrom!id
Choose!machinecode = ChooseFrom!machinecode
Choose.Update
ChooseFrom.MoveNext
Loop Until ChooseFrom.EOF ' Continue.

Choose.MoveFirst
ChooseList = Choose!id
ChooseList = Choose!machinecode
ChooseList.Requery
ChosenList.Requery

CountFiles

Form_Activate:
Exit Sub

Form_Activate_Err:
MsgBox Error$
Resume Form_Activate

End Sub

 

[Frank]

I don't want the data to be changed once it is entered, I want the rate to
be save and then remain untouched so that when a specific job is recalled
after the rates have changed it will reflect the rated values from when the
job was entered.