By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,437 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Recordset rs.Find return results

P: 78
In my database I have a varying amount of thins set up. I am trying to display multiple shifts for each day of the month in a calendar form.

I have the calendar set up properly to display the days. I have the correct information generated in table.

I am having trouble displaying multiple records. Right now it only displays one record. Either the first, last or next or whatever I call.

I will include the code below for the section where it occurs. Any help would be appreciated. I am stumped on this one.

Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInData()
  2. Dim SQL As String
  3. Dim f As Form
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim MyDate As Date
  7. Dim i As Integer
  8.  
  9. Set f = Forms!frmCalendar
  10.  
  11.     For i = 1 To 37
  12.         f("text" & i) = Null
  13.     Next i
  14.  
  15.     SQL = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & f!month & " AND YEAR(ShiftDetailDate) = " & f!year & ")) ORDER BY tblShiftDetail.ShiftDetailDate, tblShiftDetail.ShiftNumber, tblEmployee.EmployeeNumber;"
  16.  
  17.     Set db = CurrentDb()
  18.     Set rs = db.OpenRecordset(SQL)
  19.  
  20.     If rs.RecordCount > 0 Then
  21.         For i = 1 To 37
  22.             If IsDate(f("date" & i)) Then
  23.                 MyDate = Format((f("date" & i)), "mm/dd/yyyy")
  24.                 rs.FindFirst ("ShiftDetailDate = #" & MyDate & "#")
  25.                     If Not rs.NoMatch Then
  26.                         f("text" & i) = rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & rs!ShiftStartTime & "-" & rs!ShiftEndTime
  27.                     End If
  28.             End If
  29.         Next i
  30.     End If
  31.  
  32.     rs.Close
  33.     Set rs = Nothing
  34.     Set db = Nothing
  35.  
  36. End Sub
  37.  
Thank you
Nick
Apr 22 '08 #1
Share this Question
Share on Google+
14 Replies


ADezii
Expert 5K+
P: 8,601
In my database I have a varying amount of thins set up. I am trying to display multiple shifts for each day of the month in a calendar form.

I have the calendar set up properly to display the days. I have the correct information generated in table.

I am having trouble displaying multiple records. Right now it only displays one record. Either the first, last or next or whatever I call.

I will include the code below for the section where it occurs. Any help would be appreciated. I am stumped on this one.

Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInData()
  2. Dim SQL As String
  3. Dim f As Form
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim MyDate As Date
  7. Dim i As Integer
  8.  
  9. Set f = Forms!frmCalendar
  10.  
  11.     For i = 1 To 37
  12.         f("text" & i) = Null
  13.     Next i
  14.  
  15.     SQL = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & f!month & " AND YEAR(ShiftDetailDate) = " & f!year & ")) ORDER BY tblShiftDetail.ShiftDetailDate, tblShiftDetail.ShiftNumber, tblEmployee.EmployeeNumber;"
  16.  
  17.     Set db = CurrentDb()
  18.     Set rs = db.OpenRecordset(SQL)
  19.  
  20.     If rs.RecordCount > 0 Then
  21.         For i = 1 To 37
  22.             If IsDate(f("date" & i)) Then
  23.                 MyDate = Format((f("date" & i)), "mm/dd/yyyy")
  24.                 rs.FindFirst ("ShiftDetailDate = #" & MyDate & "#")
  25.                     If Not rs.NoMatch Then
  26.                         f("text" & i) = rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & rs!ShiftStartTime & "-" & rs!ShiftEndTime
  27.                     End If
  28.             End If
  29.         Next i
  30.     End If
  31.  
  32.     rs.Close
  33.     Set rs = Nothing
  34.     Set db = Nothing
  35.  
  36. End Sub
  37.  
Thank you
Nick
You can try this logic out, but I can't guarantee anything since it is past my bedtime, and my eyes are getting droopy (LOL). Let me know how you make out:
Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInData()
  2. Dim SQL As String
  3. Dim f As Form
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim MyDate As Date
  7. Dim i As Integer
  8.  
  9. Set f = Forms!frmCalendar
  10.  
  11. For i = 1 To 37
  12.   f("text" & i) = Null
  13. Next i
  14.  
  15. SQL = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & f!Month & _
  16.       " AND YEAR(ShiftDetailDate) = " & f!Year & ")) ORDER BY tblShiftDetail.ShiftDetailDate, " & _
  17.       "tblShiftDetail.ShiftNumber, tblEmployee.EmployeeNumber;"
  18.  
  19. Set db = CurrentDb()
  20. Set rs = db.OpenRecordset(SQL)
  21.  
  22. If rs.RecordCount > 0 Then
  23.   For i = 1 To 37
  24.     If IsDate(f("date" & i)) Then
  25.       MyDate = Format((f("date" & i)), "mm/dd/yyyy")
  26.       Do While Not rs.EOF
  27.         If rs![ShiftDetailDate] = "#" & MyDate & "#" Then
  28.           f("text" & i) = rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & _
  29.                           rs!ShiftStartTime & "-" & rs!ShiftEndTime
  30.             Exit Do
  31.         End If
  32.         rs.MoveNext
  33.       Loop
  34.       rs.MoveFirst
  35.     End If
  36.   Next i
  37. End If
  38.  
  39. rs.Close
  40. Set rs = Nothing
  41. Set db = Nothing
  42. End Sub
