Updating Records with a Command Button

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

Guest

Please Help, this is driving me insane.

I have a command button "Btn_Update" and an unbound Field Selector drop down
select box "Week_Num"on a form "Frm_Splash"
..
What I am trying to do is, when the Command Button is clicked, the value "A"
is written into all the records (replacing the existing value) within table
"TblResultsX", but only into the field which is the same name that I have
selected in the Filed Selector "Week_Num".
There is a field for each week in the Table "TblResultsX", they are named
w1, w2, w3 ....... W52.

I would really appreciate help with this one, and thanks for any assistance
given.

Joe
 
Please Help, this is driving me insane.

I have a command button "Btn_Update" and an unbound Field Selector drop down
select box "Week_Num"on a form "Frm_Splash"
.
What I am trying to do is, when the Command Button is clicked, the value "A"
is written into all the records (replacing the existing value) within table
"TblResultsX", but only into the field which is the same name that I have
selected in the Filed Selector "Week_Num".
There is a field for each week in the Table "TblResultsX", they are named
w1, w2, w3 ....... W52.

I would really appreciate help with this one, and thanks for any assistance
given.

Joe
Click to expand...

Hi

you could use an update query to do this. Create a query that includes the
field you want to change and pulls up the records from that table when the
criteria for week_num is

forms!frm_splash!week_num

Change the query type to update query

For the value you want to change enter in the update to box

forms!frm_splash!ValueYouWantToUpdateTo

Then on the click event have it run this query, test on non live data first
to make sure it works the way you want or adjust accordingly
 
Hi

you could use an update query to do this. Create a query that includes the
field you want to change and pulls up the records from that table when the
criteria for week_num is

forms!frm_splash!week_num

Change the query type to update query

For the value you want to change enter in the update to box

forms!frm_splash!ValueYouWantToUpdateTo

Then on the click event have it run this query, test on non live data first
to make sure it works the way you want or adjust accordingly- Hide quoted text -

- Show quoted text -
Click to expand...

In keeping with the tradition of throwing cold water on a fire, IMHO,
having a field for every week of the year is usually a poor design.
If you have more than one year of data, it becomes confusing. The
same is true when you have less than one year of data. Commercial
Report Writers generally don't expect chronology to change with each
field. On top of that, it puts an enormous load on the programmer to
duplicate everything 52 times. Compare that to adding a single
WeekOfTheYear field.
 
Thanks for the reply Falty, However I have now solved the problem using VBA,
here is the code I used on the On Click Event of the command button, in case
anyone else is interested:-

Private Sub Btn_Update_Click()
On Error GoTo Err_Btn_Update_Click
If IsNull(Me.Week_Num) Then
MsgBox "Please Select a Week Number from the List"
Forms!FRMSPLASH!Week_Num.SetFocus
Else
If MsgBox("Are You Sure That You Want to Reset This Data ? THIS CANNOT BE_
UNDONE!!!!!!", vbYesNo + vbDefaultButton2) = vbYes Then
Dim DB As Database
Set DB = CurrentDb()
DB.Execute "UPDATE TblResults " & "SET " & Me.Week_Num & " = 'A' WHERE " _
& Me.Week_Num & " <> 'A'"
DB.Close
Forms!FRMSPLASH!Week_Num.SetFocus
Me.Week_Num = Null
Else
MsgBox "Reset Cancelled"
Forms!FRMSPLASH!Week_Num.SetFocus
Me.Week_Num = Null
End If
End If
Err_Btn_Update_Click:
End Sub
 
Thanks OldPro

The database I am modifying is an old inherited one and only has one table
for year data, this data is updated each week until the week eventually comes
araound again, as this is an old system, there is no resource to be spent on
better development, therefore a quick fix was required here, as each record
was manually updated each week, and the majority was with the value A, the
fix I required was sufficient, anyway, thanks for your advice.
 
Back
Top