Re: Error Message: Two few parameters, Expected 2

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
>
>
>

Relevant Pages

  • Re: Establish connection with and transferring data to Access
    ... If you Google for SQL +"Insert into" you'll a dozen tutorials. ... The specific problem with your code is that you need to quote string ... Dim vConnection As New ADODB.Connection ... Doug's code opens and writes to an existing database. ...
    (microsoft.public.word.vba.general)
  • Re: WHERE statement syntax
    ... which is what database you're using. ... SQL syntax for dates, but I'll just cover the gamut and you can decide based ... > Dim myConnString ... > myConnString = Application ...
    (microsoft.public.frontpage.programming)
  • Re: How to read data from stand alone SQL CE database on mobile device from VB.net 2005
    ... also use Rapi to delete the file on the device when you're finished with it. ... I have a stand alone SQL CE database on my windows mobile device. ... Dim conn As New SqlCeConnection("Data Source=\My ...
    (microsoft.public.sqlserver.ce)
  • Re: Inserting a concatenated string into a text field
    ... Dim db As Database ... Dim LSQL As String ... 'Create SQL to insert item numbers 1 to PregNum into table Pregnancy ...
    (microsoft.public.access.modulesdaovba)
  • Fixed
    ... I also found this article that gives the proper way to move system dbs in sql 2008: ... The model exists where the master states it exists. ... This is the error log prior to detaching the model database. ...
    (microsoft.public.sqlserver.server)