Setting the record source of a form to a QueryDef. 1

[Petemush]

I have a single query(Query1) that I use with two different forms (Form1 + Form2).

When a button on either form is pressed, another form (Form3) opens, and the recordsource is set to Query1.

The query's criteria makes reference to Form1 so in the code called by pressing the button in Form2, I use a querydef and set the parameters for the criteria by code.

This is all fine except now, instead of setting Form3's recordsource to Query1's name, I need to set it to the querydef.

For some reason I can't do this, does anyone know how?Here's a little sample of the code I've tried:

Dim db As DAO.Database, rs As DAO.Recordset, qdf As DAO.QueryDef
Dim stDocName As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryEmployeesRelatedToCourse&quot

qdf![Forms!frmCourseVariables!CCode] = Forms![frmCourses]![CCode]
qdf![Forms!frmCourseVariables!CoDate] = "*"

Set rs = qdf.OpenRecordset()

stDocName = "qryEmployeesRelatedToCourse"

DoCmd.OpenForm "frmEmployees"
'This is the all important line
Form_frmEmployees.RecordSource = qdf
Form_frmEmployees.Requery

Cheers,

Pete

 

[oharab]

If you are using A2k then you can set the recordset directly.

Dim db As DAO.Database, qdf As DAO.QueryDef
Dim stDocName As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryEmployeesRelatedToCourse&quot

qdf![Forms!frmCourseVariables!CCode] = Forms![frmCourses]![CCode]
qdf![Forms!frmCourseVariables!CoDate] = "*"


stDocName = "qryEmployeesRelatedToCourse"

DoCmd.OpenForm "frmEmployees"
Set Form_frmEmployees.RecordSet = qdf.OpenRecordset


HTH

Ben ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[ClydeDoggie]

Two ways to do this. In Access 97 you can get the SQL statement of the querydef and set the recordsource to it. For example:

DoCmd.OpenForm "frmEmployees"
'This is the all important line
Form_frmEmployees.RecordSource = qdf.SQL
Form_frmEmployees.Requery

You can leave out the code where you open the recordset unless you need it somewhere later in the procedure.

If your are using Access 2K or XP there is a new Form property called Recordset. After you open the recordset you can just set the Forms recordst property to it. For example:

Set Form_frmEmployees.RecordSet = rs

 

[Petemush]

Cheers both of you,

am currently using A97 but it's going on a 2K system so I'll change when I get to implement it, bloody management refusing to give me 2K to work with.

ClydeDoggie your solution seems perfect and I no longer get an error but I now get asked for the parameters which I've already set earlier in the code. Any idea why it wants them (asks for them twice incidentally) again?

Cheers for all your help,

Pete

 

[Petemush]

Cheers both of you,

am currently using A97 but it's going on a 2K system so I'll change when I get to implement it, bloody management refusing to give me 2K to work with.

ClydeDoggie your solution seems perfect and I no longer get an error but I now get asked for the parameters which I've already set earlier in the code. Any idea why it wants them (asks for them twice incidentally) again? I checked the SQL in the debug window and the parameters are the same as the original query so I assume the above code doesn't change them properly. Would you know how to resolve this?

Cheers for all your help,

Pete

 

[Petemush]

Sorry! Double post.

 

[ClydeDoggie]

I am not sure why you are getting prompted for the parameters. I just created two simple forms, frm1 and frm2, and recreated your situation and I don't get prompted. Your code sample contained a few extraneous lines, so I will show you the abreviated code I used and explain what it does. Hopefully you can trim yours and get the desired result.

Private Sub cmdOpen_Click() 'This is in the click event of a button on frm1 (where you enter the id).

'only two declarations needed
Dim db As Database
Dim qdf As QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest&quot

'I used the index of the parameter instead of the name
'you would use (0) and (1)
qdf.Parameters(0) = Me.txtID

DoCmd.OpenForm "frm2" 'make sure the recordsource of frm2 is blank to begin with
Forms!frm2.RecordSource = qdf.SQL
'Requery not needed

End Sub


Let me know if you are still having problems, I can e-mail the sample db I created to test this solution if you wish.

 

[Petemush]

Still can't get it to work, appreciate it if I could see your test database. EMail is send_me_junk@hotmail.com.

Thankyou, I think my query might be set up slightly incorrectly so could i have a gander at yours? cheers!

Pete

 

[Petemush]

Thanks Peter,

Yes it does work without prompts but there is a difference in what you've designed and what I have.

In the query you created, you made the criteria for ID as

[Forms]![frm1]![txtID]

and the button that opens the query is on frm1 so in that situation there is no need to define the parameter manually because the query is going to be looking for [Forms]![frm1]![txtID] and it will find it since that form is open.

I use the query like that as well but I also want to use it on another form where the criteria the query is looking for doesn't exist and therefore have to set the parameters manually.

I.e. The criteria is [Forms]![frm1]![txtID] in my query but I'm calling the query from frm2 so the query won't find [Forms]![frm1]![txtID] since that form isn't open. Hence the need to manually set the values.

I tried changing your query so that it was looking for criteria that didn't exist. Theoretically this should be alright because your code set the parameter manually and indeed I didn't get prompted for the parameter but the correct player wasn't shown.

I also changed my database so that the query was only looking for one parameter but I still got prompted for the single parameter.

Cheers for all your help, hope you might know what's going on!

Cheers,

Pete

 

[ClydeDoggie]

