form.recordsource

C

chriske911

can I use a parameter query as recordsource for my form
and supply the parameters on the fly in VBA?

like this

dim qrs as dao.querydef
set qrs = currentdb.querydefs("somequery")
qrs.parameters("someparameter")= somevalue
me.recordsource = qrs.openrecordset

this way I can use a case for supplying the parameter

thnx

chriske
 
B

Brendan Reynolds

Option Compare Database
Option Explicit

Private mdb As DAO.Database
Private mrst As DAO.Recordset

Private Sub Form_Close()
mrst.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
Set mdb = CurrentDb
mdb.QueryDefs("qryTest").Parameters("[ID?]").Value = 3
Set mrst = mdb.QueryDefs("qryTest").OpenRecordset
Set Me.Recordset = mrst
End Sub
 
C

chriske911

Option Compare Database
Option Explicit

Private mdb As DAO.Database
Private mrst As DAO.Recordset

Private Sub Form_Close()
mrst.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
Set mdb = CurrentDb
mdb.QueryDefs("qryTest").Parameters("[ID?]").Value = 3
Set mrst = mdb.QueryDefs("qryTest").OpenRecordset
Set Me.Recordset = mrst
End Sub

--
Brendan Reynolds

chriske911 said:
can I use a parameter query as recordsource for my form
and supply the parameters on the fly in VBA?

like this

dim qrs as dao.querydef
set qrs = currentdb.querydefs("somequery")
qrs.parameters("someparameter")= somevalue
me.recordsource = qrs.openrecordset

this way I can use a case for supplying the parameter

thnx

chriske
Click to expand...
Click to expand...

nope, I already tried that and it returns a null recordset

this is my code:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 1
Me.Caption = "Customer details"
Case "Sup"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 0
Me.Caption = "Supplier details"
Case Else

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value =
vbNull
End Select
End If
Set Custrst = CurrentDb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
End Sub

thnx
 
B

Brendan Reynolds

Perhaps that's because you're using CurrentDb, instead of module-level
Database and Recordset variables, as in my (tested and working) example?

--
Brendan Reynolds

chriske911 said:
Option Compare Database
Option Explicit

Private mdb As DAO.Database
Private mrst As DAO.Recordset

Private Sub Form_Close()
mrst.Close
End Sub

Private Sub Form_Open(Cancel As Integer)
Set mdb = CurrentDb
mdb.QueryDefs("qryTest").Parameters("[ID?]").Value = 3
Set mrst = mdb.QueryDefs("qryTest").OpenRecordset
Set Me.Recordset = mrst
End Sub

--
Brendan Reynolds

chriske911 said:
can I use a parameter query as recordsource for my form
and supply the parameters on the fly in VBA?

like this

dim qrs as dao.querydef
set qrs = currentdb.querydefs("somequery")
qrs.parameters("someparameter")= somevalue
me.recordsource = qrs.openrecordset

this way I can use a case for supplying the parameter

thnx

chriske
Click to expand...
Click to expand...

nope, I already tried that and it returns a null recordset

this is my code:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 1
Me.Caption = "Customer details"
Case "Sup"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 0
Me.Caption = "Supplier details"
Case Else

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = vbNull
End Select
End If
Set Custrst = CurrentDb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
End Sub

thnx
Click to expand...
 
C

chriske911

Perhaps that's because you're using CurrentDb, instead of module-level
Database and Recordset variables, as in my (tested and working) example?
Click to expand...

nope, I already tried that and it returns a null recordset

this is my code:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 1
Me.Caption = "Customer details"
Case "Sup"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 0
Me.Caption = "Supplier details"
Case Else

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = vbNull
End Select
End If
Set Custrst = CurrentDb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
End Sub

thnx
[/QUOTE][/QUOTE]

still no go,

Option Explicit
Dim Custrst As DAO.Recordset
Dim CMdb As DAO.Database

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OpenF
Dim rs As DAO.Recordset
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"
Set CMdb = CurrentDb
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value
= 1
Me.Caption = "Customer details"
Case "Sup"
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value
= 0
Me.Caption = "Supplier details"
Case Else
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value
= ""
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
GoTo Exit_OpenF
End Select
End If
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
Exit_OpenF:
Exit Sub
Err_OpenF:
Cancel = True
Resume Exit_OpenF
End Sub

is there anything else wrong with my code?

thnx
 
B

Brendan Reynolds

In your Case Else, you open the recordset, but you never assign it to the
form's Recordset property. Also, your error handler isn't going to tell you
anything if an error occurs. The code will just fail silently with no
indication of what the problem might be. What happens if you run this code
with the error handling option set to "Break on all errors"? What's the data
type of the CustBool parameter?

--
Brendan Reynolds


chriske911 said:
Perhaps that's because you're using CurrentDb, instead of module-level
Database and Recordset variables, as in my (tested and working) example?

--
Brendan Reynolds

chriske911 said:
nope, I already tried that and it returns a null recordset

this is my code:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 1
Me.Caption = "Customer details"
Case "Sup"

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 0
Me.Caption = "Supplier details"
Case Else

CurrentDb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value =
vbNull
End Select
End If
Set Custrst = CurrentDb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
End Sub

thnx
Click to expand...
Click to expand...

still no go,

Option Explicit
Dim Custrst As DAO.Recordset
Dim CMdb As DAO.Database

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OpenF
Dim rs As DAO.Recordset
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"
Set CMdb = CurrentDb
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 1
Me.Caption = "Customer details"
Case "Sup"
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 0
Me.Caption = "Supplier details"
Case Else
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value =
""
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
GoTo Exit_OpenF
End Select
End If
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
Exit_OpenF:
Exit Sub
Err_OpenF:
Cancel = True
Resume Exit_OpenF
End Sub

is there anything else wrong with my code?

thnx
Click to expand...
 
C

chriske911

In your Case Else, you open the recordset, but you never assign it to the
form's Recordset property. Also, your error handler isn't going to tell you
anything if an error occurs. The code will just fail silently with no
indication of what the problem might be. What happens if you run this code
with the error handling option set to "Break on all errors"? What's the data
type of the CustBool parameter?
Click to expand...

still no go,

Option Explicit
Dim Custrst As DAO.Recordset
Dim CMdb As DAO.Database

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OpenF
Dim rs As DAO.Recordset
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"
Set CMdb = CurrentDb
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 1
Me.Caption = "Customer details"
Case "Sup"
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = 0
Me.Caption = "Supplier details"
Case Else
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value = ""
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
GoTo Exit_OpenF
End Select
End If
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
Exit_OpenF:
Exit Sub
Err_OpenF:
Cancel = True
Resume Exit_OpenF
End Sub

is there anything else wrong with my code?

thnx
[/QUOTE][/QUOTE]

I did manage to make it work on your advices
there was indeed a problem with the parameter's data type
I tried to cast it from string to YES/NO
then I get this error number 3421 every time

the "set CMdb" was just a mistyping of me

so now it does work when I pass 1 or 0 for the first 2 cases but no
show for the Case Else

can I pass a null value instead of 0 or 1 for a YES/NO field?
I made the parameter display all records if no parameter value is
entered
but when I pass "" or vbnull it gives me the same error from above
when I call the query manually and enter nothing it does give me all
records

thnx a bunch for your help so far
 
B

Brendan Reynolds

A JET Boolean (Yes/No) field always contains either True (-1) or False (0),
never Null. (You can assign a Null value to the field, but it is silently
converted to False). You could change the data type of the field to Integer,
or you could change the query to have two parameters. Something like ...

SELECT * FROM MyTable WHERE SomeField = [Param1] OR someField = [Param2]

To return only records where SomeField = True set both parameters to True.
To return only records where SomeField = False, set both parameters to
False. To return all records regardless of whether SomeField is True or
False, set one parameter to True and the other to False.

--
Brendan Reynolds

chriske911 said:
In your Case Else, you open the recordset, but you never assign it to the
form's Recordset property. Also, your error handler isn't going to tell
you anything if an error occurs. The code will just fail silently with no
indication of what the problem might be. What happens if you run this
code with the error handling option set to "Break on all errors"? What's
the data type of the CustBool parameter?

--
Brendan Reynolds

chriske911 said:
still no go,

Option Explicit
Dim Custrst As DAO.Recordset
Dim CMdb As DAO.Database

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_OpenF
Dim rs As DAO.Recordset
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "Cust"
Set CMdb = CurrentDb
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value =
1
Me.Caption = "Customer details"
Case "Sup"
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value =
0
Me.Caption = "Supplier details"
Case Else
CMdb.QueryDefs("qrycustdet").Parameters("[CustBool]").Value =
""
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
GoTo Exit_OpenF
End Select
End If
Set Custrst = CMdb.QueryDefs("qryCustDet").OpenRecordset
Set Me.Recordset = Custrst
DoCmd.Restore
Exit_OpenF:
Exit Sub
Err_OpenF:
Cancel = True
Resume Exit_OpenF
End Sub

is there anything else wrong with my code?

thnx
Click to expand...
Click to expand...

I did manage to make it work on your advices
there was indeed a problem with the parameter's data type
I tried to cast it from string to YES/NO
then I get this error number 3421 every time

the "set CMdb" was just a mistyping of me

so now it does work when I pass 1 or 0 for the first 2 cases but no show
for the Case Else

can I pass a null value instead of 0 or 1 for a YES/NO field?
I made the parameter display all records if no parameter value is entered
but when I pass "" or vbnull it gives me the same error from above
when I call the query manually and enter nothing it does give me all
records

thnx a bunch for your help so far
Click to expand...
 
C

chriske911

A JET Boolean (Yes/No) field always contains either True (-1) or False (0),
never Null. (You can assign a Null value to the field, but it is silently
converted to False). You could change the data type of the field to Integer,
or you could change the query to have two parameters. Something like ...

SELECT * FROM MyTable WHERE SomeField = [Param1] OR someField = [Param2]

To return only records where SomeField = True set both parameters to True. To
return only records where SomeField = False, set both parameters to False. To
return all records regardless of whether SomeField is True or False, set one
parameter to True and the other to False.
Click to expand...

I did manage to make it work on your advices
there was indeed a problem with the parameter's data type
I tried to cast it from string to YES/NO
then I get this error number 3421 every time

the "set CMdb" was just a mistyping of me

so now it does work when I pass 1 or 0 for the first 2 cases but no show
for the Case Else

can I pass a null value instead of 0 or 1 for a YES/NO field?
I made the parameter display all records if no parameter value is entered
but when I pass "" or vbnull it gives me the same error from above
when I call the query manually and enter nothing it does give me all
records

thnx a bunch for your help so far
[/QUOTE][/QUOTE]

YEP, that did it
I used 2 parameters and diff sequences of ones and zeros gives me all
the records I need

a bit more code, but what the heck, it works
and I only have to type it once ;-)

thnx a million
 
Top