ms access query and vba code issue

[ThingKing88]

I have created a calendar form where information is coming from different areas as well as being interactive. The last part of the calendar is not working out so well. One of the criteria is actual hours. I have a query that gives me actual hours. I created a form from the query, but the only value that shows up on the calendar is the first record in the form/query. The following is what I have in the Control Source box for the text box on the form:

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

There are 42 text boxes like this on the form. I would like to have all the records to appear.

I have tried vb code for this, but it didn't work. see below:

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

 

[MajP]

not really sure if I under stand your double loop. This is my guess (untested)

you have 42 pairs of fields

txtDateA1 and txtAHA1
...
txtDateA42 and txtAHA42

you want to find the matching hours value for a given date in the textgboxes and then fill in the corresponding hours.

my guess

Public Sub PutInActualHours()

    Dim strWhere As String
    Dim f As Access.Form
    Dim ctl As Access.Control
    Dim servDate As String
    Dim clientID As Long
    Set f = Forms!frmCalendar
    clientID = f.fldClientID
    'put a number in the tag of each control 1-42
    For Each ctl In f.Controls
      If ctl.Tag <> "" Then
        servDate = "#" & ctl.Value & "#"
        strWhere = "ServiceDate = " & servDate & " AND ClientID = " & clientID
        f.Controls("txtAHA" & ctl.Tag) = Nz(DLookup("actualHours", "qryActualHours", strWhere), 0)
      End If
    Next ctl
End If

 

[dhookom]

It isn't clear what you would like to appear on the form. Do you want a total per date or somehow display potentially multiple ActualHours in a single date?

If you want to display multiple records per day, consider using a subform. I would probably use 42 text boxes with the first bound to the start date (probably the first Sunday date). Every other text box would have a control source that adds 1, 2, 3,.. 41 to the start date. Then add 42 copies of the same subform to display the ActualHour records. Set the appropriate link Master to filter the subform to the appropriate text box.

Duane
Hook'D on Access
MS Access MVP

 

[ThingKing88]

Sorry I wasn't clear. It's been fustrating me for about a week. Let me try again.

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 Public Sub PutInActualHours() as mentioned in my first post.

I hope this was a little more clearer?

Any thoughts?