Setting a field in a foreign record in VB

[rcoutts]

I want to set some fields in a foreign record to the values on the fields in my local table. Right now, I get the values in the foreign record using DLookup. E.g.,

   Rate1 = DLookup("[RateAmt]", "tblHourlyTypes", "[RateNum] = 1")
[\code]
I'm looking to do the opposite -- set [RateAmt] to the value of [Rate1] in the record where [tblHourlyTypes].[RateNum] = 1.  I thought there might exist a function that worked something like this:
[code]
   DSet("[RateAmt]", [Rate1], "tblHourlyTypes", "[RateNum] = 1")
[\code]
But I can't find a function like 'DSet'.  What's the best way to do this?

Thanks!
Rich

 

[ToeShot]

I don't know how much coding you have done but one thing that you can try is manipulating the recordset. For example

Dim db As Database
Dim rs As Recordset

Set db = Currentdb
Set rs = db.OpenRecordSet("tblHourlyTypes", dbOpenDynaSet)

rs.MoveFirst

While Not rs.EOF
If (rs(RateNum) = 1) Then
rs(RateAmt) = Me.Rate1
rs.Update
end if
rs.MoveNext
Wend
rs.close
Set rs = Nothing

What this is doing is Assigning all the records in your atble to an Object called rs.
With that done we move to the first record then loop through all of the records until we come to the end of the file(EOF).
Inside the loop we test the field That holds the Rate Number.
If it is true then we set the rate amount to rate1 and update that record then move to the next record.
If it is false then we just move to the next record.
we repeat this process untill we have gone through all the records.

HTH