ms access vb to cycle through form record derived from a query

[ThingKing88]

I have a form that creates a calendar. The form consists of 42 text boxes for the day of the month (txtDayA), 42 text boxes for expected hours for an particular client associated with the day of the month (txtEHA) and 42 text boxes for actual hours for a particular client for the day of the month (txtAHA). In addition I have 42 text boxes that are always hidden that takes the txtDayA text boxes and converts it into a date (txtDateA).

When select a month and year from a combo box, the form creates a calendar based on that month and year. The text boxes not used in the month are hidden. The form works in respect to showing the appropriate text boxes (day, expected hours and actual hours) and showing the actual day and the expected hours for the client picked. The expected hours for each client comes from a query.

The issue I am having is the value of the actual hours. I had to create the query because some clients were serviced more than once on certain days. I have a query called qryEHvsAH that has the following fields: Client, Client ID, Service Date, Actual Hours. From the query I created a Form (frmEHvsAH). I thought I might be able to use the following IIf statement in the control source of each Actual Hours text box (txtAHA):

=IIf([txtDateA26]=Forms!frmEHvsAH![Service Date] And [fldClientID]=Forms!frmEHvsAH![Client ID],Forms!frmEHvsAH![Actual Hours],"0")

This was the IIf staement for txtAHA26. This statement works, but only for the first record of the form. It doesn't cycle through the form. So, the record of the form has a date of 8/21/2009 for client X and the next record is on October 19, 2009 for the same client (The form is sorted by the Client field). The calendar for client X in the month of August has all zeros in the actual hours except for 8/21/2009. The Actual Hours appear on 8/21/2009. But on 1/19/2009 a zero appears.

I also tried the following vba code:

Public Sub PutInActualHours()

Dim strSQL As String
Dim f As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim counter As Integer
Dim fld As Field
Dim i As Integer, j As Integer

For j = 0 To 41

Set f = Forms!frmCalendar

strSQL = "select [qryActualHours].ActualHours from [qryActualHours] where (([qryActualHours].ServiceDate = " _
& f("txtDateA" & j) & " and [qryActualHours].ClientID = " & f!fldClientID & ")) order by ServiceDate;"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
rs.MoveFirst
Do While Not rs.EOF
For i = 0 To 41
If rs!ServiceDate = f("txtDateA" & i) Then
rs.FindFirst "ServiceDate=#" & f("txtDateA" & i) & "#"
If Not rs.NoMatch Then
f("txtAHA" & i) = rs!actualhours
'Else
' f("txtAHA" & i) = 0
End If
End If
Next i
rs.MoveNext
Loop
End If

Next j

End Sub

I have tried other code, but I get an error saying that I can't assign a value to the object f("txtAHA" i)

can anyone help?

thank you

 

[dhookom]

I'm not sure how you got the stuff to work that does work. Apparently this is all read-only. If that is the case, I would just create a very wide crosstab query that has all of the values you need in a single record. Then bind the form to the crosstab.

You won't need any code other than to requery the form when you change the year and month.

BTW: IMO, this is wrong:

=IIf([txtDateA26]=Forms!frmEHvsAH![Service Date] And [fldClientID]=Forms!frmEHvsAH![Client ID],Forms!frmEHvsAH![Actual Hours],"0")

Your IIf() might return a number (Actual Hours) or a string ("0"). I would remove the quotes from around the 0.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

"Long live the Thing King!" _{Table Pages at the Segmented Table}

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[MajP]

or something like

public function getActualHours(dtmDay as variant,clientID as long) as single
dim strWhere as string
strWhere = "serviceDate = #" & dtmDay & "# AND clientID = " & clientID
if isdate(dtmDay)
getActualHours = dlookup("actualHours","qryActualHours",strWhere)
end if
end function

then paste into each control source (change the integer portion)
=getActualHours([txtDateA26],[clientID])