UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Select Top N - I Want N To Pull From A Form Or Default To 12, Access 2013    
 
   
jmkeuning
post Dec 3 2019, 12:34 PM
Post#1



Posts: 176
Joined: 20-October 15



I have a query that puts characters in a table so that I can generate a password. The way it is set up right now, the password length is 12:

CODE
SELECT TOP 12 tblPasswordStrings.character, tblPasswordStrings.phonetic, tblPasswordStrings.ID, Rnd([ID]) AS Random INTO tblPasswordTemp
FROM tblPasswordStrings
ORDER BY Rnd([ID]);


I would like users to be able to customize the length. The button that runs the command to populate the password field is on a form frmPasswordDBG_Str2 so I created a text box on that form: [forms].[frmPasswordDBG_Str2].[form].[length]

But I cannot figure out how to have the query look at this text box on the form to get the "TOP" criteria. Complicating this is that, even once I figure this out, I will need to put some logic in there to default to 12 if the length field is empty.

Is this possible?

EDIT - I figured out the last part. I can just have the query look at a hidden field that contains 12 or the user-specified value. So the question that remains is how to get the query to reference the field.
This post has been edited by jmkeuning: Dec 3 2019, 12:36 PM
Go to the top of the page
 
theDBguy
post Dec 3 2019, 12:42 PM
Post#2


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. The TOP keyword needs a literal number, so you won't be able to use a reference to a form or variable. Instead, you will have to recreate/construct the SQL statement and modify the query prior to executing it. You would do this using a QueryDef object.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Dec 3 2019, 01:04 PM
Post#3



Posts: 2,709
Joined: 4-February 07
From: USA, Florida, Delray Beach


All of which you are asking is possible and is basically a multi-step process:
  1. Create a Query named qryPasswords that simply includes all Records in tblPasswordStrings, namely:
    SQL
    SELECT tblPasswordStrings.character, tblPasswordStrings.phonetic, tblPasswordStrings.ID, Rnd([ID]) AS Random
    FROM tblPasswordStrings ORDER BY Rnd([ID]);
  2. Check the Value in the [length] Field.I'll use the Active Form for a Reference. If the Value in this Field is NULL, not Numeric, or > 25 then set the Default Top Value to 12.
  3. If the Form containing the [length] Form is not the Active Form, then you must set a Reference to it and the [length] Field as in:
    CODE
    Dim frm As Access.Form

    Set frm = Forms("frmPasswordDBG_Str2")

    'To now Reference the [length] Field
    frm![length]
  4. Set a Reference to qryPasswords.
  5. Dynamically modify the SQL of qryPasswords using the TOP intLen Value obtained from the [length] Field.
  6. Open the Query now reflecting these changes in Read Only Mode.
  7. The Code that accomplishes all this is as follows:
    CODE
    Dim strSQL As String
    Dim qdf As DAO.QueryDef
    Dim intLen As Integer

    'Check the [length] Field. If it is Null, not Numeric, or > 25, then Default to 12
    If IsNull(Me![length]) Or Not IsNumeric(Me![length]) Or Me![length] > 25 Then
      intLen = 12
    Else
      intLen = CInt(Me![length])
    End If

    'Set a Reference to qryPasswords
    Set qdf = CurrentDb.QueryDefs("qryPasswords")

    'Modify the SQL of qryPasswords
    strSQL = "SELECT TOP " & CStr(intLen) & " tblPasswordStrings.character, tblPasswordStrings.phonetic, " & _
             "tblPasswordStrings.ID, Rnd([ID]) AS Random FROM tblPasswordStrings " & _
             "ORDER BY Rnd([ID]);"
    qdf.SQL = strSQL
        
    'Let's see the results
    DoCmd.OpenQuery "qryPasswords", acViewNormal, acReadOnly

This post has been edited by ADezii: Dec 3 2019, 01:07 PM
Go to the top of the page
 
jmkeuning
post Dec 6 2019, 04:38 PM
Post#4



Posts: 176
Joined: 20-October 15



Thank you for taking the time to put that together. That's way more advanced than anything I have done to date, but I want to try it. I will look at this when I have the appropriate time and attention.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 09:39 AM