Problems opening a query based Recordset

[Darrenc]

I've been working on a fairly complex database lately, and for the most part everything is going well, apart from my lack of VB skills.
So i apologise for the influx of questions i've been asking lately, i'm trying really hard to find the answer before posting...promise

I'll explain what i'm trying to achieve.

I have a form where a user logs errors reported throughout our company, this particular form has a sub-form, this is linked with a '1 to many' relationship.
When the user has filled out the form, i want the user to click a button and then some code sends e-mail’s and/or relevant reports based on the information supplied.

Now, i've created a query where i plan to extract all the information for my code, the first problem is that i can't set any parameters in the query before its passed to the code, I get the error 'To few parameters. Expected 1' error.

So i read some info provided by pat, and she said that you need to set the parameters for the query in the code itself.

This is the bit i can't seem to get to work.

Public Function CloseErr()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim QD1 As DAO.QueryDef
    Dim strDepartment As String
    Dim strErrReason As String
    Dim strErrWho As String
    Dim strErrLevel As String
    Dim IntExternalID As Integer
    Dim intCount As Integer
    Dim strSubject As String
    Dim strEmailAddress As String
    Dim strEMailMsg As String
    Dim intInternalID As Integer
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
        
    
    Set dbs = CurrentDb
    Set QD1 = dbs.QueryDefs![qryCloseIntError]
    
    QD1.Parameters![Forms]![frmInternalErrors]![InternalID] = [Forms]![frmInternalErrors]![InternalID]
    Set rst = QD1.OpenRecordset
    
        strDepartment = rst![ErrorDepartment]
            strErrReason = rst![ErrorReason]
                strErrWho = rst![ErrorWhoMade]
                    strErrLevel = rst![ErrorLevel]
    
    If strErrLevel = "Level 1" Then
        MsgBox "Serious Error Test", vbExclamation, "Level 1"
            Else
                MsgBox "Not So Serious Error Test", vbExclamation, "Level 1 and 2"
            End If
    
End Function

I've not done anything useful with the code just yet, i though i'd test it out with some simple msg boxes, just to see that i'm getting the right info through.

QD1.Parameters![Forms]![frmInternalErrors]![InternalID] = [Forms]![frmInternalErrors]![InternalID]

Its this line that seems to keep falling over, the second part after the '=' is returning the right value, its the first part i'm having problems with. The error message is 'Item not found in this collection'

I only want the records that match the 'InternalID' number on the main form. I'm just not sure how to do it.

Sorry for the long post, i figured the more info i give, the better chance i'll get an answer.

Darren.

 

[EMP]

Have a look at this thread:

If then EOF Parameter Query
http://www.access-programmers.co.uk/forums/showthread.php?t=96104

~

 

[Darrenc]

I tried the solution in the other post, and that didn't work either, so i looked again on MSDN website, and found

Article ID : 209203

Which worked for me.

I think my mistake was i took the parameter out of the query thinking it was being replaced with the code, but it seems that you need both, which in hind site makes sense.