Apr 23 '08 #2

P: 78
No Adezzi That didnt work. I am not sure what exactly is going on but clearly it is not recalling multiple files. The logic there seems accurate but do I need to save it in a different place to recall it? I am not sure.

Thanks for the try though...late nights can cause delirium...one should only take in small quantities.

Nick
Apr 23 '08 #3

ADezii
Expert 5K+
P: 8,601
No Adezzi That didnt work. I am not sure what exactly is going on but clearly it is not recalling multiple files. The logic there seems accurate but do I need to save it in a different place to recall it? I am not sure.

Thanks for the try though...late nights can cause delirium...one should only take in small quantities.

Nick
Hello Nick, this time let's try Plan B since I'm actually awake now, not that this will make a difference. (LOL).
Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInData()
  2. Dim SQL As String
  3. Dim f As Form
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim MyDate As Date
  7. Dim i As Integer
  8. Dim strShiftDetails As String
  9.  
  10. Set f = Forms!frmCalendar
  11.  
  12. For i = 1 To 37
  13.   f("text" & i) = Null
  14. Next i
  15.  
  16. SQL = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & f!Month & _
  17.       " AND YEAR(ShiftDetailDate) = " & f!Year & ")) ORDER BY tblShiftDetail.ShiftDetailDate, " & _
  18.       "tblShiftDetail.ShiftNumber, tblEmployee.EmployeeNumber;"
  19.  
  20. Set db = CurrentDb()
  21. Set rs = db.OpenRecordset(SQL)
  22.  
  23. If rs.RecordCount > 0 Then
  24.   For i = 1 To 37
  25.     If IsDate(f("date" & i)) Then
  26.       MyDate = Format((f("date" & i)), "mm/dd/yyyy")
  27.       Do While Not rs.EOF
  28.         If rs![ShiftDetailDate] = "#" & MyDate & "#" Then
  29.           strShiftDetails = strShiftDetails & rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & _
  30.                             rs!ShiftStartTime & "-" & rs!ShiftEndTime & vbCrLf
  31.         End If
  32.         rs.MoveNext
  33.       Loop
  34.       f("text" & i) = Left$(strShiftDetails, Len(strShiftDetails) - 2)  'Strip vbCrLf
  35.       strShiftDetails = ""      'Reset
  36.       rs.MoveFirst
  37.     End If
  38.   Next i
  39. End If
  40.  
  41. rs.Close
  42. Set rs = Nothing
  43. Set db = Nothing
  44. End Sub
Apr 23 '08 #4

P: 78
I am sorry to say that didnt work either. I really appreciate your attempts though. I do not fully understand why neither procedure worked. Under my thoughts either should have worked as well. Any other help would be appreciated.

Nick
Apr 23 '08 #5

P: 78
aDezii

You had the right idea. I have something going on now, where I can see the second file as well. However I do not have the loop working. I am going to work on it a little longer and will post if I need help or when I get it. Thank you for your help so far though.

Nick
Apr 23 '08 #6

P: 78
Ok, Now the Code below returns 2 files. I have commented out the Loop because when Used it only returns the first record. Presently there are 4 records to return, but depending on Employee Number that will change So I do not want to depend on that.

Can anyone help me figure out this Loop issue?

ADezii, maybe you can take it from here. You helped me get here between the two of us maybe we can figure it out. LOL

