HELP for updating selected records

[explorer19]

Hi, I am fairly new to macros and I am really lost. I need to make a macro so that when I select records from my datasheet form by highlighting them I can click on the button with macro that would set a field for all of those records to a certain value that the user inputs. I would really appreciate any help. Thank you.

[June7]

Not sure macro could do that, I have done something like this in VBA to delete selected records. Check this article http://support.microsoft.com/?id=294202

[Janoose]

Yes I do it through VBA but I never had any formal training with MS Access so I am not familiar with the Macro features in general . Here is my VBA code: Ethics_App_Date and ethics_dur are names on my table and also textbox on the form sorry I don't know what level you are at MS Access so I apologize if you know the VBA already.

Private Sub Form_Load()
Dim rs1 As Recordset
DoCmd.SetWarnings False
Dim db As Database
Set db = CurrentDb()
Dim sqlString1 As String
Dim sqlString2 As String
Dim appDate As Date
Dim centreCode As String
Dim ethics_dur As Integer
Dim renewDate As Date
sqlString1 = "Select ethics_app_date, ethics_dur, centre_Code from Centres"
Set rs1 = db.OpenRecordset(sqlString1, dbOpenSnapshot, dbReadOnly)
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNull(rs1!Ethics_App_Date) And Not IsNull(rs1!ethics_dur) Then
appDate = rs1.Fields("ethics_app_date")
centreCode = rs1.Fields("centre_code")
ethics_dur = rs1.Fields("ethics_dur")
renewDate = DateAdd("YYYY", ethics_dur, appDate)
sqlString2 = "Update Centres set Ethics_Renew_Date = '" & renewDate & "' where CENTRE_CODE = '" & centreCode & "'"
DoCmd.SetWarnings False
Debug.Print sqlString2
DoCmd.RunSQL sqlString2
End If
rs1.MoveNext
Loop
rs1.Close
End Sub

[June7]

EDIT: Just realized above posts from two different posters so removed comments.

Janoose, be sure to turn back on warnings after the RunSQL with:
DoCmd.SetWarnings True

[explorer19]

I've never programmed in VBA before but I am creating a database and I feel like the only way to use updates of multiple records at a time is through programming, thank you for all your help. Janoose, Ive been trying to use your code to modify to my tables and forms but I don't understand all the steps in it, are you updating multiple dates in your code or just one date for multiple records?

Thanks again

[June7]

explorer19, Janoose's code is not updating records selected by highlighting on datasheet form but is cycling through a recordset and updating records that meet a specific condition. It also appears the update value is determined by calculation of values from each record. This does not fit your described situation. The code in posted link is applicable.

[explorer19]

I looked over the link and tried to apply it to my code but for some reasons my selHeight always stays 0 and therefore selected data is not captured but at least I am on the right track, thank you so much for the link and your help

[explorer19]

I figured out what was my problem, I was using a split form with datasheet and it couldn't really detect which records in a form I was highlighting, so instead I used continuous form and made it look like my old split form. Thank you so much for all your help!