Moving throught a subform recorset (1 Viewer)

D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
I'm adding two pictures for this It's something I've been trying to work out for ever.

I built this with help from a book back in 2000 This it was the developers hand book.

At the moment the bottom list is limited to 28 employees if you look at the second Image below
2020-04-10 (9).png
You can see I have got around the problem by adding a department limit but yes I would still want to keep that limitor and will add it to the project manager project but What I really want to do is display in the bottom list 28 employees but the active one must be in the default 28 then have a back or fowards button so I can move to the next 28 or back as the case may be.

I have updated this with a diary in the employee example but I like this one

This is the code The Department limit list uses hope somebody can help me
SQL: 
Public Sub GetData(Opt As Integer, Optional Dep As Long)
'==========================================================================
'Project      : Disc Jocky 2000 V 0.1
'Description  : Updates Controls on form Depending on Crosstab
'Called By    : Tab Control On Change
'Calls        : None
'Parameters   : None
'Returns      : None
'Author       : Michael Javes
'Date Created : 20-05-01
'Rev. History :Added to DPM And Updated
'Comments     :
'==========================================================================
Dim IntColumnCount As Integer            'Count Of Entrys In Employees Table
Dim IntCountControls As Integer         'Total Controls Will Be 14 But Use Count For Adding Controls Latter
Dim I As Integer                        'Used In Loop
Dim m_RecQry As DAO.Recordset           'The Recordset for Crosstab Data
Dim StrName As String
Dim m_Db As Database                    'Current Database Object
Dim Zfrm As Form, Rfrm As Recordset
Dim StrSQL As String
Dim Slts As DAO.Recordset, SltSQL As String
On Error Resume Next
Set Zfrm = Me.ZfrmDiaryEmpsSubform.Form
Set Rfrm = Me.ZfrmDiaryEmpsSubform.Form.RecordsetClone
Set m_Db = CurrentDb()
'Clear Old Data
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDiaryDeleteTemps"
DoCmd.OpenQuery "QryDiaryAppendTimes"
DoCmd.SetWarnings True
Zfrm.Requery
SltSQL = Null
'int([DateEntered])=" & Format(Me![txtFrom], "\#" & StrDateFormatExtra & "\#")
SltSQL = " AND int([SlotDate])=" & Format(Me![CtlDate].Value, "\#mm/dd/yyyy\#")
Select Case Me![cboDepartment].Column(2)
    Case "D"
        If Opt = 2 And Not IsMissing(Dep) Then StrSQL = " AND [DepartmentID]=" & Dep
        Set m_RecQry = m_Db.OpenRecordset("Select * FROM tblEmployees WHERE ListDiary=True" & StrSQL, dbOpenSnapshot)
    Case "G"
        Set m_RecQry = m_Db.OpenRecordset("Select * FROM QryDiaryEmployeesGroups WHERE [GroupID]=" & Me![cboDepartment], dbOpenSnapshot)
End Select
        
m_RecQry.MoveLast
m_RecQry.MoveFirst
'Get The Count for the Header section Controls
IntColumnCount = m_RecQry.RecordCount + 1
'Now Count The Control
IntCountControls = Zfrm.Detail.Controls.Count - 1
'Turn Of Sreen UpDates
Me.Painting = False

'Reset All Coloumns
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = "Column" & I
        Zfrm.Controls("lbl" & I).Tag = ""
    End If
Next I
'Reset Control To Visable
For I = 1 To IntCountControls - 1
    Zfrm.Controls("lbl" & I).Visible = True
    Zfrm.Controls("txt" & I).Visible = True
    
Next I

'Fill In The Information For Controls
'Miss First One
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = m_RecQry("EmpName")
        Zfrm.Controls("lbl" & I).Tag = m_RecQry("EmpID")
        'Fill The Controls With Details If Any
        Set Slts = m_Db.OpenRecordset("SELECT * FROM QryDiaryGetSlots WHERE [Employee]=" & m_RecQry("EmpID") & SltSQL, dbOpenSnapshot)
            Do While Not Slts.EOF
                Rfrm.FindFirst "TimeID=" & Slts("TimeID")
                    If Not Rfrm.NoMatch Then
                        Rfrm.Edit
                        Rfrm("EMP" & I - 1) = Slts("SlotTime")
                        Zfrm.Controls("txt" & I).Tag = m_RecQry("EmpID")
                        Rfrm.Update
                    End If
                Slts.MoveNext
            Loop
        m_RecQry.MoveNext
    End If
