Update Table Records, Almost There

  • Thread starter Thread starter mj
  • Start date Start date
M

mj

Hi. I have a form with a button that executes the code
below. What it's doing is looping through one table,
tblPlugsTerritory, going to the record in tblPlans where
some of it's field values are equal to
tblPlugsTerritories, and using the value in
tblPlugsTerritories to Update the field called "Plug" in
tblPlans. I think I'm almost there but I continue to get
an error that says "Runtime 3061 Too Few Parameters
Expected 4." Do I have to define tblPlans fields? How
would I do that? Any thoughts would be great! Thanks!


Private Sub UpdatePlugs_Click()

Dim db As Database
Dim rsPlans As Recordset
Dim rsPlugs As Recordset

Set db = CurrentDb

Set rsPlugs = db.OpenRecordset("SELECT * FROM
[tblPlugsTerritory]")

Do While Not rsPlugs.EOF

Set rsPlans = db.OpenRecordset("SELECT * " & _
"FROM [tblPlans] " & _
"WHERE [Territory]=rsPlugs![Territory] AND " &
_
"[MonthID]=rsPlugs![MonthID] AND " & _
"[Year]=rsPlugs![Year] AND " & _
"[ProductGroup]=rsPlugs![ProductGroup]")

rsPlans.Edit

rsPlans(Plug) = rsPlugs(Plug)

rsPlans.Update
rsPlugs.MoveNext

Loop

End Sub
 
By the way, tblPlugsTerritory has only about 12 rows right
now and it looks like this:

Territory Plug QuarterID MonthID Year ProductGroup
PORTLAND 12222.22 2 5 2004 Other

tblPlans has thousands of rows and looks like this:

Territory Plug QuarterID MonthID Year ProductGroup
PORTLAND 0 2 5 2004 Other
 
i think the problem may be your syntax in the SQL statement. try this:

Set rsPlans = db.OpenRecordset("SELECT * FROM tblPlans " _
& "WHERE Territory = " & rsPlugs!Territory & " AND MonthID = " _
& rsPlugs!MonthID & " AND Year = " & rsPlugs!Year & " AND " _
& "ProductGroup = " & rsPlugs!ProductGroup)

if any value is text rather than a number, add single quotes inside the
double quotes. example: Territory and ProductGroup are text, Month and Year
are numeric:

Set rsPlans = db.OpenRecordset("SELECT * FROM tblPlans " _
& "WHERE Territory = '" & rsPlugs!Territory & "' AND MonthID = " _
& rsPlugs!MonthID & " AND Year = " & rsPlugs!Year & " AND " _
& "ProductGroup = '" & rsPlugs!ProductGroup & "'")

hth
 
tina, you nailed it!! THANK YOU!!!
-----Original Message-----
i think the problem may be your syntax in the SQL statement. try this:

Set rsPlans = db.OpenRecordset("SELECT * FROM tblPlans " _
& "WHERE Territory = " & rsPlugs!Territory & " AND MonthID = " _
& rsPlugs!MonthID & " AND Year = " & rsPlugs!Year & " AND " _
& "ProductGroup = " & rsPlugs!ProductGroup)

if any value is text rather than a number, add single quotes inside the
double quotes. example: Territory and ProductGroup are text, Month and Year
are numeric:

Set rsPlans = db.OpenRecordset("SELECT * FROM tblPlans " _
& "WHERE Territory = '" & rsPlugs!Territory & "' AND MonthID = " _
& rsPlugs!MonthID & " AND Year = " & rsPlugs!Year & " AND " _
& "ProductGroup = '" & rsPlugs!ProductGroup & "'")

hth


Hi. I have a form with a button that executes the code
below. What it's doing is looping through one table,
tblPlugsTerritory, going to the record in tblPlans where
some of it's field values are equal to
tblPlugsTerritories, and using the value in
tblPlugsTerritories to Update the field called "Plug" in
tblPlans. I think I'm almost there but I continue to get
an error that says "Runtime 3061 Too Few Parameters
Expected 4." Do I have to define tblPlans fields? How
would I do that? Any thoughts would be great! Thanks!


Private Sub UpdatePlugs_Click()

Dim db As Database
Dim rsPlans As Recordset
Dim rsPlugs As Recordset

Set db = CurrentDb

Set rsPlugs = db.OpenRecordset("SELECT * FROM
[tblPlugsTerritory]")

Do While Not rsPlugs.EOF

Set rsPlans = db.OpenRecordset("SELECT * " & _
"FROM [tblPlans] " & _
"WHERE [Territory]=rsPlugs![Territory] AND " &
_
"[MonthID]=rsPlugs![MonthID] AND " & _
"[Year]=rsPlugs![Year] AND " & _
"[ProductGroup]=rsPlugs![ProductGroup]")

rsPlans.Edit

rsPlans(Plug) = rsPlugs(Plug)

rsPlans.Update
rsPlugs.MoveNext

Loop

End Sub
Click to expand...


.
Click to expand...
 
By the way, tblPlugsTerritory has only about 12 rows right
now and it looks like this:

Territory Plug QuarterID MonthID Year ProductGroup
PORTLAND 12222.22 2 5 2004 Other

tblPlans has thousands of rows and looks like this:

Territory Plug QuarterID MonthID Year ProductGroup
PORTLAND 0 2 5 2004 Other
Click to expand...

You don't need any VBA code to do this at all: a simple Update query
will do the job.

UPDATE tblPlans
INNER JOIN tblPlugsTerritory
ON tblPlans.Territory = tblPlugsTerritory.Territory
AND tblPlans.QuarterID = tblPlugsTerritory.QuarterID
AND tblPlans.MonthID = tblPlugsTerritory.MonthID
AND tblPlans.[Year] = tblPlugsTerritory.[Year]
SET tblPlans.Plug = tblPlugsTerritory.Plug;

This will work if you have a unique Index on the combination of fields
used to join the tables (if you need to join on ProductGroup as well,
just add it to the JOIN clause).
 
Back
Top