Re: Error Message: Two few parameters, Expected 2

From: Jack (Jack_at_discussions.microsoft.com)
Date: 10/06/04 

Date: Wed, 6 Oct 2004 15:17:03 -0700

Thanks Allen,
I appreciate it. It helped me a lot to solve the problem. However, for some
reasons the records are not saving properly to the database. I am posting a
new thread for this in the 'Programming Newsgroup'. Once again, thank you.

"Allen Browne" wrote:

> In the context of opening a recordset, the Expression Service is not
> available to resolve the references to the controls on the form. Concatenate
> the values into the string, like this:
> sql = sql & "(tblEGADPrevEducation.SubgrantIntID = " & _
> [Forms]![frmPreventionEducation1]![SubGrantSelect] & _
> ") AND (tblEGADPrevEducation.Quarter = " & _
> [Forms]![frmPreventionEducation1]![QuarterSelect] & ")"
>
>
> Note: If SubGrantIntID is a Text type field (not a Number type field), you
> need extra quotes:
> sql = sql & "(tblEGADPrevEducation.SubgrantIntID = """ & _
> [Forms]![frmPreventionEducation1]![SubGrantSelect] & _
> """) AND ...
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jack" <Jack@discussions.microsoft.com> wrote in message
> news:CE591A36-2ED1-4873-91D0-90EC4F34D075@microsoft.com...
> > Hi,
> > I have a form where a subgrant has detailed record for a particular year.
> > The way it is designed is first for a subgrant a year is chosen which
> > creates
> > the initial record. Then the detials are added to this record. There is a
> > validation check to make sure the sum in each group must be equal before
> > allowing to save the record.
> >
> > I have created the 'Save' button code as following. The validation check
> > works well but for some reasons the record is not saving with the above
> > error. Here the sql statement is referencing the form for the susgrant
> > number
> > and the particular quarter. Any help is appreciated. Thanks in advance.
> > THE CODE:
> >
> >
> > Dim Sum_Age_Group1 As Integer
> > Dim Sum_Popul_Group1 As Integer
> > Dim Sum_Race_Group1 As Integer
> > Sum_Age_Group1 = Nz([age00_04], 0) + Nz([age05_09], 0) + Nz([age10_12],
> > 0)
> > + Nz([age13_15], 0) + Nz([age16_18], 0) + Nz([age19_99], 0)
> > Sum_Popul_Group1 = Nz([popLawEnforce], 0) + Nz([popOtherCommMemb], 0) +
> > Nz([popSchoolPersonnel], 0) + Nz([popParents], 0) + Nz([popInSchool], 0) +
> > Nz([popNotInSchool], 0)
> > Sum_Race_Group1 = Nz([regAsian], 0) + Nz([regBlack], 0) +
> > Nz([regHispanic], 0) + Nz([regNatAmer], 0) + Nz([regWhite], 0)
> > If Not ((Sum_Age_Group1 = Sum_Popul_Group1) And (Sum_Popul_Group1 =
> > Sum_Race_Group1)) Then
> > MsgBox "You must have same total for each of the Age, Population and
> > Racial Group."
> > Else
> > Dim db As Database
> > Dim rst As Recordset
> > Dim sql As String
> > Set db = CurrentDb
> >
> > sql = "SELECT tblEGADPrevEducation.IntID,
> > tblEGADPrevEducation.SubgrantIntID, tblEGADPrevEducation.SubgrantNumber,"
> > sql = sql & "tblEGADPrevEducation.Quarter,
> > tblEGADPrevEducation.SubmitDate,"
> > sql = sql & "tblEGADPrevEducation.TotalRecipients,
> > tblEGADPrevEducation.age00_04,"
> > sql = sql & "tblEGADPrevEducation.age05_09,
> > tblEGADPrevEducation.age10_12, tblEGADPrevEducation.age13_15,
> > tblEGADPrevEducation.age16_18,"
> > sql = sql & "tblEGADPrevEducation.age19_99,
> > tblEGADPrevEducation.SumCountByAge, tblEGADPrevEducation.popLawEnforce,
> > tblEGADPrevEducation.popOtherCommMemb,"
> > sql = sql & "tblEGADPrevEducation.popSchoolPersonnel,
> > tblEGADPrevEducation.popParents, tblEGADPrevEducation.popInSchool,"
> > sql = sql & "tblEGADPrevEducation.popNotInSchool,
> > tblEGADPrevEducation.SumCountByPopul, tblEGADPrevEducation.regAsian,"
> > sql = sql & "tblEGADPrevEducation.regBlack,
> > tblEGADPrevEducation.regHispanic, tblEGADPrevEducation.regNatAmer,
> > tblEGADPrevEducation.regWhite,"
> > sql = sql & " tblEGADPrevEducation.SumCountByRace ,
> > tblEGADPrevEducation.CreatedDate "
> > sql = sql & "FROM tblEGADPrevEducation where "
> > sql = sql & "tblEGADPrevEducation.SubgrantIntID =
> > [Forms]![frmPreventionEducation1]![SubGrantSelect] "
> > sql = sql & "AND tblEGADPrevEducation.Quarter =
> > [Forms]![frmPreventionEducation1]![QuarterSelect]"
> > Debug.Print sql
> > Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
> > With rst
> > .Edit
> > !SumCountByAge = Sum_Age_Group1
> > !SumCountByPopul = Sum_Popul_Group1
> > !SumCountByRace = Sum_Race_Group1
> > !age00_04 = [Forms]![frmPreventionEducation1]![age00_04]
> > !age05_09 = [Forms]![frmPreventionEducation1]![age05_09]
> > !age10_12 = [Forms]![frmPreventionEducation1]![age10_12]
> > !age13_15 = [Forms]![frmPreventionEducation1]![age13_15]
> > !age16_18 = [Forms]![frmPreventionEducation1]![age16_18]
> > !age19_99 = [Forms]![frmPreventionEducation1]![age19_99]
> > !popLawEnforce = [Forms]![frmPreventionEducation1]![popLawEnforce]
> > !popOtherCommMemb =
> > [Forms]![frmPreventionEducation1]![popOtherCommMemb]
> > !popSchoolPersonnel =
> > [Forms]![frmPreventionEducation1]![popSchoolPersonnel]
> > !popParents = [Forms]![frmPreventionEducation1]![popParents]
> > !popInSchool = [Forms]![frmPreventionEducation1]![popInSchool]
> > !popNotInSchool = [Forms]![frmPreventionEducation1]![popNotInSchool]
> > !regAsian = [Forms]![frmPreventionEducation1]![regAsian]
> > !regBlack = [Forms]![frmPreventionEducation1]![regBlack]
> > !regHispanic = [Forms]![frmPreventionEducation1]![regHispanic]
> > !regNatAmer = [Forms]![frmPreventionEducation1]![regNatAmer]
> > !regWhite = [Forms]![frmPreventionEducation1]![regWhite]
> > .Update
> > End With
>
>
>