Next I
'Now Hide Rest Of Controls
For I = IntColumnCount + 1 To IntCountControls
    Zfrm.Controls("lbl" & I).Visible = False
    Zfrm.Controls("txt" & I).Visible = False
Next I
m_RecQry.Close
'Now Update Form
Me.Painting = True
End Sub
 

Attachments

  • 2020-04-10 (8).png
    2020-04-10 (8).png
    47.8 KB · Views: 260
Uncle Gizmo

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 12:46
Joined
Jul 9, 2003
Messages
16,304
I'm not clear about the problem, so I thought I would explain what I think the problem is and go from there. You have two sub-forms "slot times" and "booking times" both in continuous form view.

Referring to the image with the two employees Mick and Carol, listed in "booking times" you want to select an employee from the "booking for employee" combo box and have that employee appear in a new column adjacent to Mick and Carol. You want to be able to do this for up to 25 columns. If there are more than 25 employees to list you want to be able to paginate across the form showing more employee details.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:46
Joined
May 21, 2018
Messages
8,555
I am not sure what the "Active One" is, but you should be able to determine the absolute position of the that record. Assume it is record 50.
Save that number in module level variable
private Position as long
position = ...absolutePosition - 1

Then the list should be based on a recordset
select Top 28 * from somequery as A where A.someID not in (select Top " & Position & " * from someQuery as B).

in the forward button
position = position + 28
in the back button
if Position > 28 then
postion = position - 28
else
position = 0
end if

FYI, this has not been tried, but that is the general Idea how I do it.
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
Thanks @MajP I'll give it a go tomorrow and let you know I thought the absolute position was there somewhere but never managed to figure it out your a life saver many thanks
 
Last edited:
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:46
Joined
May 21, 2018
Messages
8,555
Select Top 28 not in select top 49, should give you records 50 to 78. So record 50 would be the top record.
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
have it working sort of I know what the problem is and think I have a work around the code below works but misses the first record this is because The top wont seem to except any value less than 1 the thing errors with a SQL error.
Code: 
Public Sub GetData(Opt As Integer, Optional Dep As Long)
Dim IntColumnCount As Integer            'Count Of Entrys In Employees Table
Dim IntCountControls As Integer         'Total Controls Will Be 14 But Use Count For Adding Controls Latter
Dim I As Integer                        'Used In Loop
Dim m_RecQry As DAO.Recordset           'The Recordset for Crosstab Data
Dim strName As String
Dim m_Db As Database                    'Current Database Object
Dim Zfrm As Form, Rfrm As Recordset
Dim StrSQL As String
Dim Slts As DAO.Recordset, SltSQL As String, SQLNotIn As String

On Error Resume Next
Set Zfrm = Me.ZfrmDiaryEmpsSubform.Form
Set Rfrm = Me.ZfrmDiaryEmpsSubform.Form.RecordsetClone
Set m_Db = CurrentDb()
'Clear Old Data
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDiaryDeleteTemps"
DoCmd.OpenQuery "QryDiaryAppendTimes"
DoCmd.SetWarnings True
Zfrm.Requery
SltSQL = ""
position = 1
SQLNotIn = "Not In (SELECT Top " & position & " QryEmployeesDiary1.StaffID FROM QryEmployeesDiary1)))"
'int([DateEntered])=" & Format(Me![txtFrom], "\#" & StrDateFormatExtra & "\#")
SltSQL = " AND [SlotDate]=" & Format(Me![CtlDate], "\#mm/dd/yyyy\#")
'Select Case Me![cboDepartment].Column(2)
'   Case "D"
        'If Opt = 2 And Not IsMissing(Dep) Then StrSQL = " AND [DepartmentID]=" & Dep
      
        Set m_RecQry = m_Db.OpenRecordset("Select Top 25 * FROM QryEmployeesDiary WHERE (((QryEmployeesDiary.StaffID) " & SQLNotIn, dbOpenSnapshot)
        Debug.Print m_RecQry.RecordCount
  '  Case "G"
       ' Set m_RecQry = m_Db.OpenRecordset("Select * FROM QryDiaryEmployeesGroups WHERE [GroupID]=" & Me![cboDepartment], dbOpenSnapshot)
