We help IT Professionals succeed at work.

Access 2003 VBA error when executing INSERT statement - "Too few Parameters. Expected 1".

458 Views
Last Modified: 2013-03-10
Hello,

In an Access 2003 database, I've created a button on a form that opens a subform in a popup window, linked to the parent form via the ClaimAutoID field (the underlying tables for the form and the subform are also linked via this field).  The code for this is as follows:

Private Sub AddNewNoteCmd_Click()
On Error GoTo Err_AddNewNoteCmd_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Add New Note"
    stLinkCriteria = "[ClaimAutoID] = " & Me.ClaimAutoID
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec

Exit_AddNewNoteCmd_Click:
    Exit Sub

Err_AddNewNoteCmd_Click:
    MsgBox Err.Description
    Resume Exit_AddNewNoteCmd_Click
   
End Sub

The code that links the popped up subform to the parent form via the ClaimAutoID works fine, in that the records you see if you scroll through entries in the subform are only the records that are related to the current record on the parent form.  However, when you save a new record on this subform, it gets saved without any data in the ClaimAutoID field, so the new record that get saved are no longer linked to the current record in the parent form.  To deal with this I've created a "Save" button on the subform which is supposed to insert the value in the ClaimAutoID field from the current record on the parent form into the ClaimAutoID field for the current record being saved in the subform.  The ClaimAutoID field is not visible in the subform.  Here is the code:

Private Sub SaveNote_Click()
On Error GoTo Err_SaveNote_Click

    Dim strSQL As String

    strSQL = "INSERT Into tblClaims_Notes (ClaimAutoID) VALUES (Me.ClaimAutoID)"
   
    CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_SaveNote_Click:
    Exit Sub

Err_SaveNote_Click:
    MsgBox Err.Description
    Resume Exit_SaveNote_Click
   
End Sub


When I click on this save button, I get the error "Too few parameters. Expected 1".  Can someone please tell me what I'm doing wrong?  Any help is greatly appreciated.

Thank you.
Comment
Watch Question

bluelangroup

try this...
strSQL = "INSERT Into tblClaims_Notes (ClaimAutoID) VALUES (" & Me.ClaimAutoID & ")"

Open in new window


I think you just forgot that you wanted to include the value of Me.ClaimAutoID in the string, not the name of the field "Me.ClaimAutoID" itself.


Good luck!
CERTIFIED EXPERT

Commented:
The Me.ClaimAutoID is on the the parent form. When you use it on the subform it cannot e found. You must point i to the main form control.  you need something like

strSQL = "INSERT Into tblClaims_Notes (ClaimAutoID) VALUES (Forms!Parentformname!ClaimAutoID)"

I'm not sure if you solving tohe problem though. Is this a true subform, or just a popup, populated from the main form. If the latter,I'd pass the ClaimAutoID as an open arg to the popup and have a non visible field for that field and populate it from the open arg if you create a record to save.

Kelvin
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
as Kelvin mentions, this will work differently on a subform that is linked to the parent via a master/child relationship and a popup form based upon the ClaimAutoID field in the main form.

If you are actually using a popup form, what I would do is modify the Current event of that form, so that it automatically fills in the ClaimAutoId if you are on a new record.  It would look like:

Private Sub Form_Current()

    IF me.NewRecord Then

        me.txt_ClaimAutoID = Forms!MainFormName.txt_ClaimAutoID

    End IF

End Sub

This way, any time you enter a new record on the popup form, it will automatically fill in the ClaimAutoId for that record.

Author

Commented:
Thanks so much for the input.

First I tried a combination of the first two methods (calling the ClaimAutoID value from the parent form as posted by kelvinsparks, withthe syntax as posted by aebea).  The new code for the Save button is as follows:

Private Sub SaveNote_Click()
On Error GoTo Err_SaveNote_Click

    Dim strSQL As String

    strSQL = "INSERT Into tblClaims_Notes (ClaimAutoID) VALUES (" & Forms!frmClaims_Management!ClaimAutoID & ")"
   
    CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_SaveNote_Click:
    Exit Sub

Err_SaveNote_Click:
    MsgBox Err.Description
    Resume Exit_SaveNote_Click
   
End Sub


Now what happens is it saves the current record with the ClaimAutoID field blank, then it creates a new record with the correct ClaimID and nothing else.  So I get two records, one with only the ClaimAutoID and the other one with everything else but no ClaimAutoID.  The result is the same even if I reverse the "CurrentDB" and "DoCmd" commands.

I tried the code fyed provided to modify the Current Event for the subform so it says the following:

Private Sub Form_Current()

    If Me.NewRecord Then

        Me.txt_ClaimAutoID = Forms!frmClaims_Management.txt_ClaimAutoID

    End If

End Sub

When I open the subform with this code I get "Compile error: Method or data member not found" and the debugger opens with ".txt_ClaimAutoID =" highlighted.

Thanks again, I think we're almost there.
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.