Update many fields of the CURRENT record of a subform

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
 
B

Ben

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

Céline Brien

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

Céline Brien

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

Céline Brien

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem referencing from subform 4
Open Subform - GoToRecord = Current Record 3
Edited data record to be inserted as a new data record under the same PO number in Microsoft Access 0
Can't edit record in subform datasheet view. 1
Print Current Record From A Form 2
requery a combobox in a continuious form subform 1
record source on subform 4
Set record from subform to current record in Mainform 18

Top