Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Loop Texts (A2k)

    The following code should, fill a row of texts with the "Workgroup" information from qryStaff.
    At the moment, it doesn't.
    I managed to solve it with a list box and a manual Requery Command button, but would rather implement it via code.
    Here's what I have so far.
    The problem lies in "strWhere"
    If I set debug points, strWhere returns ""
    Everything is declared correctly. (I think)

    Dim strFirstDate As String
    Dim strWhere As String

    For x = 1 To cNumRows
    Set tgl = Me.Controls("tglUnit" & Format(x, "00"))
    If tgl.Tag <> "" Then
    strFirstDate = "#" & Format(madDisplay(1), "mm/dd/yyyy") & "#"
    'strWhere = cUnitPK & "=" & tgl.Tag & _
    ' " AND " & cFromFld & " <= " & strFirstDate & _
    ' " AND " & cThruFld & " >= " & strFirstDate
    strWhere = "SELECT qryStaff.Unit, qryStaff.Workgroup FROM qryStaff WHERE (((qryStaff.Unit)=[forms]![frmAttendance]![txtStaffID]));"
    Me("txtPeriod" & Format(x, "00")) = DLookup(cPeriodPK, cPeriodRS, strWhere)
    Me("txtEmp" & Format(x, "00")) = DLookup(cWorkgroup, cPeriodRS, strWhere)
    End If
    Next x

    This is what it was before I changed it:-

    Dim strFirstDate As String
    Dim strWhere As String

    For x = 1 To cNumRows
    Set tgl = Me.Controls("tglUnit" & Format(x, "00"))
    If tgl.Tag <> "" Then
    strFirstDate = "#" & Format(madDisplay(1), "mm/dd/yyyy") & "#"
    strWhere = cUnitPK & "=" & tgl.Tag & _
    " AND " & cFromFld & " <= " & strFirstDate & _
    " AND " & cThruFld & " >= " & strFirstDate
    Me("txtPeriod" & Format(x, "00")) = DLookup(cPeriodPK, cPeriodRS, strWhere)
    Me("txtEmp" & Format(x, "00")) = DLookup(cWorkgroup, cPeriodRS, strWhere)
    End If
    Next x

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Texts (A2k)

    Dave,

    Set a breakpoint on the line If tgl.Tag <> "" Then.
    When the code pauses here, single-step through it with F8.
    Do the lines after it such as strFirstDate = ... get executed, or does the code always jump to End If?
    strWhere will only be assigned a value after strWhere = ... has been executed; when the line is highlighted in yellow it hasn't been executed yet.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Texts (A2k)

    Hans
    Here's the process.
    On the old strWhere, the line includes tgl.Tag , where I have not referenced this in my strWhere.
    So I think, my strWhere doesn't use the statement as it should ??
    Do you think so ?
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Texts (A2k)

    Dave,

    strWhere is used as a WHERE condition, so you shouldn't try to fit an entire SQL statement into it, only the part after WHERE. Here are two possibilities to try (I don't know what your new form is doing, so I'm just guessing here):

    strWhere = "Unit=[forms]![frmAttendance]![txtStaffID]"

    or

    strWhere = "Unit = " & tgl.Tag

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Texts (A2k)

    <P ID="edit" class=small>(Edited by D Willett on 02-Sep-03 15:25. Edited to show better pic of requirements.)</P>Hans
    Neither worked so I changed it a little, taking in consideration, the original strWhere is needed elswhere.

    Dim strFirstDate As String
    Dim strWhere As String
    Dim strWG As String

    For x = 1 To cNumRows
    Set tgl = Me.Controls("tglUnit" & Format(x, "00"))
    If tgl.Tag <> "" Then
    strFirstDate = "#" & Format(madDisplay(1), "mm/dd/yyyy") & "#"
    strWhere = cUnitPK & "=" & tgl.Tag & _
    " AND " & cFromFld & " <= " & strFirstDate & _
    " AND " & cThruFld & " >= " & strFirstDate
    strWG = "Unit=[forms]![frmAttendance]![txtStaffID]"
    Me("txtPeriod" & Format(x, "00")) = DLookup(cPeriodPK, cPeriodRS, strWhere)
    Me("txtEmp" & Format(x, "00")) = DLookup(cWorkgroup, cPeriodRS, strWhere)
    End If
    Next x

    But this won't work because I do not mention the workgroup and doesn't fit into the statement.
    I can go back to the other way I made it work, via a hidden list.
    I just thought if I could use the rowsource query as strWhere, it would work.
    Obviously not.
    Don't worry if this is not solved. It's about time I racked my brain cells anyway.
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Texts (A2k)

    Shouldn't

    Me("txtEmp" & Format(x, "00")) = DLookup(cWorkgroup, cPeriodRS, strWhere)

    be

    Me("txtEmp" & Format(x, "00")) = DLookup(cWorkgroup, cPeriodRS, strWG)

    and are you sure that the record set to look in for the employee is cPeriodRS?

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Texts (A2k)

    Hans
    In theory, yes the syntax is ok.
    cPeriodRS is the constant for tblAttPeriod holding, the PeriodID, Unit, UnitID, FromDate, ThruDate and recently added "Workgroup".
    The problem lies in pushing "Workgroup" into the cPeriodRS.
    I did this manually and it worked out ok, It's linking the "Workgroup" with the Unit, as I did with Master and Child with the list.
    Then we have to pull that DLookup info to the correct tglUnit.

    I'm close to this so I'll go back from the start and go thru it again.
    Thanks for the help anyway Hans.
    I'll let you know how I get on. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Loop Texts (A2k)

    Hans
    I sorted it, possibly the long way around, but effective none the less.
    On the main form, a list which looks in qryStaff and links the required Unit and pulls the Workgroup info:

    SELECT qryStaff.Unit, qryStaff.Workgroup
    FROM qryStaff
    WHERE (((qryStaff.Unit)=[forms]![frmAttendance]![txtStaffID]));


    This is fired from the ToggleRed function:

    Private Sub Togglered()
    Dim z As Integer
    For z = 1 To 17
    If Me.Controls("TglUnit" & Format(z, "00")) = True Then
    Me("tglUnit" & Format$(z, "00")).ForeColor = vbRed
    Me.lstWorkGroup.Requery
    lstWorkGroup = lstWorkGroup.ItemData(0)
    Else
    Me("tglUnit" & Format$(z, "00")).ForeColor = vbDarkBlue
    End If
    Next z
    End Sub


    txtWorkGroup on the main form :-
    =lstWorkGroup.Column(1)

    And then txtWorkGroup is pushed into the table via an strSQL statement.

    And then:-

    Me("txtEmp" & Format(x, "00")) = DLookup(cWorkgroup, cPeriodRS, strWhere)

    Shows the information in txtEmp,s
    Not quite the way I wanted to but it'll do for now.
    All I have to do now is edit the already existent strSQL statement and the error checking procedure's.


    Jobs a good un,Bob's your uncle. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Loop Texts (A2k)

    Good for you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •