Results 1 to 9 of 9

Thread: Counter

  1. #1
    Join Date
    Apr 2012
    Posts
    22

    Unanswered: Counter

    Hi, How do i add a counter to my database so that everytime i enter client name under client table then it stores, and on the fifth entry the box then pops up and say max number of clients entered...something like that...Thx a lot

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I wanted to pop a warning message and then close the Form, but the Access Gnomes are very picky about where you're allowed to close the Form from, so this was the best that I could kludge together.

    First off, set the Data Entry Property for the Form to Yes.

    Then use this code
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If RecordsetClone.RecordCount = 4 Then
     MsgBox "Maximum Entries Have Been Made! Please Close the Form."
    End If
    
    End Sub
    Code:
    Private Sub Form_Current()
    
    If RecordsetClone.RecordCount = 5 Then
     Me.AllowAdditions = False
     Me.AllowEdits = False
    End If
      
    End Sub
    So here's what happens:

    • Messagebox is popped up informing user that 'maximum entries have been made, please Close the Form
    • If they ignore this warning, they'll find themselves on the last Record entered
    • They'll not be able to Add another Record nor Edit a Record

    So the only option they have available is to Close the Form, as previously instructed!

    Hope this works for you! Perhaps someone else will have a more eloquent solution.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2012
    Posts
    22
    Thanks Missingling, I will follow your steps and let you know the outcome.

    Thanks again for your help.

    regards
    James

  4. #4
    Join Date
    Apr 2012
    Posts
    22
    Hi,

    This is what I currently have under the appointments form. What string do i need to change ? Sorry i am clueless when coming to VBA. See the attached picture on how the view looks..

    Option Compare Database

    Private Sub AddAppointment_Click()
    Dim rsAppointments As Recordset
    Dim strTime As String
    Dim dtTime As Date

    strTime = Form_AppointmentInformation.cboHour & ":" & Form_AppointmentInformation.cboMinute

    Set rsAppointments = CurrentDb.OpenRecordset("Select * from Appointments")
    rsAppointments.AddNew
    rsAppointments.Fields(1) = Form_AppointmentInformation.AppointmentDate
    rsAppointments.Fields(2) = Form_AppointmentInformation.cboHour & ":" & Form_AppointmentInformation.cboMinute

    End Sub

    Private Sub Form_Load()

    End Sub
    Attached Thumbnails Attached Thumbnails AppointmentsScreen.JPG  

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And the problem is?
    what part of Missingling's code didn't work
    and / or what do you believe your code is doing?
    what does it do and what do you think needs to be done to make it do what you want
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Also, what does Post #4 have to do with your original question?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Apr 2012
    Posts
    22

    Thanks Missingling

    Thanks Missingling, the counter is working exactly like how i wanted however i want it to count per "user id" , pop a message and still continue taking other bookings, for other people. e.g. If I make a booking for John Doe, the booking will run till the fifth one and only pop a message saying maximum bookings made. Same must happen with other users. In other words it counts per user id not by overall bookings.If Peter made 3 and John made 2 bookings - the message must not pop up although 3 + 2 = 5. But must only pop up for Peter once he reaches 5 and for John on his 5th booking as well. Hope this is clear. Again thanks for your help

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Here's some code I did a while back; see if it suits. You'll need to replace EmpID and TrainingTableName with your own actual names, of course, as well as your desired message.

    For EmpID as a Text field:
    Code:
    Private Sub EmpID_AfterUpdate()
       If DCount("EmpID", "TrainingTableName", "[EmpID] = '" & Me.EmpID & "'") = 5 Then
         MsgBox "Only Five(5) Entries are Allowed Per Employee!"
         Me.Undo
       End If
      End Sub
    For EmpID asa Numbers field:
    Code:
    Private Sub EmpID_AfterUpdate()
       If DCount("EmpID", "TrainingTableName", "[EmpID] = " & Me.EmpID) = 5 Then
         MsgBox "Only Five(5) Entries are Allowed Per Employee!"
         Me.Undo
       End If
      End Sub
    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Apr 2012
    Posts
    22

    Thanks

    Thanks Missingling, please see my other thread - I think I might might have messed up my original.

    http://www.dbforums.com/microsoft-ac...-property.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •