Record counter at button Next/Previous (1 Viewer)

shafara7

Registered User
Joined
May 8, 2017
Messages
118
I have a form which shows the task of employees for the whole week and it works like a calendar.
The is a small circle on top of the button Previous and button Next, which shows the number of tasks in the previous and next week.

The problem is, I have to manage the task calendar for 2 site locations.
I only want the calendar to show the task on 1 location, which I just update some values on the related tables.
But the circle that shows the number of tasks for the previous and next week still count for the second location. I have the screenshot attached for reference.

Below is the code for the small circle with number of task..
Code:
Private Sub dotUpdate()
    Dim prv As Integer, nxt As Integer
    prv = DCount("*", "tblMeasurementBill", "IIf(IsNull(.........)")
    nxt = DCount("*", "tblMeasurementBill", "IIf(IsNull(.........)")

    picDotPrev.Visible = (prv <> 0)
    lblDotPrev.value = IIf(prv <> 0, prv, "")
    picDotNext.Visible = (nxt <> 0)
    lblDotNext.value = IIf(nxt <> 0, nxt, "")
End Sub
There is a tblMeasuringEquipment where a field [lngGroup] is assigned to which location it belongs to.
The form Calendar only includes lngGroup which has number 1 - 5, which is the first location.
lngGroup 6 - 10 are for second location, which I don't want to include.

So I tried editing my code to..
Code:
Private Sub dotUpdate()
    Dim prv As Integer, nxt As Integer
    prv = DCount("*", "tblMeasurementBill", "IIf(IsNull(.........)" WHERE tblMeasuringEquipment.lngGroup <6 )
    nxt = DCount("*", "tblMeasurementBill", "IIf(IsNull(.........)" WHERE tblMeasuringEquipment.lngGroup <6 )

    picDotPrev.Visible = (prv <> 0)
    lblDotPrev.value = IIf(prv <> 0, prv, "")
    picDotNext.Visible = (nxt <> 0)
    lblDotNext.value = IIf(nxt <> 0, nxt, "")
End Sub
But it end up not showing anything.
Any idea how the codes should be structured or placed?
 

Attachments

static

Registered User
Joined
Nov 2, 2015
Messages
823
Wouldn't the Where clause be the same as that used to display items on the calendar, just for the different weeks?
 

shafara7

Registered User
Joined
May 8, 2017
Messages
118
I am not sure I understand what you mean.
The items displayed on the calendar does not require VBA code. But these buttons do need a code that should prevent counting all Task for both site locations.
 

static

Registered User
Joined
Nov 2, 2015
Messages
823
The items displayed on the calendar does not require VBA code.
Why? Because the form is based on query that has a WHERE clause.

DCount has Criteria which is a WHERE clause without the word WHERE. Use the same where, just plug different week/site values into it.

If you can't do that you'll need to provide more details. IIf(IsNull(.........) doesn't tell us anything.
 

shafara7

Registered User
Joined
May 8, 2017
Messages
118
I want to share you the file but the size exceed 2MB, even after I had simplified everything.
So to make things clear for you, I copied the VBA codes in Note.
So the form name is frmRunde and the calendar is a subform in loop.

The thing that will differs between the two location is lngGruppe from tblMessanlage.
 

Attachments

static

Registered User
Joined
Nov 2, 2015
Messages
823
So the form is made up of 25 sub forms.
And the sub form contains a procedure called setContent which sets the recordsource from sql based on a date and group

Code:
Sub setContent(pDate As Date, pGroup As Long)
    datum = pDate
    gruppe = pGroup
    RecordSource = sql(datConvert(pDate), pGroup)
End Sub
So you can use the WHERE

Code:
Function sqlWHERE(pDatum As String, pGruppe As Long) As String
    sqlWHERE = "IIf(IsNull([datMessungSoll]),arbeitstageAddieren([datEinarbeitIst],getKPI('Messung', False)),[datMessungSoll]) = #" & pDatum & "# AND IIf(IsNull([indAbweichendeMessanlage]),[qryBelegungMessanlage].[lngGruppe],[tblMessanlage].[lngGruppe]) = " & pGruppe & " AND ISNULL(datMessungIst) AND (NOT ISNULL(datEinarbeitSoll) OR NOT ISNULL(datEinarbeitIst) OR NOT ISNULL (datMessungSoll))"
End Function
but you'd need to pass in a date range and use BETWEEN in the SQL

[datMessungSoll] between #" & pDatumfrom & "# AND #" & pDatumto & "#
 

shafara7

Registered User
Joined
May 8, 2017
Messages
118
Thank you for your reply!
So I need to add sqlWHERE in my VBA?
And can you explain a little bit why it has to be related with the dates?
I mean, I thought it has to do with a field on the location table.
 

static

Registered User
Joined
Nov 2, 2015
Messages
823
You need to add the function to your code, yes.
And you need to modify it to accept 2 dates instead one so that it returns all records for the period instead records for a particular day.

Function sqlWHERE(pDatumFrom As String, pDatumTo As String, pGruppe As Long) As String

And can you explain a little bit why it has to be related with the dates?
I mean, I thought it has to do with a field on the location table.
Well you are showing events for a time period.

The groups/sites/locations/whatever are hard coded 1-5.

If you only have 5 groups in the table you can take pGruppe out of the SQL so that it returns everything. Otherwise you need to restrict it.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom