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()
Forms![My Games].RecordSource = qdf.SQL
DoCmd.OpenForm "My Games"
Dim q As String
q = Replace(qdf.SQL, "[Enter User Name]", Me.txt_UserName)
Forms![My Games].RecordSource = q
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.