'End Select
      
m_RecQry.MoveLast
m_RecQry.MoveFirst
'Get The Count for the Header section Controls
IntColumnCount = m_RecQry.RecordCount + 1
'Now Count The Control
IntCountControls = Zfrm.Detail.Controls.Count - 1
'Turn Of Sreen UpDates
Me.Painting = False

'Reset All Coloumns
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = "Column" & I
        Zfrm.Controls("lbl" & I).Tag = ""
    End If
Next I
'Reset Control To Visable
For I = 1 To IntCountControls - 1
    Zfrm.Controls("lbl" & I).Visible = True
    Zfrm.Controls("txt" & I).Visible = True
  
Next I

'Fill In The Information For Controls
'Miss First One
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = m_RecQry("LoginName") ', 1)) & " " & m_RecQry("LastName")
        Zfrm.Controls("lbl" & I).Tag = m_RecQry("StaffID")
        'Fill The Controls With Details If Any
        Set Slts = m_Db.OpenRecordset("SELECT * FROM QryDiaryGetSlots WHERE [Employee]='" & m_RecQry("StaffID") & "'" & SltSQL, dbOpenSnapshot)
            Do While Not Slts.EOF
                Rfrm.FindFirst "TimeID=" & Slts("TimeID")
                    If Not Rfrm.NoMatch Then
                        Rfrm.Edit
                        Rfrm("EMP" & I - 1) = Slts("SlotTime")
                        Zfrm.Controls("txt" & I).Tag = m_RecQry("StaffID")
                        Rfrm.Update
                    End If
                Slts.MoveNext
            Loop
        m_RecQry.MoveNext
    End If
Next I
position = m_RecQry.AbsolutePosition + 1
Debug.Print position
'Now Hide Rest Of Controls
For I = IntColumnCount + 1 To IntCountControls
    Zfrm.Controls("lbl" & I).Visible = False
    Zfrm.Controls("txt" & I).Visible = False
Next I
m_RecQry.Close
'Now Update Form
Me.Painting = True
End Sub

My work around is to test the value of position and run a different query if it comes back as 0 I.E. I would run the top 25 without the Subquery attachment witch I'll edit so it can run by itself.

Idears comment more than welcome thanks for the help so far I'm going to import a load of employees from the employee example that should give me over 40 to play with.

P.S. There are only 25 columns for employees 2 for descriptions 1 at each end of form like the employee example time sheet

thanks mick
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
I now Have a working version with 2 employees so will import the test set and work from there

This is the code I'm running
Code: 
If position <= 25 Then
    SQLNotIn = ""
Else
    SQLNotIn = " WHERE (((QryEmployeesDiary.StaffID) Not In (SELECT Top " & position & " QryEmployeesDiary.StaffID FROM QryEmployeesDiary)))"
End If
 
Last edited:
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
This is the final code that works although I only added another 46 employees I intend to make that number over 50 so I can get more than 2 pages

I also intend to add the Departments but that can be done tomorrow :)
The form load has position =1

The buttons code NOTE: I still need to do some work as I need to enable/disable them depending on position plus add error code

Thanks @MajP I would probably still be scratching my head if you haden't pointed me in the right direction many thanks
This will be available in the project manager Downloads I will export both forms and the query to a blank db so user can replace there's

The Project Manager Developer Project links are on my home page


Code: 
Private Sub Cmd_Next25_Click()
position = position + 25
CtlDate_AfterUpdate

End Sub

Private Sub Cmd_Previous25_Click()
If position > 25 Then
    position = position - 25
Else
    position = 1
End If
CtlDate_AfterUpdate

End Sub
Code: 
Public Sub GetData(Opt As Integer, Optional Dep As Long)
Dim IntColumnCount As Integer            'Count Of Entrys In Employees Table
Dim IntCountControls As Integer         'Total Controls Will Be 14 But Use Count For Adding Controls Latter
Dim I As Integer                        'Used In Loop
Dim m_RecQry As DAO.Recordset           'The Recordset for Crosstab Data
Dim strName As String
Dim m_Db As Database                    'Current Database Object
Dim Zfrm As Form, Rfrm As Recordset
Dim StrSQL As String
Dim Slts As DAO.Recordset, SltSQL As String, SQLNotIn As String

On Error Resume Next
Set Zfrm = Me.ZfrmDiaryEmpsSubform.Form
Set Rfrm = Me.ZfrmDiaryEmpsSubform.Form.RecordsetClone
Set m_Db = CurrentDb()
'Clear Old Data
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDiaryDeleteTemps"
DoCmd.OpenQuery "QryDiaryAppendTimes"
DoCmd.SetWarnings True
Zfrm.Requery
SltSQL = ""

If position < 25 Then
    SQLNotIn = ""
Else
    SQLNotIn = " WHERE (((QryEmployeesDiary.StaffID) Not In (SELECT Top " & position & " QryEmployeesDiary.StaffID FROM QryEmployeesDiary)))"
End If
SltSQL = " AND [SlotDate]=" & Format(Me![CtlDate], "\#mm/dd/yyyy\#")
'Select Case Me![cboDepartment].Column(2)
 '   Case "D"
        'If Opt = 2 And Not IsMissing(Dep) Then StrSQL = " AND [DepartmentID]=" & Dep
        
        Set m_RecQry = m_Db.OpenRecordset("Select Top 25 * FROM QryEmployeesDiary" & SQLNotIn, dbOpenSnapshot)
'  Case "G"
       ' Set m_RecQry = m_Db.OpenRecordset("Select * FROM QryDiaryEmployeesGroups WHERE [GroupID]=" & Me![cboDepartment], dbOpenSnapshot)
'End Select
        
m_RecQry.MoveLast
m_RecQry.MoveFirst
'Get The Count for the Header section Controls
IntColumnCount = m_RecQry.RecordCount + 1
'Now Count The Control
IntCountControls = Zfrm.Detail.Controls.Count - 1
'Turn Of Sreen UpDates
Me.Painting = False

'Reset All Coloumns
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = "Column" & I
        Zfrm.Controls("lbl" & I).Tag = ""
    End If
Next I
'Reset Control To Visable
For I = 1 To IntCountControls - 1
    Zfrm.Controls("lbl" & I).Visible = True
    Zfrm.Controls("txt" & I).Visible = True
    
Next I

'Fill In The Information For Controls
'Miss First One
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = m_RecQry("LoginName") ', 1)) & " " & m_RecQry("LastName")
        Zfrm.Controls("lbl" & I).Tag = m_RecQry("StaffID")
        'Fill The Controls With Details If Any
        Set Slts = m_Db.OpenRecordset("SELECT * FROM QryDiaryGetSlots WHERE [Employee]='" & m_RecQry("StaffID") & "'" & SltSQL, dbOpenSnapshot)
            Do While Not Slts.EOF
                Rfrm.FindFirst "TimeID=" & Slts("TimeID")
                    If Not Rfrm.NoMatch Then
                        Rfrm.Edit
                        Rfrm("EMP" & I - 1) = Slts("SlotTime")
                        Zfrm.Controls("txt" & I).Tag = m_RecQry("StaffID")
                        Rfrm.Update
                    End If
                Slts.MoveNext
            position = position + 1
            Loop
        m_RecQry.MoveNext
    End If
Next I
position = position - 1

'Now Hide Rest Of Controls
For I = IntColumnCount + 1 To IntCountControls
    Zfrm.Controls("lbl" & I).Visible = False
    Zfrm.Controls("txt" & I).Visible = False
Next I
m_RecQry.Close
'Now Update Form
Me.Painting = True
End Sub
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
Been at it 6 hrs and still not right so Wasn't getting anything from the absaluteposition maybe I was applying it wrong anyways I knew the query work in it goes to the next set just having problems after that I have 51 employees entered so 2 pages.
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
I've now got it moving foward on the page 3 I.E. Record 51 52

But the Moveing back is a bitch just keeps on going to page 1 but I am a lot closer

Code: 
Private Sub Cmd_Next25_Click()
On Error GoTo HandleErr
'May need to get a total for the records just to make
'Sure it don't overrun
'Only count those with list diary = true
position = position + 25
CtlDate_AfterUpdate
    If (position + position) > E Then
        Me![Cmd_Previous25].SetFocus
        Me![Cmd_Next25].Enabled = False
    End If
    Me![Cmd_Previous25].Enabled = True
'Debug.Print "NEXT: " & E, position
HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
            Call GlobalErrs(Err.Number, Err.Description, Err.Source, "frmDiary", "Cmd_Next25_Click")
            Resume HandleExit
        Resume
    End Select
