Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-01-2017, 12:37 AM   #1
shafara7
Newly Registered User
 
Join Date: May 2017
Posts: 118
Thanks: 23
Thanked 0 Times in 0 Posts
shafara7 is on a distinguished road
Record counter at button Next/Previous

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?
Attached Images
File Type: jpg Runde.jpg (51.1 KB, 45 views)

shafara7 is offline   Reply With Quote
Old 06-01-2017, 02:15 AM   #2
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Record counter at button Next/Previous

Wouldn't the Where clause be the same as that used to display items on the calendar, just for the different weeks?
static is offline   Reply With Quote
Old 06-01-2017, 02:48 AM   #3
shafara7
Newly Registered User
 
Join Date: May 2017
Posts: 118
Thanks: 23
Thanked 0 Times in 0 Posts
shafara7 is on a distinguished road
Re: Record counter at button Next/Previous

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.

shafara7 is offline   Reply With Quote
Old 06-01-2017, 03:04 AM   #4
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Record counter at button Next/Previous

Quote:
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.
static is offline   Reply With Quote
Old 06-01-2017, 03:57 AM   #5
shafara7
Newly Registered User
 
Join Date: May 2017
Posts: 118
Thanks: 23
Thanked 0 Times in 0 Posts
shafara7 is on a distinguished road
Re: Record counter at button Next/Previous

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.
Attached Files
File Type: txt Access Code Runde.txt (10.3 KB, 23 views)
shafara7 is offline   Reply With Quote
Old 06-01-2017, 07:54 AM   #6
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Record counter at button Next/Previous

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 & "#
static is offline   Reply With Quote
Old 06-01-2017, 10:15 PM   #7
shafara7
Newly Registered User
 
Join Date: May 2017
Posts: 118
Thanks: 23
Thanked 0 Times in 0 Posts
shafara7 is on a distinguished road
Re: Record counter at button Next/Previous

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.

shafara7 is offline   Reply With Quote
Old 06-02-2017, 12:39 AM   #8
static
Newly Registered User
 
Join Date: Nov 2015
Posts: 823
Thanks: 4
Thanked 194 Times in 180 Posts
static will become famous soon enough static will become famous soon enough
Re: Record counter at button Next/Previous

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

Quote:
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.

static is offline   Reply With Quote
Reply

Tags
button , indicator

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create a button to copy from a previous record and paste into current record? I_M_Desperate Modules & VBA 3 06-24-2013 07:00 AM
Prevent Next record button from saving previous JayJay00 Forms 4 06-30-2011 01:37 PM
[SOLVED] Problem with previous record button jaspa Forms 0 04-12-2006 05:28 AM
Blank Record saved when click on previous button dooda Forms 3 07-31-2005 07:56 PM
Button to retreive previous record Rookie89 Forms 2 10-12-2003 12:40 AM




All times are GMT -8. The time now is 12:08 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World