Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163

    Comparing form data to table data

    Hi



    I have a form that lets me pick 5 numbers for a lottery drawing. The form is unbound. The five numbers are located in ball1, ball2, ball3, ball4 and ball5. These are the names of the text boxes on my form.

    I'd like to , in real time, check those 5 text box values against a table, which has all of the past winning numbers.


    The winning numbers are stored in a table called tblWinningNums. The field names for each of the numbers are One,Two,Three,Four and Five.


    I'd like to know if the combination of numbers I have in the text boxes on the form match any winning number combinations in the table.




    My answer may lie in a query but I though there might be some VBA that made better sense.

    Thanks
    Lefty

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Depending on what you want to do, a query will generally be most efficient. In your case, presuming I'm understanding, criteria on each of the numbered fields to its form equivalent.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Thanks pbaldy. In the mean time, I actually found some code that appears to work. However, I have no idea why. So I thought maybe you could explain it to me. I am a newbie.

    I built a query as you had suggested. I'm looking for the number records in the query, which finds all matches between the form fields and table. I used the following code to return the number of matches, but have no idea what in means in its entirety.

    Dim Match As Long
    Match = DCount("*", "myqueryname")
    findMatch = Match

    I just put this code in a function and called it from the sub I was working with.
    It works but....
    What is the significance of the "*"?

    Thanks
    Lefty

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's the field argument, but you can use the * when counting if the field is irrelevant. It does count nulls if that's an issue.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    JeroenMioch's Avatar
    JeroenMioch is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2012
    Location
    Den Haag, Netherlands
    Posts
    368
    Might i ask what the purpose is of your database ? Just curious

  6. #6
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by JeroenMioch View Post
    Might i ask what the purpose is of your database ? Just curious

    You might but I may not have a really good answer. It's a relatively simple database containing past winning lottery numbers. I made a form so I can pick the number I want to play and the form does some cool little tricks, one of which is to check all of the past winning combinations to see if my pick matches any of them.

    I've built a couple databases for a couple of companies I have...to track goings on and so forth. I recently picked up a great VBA book and I didn't want to mess with my existing databases so I thought this lottery project would keep me busy.

    So I guess the purpose is simply to practice VBA.

    Thanks
    Lefty

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For added practice you can add a button to randomly pick the numbers, avoiding duplicates if appropriate. I recently made an app for a friend that randomly assigns people to the 10x10 grid of an American football pool, plus randomly assigns the numbers along the top and side.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by pbaldy View Post
    For added practice you can add a button to randomly pick the numbers, avoiding duplicates if appropriate. I recently made an app for a friend that randomly assigns people to the 10x10 grid of an American football pool, plus randomly assigns the numbers along the top and side.

    Challenge accepted.

  9. #9
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Actually...there's a feature I wanted to add if it was possible.

    I have a table that keeps track of the numbers. The columns are: Number, Frequency, Rank.

    As you may suspect, Number is the number, frequency is how many times the number as been drawn and Rank is the rank of the number, based on the number of times it's been drawn.

    My form I use for picking numbers has 35 toggle buttons. The name of each button is the number and the caption is the rank.

    The caption or rank is currently static, meaning it was the rank of the numbers at the time I created the form.

    I'd like to make it dynamic based on the table that tracks rank. Said table is updated daily as it gets it's data from a table with the winning numbers. I was thinking I could add some VBA in the "on Load" event of the form that would check the rank table and set the caption property of the 35 toggles to the current rank of each number. But I don't know where to start.

    Any thoughts?
    Thanks
    Lefty

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, it would be problematic to change the name of the buttons, as they'd lose connection to any code behind them. Not sure about embedded macros, as I don't use them. Changing captions isn't tough. Think For/Next loop and a recordset opened on an SQL statement that gets the table values in order. Using your For/Next loop counter (let's say X), you can refer to buttons (presuming they have consistent names like "Button1...Button35":

    Me("Button" & X).Caption = Whatever
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Yes...just caption. I don't want to change the name. Please let me know if this was not very bright but I named my toggles 1,2,3 to 35.

    So I have 35 records in my rank table. Each record as a field "number", 1 to 35. There are 35 records. Each record also has a field "rank". I'm looking to match the name of the toggle button with the "number" field in the table and return the "rank" field value. I then want to assign the "rank" value to the caption value of the toggle button with the name that matches "number" in the rank table.

    I'm sorry if this is confusing.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Ah, ditch the For/Next loop and loop the recordset. Use the technique above but with the recordset number field instead of X, and the recordset rank field where I have Whatever. Or am I still confused?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    I think you have it. I'm looking to match the toggle name on the form with the "number" field of the recordset. And then I want to set the caption of the same toggle button to the "rank" field value in the same record as the matching number.

    So a loop is the answer, just not sure how to structure it. I want it to do this for all 35 toggle buttons on the form.

    I'm very new... only done a few bits of code with DAO stuff. Simple adding records and so forth.

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    A word of warning: the Access Gnomes sometimes get really persnickety when object names begin with a number.

    See this thread http://www.vbforums.com/showthread.p...g-with-numbers

    And http://www.vbforums.com/showthread.p...-tables-fields

  15. #15
    lefty2cox is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    May 2013
    Posts
    163
    Quote Originally Posted by ssanfu View Post
    A word of warning: the Access Gnomes sometimes get really persnickety when object names begin with a number.

    See this thread http://www.vbforums.com/showthread.p...g-with-numbers

    And http://www.vbforums.com/showthread.p...-tables-fields
    It's even worse than that. The field name doesn't start with a number...it IS a number. I had a feeling it was a bad idea when I did it but I was lazy. It made it easy to use the button to put a value into a text box by just setting the text box value to the name of the button. I realize now, the more I think about it, how confusing that could get if I ever have say something like.....if 1 =2 then....

    So what I'll probably due is change the names of the toggles to Num1, Num2 etc... or maybe tog1, tog2.... And then I'll just have to use code to assign the numbers to variables. Maybe I can use a loop for that.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 04-04-2015, 08:10 AM
  2. Comparing Data From Two Tables
    By dcoley in forum Access
    Replies: 12
    Last Post: 05-01-2014, 12:09 PM
  3. Replies: 14
    Last Post: 01-07-2014, 04:20 PM
  4. Replies: 5
    Last Post: 12-09-2012, 02:29 PM
  5. Replies: 2
    Last Post: 07-30-2012, 03:26 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