End Sub
Not going to post the Previous button as it's not working yet

Code: 
Public Sub GetData(Opt As Integer, Optional Dep As Long)
Dim IntColumnCount As Integer            'Count Of Entrys In Employees Table
Dim IntCountControls As Integer         'Total Controls Will Be 14 But Use Count For Adding Controls Latter
Dim I As Integer                        'Used In Loop
Dim m_RecQry As DAO.Recordset           'The Recordset for Crosstab Data
Dim strName As String
Dim m_Db As Database                    'Current Database Object
Dim Zfrm As Form, Rfrm As Recordset
Dim StrSQL As String
Dim Slts As DAO.Recordset, SltSQL As String, SQLNotIn As String

On Error GoTo HandleErr

Set Zfrm = Me.ZfrmDiaryEmpsSubform.Form
Set Rfrm = Me.ZfrmDiaryEmpsSubform.Form.RecordsetClone
Set m_Db = CurrentDb()
'Clear Old Data
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryDiaryDeleteTemps"
DoCmd.OpenQuery "QryDiaryAppendTimes"
DoCmd.SetWarnings True
Zfrm.Requery
SltSQL = ""

If position < 25 Then
    SQLNotIn = ""
Else
    SQLNotIn = " WHERE (((QryEmployeesDiary.StaffID) Not In (SELECT Top " & position & " QryEmployeesDiary.StaffID FROM QryEmployeesDiary)))"
End If
SltSQL = " AND [SlotDate]=" & Format(Me![CtlDate], "\#mm/dd/yyyy\#")
'Select Case Me![cboDepartment].Column(2)
 '   Case "D"
        'If Opt = 2 And Not IsMissing(Dep) Then StrSQL = " AND [DepartmentID]=" & Dep
        
        Set m_RecQry = m_Db.OpenRecordset("Select Top 25 * FROM QryEmployeesDiary" & SQLNotIn, dbOpenSnapshot)
'  Case "G"
       ' Set m_RecQry = m_Db.OpenRecordset("Select * FROM QryDiaryEmployeesGroups WHERE [GroupID]=" & Me![cboDepartment], dbOpenSnapshot)
'End Select
        
m_RecQry.MoveLast
m_RecQry.MoveFirst
'Get The Count for the Header section Controls
IntColumnCount = m_RecQry.RecordCount + 1
'Now Count The Control
IntCountControls = Zfrm.Detail.Controls.Count - 1
'Turn Of Sreen UpDates
Me.Painting = False

'Reset All Coloumns
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = "Column" & I
        Zfrm.Controls("lbl" & I).Tag = ""
    End If
Next I
'Reset Control To Visable
For I = 1 To IntCountControls - 1
    Zfrm.Controls("lbl" & I).Visible = True
    Zfrm.Controls("txt" & I).Visible = True
    
Next I

'Fill In The Information For Controls
'Miss First One
For I = 1 To IntColumnCount
    If I > 1 Then
        Zfrm.Controls("lbl" & I).Caption = m_RecQry("LoginName") ', 1)) & " " & m_RecQry("LastName")
        Zfrm.Controls("lbl" & I).Tag = m_RecQry("StaffID")
        'Fill The Controls With Details If Any
        Set Slts = m_Db.OpenRecordset("SELECT * FROM QryDiaryGetSlots WHERE [Employee]='" & m_RecQry("StaffID") & "'" & SltSQL, dbOpenSnapshot)
            Do While Not Slts.EOF
                Rfrm.FindFirst "TimeID=" & Slts("TimeID")
                    If Not Rfrm.NoMatch Then
                        Rfrm.Edit
                        Rfrm("EMP" & I - 1) = Slts("SlotTime")
                        Zfrm.Controls("txt" & I).Tag = m_RecQry("StaffID")
                        Rfrm.Update
                    End If
                Slts.MoveNext
            Loop
        position = position + 1
        m_RecQry.MoveNext
    End If
Next I
position = position - 25

Debug.Print "lOOP:" & position

On Error Resume Next

'Now Hide Rest Of Controls
For I = IntColumnCount + 1 To IntCountControls
    Zfrm.Controls("lbl" & I).Visible = False
    Zfrm.Controls("txt" & I).Visible = False
