Update many fields of the CURRENT record of a subform

  • Thread starter Thread starter Céline Brien
  • Start date Start date
C

Céline Brien

Hi everybody,

I would like to update many fields of the CURRENT record of a subform.

I create a query to update these fields and a macro to execute the query.

Then, in the form containing the subform, I created a button to execute the
macro.

The codes are below.

If I execute this macro, it will update ALL the records of the subform.

I would like to update only the CURRENT record.

What codes should I add to the codes below ???

The field in the subform containing the automatic number is NoAuto

Many thanks for your help,

Céline

--------------------------------------------------------------------------------------------------Private Sub AncienneteFaux_Click()On Error GoTo Err_AncienneteFaux_Click Dim stDocName As String stDocName = "AncienneteFauxFeuilleDeTemps" DoCmd.RunMacro stDocNameExit_AncienneteFaux_Click: Exit SubErr_AncienneteFaux_Click: MsgBox Err.Description Resume Exit_AncienneteFaux_ClickEnd Sub
 
Hi Celine,

In order to update just the current record using a query you'll have to
apply criteria to that query that restrict it's effect to just the current
record.

You might be better off with one of the different options: You could create
an SQL command and execute it entirely in code, you could use assignments
like field1="value1" etc... looking up the values through Dlookups if
there's only a few, or using a recordset if there's a lot.

If you're interested in this solution give me a shout - but the alternative
would be to change your stored query.

Include the Primary Key field of your table in the query, and apply a
criteria that is must match the value of the primary key on the form. This
would look something like:

[forms]![myform]!ID

In the Critera row underneath the field in the query builder. Post the SQL
if this makes no sense and I'll see if I can't adapt it to include this
field.

Small warning: This will work fine as long as the Query is not used
elsewhere when the form is not open.

The only other bit I would recommend is to make sure you requery or refresh
the form after running the query to be sure of seeing the results!

Really hope this helps - I think your code got a but garbled in the post so
if you're still stuck repost and I'd be glad to try and help you out.

Ben.
 
Hi Ben,
Hi everybody,
Thank you so much for your answer.
I will come back soon with a follow-up.
Have a good day,
Céline

Ben said:
Hi Celine,

In order to update just the current record using a query you'll have to
apply criteria to that query that restrict it's effect to just the current
record.

You might be better off with one of the different options: You could
create an SQL command and execute it entirely in code, you could use
assignments like field1="value1" etc... looking up the values through
Dlookups if there's only a few, or using a recordset if there's a lot.

If you're interested in this solution give me a shout - but the
alternative would be to change your stored query.

Include the Primary Key field of your table in the query, and apply a
criteria that is must match the value of the primary key on the form.
This would look something like:

[forms]![myform]!ID

In the Critera row underneath the field in the query builder. Post the
SQL if this makes no sense and I'll see if I can't adapt it to include
this field.

Small warning: This will work fine as long as the Query is not used
elsewhere when the form is not open.

The only other bit I would recommend is to make sure you requery or
refresh the form after running the query to be sure of seeing the results!

Really hope this helps - I think your code got a but garbled in the post
so if you're still stuck repost and I'd be glad to try and help you out.

Ben.

Céline Brien said:
Hi everybody,

I would like to update many fields of the CURRENT record of a subform.

I create a query to update these fields and a macro to execute the query.

Then, in the form containing the subform, I created a button to execute
the macro.

The codes are below.

If I execute this macro, it will update ALL the records of the subform.

I would like to update only the CURRENT record.

What codes should I add to the codes below ???

The field in the subform containing the automatic number is NoAuto

Many thanks for your help,

Céline

--------------------------------------------------------------------------------------------------Private
Sub AncienneteFaux_Click()On Error GoTo Err_AncienneteFaux_Click Dim
stDocName As String stDocName = "AncienneteFauxFeuilleDeTemps"
DoCmd.RunMacro stDocNameExit_AncienneteFaux_Click: Exit
SubErr_AncienneteFaux_Click: MsgBox Err.Description Resume
Exit_AncienneteFaux_ClickEnd Sub
Click to expand...
Click to expand...
 
Hi everybody,
Hi Ben,
The SQL codes are below.
When I execute the Query with a command button in the form, I obtain a
dialog box : Enter the parameters...
Many thanks for your help !
Céline

Form : FEmploye
Subform : SousFormulaireFeuilleDeTemps
Primary key : NoAuto
UPDATE FeuilleDeTemps SET FeuilleDeTemps.SiDim1 = 0, FeuilleDeTemps.SiLun1 =
0, FeuilleDeTemps.SiMar1 = 0, FeuilleDeTemps.SiMer1 = 0,
FeuilleDeTemps.SiJeu1 = 0, FeuilleDeTemps.SiVen1 = 0, FeuilleDeTemps.SiSam1
= 0, FeuilleDeTemps.SiDim2 = 0, FeuilleDeTemps.SiLun2 = 0,
FeuilleDeTemps.SiMar2 = 0, FeuilleDeTemps.SiMer2 = 0, FeuilleDeTemps.SiJeu2
= 0, FeuilleDeTemps.SiVen2 = 0, FeuilleDeTemps.SiSam2 = 0
WHERE
((([FeuilleDeTemps].[NoAuto])=([Forms]![SousFormulaireFeuilleDeTemps].NoAuto)));

