Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 56
  1. #16
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    I can't use a totals query because in some cases some votes are not counted. A car owner can't vote for his own car, and a club member can't vote for another club member's car, also a car owner can only vote for a car one time. My module can take care of those cases.

  2. #17
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, got it.


    Public Function VCount(tVote.Car_Number As Integer, tRegistration.Car_Number As Integer, TRegistration.Count As Integer, tVote.Car_1 As Integer, tVote.Car_2 As Integer, tVote.Car_3 As Integer, tVote.Car_4 As Integer, tVote.Car_5 As Integer) As Integer
    You have 8 arguments for this function, but you don't/shouldn't use actual table.field names. The function arguments are a list of variables representing values that are passed to the Function procedure when it is called.

    You could name the arguments "Banana", "Grape", "Orange", "Ugly", "Tangerine", "Pineapple", "Cherry" and "Salmonberry" - they are just variables (but the berry names are not very descriptive).

    Try
    Public Function VCount(aVoteCarNum As Integer, aRegCarNum As Integer, aRegCount As Integer, aVoteCar1 As Integer, aVoteCar2 As Integer, aVoteCar As Integer, aVoteCar4 As Integer, aVoteCar5 As Integer) As Integer
    The "a" prefix stands for "argument", as opposed to normal variables declared in the function.

    When you USE the function, you reference the fields in a query or set the values of the arguments.

  3. #18
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    When I run the Macro Vote_Count that calls the Function VCount(expr, etc), I get a message that states Microsoft Access can't find the name 'aRegCar_Number' that you entered in the expression.
    It also states that the Control Name should be preceded with the name of the Form or Report. The Form name is fRegistration. Do I need to put fRegistration VCount(expr, etc) in the expression?

  4. #19
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would you post the code for the Function VCount? Or the dB with a couple of test records?


    When I run the Macro Vote_Count that calls the Function VCount(expr, etc), I get a message that states Microsoft Access can't find the name 'aRegCar_Number' that you entered in the expression.
    It also states that the Control Name should be preceded with the name of the Form or Report. The Form name is fRegistration. Do I need to put fRegistration VCount(expr, etc) in the expression?
    If you are actually using a macro, I would have to see the macro. I never use macros, so I'm not sure of the problem.

  5. #20
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    The database file is attached.
    Attached Files Attached Files

  6. #21
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The votes table was designed like a spreadsheet, so I kinda changed the table designs...

    The dB you posted was an mdb, so I converted it to A2010 format.

    I wasn't sure if the bar code and the car number would be the same number, so I left both fields in the table.
    There are a lot more verifications that could/should be done.

    Both the mdb format and the accdb format are in the attached zip.
    Attached Files Attached Files

  7. #22
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    I can't open the accdb format. My version of Access is A2000, not A2010. I did open the mdb version and ran it. It looks good, but I need the car number to be the reference for calculations, not the Bar Code. The Bar Code is only needed to get the right record, and will probably be different from the Car Number. I tried to modify some code and got an error. Here is the code:

    Private Sub btnSave_Click()
    Dim d As DAO.Database
    Dim i As Integer
    Dim sSQL As String
    Set d = CurrentDb
    For i = 1 To 5
    If Me("v" & i) <> Me.ubBarCode Then
    sSQL = "INSERT INTO tVote2 ( BarCodeID_FK, Car_Number, CarVotedFor, VoteDate )"
    sSQL = sSQL & " VALUES ( " & Me.ubBarCode & ", " & Me.ubCar_Number & ", " & Me("v" & i) & ", #" & ubVoteDate & "#);"
    Debug.Print sSQL
    d.Execute sSQL, dbFailOnError
    End If
    Next
    Me.ubBarCode = Null
    Me.V1 = Null
    Me.V2 = Null
    Me.V3 = Null
    Me.V4 = Null
    Me.V5 = Null
    End Sub

  8. #23
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I saw that you have Win 10 and Access 2010 64bit, so I converted the mdb to Accdb format. I will test using A2K.

    What is the error? What line is the error at?
    It looks like you tried to add a field to the save routine. Did you add a field "Car_Number" to the table "tVote2".

    but I need the car number to be the reference for calculations,
    The car number IS being used to the vote tally. Open the query "qryReport". You will see that the two queries are linked by car number, not barcode.


    But I added the car number to table "tVotes2".
    Attached Files Attached Files

  9. #24
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    Quote Originally Posted by ssanfu View Post
    I saw that you have Win 10 and Access 2010 64bit, so I converted the mdb to Accdb format. I will test using A2K.

    What is the error? What line is the error at?
    It looks like you tried to add a field to the save routine. Did you add a field "Car_Number" to the table "tVote2".


    The car number IS being used to the vote tally. Open the query "qryReport". You will see that the two queries are linked by car number, not barcode.


    But I added the car number to table "tVotes2".

    Yes, I tried to add the Car_Number field. It does use Car Number in the Report, but it should also use Car Number in the For, Next loop instead of Bar Code.

    For i = 1 To 5
    'If Me("v" & i) <> Me.ubBarCode Then
    If Me("v" & i) <> ubCarNumber Then
    sSQL = "INSERT INTO tVote2 ( BarCodeID_FK, Car_Number, CarVotedFor, VoteDate )"
    sSQL = sSQL & " VALUES ( " & Me.ubBarCode & ", " & ubCarNumber & ", " & Me("v" & i) & ", #" & ubVoteDate & "#);"
    ' Debug.Print sSQL
    d.Execute sSQL, dbFailOnError
    End If
    Next

  10. #25
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, try this:
    Code:
    Private Sub btnSave_Click()
        Dim d As DAO.Database
        Dim i As Integer
        Dim sSQL As String
    
        Set d = CurrentDb
    
        For i = 1 To 5
            If Me("V" & i) <> Me.ubCarNumber Then
                sSQL = "INSERT INTO tVote2 ( BarCodeID_FK, Car_Number, CarVotedFor, VoteDate )"
                sSQL = sSQL & " VALUES ( " & Me.ubBarCode & ", " & Me.ubCarNumber & ", " & Me("V" & i) & ", #" & ubVoteDate & "#);"
                '    Debug.Print sSQL
                d.Execute sSQL, dbFailOnError
            End If
        Next
    
        Me.ubBarCode = Null
        Me.ubCarNumber = Null
        Me.V1 = Null
        Me.V2 = Null
        Me.V3 = Null
        Me.V4 = Null
        Me.V5 = Null
    
        Set d = Nothing    '<<-- added line
    
    End Sub
    Note that I added a line at the bottom of the sub. I hate it when I do that!!

  11. #26
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    It seems to work correctly, but the data window lights up when vote = Bar Code, it should be vote = Car Number. The totals are correct.

    The line that you added would only compile, if I edited it to this:

    Set d = Nothing '<<-- added line

  12. #27
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Yep, I forgot to change the conditional formatting.

    How about this??
    Attached Files Attached Files

  13. #28
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    OK, that works, but I need 2 more conditions.

    If !tRegistration.delrodsmember || !tVote.delrodsmember Then Count Vote.

    If Me.V5 = M[COLOR=rgb(0,0,255)]e.V1[/COLOR] || Me.V2 || Me.V3 || Me.v4 Then Me.V5 = 0
    Else If Me.V4 = Me.V3 || Me.V2 || Me.V1 Then Me.V4 = 0
    Else If Me.V3 = Me.V2 || Me. V1 Then Me.V3 = 0
    Else If Me.V2 = Me.V1 Then Me.V2 = 0
    End If
    End If
    End If
    End If

  14. #29
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am guessing that "||" is the PHP Logical Operator for VBA "Or" Logical Operator.

    If !tRegistration.delrodsmember || !tVote.delrodsmember Then Count Vote.
    Are you saying that the car (member) voting MUST be a del rods member else all 5 votes do not count AND
    the cars voted for MUST be del rods members, else the the vote for the car that is not a member does not count?


    Code:
        If Me.V5 = Me.V1 Or Me.V5 = Me.V2 Or Me.V5 = Me.V3 Or Me.V5 = Me.V4 Then
            Me.V5 = 0
        ElseIf Me.V4 = Me.V3 Or Me.V4 = Me.V2 Or Me.V4 = Me.V1 Then
            Me.V4 = 0
        ElseIf Me.V3 = Me.V2 Or Me.V3 = Me.V1 Then
            Me.V3 = 0
        ElseIf Me.V2 = Me.V1 Then
            Me.V2 = 0
        End If
    Does this mean that each of the 5 votes must be unique? The same car cannot be voted for more than once by a member?


    Something like this???
    Attached Files Attached Files
    Last edited by ssanfu; 02-06-2017 at 02:53 AM.

  15. #30
    Dan Lee is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2017
    Posts
    57
    Quote Originally Posted by ssanfu View Post
    I am guessing that "||" is the PHP Logical Operator for VBA "Or" Logical Operator.


    Are you saying that the car (member) voting MUST be a del rods member else all 5 votes do not count AND
    the cars voted for MUST be del rods members, else the the vote for the car that is not a member does not count?

    The car(member) voting and the car voted for can't both be del rods members. In plane English, a member cannot vote for a club member.


    Does this mean that each of the 5 votes must be unique? The same car cannot be voted for more than once by a member?



    Something like this???

    Yes, my code was wrong, yours looks good. I'll try it.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. 30 day registration for access
    By CHEECO in forum Access
    Replies: 7
    Last Post: 06-05-2016, 12:31 PM
  2. Using access registration details
    By BSJoJaMAx4 in forum Access
    Replies: 7
    Last Post: 10-05-2015, 06:05 PM
  3. registration database
    By hodagh in forum Queries
    Replies: 7
    Last Post: 11-05-2012, 12:52 PM
  4. Registration of Attendees
    By camende in forum Programming
    Replies: 1
    Last Post: 06-30-2010, 03:47 PM
  5. Registration Form
    By nengster in forum Forms
    Replies: 0
    Last Post: 02-16-2009, 04:22 AM

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