David Barishev David Barishev - 1 year ago 54
SQL Question

Personalize forms based on select queries with parameters

I Have been working on a Access 2013 database , and kinda got stuck.

My database is based on a login . Once you login , you can see all your games , items etc.

I Have made a login form , that when username & password gets entered correctly moves you to the main form.Also it sets a label on the main form with the logged in user name.

For the data about the user , i have build queries , that retrieve information by Username as Parameter.

My question is how can i use this queries (for example - all the user's games), and show the information in a form ?

What i have thinked about is running the query with the parameters from code:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set dbs = CurrentDb

'Get the parameter query
Set qdf = dbs.QueryDefs("Get User's Games")

'Supply the parameter value
qdf.Parameters(0) = Me.txt_UserName <- This is the user name from the main form

'Open a Recordset based on the parameter query
Set rst = qdf.OpenRecordset()

This code does the job for retrieving the data needed , but i don't know how to inject it into a form ? I Have build the corresponding form

I have tried using the following :

Forms![My Games].RecordSource = qdf.SQL
DoCmd.OpenForm "My Games"

But the sql , is without the parameter , so it doesn't work.Maybe its possible to replace the parameter with the value for it to work ?

I have tried this , but couldn't get it to compile :

Dim q As String
q = Replace(qdf.SQL, "[Enter User Name]", Me.txt_UserName)
Forms![My Games].RecordSource = q

Any ideas ? Also if there is a better way to do this , I'm open to suggestions

Answer Source

I Have Been Able to Answer My Question.
The solution i have found is not the prettiest , but it works.

If i have gotten a form , based on user's games for example , i use a regular select query with parameter , to get the info , and use this bit of code to make it work:

Public Function ChangeQuery(qdf As DAO.QueryDef, userName As String) As String
    Dim q As String
    q = qdf.sql

    Dim UserNameAsString As String
    UserNameAsString = "'" & userName & "'"

    q = Replace(q, "[Enter User Name]", UserNameAsString )
    ChangeQuery = q
End Function

This Function Replaces the Parameter [Enter User Name] , To whatever i give in the function as userName and replaced the sql, so it doesn't have any parameters.
Here is how you can use the function:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb

'Get the parameter query
Set qdf = dbs.QueryDefs("Get User's Games") // Get The needed Query

Me.RecordSource = ChangeQuery(qdf, Me.OpenArgs) //Sets the recordsource of the form to the non parameter sql.

If i needed a non select query , i would use it like so

Dim qry As DAO.QueryDef
Set qry = dbs.QueryDefs("Query Name")
qry .Parameters(0) = // some parameter 
qry .Parameters(1) = // some parameter 

qry .Execute

I Hope this helps anyone who countered it.