Ok, I guess I had a slight misunderstanding of what you were doing. I do know why you are getting prompted now. The SQL behind the querydef doesn't store the parameter value, just the query text. The value is stored in the Parameter property of the querydef but this doesn't get sent to the form with the query text. I have added a third form and made it work using string manipulation functions. This is the code to put under the button that opens frm3. I sent you a new version of the test database I sent yesterday, but it is in Access 2K (all I had on this machine). If you need it in Access 97 I can send one tonight. Let me know.

Private Sub cmdFrm3_Click()

Dim db As Database
Dim qdf As QueryDef
Dim sSQL As String
Dim nID As Long

Set db = CurrentDb
Set qdf = db.QueryDefs("qryTest&quot
sSQL = qdf.SQL
'You would need to set this variable to whatever you want
'the parameter to be, Forms![frmCourses]![CCode] I think
nID = 3
'This replaces the parameter name with a value
sSQL = Replace(sSQL, "[Forms]![frm1]![txtID]", nID)

DoCmd.OpenForm "frm3"
Forms!frm3.RecordSource = sSQL

End Sub

 

[Petemush]

This is the perfect answer! I did wonder whether I'd have to edit the SQL text but never did anything. Only problem is, is I think that Replace is an A2K function only? It isn't recognised when I type it. I expect there is another way but before I try it, can you confirm whether it is A2K only or not?

Thank you so much Peter, you've been a star!

Cheers,

Pete

 

[oharab]

Pete,
here is a function that you can use in A97 to replace the text.

'Code Courtesy of
'Alden Streeter
'
Function FindAndReplace(ByVal strInString As String, _
strFindString As String, _
strReplaceString As String) As String
Dim intPtr As Integer
If Len(strFindString) > 0 Then
Do
intPtr = InStr(strInString, strFindString)
If intPtr > 0 Then
FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
strReplaceString
strInString = Mid(strInString, intPtr + Len(strFindString))
End If
Loop While intPtr > 0
End If
FindAndReplace = FindAndReplace & strInString
End Function


I didn't realise Replace existed, so we have both been enlightened!

HTH

B ----------------------------------
Ben O'Hara
Home: bpo@RobotParade.co.uk
Work: bo104@westyorkshire.pnn.police.uk
Web:

http://www.RobotParade.co.uk

----------------------------------

 

[Petemush]

Thanks Ben,

I managed already to get some similar code from elsewhere and am happy to say it all works! Woohoo!

On another note(and at risk of making this thread far too long) I manipulated the SQL text and replaced

"[Forms]![frmCourseVariables]![CoDate]"

with

"'*' OR (tblEmployeeCourses.[Course Date]) Is Null"

since I want any course date as well as ones that haven't been set yet, to be displayed.

This works fine but I want to adapt it for something else.

In another form I set the parameters in a querydef as normal and have no problem. But how do I set it in a similar way to above?

I tried this:

qdf![Forms!frmMassEmail!Status] = Nz(Forms![frmMassEmail]![Status], "* Or Is Null&quot

and this:

qdf![Forms!frmMassEmail!Status] = Nz(Forms![frmMassEmail]![Status], "* Or (tblEmployeeCourses.[Course Date]) Is Null&quot

but neither works.

So does anyone know how to use an OR operator when setting the parameters for a query in code?

Cheers,

Pete

 

[ClydeDoggie]

You are going to be the parameter King! I think the problem you are having is misleading you. You are actually performing two checks, one involving a parameter (named [Forms!frmMassEmail!Status]) and one checking to see if a table field is null.
If you open your query in SQL view and replace the Where clause with the one below, then in code set the parameter as normal without the OR part I think you will get the desired results. The second part of this Where statement uses a calculated field that derives from the Is Null check of the table field. If you are having trouble understanding what is happening let me know and I will give the long version of the explanation. If you have never used calculated fields in your queries you may want to experiment with them. They can solve some tricky situations involving complicated filtering.

WHERE (((tblEmployeeCourses.[Course Date])=[Forms![frmMassEmail]![Status])) OR (((nz([tblEmployeeCourses.[Course Date],-1))<0))

 

[ClydeDoggie]

Oh, Yeah-
The Replace function comes from the Strings collection within VBA 6. If you have A97 and A2K on one machine you can change the reference for VBA to the v6 library, otherwise you will have to wait for A2K (or steal the dll from some other machine and manually register it).

 

[Petemush]

Works like a charm, if I could give you another star I would.

I have access to another machine with 2K but I don't want to register it cos I'm not sure what I'm doing and may well screw up the machine. I've copied some dlls and olbs over to my machine and had a look at the reference for VBA but the path to the file it uses is too long to be seen on the references window so I can't check what it is currently pointing to.

If you know how could you let me know?

Cheers, and thanks again for you invaluable help!

Pete

 

[ClydeDoggie]

Pete:
The library you want is VBE6.DLL and the path on a machine with a default installation is C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6.DLL. Just as a side note I couldn't read the path in the object browser either so I whipped this up.

Public Sub ShowRefPath()
Dim r As Reference
For Each r In Access.References
MsgBox r.Name & &quot; - &quot; & r.FullPath
Next r
End Sub

 

[Petemush]

Perfect!

I stole some help files too, is it possible to implement them? Since pressing F1 with Replace highlighted just gives you Keyword not found I'm assuming this would happen with everything in VB6 that's new.

I will squeeze every last piece of information I can out of you!

Cheers,

Pete

 

[ClydeDoggie]

Not sure why help isn't coming up. Did you register the dll or just browse to it and set a reference? If you didn't register it, try using Regsvr32 and see if that fixes it. If you don't know how to manually register a file let me know and I will give you directions.

 

[Petemush]

If I try to use Regsvr32 I get:


LoadLibrary(&quot;vbe6.dll&quot failed.
GetLastError returns 0x00000485.