Create RecordSet

[qlan]

Hi,

Below is the structure of my tblWBS.

WBSNum Description COS
101 Training 1
104 Admin 0
502 File Testing 1

On my tblProjectTimes, some of my field names are ProjectTimeID ProjectNum WBSNum Hours Billable.

ProjectTimeID is the primary key and it is automatic increment by 1.

When I open my form for ProjectNum 2019VN, For WBSNum, I choose 101 for instance and entered # of Hours to Hours Field. For the Billable Field, I want to do an Event Procedure so it would update where

tblProjectTimes.WBSNum = tblWBS.WBSNum ( In this case, 101), Then take whatever I have under tblWBS.COS to update it to my tblProjectTimes.Billabe. Therefore, I have the procudure like this:

Private Sub WBSNum_LostFocus()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunSQL "UPDATE tblProjectTimes SET Billable=Me!COS" _
& " WHERE WBSNum=[WBSNum]" _
& " AND [ProjectTimeID] = ProjectTimeID " _
& " AND [ProjectNum] = '" & Me!ProjectNum & "'"

End Sub


However, after I chose WBSNum for my tblProjectTimes, it asks for Me!COS Parameter Value instead of updating Billable = Me!COS. Also, after I entered the value , it is updating all the rows that I have for this project instead of only updating the current ProjectTimeID.

I was trying to do the RecordSet hoping it would fix the problem about enter the parameter value for my COS.

Dim MyDb As Database, Bill As Recordset

Set MyDb = CurrentDb
Set Bill = MyDb.OpenRecordset("select COS from tblWBS where [WBSNum] = " & WBSNum, dbOpenSnapshot, dbSeeChanges)
If Bill.EOF Then

Set Bill = Nothing
Me.Requery
End If

However, I still updating all the rows.

Thanks so much in advance for your help!

 

[PHV]

DoCmd.RunSQL "UPDATE tblProjectTimes AS P INNER JOIN tblWBS AS W ON P.WBSNum = W.WBSNum" _
& " SET P.Billable = W.COS" _
& " WHERE P.WBSNum = " & Me!WBSNum _
& " AND P.ProjectTimeID = " & Me!ProjectTimeID _
& " AND P.ProjectNum = '" & Me!ProjectNum & "'"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886