Update multiple fields in table from form

  • Thread starter Thread starter deb
  • Start date Start date
D

deb

access 2003

optCTorSTorGEN is an option group on the main form fWarrantyUnit.
If there is a change in the selection of the opt group, I would like 3
fields in the subforms table, set to Null where WarrantyUnitID =
strWarrantyUnitID

the subform is fWarrantyDetails and is a continuous form so the fields will
need to be set for each recordset where WarrantyUnitID = strWarrantyUnitID

The fields are optHPorIPorLP, BB1 and BB2 and are all Numeric.

Private Sub optCTorSTorGEN_AfterUpdate()
strWarrantyUnitID = WarrantyUnitID.Value

CurrentDb.Execute "UPDATE [t72WarrantyDetails] SET [optHPorIPorLP] = " &
Null
and [BB1] =Null and [BB2]=Null & " WHERE
[t72WarrantyDetails].WarrantyUnitID = " & strWarrantyUnitID

Thank you in advance for helping me learn!
 
Deb -

The SQL to update the detail records is this:

CurrentDb.Execute "UPDATE [t72WarrantyDetails] " & _
" SET [optHPorIPorLP] = Null, [BB1] = Null, [BB2]=Null " & _
" WHERE [t72WarrantyDetails].WarrantyUnitID = " & Me.strWarrantyUnitID & ";"

After you get this working, you need to refresh the subform data so the
records you updated in the database will now show in the subform (use your
subformname):
Me.[subformname].Form.Refresh
 
I am getting error - Too few parameters, at least 1 expected


--
deb


Daryl S said:
Deb -

The SQL to update the detail records is this:

CurrentDb.Execute "UPDATE [t72WarrantyDetails] " & _
" SET [optHPorIPorLP] = Null, [BB1] = Null, [BB2]=Null " & _
" WHERE [t72WarrantyDetails].WarrantyUnitID = " & Me.strWarrantyUnitID & ";"

After you get this working, you need to refresh the subform data so the
records you updated in the database will now show in the subform (use your
subformname):
Me.[subformname].Form.Refresh

--
Daryl S


deb said:
access 2003

optCTorSTorGEN is an option group on the main form fWarrantyUnit.
If there is a change in the selection of the opt group, I would like 3
fields in the subforms table, set to Null where WarrantyUnitID =
strWarrantyUnitID

the subform is fWarrantyDetails and is a continuous form so the fields will
need to be set for each recordset where WarrantyUnitID = strWarrantyUnitID

The fields are optHPorIPorLP, BB1 and BB2 and are all Numeric.

Private Sub optCTorSTorGEN_AfterUpdate()
strWarrantyUnitID = WarrantyUnitID.Value

CurrentDb.Execute "UPDATE [t72WarrantyDetails] SET [optHPorIPorLP] = " &
Null
and [BB1] =Null and [BB2]=Null & " WHERE
[t72WarrantyDetails].WarrantyUnitID = " & strWarrantyUnitID

Thank you in advance for helping me learn!
Click to expand...
Click to expand...
 
I had the option group name instead of the field name.
My mistake. found it when using Ctrl G....

Thank you all!!

--
deb


BruceM via AccessMonster.com said:
What is the question? The SQL string is incorrect, so if you are saying the
update is not happening, that is at least part of the problem.

You should add Option Explicit to the top of the code module, just below
Option Compare Database, then click Compile on the Debug menu. In the VBA
Editor, click Tools >> Options, click the Editor tab, and check Require
Variable Declaration. If you do not declare variables, undeclared variables
such as strWarrantyUnitID default to variables of variant type. Aside from a
variant not necessarily being a type that will work, anything the VBA editor
doesn't recognize tends to be treated as a variant, even if it is really a
typo.

I find it helps to build a string outside of the expression in which it will
be used. This allows it to be tested more easily:

Dim strSQL As String
Dim lngWarrantyUnitID As Long
Dim db As DAO.Database

strWarrantyUnitID = Me.WarrantyUnitID
Set db = DBEngine(0)(0)
strSQL = "UPDATE [t72WarrantyDetails] " & _
"SET [optHPorIPorLP] = Null " & _
"And [BB1] =Null " & _
"And [BB2]=Null " & _
"WHERE [t72WarrantyDetails].WarrantyUnitID = " &
lngWarrantyUnitID
Debug.Print strSQL
db.Execute strSQL, dbFailOnError

Debug.Print is for troubleshooting. After running the code, press Ctrl + G
to open the immediate code window, where you will see the SQL string printed.
dbFailOnError rolls back the update if there are errors. It could be that
CurrentDB.Execute will work also, but I have used the above to good effect,
so I have posted it.

In the string I posted, the line breaks are for convenience, and do not need
to be exactly as I have shown. Also, I am assuming WarrantyUnitID is a
number.

Having said all of that, I assume the idea is that the user has to go back in
and make selections for the fields you have rendered Null, and that there may
be different selections for different subform records. If they are always
the same for a given parent record the values should be in the parent record,
not the child records.

Seeing BB1 and BB2 is a bit worrisome. Is there, or could there ever be, a
BB3?
access 2003

optCTorSTorGEN is an option group on the main form fWarrantyUnit.
If there is a change in the selection of the opt group, I would like 3
fields in the subforms table, set to Null where WarrantyUnitID =
strWarrantyUnitID

the subform is fWarrantyDetails and is a continuous form so the fields will
need to be set for each recordset where WarrantyUnitID = strWarrantyUnitID

The fields are optHPorIPorLP, BB1 and BB2 and are all Numeric.

Private Sub optCTorSTorGEN_AfterUpdate()
strWarrantyUnitID = WarrantyUnitID.Value

CurrentDb.Execute "UPDATE [t72WarrantyDetails] SET [optHPorIPorLP] = " &
Null
and [BB1] =Null and [BB2]=Null & " WHERE
[t72WarrantyDetails].WarrantyUnitID = " & strWarrantyUnitID

Thank you in advance for helping me learn!
Click to expand...
Click to expand...
 
Back
Top