Many Thanks
Nick

Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInData()
  2. Dim sql As String
  3. Dim f As Form
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim MyDate As Date
  7. Dim i As Integer
  8. Dim strShiftDetails As String
  9.  
  10. Set f = Forms!frmCalendar
  11.  
  12.     For i = 1 To 37
  13.         f("text" & i) = Null
  14.     Next i
  15.  
  16.     sql = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & f!month & " AND YEAR(ShiftDetailDate) = " & f!year & ")) ORDER BY tblShiftDetail.ShiftDetailDate, tblEmployee.EmployeeFirstName;"
  17.  
  18.     Set db = CurrentDb()
  19.     Set rs = db.OpenRecordset(sql)
  20.  
  21.     If rs.RecordCount > 0 Then
  22.         For i = 1 To 37
  23.             If IsDate(f("date" & i)) Then
  24.                 MyDate = Format((f("date" & i)), "mm/dd/yyyy")
  25.                 rs.FindFirst ("ShiftDetailDate = #" & MyDate & "#")
  26.                     If Not rs.NoMatch Then
  27.                         strShiftDetails = rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & rs!ShiftStartTime & "-" & rs!ShiftEndTime
  28.                         rs.FindNext ("ShiftDetailDate = #" & MyDate & "#")
  29.                            ' Do While rs!ShiftDetailDate = "#' & MyDate $ '#"
  30.                                 strShiftDetails = strShiftDetails & vbCrLf & rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & rs!ShiftStartTime & "-" & rs!ShiftEndTime
  31.                             'rs.MoveNext
  32.                             'Loop
  33.                         f("text" & i) = strShiftDetails
  34.                   End If
  35.             strShiftDetails = ""
  36.             End If
  37.         Next i
  38.     End If
  39.  
  40.     rs.Close
  41.     Set rs = Nothing
  42.     Set db = Nothing
  43.  
  44. End Sub
  45.  
Apr 23 '08 #7

ADezii
Expert 5K+
P: 8,601
Ok, Now the Code below returns 2 files. I have commented out the Loop because when Used it only returns the first record. Presently there are 4 records to return, but depending on Employee Number that will change So I do not want to depend on that.

Can anyone help me figure out this Loop issue?

ADezii, maybe you can take it from here. You helped me get here between the two of us maybe we can figure it out. LOL

Many Thanks
Nick

Expand|Select|Wrap|Line Numbers
  1. Public Sub PutInData()
  2. Dim sql As String
  3. Dim f As Form
  4. Dim db As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim MyDate As Date
  7. Dim i As Integer
  8. Dim strShiftDetails As String
  9.  
  10. Set f = Forms!frmCalendar
  11.  
  12.     For i = 1 To 37
  13.         f("text" & i) = Null
  14.     Next i
  15.  
  16.     sql = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & f!month & " AND YEAR(ShiftDetailDate) = " & f!year & ")) ORDER BY tblShiftDetail.ShiftDetailDate, tblEmployee.EmployeeFirstName;"
  17.  
  18.     Set db = CurrentDb()
  19.     Set rs = db.OpenRecordset(sql)
  20.  
  21.     If rs.RecordCount > 0 Then
  22.         For i = 1 To 37
  23.             If IsDate(f("date" & i)) Then
  24.                 MyDate = Format((f("date" & i)), "mm/dd/yyyy")
  25.                 rs.FindFirst ("ShiftDetailDate = #" & MyDate & "#")
  26.                     If Not rs.NoMatch Then
  27.                         strShiftDetails = rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & rs!ShiftStartTime & "-" & rs!ShiftEndTime
  28.                         rs.FindNext ("ShiftDetailDate = #" & MyDate & "#")
  29.                            ' Do While rs!ShiftDetailDate = "#' & MyDate $ '#"
  30.                                 strShiftDetails = strShiftDetails & vbCrLf & rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & rs!ShiftStartTime & "-" & rs!ShiftEndTime
  31.                             'rs.MoveNext
  32.                             'Loop
  33.                         f("text" & i) = strShiftDetails
  34.                   End If
  35.             strShiftDetails = ""
  36.             End If
  37.         Next i
  38.     End If
  39.  
  40.     rs.Close
  41.     Set rs = Nothing
  42.     Set db = Nothing
  43.  
  44. End Sub
  45.  
The problem would be much easier to diagnose if you E-Mailed me a copy of the Database or a subset of it, is that possible? If so, I can assure you complete privacy.
Apr 23 '08 #8

P: 78
Absolutely if you want to PM me you email I will send it off.

Thank you

NIck
Apr 24 '08 #9

ADezii
Expert 5K+
P: 8,601
Absolutely if you want to PM me you email I will send it off.

Thank you

NIck
Check your Private Messages, you'll have my Address there soon. Please provide all relevant Details as well.
Apr 24 '08 #10

P: 78
Check your Private Messages, you'll have my Address there soon. Please provide all relevant Details as well.

Azii,

I did figure it out. It is probably a little sloppy but it works. I am working on some other stuf but will post on monday what I have so you can see it.

Thanks

Nick
Apr 25 '08 #11

ADezii
Expert 5K+
P: 8,601
Azii,

I did figure it out. It is probably a little sloppy but it works. I am working on some other stuf but will post on monday what I have so you can see it.

Thanks

Nick
I actually have the revised code which I'll post tonight.
Apr 25 '08 #12

ADezii
Expert 5K+
P: 8,601
Azii,

I did figure it out. It is probably a little sloppy but it works. I am working on some other stuf but will post on monday what I have so you can see it.

Thanks

