Results 1 to 15 of 15
  1. #1
    Slashback115 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    7

    Question Coding Help with ComboBox and Button, Access 2013

    Hey everyone, first time posting.

    I am working on a project (for fun) and am almost complete but stumped on this last little part. Any help would be greatly appreciated.

    This is for Access 2013

    I have a basic Excel table with names. Artname, Dwarven, Elvish, Human.

    Button (artbutton)
    ComboBox (selectname)
    Text Box (arttext)
    Table (Gen)
    Query (Genq)



    I need to have the combo box select 1 of the many columns. Once selected the button (on_click) will randomize all the values in that column and display one in the text field.

    I have it working to the point of selecting the first field Artname and randomizing it. But no matter what I try, I cannot get the button to recognize the combobox selection.

    Here is the code I currently have. Thanks again!

    (This works for the 1 random column.)
    ----------------------------------------------------------------------------------------------------------

    Private Sub artbutton_Click()
    Dim MinID As Integer
    Dim MaxID As Integer
    Dim RandomVal As Integer
    Dim arttext As String


    MinID = DMin("ID", "Gen")
    MaxID = DMax("ID", "Gen")
    Randomize
    RandomVal = Int((MaxID * Rnd) + MinID)
    arttext = getRandomStuff(RandomVal)
    Me.arttext.Value = arttext


    End Sub

    Public Function getRandomStuff(lookupVal As Integer) As String

    Dim rst As DAO.Recordset
    Dim sqlStr As String

    sqlStr = "Select Gen.Artname FROM Gen Where Gen.ID = " & lookupVal & ""
    Set rst = CurrentDb.OpenRecordset(sqlStr)
    getRandomStuff = rst!Artname
    rst.Close
    Set rst = Nothing



    End Function


    ----------------------------------------------------------------------------------------------------------





    This was my attempt getting it to work with multiple columns.

    ----------------------------------------------------------------------------------------------------------
    Private Sub artbutton_Combo(Rannum)
    Dim MinID As Integer
    Dim MaxID As Integer
    Dim RandomVal As Integer
    Dim arttext As String


    MinID = DMin("ID", "Gen")
    MaxID = DMax("ID", "Gen")

    If Ranname = "Artname" Then MinID = 2 & MaxID = 2047
    If Ranname = "Dwarven" Then MinID = 2 & MaxID = 1231
    If Ranname = "Drow M" Then MinID = 2 & MaxID = 1907
    If Ranname = "High Elf" Then MinID = 2 & MaxID = 838
    If Ranname = "Eastern" Then MinID = 2 & MaxID = 1225
    If Ranname = "Orcish" Then MinID = 2 & MaxID = 1747
    If Ranname = "Gnomish" Then MinID = 2 & MaxID = 933
    If Ranname = "Roman" Then MinID = 2 & MaxID = 847
    If Ranname = "Common Male" Then MinID = 2 & MaxID = 318
    If Ranname = "Common Female" Then MinID = 2 & MaxID = 345
    If Ranname = "Location" Then MinID = 2 & MaxID = 959

    Randomize
    RandomVal = Int((MaxID * Rnd) + MinID)
    arttext = getRandomStuff(RandomVal)
    Me.arttext.Value = arttext


    End Sub


    Public Function getRandomStuff(lookupVal As Integer) As String

    Dim rst As DAO.Recordset
    Dim sqlStr As String

    sqlStr = "Select Gen.Artname FROM Gen Where Gen.ID = " & lookupVal & ""
    Set rst = CurrentDb.OpenRecordset(sqlStr)
    getRandomStuff = rst!Artname
    rst.Close
    Set rst = Nothing

    End Function

    Private Sub selectname_OnChange()
    Dim Ranname As String

    Ranname = selectname.Selected
    artbutton_Combo Ranname

    End Sub
    ----------------------------------------------------------------------------------------------------------

  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
    I'm not really clear on what you're trying to achieve, but this code:

    If Ranname = "Artname" Then MinID = 2 & MaxID = 2047

    won't work, would have to be (though I'm not a fan of the one-line format):

    If Ranname = "Artname" Then MinID = 2 : MaxID = 2047

    Edit: by the way, I deleted your duplicate thread.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Slashback115 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    7
    Thank you for the reply, that did not work. Same issue. The button only finds the value of column 1 "Artname". Not the Combobox selected.

    (Thanks for deleting the duplicate, I thought I did)

  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
    Is Ranname the name of the combo? Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Slashback115 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    7
    -It won't let me upload the ACCDB file, probably too big. But yes I declared the name. I attached the code itself. Code Backup.txt

    -------------------------------------------------------
    Private Sub selectname_OnChange()

    Dim Ranname As String
    Ranname = selectname.Selected
    artbutton_Combo Ranname
    ---------------------------------------------------------

  6. #6
    Slashback115 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    7
    Maybe this will help add some clarity. Click image for larger version. 

Name:	breakdown.png 
Views:	11 
Size:	249.9 KB 
ID:	25953

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are many things that are not my way of programming, but I'll have to let it go......

    Here are a couple of things for you to try/changes I would make:
    Code:
    Option Compare Database '<<-- EVERY module should have these two lines
    Option Explicit         '<<-- EVERY module should have these two lines
    
    'I would have a prefix indicating these are module level variables.
    Dim MCR As String
    Dim Artname As String
    Dim Ranname As String
    Dim Rannum As Integer
    Dim amod As Integer
    Dim dmmod As Integer
    Dim final As Integer
    Dim rannum2 As Integer
    Dim mname As String
    Dim cexp As Integer
    Dim SQL As String    ' <<-- "SQL" is a reserved word in Access and shouldn't be an object name
    Dim p1ac As Integer
    Dim p2ac As Integer
    Dim p3ac As Integer
    Dim pexp As Integer
    Dim texp As Integer
    Code:
    Sub artbutton_Combo(Rannum)  ' <<-- why is there an argument??? Rannum is not used...
        Dim MinID As Integer
        Dim MaxID As Integer
        Dim RandomVal As Integer
        Dim arttext As String   "see below 
    
    
        MinID = DMin("ID", "Gen")
        MaxID = DMax("ID", "Gen")
    
        MinID = 2  'common to all
    
        'SELECT CASE instead of IF...End IF
        Select Case Ranname
            Case "Common Male"
                MaxID = 318
            Case "Common Female"
                MaxID = 345
            Case "High Elf"
                MaxID = 838
            Case "Roman"
                MaxID = 847
            Case "Gnomish"
                MaxID = 933
            Case "Location"
                MaxID = 959
            Case "Eastern"
                MaxID = 1225
            Case "Dwarven"
                MaxID = 1231
            Case "Orcish"
                MaxID = 1747
            Case "Drow M"
                MaxID = 1907
            Case "Artname"
                MaxID = 2047
            Case Else
    
        End Select
    
        Randomize
        RandomVal = Int((MaxID * Rnd) + MinID)
        arttext = getRandomStuff(RandomVal)   '<<-- I would not have a variable named the same as a control on a form
        Me.arttext = arttext                  '<<-- I would not have a variable named the same as a control on a form
    
    End Sub

    Code:
    Public Function getRandomStuff(lookupVal As Integer) As String
        Dim rst As DAO.Recordset
        Dim sqlStr As String
    
        ' "Ranname" is the table column name
        sqlStr = "Select Gen." & Ranname & " FROM Gen Where Gen.ID = " & lookupVal
        '    sqlStr = "Select Gen.Artname FROM Gen Where Gen.ID = " & lookupVal & ""
        Set rst = CurrentDb.OpenRecordset(sqlStr)
    
        If rst.BOF And rst.EOF Then
            ' maybe a message here about no records found
        Else
            getRandomStuff = rst!Artname
        End If
    
        rst.Close
        Set rst = Nothing
    
    End Function


    ------------------------
    If you want to post your dB, do a "Compact and Repair", then Zip it. Max Zip file size = 2 mb.

  8. #8
    Slashback115 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    7
    Thank you for the reply, I made some of these changes you suggested, The error I keep getting is "too few parameters" on the getrandomstuff Function.


    I now have




    Public Function getRandomStuff(lookupVal As Integer) As String


    Dim rst As DAO.Recordset
    Dim sqlStr As String


    sqlStr = "Select Gen.[" & Ranname & "] FROM Gen Where Gen.ID = " & lookupVal
    Set rst = CurrentDb.OpenRecordset(sqlStr)
    getRandomStuff = rst!Artname
    rst.Close
    Set rst = Nothing

    Private Sub selectname_OnChange()
    Dim Ranname As String


    Ranname = selectname.Selected
    artbutton_Combo Ranname


    End Sub

    Private Sub artbutton_Combo(Ranname)
    Dim MinID As Integer
    Dim MaxID As Integer
    Dim RandomVal As Integer
    Dim arttext As String




    MinID = DMin("ID", "Gen")
    MaxID = DMax("ID", "Gen")




    Select Case Ranname
    Case "Common Male"
    MaxID = 318
    Case "Common Female"
    MaxID = 345
    Case "High Elf"
    MaxID = 838
    Case "Roman"
    MaxID = 847
    Case "Gnomish"
    MaxID = 933
    Case "Location"
    MaxID = 959
    Case "Eastern"
    MaxID = 1225
    Case "Dwarven"
    MaxID = 1231
    Case "Orcish"
    MaxID = 1747
    Case "Drow M"
    MaxID = 1907
    Case "Artname"
    MaxID = 2047
    Case Else


    End Select


    Randomize
    RandomVal = Int((MaxID * Rnd) + MinID)
    arttext = getRandomStuff(RandomVal, Ranname)
    Me.Controls(Ranname).Value = arttext


    End Sub


  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    At the bottom of the procedure "artbutton_Combo(Ranname)" you have
    Code:
    arttext = getRandomStuff(RandomVal, Ranname)
    Me.Controls(Ranname).Value = arttext
    Note that you have 2 parameters (in blue).


    Remove the 2nd parameter (Ranname) from the line of code
    Code:
    arttext = getRandomStuff(RandomVal, Ranname)
    
    becomes
    
    arttext = getRandomStuff(Ranname)

    ---------------------------------------------------------------


    IMPORTANT!!
    I made a change the this procedure:
    Code:
    Public Function getRandomStuff(lookupVal As Integer) As String
        Dim rst As DAO.Recordset
        Dim sqlStr As String
    
        ' "Ranname" is the table column name
        sqlStr = "Select Gen." & Ranname & " FROM Gen Where Gen.ID = " & lookupVal
    '    Debug.Print sqlStr
        Set rst = CurrentDb.OpenRecordset(sqlStr)
    
        If rst.BOF And rst.EOF Then
            ' maybe a message here about no records found
        Else
            getRandomStuff = rst.Fields(0)    ' <<-- this change will return the value regardless of the field name
        End If
    
        rst.Close
        Set rst = Nothing
    
    End Function
    BTW, I see you removed the IF..End IF from the code. You DO know why I added it...right??

  10. #10
    Slashback115 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    7
    I am guessing so it will display something even if there is an error?

    As for your changes I did them and am getting this error. "Syntax error, missing operator in Query expression Gen."

    Seems to be coming from getrandomstuff.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If there are no records returned in the recordset and you try to read a field in the recordset, you will get an error.
    Because there is no error handler, a debug window will be displayed.


    As far as the error, I would have to see what you have for code now or your dB.

    Have you set a breakpoint and stepped through the code to see where the error occurs?
    Uncomment the "Debug.print" line to see what the SQL looks like.

    Debugging VBA Code
    http://www.cpearson.com/excel/debug.htm

  12. #12
    Slashback115 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    7
    Public Function getRandomStuff(lookupVal As Integer) As String
    Dim rst As DAO.Recordset
    Dim sqlStr As String


    ' "Ranname" is the table column name
    sqlStr = "Select Gen." & Ranname & " FROM Gen Where Gen.ID = " & lookupVal
    Debug.Print sqlStr
    Set rst = CurrentDb.OpenRecordset(sqlStr)


    If rst.BOF And rst.EOF Then <----Breakpoint where error occurs.
    ' Unable to find record
    Else
    getRandomStuff = rst.Fields(0)
    End If


    rst.Close
    Set rst = Nothing


    End Function

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's the error description? I personally only test for EOF, but I wouldn't expect an error there.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If the error is in the previous line, there's possibly a problem with the SQL. Use this:

    BaldyWeb-Immediate window

    If there are spaces in the field names, they'd have to be bracketed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    And what is printed in the immediate window by the "Debug.Print" statement?



    @Paul
    Thanks for reminding me about that page on your site. I haven't had to refer to it in so long I forgot about it.

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

Similar Threads

  1. Coding problem with Exit button
    By NightWalker in forum Programming
    Replies: 11
    Last Post: 06-06-2016, 01:05 PM
  2. Replies: 1
    Last Post: 06-25-2015, 05:24 AM
  3. Replies: 4
    Last Post: 02-12-2014, 12:49 PM
  4. Button Coding
    By yamie in forum Programming
    Replies: 3
    Last Post: 02-22-2010, 10:45 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