Select Query in Code is driving me nuts

[boborsipho]

I've been trying to get this to work for days now. My code is below. I'm not really familiar with VB.
The first error was the "Too few parameters, expected 1".
I changed my code a bit and now it's "Object 'Times' already exists".

What am I doing wrong and how do I fix it. Is it necessary for the QueryDef & Recordset?

Thanx Bob

Dim rs As Recordset
Dim db As Database
Dim qdf As QueryDef
Dim temp As String

temp = "Date" & mynum & ""
//mynum is a parameter of the function

sql = "SELECT [Entertainment]![Name] & [Entertainment]![FirstName] & ' ' & [Entertainment]![Surname] AS TheName, EventCalendar.Date, EventCalendar.Time, EventCalendar.Highlighted FROM Entertainment, EventCalendar WHERE Entertainment.UniqueEntertainmentNumber=EventCalendar.UniqueEntertainmentNumber AND EventCalendar.Date=" & "[Forms]![frmCalender]!#[" & temp & "]#;"

Set qdf = db.CreateQueryDef("Times", sql)
Set rs = qdf.OpenRecordset(dbOpenDynaset)

 

[danvlas]

CreateQuerydef, as its name says, creates a query. Next time you run the code, it finds an existing query with that name and that's why your error occurs.

To solve the problem:

On Error Resume Next
'change the error handling if not appropriate
db.QueryDefs.Delete "Times"
Set qdf = db.CreateQueryDef("Times", sql)
Set rs = qdf.OpenRecordset(dbOpenDynaset)

But why a querydef?
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
would do what you want (I suppose)

Regards


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[boborsipho]

What do you think I should do in stead of
Set rs = db.OpenRecordset(sql, dbOpenDynaset).

What does that statement do?

How do I run the Recordset/Querydef?

Thanx
Bob

 

[danvlas]

Set rs = db.OpenRecordset(sql, dbOpenDynaset)

opens the recordset defined by the SQL statement as a dynamic (updatable) recordset. With the recordset open, you can navigate through records, search for values, add records, delete records and so on.
But it does not display the records on the screen... If that's what you want, you need to use DoCmd.OpenQuery:

'If an error occurs, just ignore it
On Error Resume Next
'if query exists, delete it if possible
db.QueryDefs.Delete "Times"
'Create new query (if possible)
Set qdf = db.CreateQueryDef("Times", sql)
'Run the query and display results on screen
DoCmd.OpenQuery "Times"

HTH

Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[mymou]

Hi boborsipho

Havent read through all replies - but from your first posting I would question the need for programming.

Why not just create a query with a few expressions. Simplier and easer to follow.

The only possible problem would be the '& temp & ' varible - but im sure this could be solved a different way (hidden field or parameter in query)

Stew