Ben said:
Hi Celine,

In order to update just the current record using a query you'll have to
apply criteria to that query that restrict it's effect to just the current
record.

You might be better off with one of the different options: You could
create an SQL command and execute it entirely in code, you could use
assignments like field1="value1" etc... looking up the values through
Dlookups if there's only a few, or using a recordset if there's a lot.

If you're interested in this solution give me a shout - but the
alternative would be to change your stored query.

Include the Primary Key field of your table in the query, and apply a
criteria that is must match the value of the primary key on the form.
This would look something like:

[forms]![myform]!ID

In the Critera row underneath the field in the query builder. Post the
SQL if this makes no sense and I'll see if I can't adapt it to include
this field.

Small warning: This will work fine as long as the Query is not used
elsewhere when the form is not open.

The only other bit I would recommend is to make sure you requery or
refresh the form after running the query to be sure of seeing the results!

Really hope this helps - I think your code got a but garbled in the post
so if you're still stuck repost and I'd be glad to try and help you out.

Ben.

Céline Brien said:
Hi everybody,

I would like to update many fields of the CURRENT record of a subform.

I create a query to update these fields and a macro to execute the query.

Then, in the form containing the subform, I created a button to execute
the macro.

The codes are below.

If I execute this macro, it will update ALL the records of the subform.

I would like to update only the CURRENT record.

What codes should I add to the codes below ???

The field in the subform containing the automatic number is NoAuto

Many thanks for your help,

Céline

--------------------------------------------------------------------------------------------------Private
Sub AncienneteFaux_Click()On Error GoTo Err_AncienneteFaux_Click Dim
stDocName As String stDocName = "AncienneteFauxFeuilleDeTemps"
DoCmd.RunMacro stDocNameExit_AncienneteFaux_Click: Exit
SubErr_AncienneteFaux_Click: MsgBox Err.Description Resume
Exit_AncienneteFaux_ClickEnd Sub
Click to expand...
Click to expand...
 
With your help and a lot of tests, it is working !
Thanks again for your help.
Céline

Céline Brien said:
Hi everybody,
Hi Ben,
The SQL codes are below.
When I execute the Query with a command button in the form, I obtain a
dialog box : Enter the parameters...
Many thanks for your help !
Céline

Form : FEmploye
Subform : SousFormulaireFeuilleDeTemps
Primary key : NoAuto
UPDATE FeuilleDeTemps SET FeuilleDeTemps.SiDim1 = 0, FeuilleDeTemps.SiLun1
= 0, FeuilleDeTemps.SiMar1 = 0, FeuilleDeTemps.SiMer1 = 0,
FeuilleDeTemps.SiJeu1 = 0, FeuilleDeTemps.SiVen1 = 0,
FeuilleDeTemps.SiSam1 = 0, FeuilleDeTemps.SiDim2 = 0,
FeuilleDeTemps.SiLun2 = 0, FeuilleDeTemps.SiMar2 = 0,
FeuilleDeTemps.SiMer2 = 0, FeuilleDeTemps.SiJeu2 = 0,
FeuilleDeTemps.SiVen2 = 0, FeuilleDeTemps.SiSam2 = 0
WHERE
((([FeuilleDeTemps].[NoAuto])=([Forms]![SousFormulaireFeuilleDeTemps].NoAuto)));

Ben said:
Hi Celine,

In order to update just the current record using a query you'll have to
apply criteria to that query that restrict it's effect to just the
current record.

You might be better off with one of the different options: You could
create an SQL command and execute it entirely in code, you could use
assignments like field1="value1" etc... looking up the values through
Dlookups if there's only a few, or using a recordset if there's a lot.

If you're interested in this solution give me a shout - but the
alternative would be to change your stored query.

Include the Primary Key field of your table in the query, and apply a
criteria that is must match the value of the primary key on the form.
This would look something like:

[forms]![myform]!ID

In the Critera row underneath the field in the query builder. Post the
SQL if this makes no sense and I'll see if I can't adapt it to include
this field.

Small warning: This will work fine as long as the Query is not used
elsewhere when the form is not open.

The only other bit I would recommend is to make sure you requery or
refresh the form after running the query to be sure of seeing the
results!

Really hope this helps - I think your code got a but garbled in the post
so if you're still stuck repost and I'd be glad to try and help you out.

Ben.

Céline Brien said:
Hi everybody,

I would like to update many fields of the CURRENT record of a subform.

I create a query to update these fields and a macro to execute the
query.

Then, in the form containing the subform, I created a button to execute
the macro.

The codes are below.

If I execute this macro, it will update ALL the records of the subform.

I would like to update only the CURRENT record.

What codes should I add to the codes below ???

The field in the subform containing the automatic number is NoAuto

Many thanks for your help,

Céline

--------------------------------------------------------------------------------------------------Private
Sub AncienneteFaux_Click()On Error GoTo Err_AncienneteFaux_Click Dim
stDocName As String stDocName = "AncienneteFauxFeuilleDeTemps"
DoCmd.RunMacro stDocNameExit_AncienneteFaux_Click: Exit
SubErr_AncienneteFaux_Click: MsgBox Err.Description Resume
Exit_AncienneteFaux_ClickEnd Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top