Results 1 to 15 of 15
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    vba Programming that tell user record exist 3times exit sub

    Hello family!

    Let me first say never was a math genius and am not an experience coder;
    Am about to do a visit-log form. I want to track the Foreign key of a person in another table however persons can only have three visit per week i.e. between Monday and Friday would like to trap this information and every time a person_id shows up >=3 the user is blocked from making another entry say a msgbox pops up.. Any suggestions bear with me guys should it start something like

    dim X an interger


    if X >=3 then....

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use DCount() to get the number of visits for required period.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You are asking for suggestions but need to know more

    why does between Monday and Friday matter? can they have more visits over the weekend? or you don't do visits over the weekend?

    Assuming these visits are booked in advance, you need a basis for determining which week you are looking to book, so perhaps you need to select this first otherwise your user could be hunting around entering a date then being told the week is booked, so they try another date to be told the same. So perhaps you need to be providing a user with a list of (the next 4?) weeks where there are 2 or less visits booked.

    are these persons customers or employees? presumably the former

    if the former are these visits by one or more employees?

    How do you make sure an employee is not double booked?

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    As ajax suggested, more info and context needed to offer more focused response. If you are new to database, then I suggest you work through this tutorial from RogersAccessLibrary. There are more tutorials from Rogers and articles on Database Planning and Design here.
    Work through the tutorial (~30 minutes) and you will experience a procedure that can be used with any database.
    Good luck.

  5. #5
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Ajax View Post
    You are asking for suggestions but need to know more

    why does between Monday and Friday matter? can they have more visits over the weekend? or you don't do visits over the weekend?
    I don't know how much more clear I can be, am trying to automate a procedure with the limited programming knowledge i have and yes there are no visits on a weekend Only during the week

    Quote Originally Posted by Ajax View Post
    Assuming these visits are booked in advance, you need a basis for determining which week you are looking to book, so perhaps you need to select this first otherwise your user could be hunting around entering a date then being told the week is booked, so they try another date to be told the same. So perhaps you need to be providing a user with a list of (the next 4?) weeks where there are 2 or less visits booked.
    Nope! cannot be booked but its usually a first come first served basis i.e. the visitors

    Quote Originally Posted by Ajax View Post
    are these persons customers or employees? presumably the former
    Actually they're custodians of the State now please note:

    1. Its a one to many relationship because during one's tenure or stay u can have many visits/visitors
    2. However only 3 visits are allowed per week per person_id no visits on weekend except authorized "this is rare!"
    3. Everything is set up tables, forms jus wanted to know in ms access can this argument be automated i.e the user can only make 3 entries per persons per week-cycle

  6. #6
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    This is what i could brainstorm around my puny brain Lol

    Code:
      Function Visits(Visitdate As Date, person_id As String) As Date 
         Visitdate = Between(vbMonday And vbFriday)
     If person_id >= 3 Then
     MsgBox "No More Visits are Allowed!", vbOKOnly, "Visitors Logs"
    End Function 

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's a little DB that can count to 3:

    Click image for larger version. 

