Access 2003 VBA error when executing INSERT statement - "Too few Parameters. Expected 1".
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.
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.
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!Clai
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
strSQL = "INSERT Into tblClaims_Notes (ClaimAutoID) VALUES (Forms!Parentformname!Clai
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
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_Cla
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.
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_Cla
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.
ASKER
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
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
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.
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
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try this...
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!