Error Message: Two few parameters, Expected 2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Reply to group, rather than allenbrowne at mvps dot org.

Jack said:
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
Click to expand...
 
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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Jack said:
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
Click to expand...
Click to expand...
 
Hello Allen,
I thought of posting the 'Save' code here too. The problem is when I am
closing the form after saving it gives a write conflict error with a prompt
to save or discard the data. When I am clicking to yes, it saves the data.
However, the calculated fields do not get saved here. I have no clue why this
is happening.
THE CODE (after the modification as per your advise)
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."
' Cancel = True
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
End If

Jack said:
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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

Jack said:
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
Click to expand...
Click to expand...
Click to expand...
 
If you have a bound form, and the form is dirty (i.e. the user has started
editing something, or a value has been assigned programmatically), and you
then make a programmatic change to the same data in the table, when you go
to close the form, you will receive the conflict dialog.

It means:

You changed the data in the table, so what I have in the form does not match
any more. Do you want to keep the change you made programmatically? Or do
you want to lose that change, and save these changes in the form instead?

The message can also occur if there are memo fields involved in the
form/update query.
 
Thanks Allen for the reply. So, with the present scenario what resolution do
I have with my problem. Thanks.
 
The resolution is to use one or the other.

To retain the bound form, use the BeforeUpdate event of the form to assign
the values to the controls.

If you want to use the query to write the data, use an unbound form (not
recommended).
 
Thanks again for the confirmation. Regards

Allen Browne said:
The resolution is to use one or the other.

To retain the bound form, use the BeforeUpdate event of the form to assign
the values to the controls.

If you want to use the query to write the data, use an unbound form (not
recommended).
Click to expand...
 
Back
Top