too few parameters expected 1 in a query referencing a subform control 1

[swaybright]

I am trying to create a way for users to fill a number of default records in a subform at the click of a button. the form is structured with subforms 3 layers deep (form-->subform1-->subform2-->subform3). Subform3 is the one I want to have the button fill. My approach is to have a button that when the users click it, an append query is run that places the records with the appropriate foreign key into the data table for subform3 record source. However, I only want the button to be available if there are no records in the data table having the foreign key (otherwise, the append will place a lot of unnecessary records if a user gets click happy ). So for the subform on current event, I have the following code (with the idea being to set the button visible property to false if icount>0):

Private Sub Form_Current()
Dim rs As Recordset
Dim db As Database
Dim icount As Integer
Dim iID As Long

iID = Forms!frmSample!frmNMRSample!frmNMRResults.Form.NMRResultsID
Debug.Print iID
Set db = CurrentDb
Set rs = db.OpenRecordset("qryNMRfill")
icount = rs.RecordCount
Debug.Print icount
End Sub

The qry sql statement is
SELECT Count(tblNMRResultDetails.NMRResultDetailsID) AS Total
FROM tblNMRResultDetails
WHERE (((tblNMRResultDetails.NMRResultsID)=[forms]![frmSample]![frmNMRSample]![frmnmrresults].[form]![nmrresultsid]));


The iID returns the expected value in the immediate window. When I run the query on its own with the form open, I get the expected recordset. However when I set the recordset, I get the too few parameters expected 1 error, it seems I cannot pass the value to the query. Any thoughts?

Thanks,
SW

 

[PHV]

What about this ?
icount = DCount("NMRResultDetailsID", "tblNMRResultDetails", "NMRResultsID=" & iID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[swaybright]

Very nice solution! Thank you. Your approach is much more elegant than mine. Any thoughts on why my approach failed? Do I have a syntax error or a logic flaw? In this case, your simple approach is best, but I may run into a future case where something like what I tried would be a necessary solution.

My final code is below attached to the frmNMRResultDetails on current event:

Private Sub Form_Current()
Dim recordcount As Integer
Dim testID As Long

If IsNull([Forms]![frmSample]![frmNMRSample]![frmNMRResults].Form.[NMRResultsID]) Then
Me!Command17.Visible = True
Exit Sub
Else
testID = [Forms]![frmSample]![frmNMRSample]![frmNMRResults].Form.[NMRResultsID]
recordcount = DCount("NMRResultDetailsID", "tblNMRResultDetails", "NMRResultsID=" & testID)
Debug.Print testID & " " & recordcount
End If

If recordcount > 0 Then
Me!Command17.Visible = False
Else
Me!Command17.Visible = True
End If

End Sub


Thanks!
Shane

 

[Andrzejek]

When you solve your problem with the query not working, you will find another problem:

iID = Forms!frmSample!frmNMRSample!frmNMRResults.Form.NMRResultsID
Debug.Print iID
Set db = CurrentDb
Set rs = db.OpenRecordset("qryNMRfill")[red]
icount = rs.RecordCount[/red]
Debug.Print icount
End Sub

Icount will always be 1, because your query will always return one record (since you ask for COUNT)

Consider:

iID = Forms!frmSample!frmNMRSample!frmNMRResults.Form.NMRResultsID
Debug.Print iID
Set db = CurrentDb
Set rs = db.OpenRecordset("qryNMRfill")[blue][tt]
icount = rs!Total.Value[/tt][/blue]
Debug.Print icount
End Sub



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[swaybright]

Great point, Andy. that would have certainly been my next issue. Thanks for thinking about my problem.

I have implemented PH's solution with great success.

I am still curious about why my query failed. It feels like some basic limitation in access; like one cannot reference that many nested subforms? Does this make sense?

 

[PHV]

As qryNMRfill is a parameterized query you have to populate the Parameters values when using a Recordset.
Have a look at my post here:
thread701-1545663

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[swaybright]

Oh. I see, thank you!

SW