Name:	visits.png 
Views:	33 
Size:	39.0 KB 
ID:	45905

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Jamesy_007 View Post
    This is what i could brainstorm around my puny brain Lol

    Code:
      Function Visits(Visitdate As Date, person_id As String) As Date 
         Visitdate = Between(vbMonday And vbFriday)
     If person_id >= 3 Then
     MsgBox "No More Visits are Allowed!", vbOKOnly, "Visitors Logs"
    End Function 
    That is called psuedo code.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by Welshgasman View Post
    That is called psuedo code.
    THIS was a module i was trying to develop creating my own function relative to the argument it is as is... all our records are on books.... Hmmmm! were sort of 25 years behind the database Management, started a main db and now working on another aspect of operations on Job

  10. #10
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by davegri View Post
    Here's a little DB that can count to 3:
    Once again davgeri you've help me and grasp the concept but trust me this module is to long for my comprehension....you and micron were the first to help me on this forum with the Many to Many relationship in access; a bit more competent now But maybe this is ah lil to complex.... Check in later guys!


    Have a Gr8 day

  11. #11
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Ok, let's try and start with the basics (as I see them)

    Unless you are going to require this check all over the DB, I would put it where you need it now.?
    If it is needed all over then I would write something like

    Code:
    Public Function MaxVisits(lngPersonID As Long, dtVisitDate As Date, Optional intMax As Integer = 3) As Boolean
    Dim strCriteria As String
    
    strCriteria = "CMS = " & lngPersonID & " AND TransactionDate = " & Format(dtVisitDate, strcJetDate)
    Debug.Print strCriteria
    
    If DCount("*", "Emails", strCriteria) > intMax - 1 Then
        MaxVisits = True
    End If
    
    End Function
    I would expect an ID field to be Long, not String as well?

    You would call it with

    Code:
    If MaxVisits(Me.PersonID, Me.VisitDate) then 'Defaulting to max 3, supply third parameter if max increases.
        MSGBOX "Max Visits already reached for date " & Format(Me.VisitDate,"dd/mm/yyyy")
    End If
    The format string is held in a module, as I always forget the syntax.
    Code:
    Public Const strcJetDate = "\#mm\/dd\/yyyy\#"  'Needed for dates in queries as Access expects USA format.
    Debug output
    Code:
    CMS = 125521 AND TransactionDate = #07/23/2015#
    True
    Comment out the Debug.Print when you have the criteria correct
    If nothing else, it should get you started.

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  12. #12
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    trust me this module is to long for my comprehension..
    The module was was a generalized code of many different date/time calculations. For your specific need we can replace all the generalized code with this:

    Code:
    Option Compare Database
    Option Explicit
    
    
    
    
    '---------------------------------------------------------------------------------------
    ' Method : fcnFindStartOfWeek
    ' Author : davegri
    ' Date   : 8/4/2021
    ' Purpose: Find start of week date based on supplied dteDate
    '   OffInt is the number of days (offset) from Sunday to indicate the desired
    '   definition of FIRST day of week.  For Monday, use OffInt = 2
    '--------------------------------------------------------------------------------------
    Public Function fcnFindStartOfWeek(dteDate, Optional OffInt As Integer) As Date
            Dim DOW As Integer      'Day of week, 1-7
            If Weekday(dteDate) = OffInt Then
                fcnFindStartOfWeek = dteDate
                Exit Function
            End If
            DOW = Weekday(dteDate)
            If DOW <= OffInt Then
                DOW = 7 - OffInt
            Else
                DOW = DOW - OffInt
            End If
            fcnFindStartOfWeek = dteDate - DOW
    End Function
    And the form code changes to

    Code:
    Option Compare Database
    Option Explicit
    
    
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        Dim FDOW As Date    'first day of week
        Dim LDOW As Date    'last day of week
        Dim pCount As Integer
        
        If IsNull(Me.VisitDate) Then
            MsgBox "Date is required"
            Cancel = True
            Exit Sub
        End If
        FDOW = fcnFindStartOfWeek(Me.VisitDate, 2)
        LDOW = FDOW + 4
        pCount = DCount("Person_FK", "tblVisits", "VisitDate Between #" & FDOW & "# AND #" & LDOW & "#")
        Debug.Print VisitDate, FDOW, LDOW, pCount
        Select Case Me.NewRecord
            Case False
                If pCount > 3 Then
                    MsgBox "Only 3 visits allowed per week"
                    Cancel = True
                    Me.Undo
                End If
            Case True
                If pCount > 2 Then
                    MsgBox "Only 3 visits allowed per week"
                    Cancel = True
                    Me.Undo
                End If
        End Select
    End Sub
    Last edited by davegri; 08-04-2021 at 04:23 PM. Reason: Added form before_update change

  13. #13
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by davegri View Post
    The module was was a generalized code of many different date/time calculations. For your specific need we can replace all the generalized code with this:
    davegri! you are a GENIUS perfecto! lol, can't pay for these tips&tricks....

    Quote Originally Posted by davegri View Post
    pCount = DCount ("Person_FK","tblVisits","VisitDate Between #" & FDOW & "# AND #" & LDOW & "#")
    Could you please explain this i know its some concatenation but what exactly it means???
    count the no. of foreign_keys in tblVisists, field_VisitDate that's between first day of the week and last day of the week ok....but what is this concept #"&???

  14. #14
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The pound signs are delimiters required for date variables, like single quotes are delimiters required for string variables.
    A more understandable format, showing the delimiters individually would be

    pCount = DCount ("Person_FK","tblVisits","VisitDate Between " & "#" & FDOW & "#" & " AND " & "#" & LDOW & "#")

    Also have tweak for code provided in post#12
    Added Person_FK to criteria of the DCount to restrict the count to the person showing in the main form.

    Code:
        FDOW = fcnFindStartOfWeek(Me.VisitDate, 2)
        LDOW = FDOW + 4
        pCount = DCount("Person_FK", "tblVisits", "VisitDate Between #" & FDOW & "# AND #" & LDOW & "# AND Person_FK=" & Person_FK)
        Debug.Print VisitDate, FDOW, LDOW, pCount
        Select Case Me.NewRecord
            Case False
                If pCount >= 3 Then
                    MsgBox "Only 3 visits allowed per week"
                    Cancel = True
                    Me.Undo
                End If
            Case True
                If pCount > 2 Then
                    MsgBox "Only 3 visits allowed per week"
                    Cancel = True
                    Me.Undo
                End If
        End Select
    End Sub
    Last edited by davegri; 08-05-2021 at 08:04 PM. Reason: updates

  15. #15
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Quote Originally Posted by davegri View Post
    The pound signs are delimiters required for date variables, like single quotes are delimiters required for string variables.
    A more understandable format, showing the delimiters individually would be

    pCount = DCount ("Person_FK","tblVisits","VisitDate Between " & "#" & FDOW & "#" & " AND " & "#" & LDOW & "#")

    Also have tweak for code provided in post#12
    Added Person_FK to criteria of the DCount to restrict the count to the person showing in the main form.
    Ok. I understand. Bought books on Access and vba they talk about concatenation but not in-depth like this; guess it'll take a while to learn all the syntax out there.
    The code works perfectly and as intended. sometimes thank u is jus not enough Wow!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Log user off computer on database exit
    By redekopp in forum Access
    Replies: 4
    Last Post: 02-03-2017, 11:23 AM
  2. Replies: 1
    Last Post: 08-19-2016, 01:40 PM
  3. Replies: 10
    Last Post: 03-27-2014, 03:58 PM
  4. Replies: 2
    Last Post: 12-20-2012, 03:06 PM
  5. Replies: 4
    Last Post: 12-14-2012, 06:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums