Help with button to prompt on form (1 Viewer)

[lookforsmt]

HI! all, I have form with button on click event with below code

Private Sub Command95_Click()
  On Err GoTo Park
    
    Dim rec As Recordset
    Dim qdef As DAO.QueryDef
    Dim IncidentId As Integer
    
    Set qdef = CurrentDb.QueryDefs("qry_Voucher")
    qdef.Parameters("Entry Id").Value = IncidentId

    qdef.Execute
    MsgBox qdef.RecordsAffected & " - " & IncidentId & " " & " added"
    Set qdef = Nothing
    Set rec = qdef.OpenRecordset
Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

        If Me.Dirty Then
            Me.Dirty = False ' save the record
        End If
End Sub

After i click the button, it should prompt the user to input the "IncidentId" which is a number field.
I am getting an error "Run-time error 3265, "Item Not found in this collection"

can anyone help with the correct code
Thanks

 

[theDBguy]

Hi. Can you post the SQL statement for your query? Thanks!

 

[Isaac]

HI! all, I have form with button on click event with below code

Private Sub Command95_Click()
  On Err GoTo Park
   
    Dim rec As Recordset
    Dim qdef As DAO.QueryDef
    Dim IncidentId As Integer
   
    Set qdef = CurrentDb.QueryDefs("qry_Voucher")
    qdef.Parameters("Entry Id").Value = IncidentId

    qdef.Execute
    MsgBox qdef.RecordsAffected & " - " & IncidentId & " " & " added"
    Set qdef = Nothing
    Set rec = qdef.OpenRecordset
Park:
    If Err.Number > 0 Then
        MsgBox Err.Number & " " & Err.Description
    End If

        If Me.Dirty Then
            Me.Dirty = False ' save the record
        End If
End Sub

After i click the button, it should prompt the user to input the "IncidentId" which is a number field.
I am getting an error "Run-time error 3265, "Item Not found in this collection"

can anyone help with the correct code
Thanks

It seems like maybe?
- There is no saved, query object named "qry_Voucher", or,
- That saved query object has no actual parameter named "Entry Id"

 

[The_Doc_Man]

On which line do you get the error? That information would go a long way in helping us to identify the deficient collection.

 

[lookforsmt]

Thank you all for the response and applogies for the delay due to the time difference.

Below is my SQL Statement. The query is saved by query_Voucher and there is parameter called Entry Id also.

SELECT tbl_Voucher.IncidentId, tbl_Voucher.ORG, tbl_Voucher.VoucherDate, tbl_Voucher.DR2,
tbl_Voucher.DR3, tbl_Voucher.DR4, tbl_Voucher.CR1, tbl_Voucher.CR2, tbl_Voucher.CR3,
tbl_Voucher.CR4, tbl_Voucher.RD1, tbl_Voucher.RD2, tbl_Voucher.RD3, tbl_Voucher.RD4,
tbl_Voucher.RC1, tbl_Voucher.RC2, tbl_Voucher.RC3, tbl_Voucher.RC4,
tbl_Voucher.PreparedBy
FROM tbl_Voucher
WHERE (((tbl_Voucher.IncidentId)=[Entry Id]));

The error comes on line: qdef.Parameters("Entry Id").Value = IncidentId

thanks

 

[arnelgp]

use Inputbox:

Private Sub Command95_Click()
  On Err GoTo Park
  
    Dim rec As Recordset
    Dim qdef As DAO.QueryDef
    Dim IncidentId As Integer
  
    'arnelgp
    IncidentId = Val("0" & Inputbox("Enter Incident ID:", "Incident"))
  
    Set qdef = CurrentDb.QueryDefs("qry_Voucher")
    qdef.Parameters("Entry Id").Value = IncidentId
  
    ...
    ...

 

[lookforsmt]

Thanks arnelgp, i am getting an error on below line, so i excluded and it works fine.
"qdef.Parameters("Entry Id").Value = IncidentId"

just wondering, if there is way to change the msg box

 

[arnelgp]

what is the datatype of IncidentID. Long?

Private Sub Command95_Click()
  On Err GoTo Park
 
    Dim rec As Recordset
    Dim qdef As DAO.QueryDef
    Dim IncidentId As Long
    Dim db As DAO.Database
    'arnelgp
    IncidentId = Val("0" & Inputbox("Enter Incident ID:", "Incident"))
    Set db =Currentdb
    Set qdef = Db.QueryDefs("qry_Voucher")
    qdef.Parameters(0).Value = IncidentId
 
    ...
    ...

 

[lookforsmt]

Thanks arnelgp, IncidentId is long but i am still getting previous similar msgbox.
and sorry as mentioned in my post#7 i need to include the line but i corrected the parameter. You provided the code correctly.

 

[lookforsmt]

Sir, one more request, if click Private Sub Command95_Click() instead of msgbox can it copy the current record on the form (IIncidentd)

 

[arnelgp]

just assign it to the parameter:

qdef.Parameters(0).Value = Nz(Me!IncidentID, 0)

 

[Isaac]

Thanks arnelgp, i am getting an error on below line, so i excluded and it works fine.
"qdef.Parameters("Entry Id").Value = IncidentId"

just wondering, if there is way to change the msg box

Just to clarify, going back to earlier posts where you were told it was from miss-naming one of the items in the collection like the parameter name. the reason arnell's code works for you is because he used parameters (0) instead of the parameter name.. if you had corrected the parameter name it would work.