Newbie :: Make a form of a Query run in VBA

Hi all,

This might seem like a very dumb question looking at the previous topics on this forum, but I still hope someone is willing to help me out. I've got 2 questions.

1. I've designed a SQL query in VBA using DAO. (It's for a school assignment, and we have to use DAO instead ADO. But I don't think it will make much differents for what I need help with.)
I have the VBA code set on "Form_Load", but when I run the query, the result is just as wanted it. But it is shown in a Record-type edit field.

I was hoping someone could explain to me how I can make it show in FORM view. I've created the Form, but when I open it. All I see is the Record-type edit fields.
(I hope you guys understand what I mean, since english is not my native language.) It shows every game yet to be played. (via "datediff()")

Here is my VBA code:

Private Sub Form_Load()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryGames")

strSQL = "SELECT tblGames.GameID, tblGames.GameTeam1, tblGames.GameTeam2, tblGames.GameDate " & _
"FROM tblGames " & _
"WHERE (((tblGames.GameDate)>Date()));"

' MsgBox strSQL

qdf.SQL = strSQL
DoCmd.OpenQuery "qryGames"
' DoCmd.Close acForm, Me.Name

Set qdf = Nothing
Set db = Nothing

End Sub

I hope someone can explain to me how to make this show in a form. ;-)
I heard someone say I should use the "Requery" command? But I cant figure that out either.
---------------------------------------

2. How can I declare a variable that can be called back in every form & report I use in the entire database?
And how do I need to recall it?
I want to use this for the following: User logs in via a Login Form.
Now I want every other Form he opens to show his login name on the top of the form.
Any examples would be well appreciated



Thanks alot in advance for all replies!
This really seems like a great forum.

Hi ThomCube,

Not sure exactly what your "school assignment" is, but the code you have written is doing exactly what you wrote. The DoCmd.OpenQuery "qryGames" is opening your query. What you would want is to set the record source of your form to your sql.

Or:

Place the criteria directly into your query and then open the form without any code as long as your record source is set to your query

Or:

Use a filter in the open event of your form, something like this:

Dim strFilter As String
strFilter = "GameDate > Date()"
Me.Filter = strFilter
Me.FilterOn = True

For you second question, just hide your login form and then reference back to this form in your other forms. Also look at CurrentUser() as the default in your forms control and see if that gets the results you are looking for.

lwells