Next I
m_RecQry.Close
'Now Update Form
Me.Painting = True

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case 2501 'Cancel = True
            Exit Sub
        Case Else
            Call GlobalErrs(Err.Number, Err.Description, Err.Source, "frmDiary", "TaskID_Enter")
            Resume HandleExit
        Resume
    End Select
End Sub

I'm calling it a day and may decide to just make the previous button a reset and be done with it for the next 20 years lol

keep safe mick
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:46
Joined
May 21, 2018
Messages
8,555
See if this demo helps.
 

Attachments

  • NextNrecords.zip
    160.4 KB · Views: 328
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
I think I'll Back it up then start again as my problem started because I wan't using ABS() At least I think thats what it was

thanks @MajP

I was thinking about using MOD??
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
Sorry pat but it's the bottom selection screen I nearly have it working thanks to @MajP still getting an error with the starting value which will always be 0 the SQL really dont like that so I'm going to create a sql statement for the startup when direction =0
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
Well I can now move forward to page 2 and 3 then back to 2 then 1 then back up the pages but as soon as I go back it loses its place I've put it to bed now will go over it tomorrow as it needs cleaning up before I go any further.

Night all keep safe mick
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:46
Joined
May 21, 2018
Messages
8,555
Starting value which will always be 0 the SQL really dont like that so I'm going to create a sql statement for the startup when direction =0
Click to expand...
Not sure if this is your issue, but if you see in my example I had to be careful about 0 and account for that. This will give you an error if you resolve the string to:

Select Top 25.... WHERE SomeID Not in (Select TOP 0 * ....)
The subquery needs a value between 1 and recordcount.
 
D

Dreamweaver

Well-known member
Local time
Today, 12:46
Joined
Nov 28, 2005
Messages
2,466
Thanks @MajP Got It working had to alter it a bit from your example which I would put in the example db's as that will be very usefull to others.

There was no problems moving Up but movng back worked for the first set but failed second time around so i did the below in the prevoius button:

Code: 
Private Sub Cmd_Previous25_Click()
On Error GoTo HandleErr

If position = 25 Then position = 0
 GetData 1, -25

HandleExit:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        Case Else
            Call GlobalErrs(Err.Number, Err.Description, Err.Source, "frmDiary", "Cmd_Next25_Click")
            Resume HandleExit
        Resume
    End Select
End Sub

And the main code I've only posted the important bit to make it easier to see
Code: 
totalRecords = DCount("*", "QryEmployeesDiary")

SQLNotIn = "SELECT TOP 25 * FROM QryEmployeesDiary"

If position + Direction > totalRecords Then
        SQLNotIn = SQLNotIn & " WHERE [StaffID] Not In (SELECT TOP " & position & " B.StaffID FROM QryEmployeesDiary As B)"
ElseIf Not (position + Direction <= 0) Then
    SQLNotIn = SQLNotIn & " WHERE [StaffID] Not In (SELECT TOP " & Direction + position & " B.StaffID FROM QryEmployeesDiary As B)"
    position = Direction + position
End If
    Debug.Print "Total: " & totalRecords & " Direction: "; Direction & " , position: "; position & " Plus Both: " & Direction + position
SltSQL = " AND [SlotDate]=" & Format(Me![CtlDate], "\#mm/dd/yyyy\#")

'Select Case Me![cboDepartment].Column(2)
 '   Case "D"
        'If Opt = 2 And Not IsMissing(Dep) Then StrSQL = " AND [DepartmentID]=" & Dep
        
        Set m_RecQry = m_Db.OpenRecordset(SQLNotIn, dbOpenSnapshot)

I'm going to add another 25 employees make it a total of 77 then add in the Department limit

Many thanks for all your help I think it would have been bugging me for another 20 years lol

keep safe mick
 
You must log in or register to reply here.

Similar threads

Z
Solved Label not displaying properly after selecting a record
Replies
13
Views
1,490
bastanu
bastanu
D
Slow Loading form
Replies
4
Views
887
Dreamweaver
D
G
Question Pass 3 MultiSelect ListBox Columns to a Subform
Replies
4
Views
714
arnelgp
arnelgp
H
Problem referencing a control on a tabbed subform
2 3 4
Replies
68
Views
4,615
HillTJ
H
D
Enabling and disabling controls via a "access level"
Replies
4
Views
602
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom