OpenRecordset error

[fluffyozzy]

Could someone help me please? I am trying to open two tables for comparing values. First table contains a number of records and I need to count how many records there are, depending on a field called QuoteID. Second table contains a field specifying the maximum number of records I can have on the first table with the same QuoteID. The limiting value is the QuoteID which is displayed on an active form.

I hope this makes sense...

I've been trying different things for hours but can't seem to get it to work. It gives me a "too few parameters" error, I have tried various approaches suggested in the forum but can't seem to be able to apply them to my problem.

Any help at all would be very much appreciated. Thank you!

Private Sub ProduceTheoryCmd_Click()
    

       
    Dim intCountMCQBatch As Integer 'Count of number of current candidates for organisation
    Dim intQuoteLimit As Integer 'The number of candidates allocated for current quote
    Dim rst As Recordset 'Recordset for counting the number of papers already created for current quote
    Dim rsta As Recordset 'Recordset for current quote limit
    
    Set rst = CurrentDb.OpenRecordset("SELECT MCQBatchTbl.MCQBatchID, MCQBatchTbl.QuoteID FROM MCQBatchTbl WHERE MCQBatchTbl.QuoteID = Forms!MainFrm!QuoteID;")
    Set rsta = CurrentDb.OpenRecordset("SELECT QuotesTbl.QuoteID, QuotesTbl.NoOfCands FROM QuotesTbl WHERE QuotesTbl.QuoteID = Forms!MainFrm!QuoteID;")
    intCountMCQBatch = Nz(DCount("MCQBatchID", "MCQBatchTbl"), 0)
    intQuoteLimit = QuotesTbl.NoOfCands
    
        If intCountMCQBatch > intQuoteLimit Then
            MsgBox "Number of papers allocated for the current quote has been reached"
        Else
            Call PrepMCQBatchID
            Forms!MainFrm!MainHoldFrm.SourceObject = "MainHoldFrm"
        End If
        
End Sub

 

[ByteMyzer]

Try the following modified code:

Private Sub ProduceTheoryCmd_Click()

    Dim intCountMCQBatch As Integer 'Count of number of current candidates for organisation
    Dim intQuoteLimit As Integer 'The number of candidates allocated for current quote
    Dim rst As Recordset 'Recordset for counting the number of papers already created for current quote
    Dim rsta As Recordset 'Recordset for current quote limit

    Set rst = CurrentDb.OpenRecordset( _
        "SELECT MCQBatchTbl.MCQBatchID, MCQBatchTbl.QuoteID " _
        & "FROM MCQBatchTbl " _
        & "WHERE MCQBatchTbl.QuoteID = [b]" & Forms("MainFrm").QuoteID & "[/b];")
    Set rsta = CurrentDb.OpenRecordset( _
    "SELECT QuotesTbl.QuoteID, QuotesTbl.NoOfCands " _
        & "FROM QuotesTbl " _
        & "WHERE QuotesTbl.QuoteID = [b]" & Forms("MainFrm").QuoteID & "[/b];")
    intCountMCQBatch = Nz(DCount("MCQBatchID", "MCQBatchTbl"), 0)
    intQuoteLimit = QuotesTbl.NoOfCands

    If intCountMCQBatch > intQuoteLimit Then
        MsgBox "Number of papers allocated for the current quote has been reached"
    Else
        Call PrepMCQBatchID
        [b]Forms("MainFrm").MainHoldFrm.[/b]SourceObject = "MainHoldFrm"
    End If

End Sub

Of course, if QuoteID is a non-numeric field, you will need to substitute these two lines:

    Set rst = CurrentDb.OpenRecordset( _
        "SELECT MCQBatchTbl.MCQBatchID, MCQBatchTbl.QuoteID " _
        & "FROM MCQBatchTbl " _
        & "WHERE MCQBatchTbl.QuoteID = [b]'" & Forms("MainFrm").QuoteID & "'[/b];")
    Set rsta = CurrentDb.OpenRecordset( _
    "SELECT QuotesTbl.QuoteID, QuotesTbl.NoOfCands " _
        & "FROM QuotesTbl " _
        & "WHERE QuotesTbl.QuoteID = [b]'" & Forms("MainFrm").QuoteID & "'[/b];")

See if this works for you.

 

[fluffyozzy]

Hi ByteMyzer,

Thank you so much. It is at least going through some lines of code, makes me feel hopeful again. But, it gives me an "object required" error on the line below. Any ideas why?

intQuoteLimit = QuotesTbl.NoOfCands


The QuoteID is the primary key of the QuotesTbl, so, it is a numeric field. NoOfCands is a numeric field too.

I would have never thought of putting quotation marks and brackets on the form names when referring to them.. I live and I learn

 

[WayneRyan]

fluffy,

intQuoteLimit = rsta!NoOfCands

Wayne

 

[fluffyozzy]

Of course!!!! Yet another d'oh moment for me

Thank you very much for you help guys, much appreciated.

 

[fluffyozzy]

Could I ask another teeny weeny question please?

I need the DCount to count the results of the rst rather than straight from the table like below. How would I reference that?

intCountMCQBatch = Nz(DCount("MCQBatchID", "MCQBatchTbl"), 0)

I tried

intCountMCQBatch = Nz(DCount(rst!MCQBatchID),0)

and of course, it doesn't work!

 

[Banana]

Realize what you can do in queries isn't always true in VBA. Nz() is a query function (I was going to say SQL, but I hesistate because I don't know which functions that SQL doesn't have that JET uses).

In this case, you only need to do something like this:

Me.Mytxtbox = rst.Recordcount

There are more ways to skin a cat, and this will work; I recall seeing a cautionary remark about recordcount in my reference, but can't remember what it was, so caveat emperor.


EDIT = Remember now. Recordcount property will only count records you've visited (e.g. from start of recordset to the current position). Therefore you need to add a line before recordcount:

rst.MoveLast
rst.Recordcount

Understand that there is a performance penalty when moving pointer to the last record if the recordset is very large (let's say 100k).

HTH.

 

[fluffyozzy]

Thanks Banana,

Mm, okay, I have taken your advice and did the following, now the code just runs without taking into account of the first part of the IF statement. It never takes into account if one value is higher than the other, it should give a message. I'm fairly sure this is simple to do, but I just can't get it right


intCountMCQBatch = rst.RecordCount
intQuoteLimit = rsta!NoOfCands

If intCountMCQBatch > intQuoteLimit Then
MsgBox "Number of papers allocated for the current quote has been reached"
Else
Call PrepMCQBatchID
Forms("MainFrm").MainHoldFrm.SourceObject = "MainHoldFrm"
End If

 

[fluffyozzy]

I understand the performance penalty with this. It is something we have to just put up with it, unless there is another way of counting records effectively.

I have still not completely solved my problem, the code still runs through without stopping at the appropriate place.

 

[Banana]

Add this just before your If statement:

Debug.Print intCountMCQBatch
Debug.Print intQuoteLimit

This will output the value into immediate windows. Ensure that you're getting the value you were expecting. If it's not what you expected, it means that code is working correctly, but you need to fix the logic. Most probable explanation is that the two recordset are not synchronized.

 

[fluffyozzy]

Okay, I have now used the rst.MoveLast - it sort of works, it does give the message at the appropriate time, ie, when the quote limit is reached - inching slowly to the final solution.

rst.MoveLast assumes that there are records already present with the specified parameters. This is not always the case. If the count is 0, it gives me an error message saying "No current record" and it is right, there are no records yet. In this case, it should just go ahead (to the Else part of the If statement) and run the code which creates the first record then.. I hope this makes sense. Is there a solution for this?

 

[Banana]

I forgot that you have to trap for that case;

If Not rst.Recordcount = 0 Then
   rst.MoveLast
   intCountMCQBatch = rst.RecordCount
   intQuoteLimit = rsta!NoOfCands
   If intCountMCQBatch > intQuoteLimit Then
       MsgBox "Number of papers allocated for the current quote has been reached"
   Else
     Call PrepMCQBatchID
     Forms("MainFrm").MainHoldFrm.SourceObject = "MainHoldFrm" 
   End If
Else
   'What do you want to do if recordcount is zero?
End If

 

[fluffyozzy]

Respect!

IT WORKS!

Thank you so much for all your help Banana, very much appreciated

 

[Banana]

Glad to help.