Nick
  1. Below please find the revised code for the PutInData() Sub-Routine. It has been tested, and is fully operational. You can just replace replace your entire Procedure with this new code, or Copy and Paste over the existing code.
    Expand|Select|Wrap|Line Numbers
    1. Public Sub PutInData()
    2. On Error GoTo Err_PutIndata
    3. Dim sql As String
    4. Dim f As Form
    5. Dim db As DAO.Database
    6. Dim rs As DAO.Recordset
    7. Dim MyDate As Date
    8. Dim i As Integer
    9. Dim strShiftDetails As String
    10.  
    11. Set f = Forms!frmCalendar
    12.  
    13. For i = 1 To 37
    14.   f("text" & i) = Null
    15. Next i
    16.  
    17. sql = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & _
    18.        f!month & " AND YEAR(ShiftDetailDate) = " & f!year & ")) " & _
    19.        "ORDER BY tblShiftDetail.ShiftDetailDate, tblEmployee.EmployeeFirstName;"
    20.  
    21. Set db = CurrentDb()
    22. Set rs = db.OpenRecordset(sql)
    23.  
    24. 'Test Loop for Debugging purposes
    25. 'Do While Not rs.EOF
    26.   'Debug.Print rs![ShiftDetailDate] & " ==> " & rs!EmployeeFirstName & " " & _
    27.               'rs!EmployeeLastName & " " & rs!ShiftStartTime & "-" & rs!ShiftEndTime
    28.   'rs.MoveNext
    29. 'Loop
    30.   'Exit Sub
    31.  
    32. If rs.RecordCount > 0 Then
    33.   For i = 1 To 37
    34.     If IsDate(f("date" & i)) Then
    35.     MyDate = Format((f("date" & i)), "mm/dd/yyyy")
    36.       Do While Not rs.EOF
    37.         If rs![ShiftDetailDate] = MyDate Then
    38.           strShiftDetails = strShiftDetails & rs!EmployeeFirstName & " " & rs!EmployeeLastName & " " & _
    39.                             rs!ShiftStartTime & "-" & rs!ShiftEndTime & vbCrLf
    40.         End If
    41.         rs.MoveNext
    42.       Loop
    43.       If Len(strShiftDetails) > 0 Then
    44.         f("text" & i) = Left$(strShiftDetails, Len(strShiftDetails) - 2)      'Strip vbCrLf
    45.       End If
    46.       strShiftDetails = ""      'Reset
    47.       rs.MoveFirst
    48.     End If
    49.   Next i
    50. End If
    51.  
    52. rs.Close
    53. Set rs = Nothing
    54. Set db = Nothing
    55.  
    56. Exit_PutIndata:
    57.   Exit Sub
    58.  
    59. Err_PutIndata:
    60.   MsgBox Err.Description, vbExclamation, "Error in PutIndata()"
    61.   Resume Exit_PutIndata
    62. End Sub
  2. Be advised that the PutInData() Routine was being called twice when frmCalendar Opens. In the frmCalendar_Load() Event, the next to the last line calls the Cal([month], [year]) Routine. The last line in this Sub-Procedure calls the PutInData() Routine, then returns code execution back to the frmCalendar_Load() Event. The last line in the Load() Event also calls the Cal(m, y) Routine. Since the call to PutInData() needs to exist independently from the Call in the Load() Event, delete the last line in the frmCalendar_Load() Event, namely, Call Cal([month], [year]). Just download the Attachment to see what I mean.
  3. The way frmCalendar is structured, you can not display > 4 Shifts per Day, which is evidenced by only 4 of the 5 Shifts being displayed for April 14, 2008. You need to make provisions for this also.
  4. Any other questions, feel free to ask.
Apr 25 '08 #13

P: 78
That works Perfectlly. Much better then the sloppy version I came up with. The dispplay problem on the calendar is simply text size. Visually you could not see it but it was there.

I have added some more functions to the db and made it more visually pleasing...fixing the problem with not showing more then 4 employees on the calendar. Thank you so much for your help.

Now, I am at the point that I am going to add in time off dates and specific shift dates. I honestly am not sure where to start but I am trying some different things. Any help on this would be appreaciated.

Thank you for everything

Nick
Apr 29 '08 #14

ADezii
Expert 5K+
P: 8,601
That works Perfectlly. Much better then the sloppy version I came up with. The dispplay problem on the calendar is simply text size. Visually you could not see it but it was there.

I have added some more functions to the db and made it more visually pleasing...fixing the problem with not showing more then 4 employees on the calendar. Thank you so much for your help.

Now, I am at the point that I am going to add in time off dates and specific shift dates. I honestly am not sure where to start but I am trying some different things. Any help on this would be appreaciated.

Thank you for everything

Nick
You are quite welcome, Nick.
Apr 29 '08 #15

Post your reply

Sign in to post your reply or Sign up for a free account.