missing parameters

[KellyK]

Hi all. I am trying to open a recordset with a query as the source. I get the error "no value given for one or more required parameters." The problem is occuring because in my query I reference a combo box on my main form. I have no problem using the combo box value in my code for this second form, but when I try to create a recordset based on the query there are problems. Up until now I have been running a make table query and using the table as a source for my recordset, but I know this is unnecessary. Can anyone shed some light on this for a newbie?
Thanks,
Kelly

 

[BanditWk]

Are you saying that you're betatesting your recordset without the form open, hence not allowing the query to reference your combo box? Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[KellyK]

Nope, the main form is open. I click a button on the main form which opens the second form. The code in question opens a recordset to populate a text box on the second form.

 

[BanditWk]

Could you send me your code? I could probably figure it out from there.
Roy
aka BanditWk
Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

 

[KellyK]

I'll post it here in case anyone else is following this thread:

Private Sub Form_Load()

Dim rstOffice As ADODB.Recordset
Dim rstYTD As ADODB.Recordset
Dim intWorkdays As ADODB.Recordset
Dim strControl As String
Dim strControl2 As String
Dim strControl3 As String
Dim strPrefix As String

'turn warnings off
DoCmd.SetWarnings False

'create tables from queries
DoCmd.OpenQuery "qmaktblRptTotJerryYtd"
DoCmd.OpenQuery "qmaktblTotalWorkdays"

Set rstOffice = New ADODB.Recordset
Set rstYTD = New ADODB.Recordset
Set intWorkdays = New ADODB.Recordset

intWorkdays.Open "SELECT * FROM tblTotalWorkdays", CurrentProject.Connection

'intWorkdays.Open "SELECT * FROM qselTotalWorkdaysYTD", CurrentProject.Connection

The commented line is the one that I'm receiving the error on. My query's SQL is this:

SELECT Sum(qselWorkdays.Workdays) AS TotalWorkdays, Count(qselWorkdays.Month) AS NumMonths
FROM qselWorkdays
WHERE (((Left([Month],2))<Format([Forms]![fmnuMain].[cboMonth],&quot;mm&quot;)) AND ((Right([Month],4))=Format([Forms]![fmnuMain].[cboMonth],&quot;yyyy&quot;)))
WITH OWNERACCESS OPTION;

Any glaring errors?

 

[Junior1544]

Just a side note, I know i've had problems with basing a recordset from a query that has a perameter as a control on a form... what i had to do was write the sql to make the recordset, and reference the the control in the sql with a where statment... hope this little bit of info helps...

--Junior JHauge@jmjpc.net
Life is change. To deny change is to deny life.

 

[cmmrfrds]

You could try using the query directly without going through the outer select. First search through the Views and find the query and use that as the reference.

''- Microsoft ADO Ext. 2.6 for DDL and Security
Dim cg As New ADOX.Catalog, cn as New ADODB.Connection
Set cg.ActiveConnection = CurrentProject.Connection
set cn = CurrentProject.Connection

Dim v As View
Dim vn As View
For Each v In cg.Views
Debug.Print "views = "; v.Name
If v.Name = "query1" Then
Set vn = v
End If
Next
rs.Open vn.Name, cn

 

[KyleS]

Kelly,
I've run into this before, but when I've run into this it is when using a FrontEnd-BackEnd setup, not when the whole dB is self-contained. Have you tried using this line:

'intWorkdays.Open "qselTotalWorkdaysYTD", CurrentProject.Connection,,,adCmdTable

Anyway you can pass the parameter (although I believe you need to go into the query and save as a parameter query) I don't remember how, but I'll look it up for you and post it back. But that's kind of a pain to remember to do with all your queries, so I usually just write the SQL into the code, incorporating whatever values I may need. (I realize that SQL in code has to be compiled and interepereted so it is slower than a saved query, but unless you dB is HUGE and this query is extremely complicated (or run on a large table with no indexes) you won't notice.


dim strSQL as String
strSQL = "SELECT * FROM tblWorkDays WHERE...."

intWorkdays.Open strSQL, CurrentProject.Connection,,, adCmdTable Kyle

 

[KellyK]

Thank you for the helpful suggestions. I wanted to make sure that I wasn't doing something obviously very wrong but it seems that others have experienced this problem as well. I will consider changing my queries and referencing the combo box in the sql itself, but at this point I might take the "if it aint broke don't fix it" approach. Thanks all.