How do I update multiple fields simultaneously? 5

[JamesMack]

I am in the process of developing a training records dbase.
Is there a way I can update records for a number of people (up to say 50 or perhaps more) who achieve a training objective on the same date. I would rather not have to input this info 1 record at a time!
Personnel do have individual identities in the form of 'student number'

 

[ghubbell]

Hi Adrian,
Well, keep trying the choices till you find one that agrees with your datatypes...

Let's run it like this:

Do Until Rs.EOF 'loop the temp tables entries.
SQL = "UPDATE [Daily Metrics] SET [Daily Metrics].[DocumentsCurrentlyAssignedto] = " & Me.Assignedto & ", DateAssigned = #" & Me.DateAssigned & "# WHERE [Ref No]= " & Rs!RefNo
Db.Execute (SQL)
Rs.MoveNext
Loop
Rs.Close

Each record in your temp table (now a recordset) will be used until we've reached the 'end of file'. So, starting on the first refno in the recordset we'll execute the SQL string, then move to the next refno in the recordset and loop back to the 'Do'. Because Rs!Refno is going to change each time we 'movenext', you are refilling this SQL string with the current record's 'Refno' value. When finished, we close the recordset.

Keep at it! If you want to shoot over a zipped copy I can check it here..So close!!


Gord
ghubbell@total.net

 

[Kobayashi]

Good afternoon Gord,

Well, I've now got code everywhere in my click event procedure but still none of it works! I've now completely lost the plot!
If you don't mind I'll zip a copy of the mdb and send it over to you. It's a pretty large file so I'll delete all of the non-relevant tables etc. and I'll just send the relevant tables/forms to you 'structure only' to keep the size down.
Sending soon.

Thanks.

Adrian

 

[ghubbell]

Helllllloooo Adrian!
Please excuse my absence as I really messed up my computer today at work. XP...why's it gotta be XP?!!

This works:

Private Sub CmdUpdate_Click()
On Error GoTo Err1
Dim SQL As String, Rs As Recordset, Db As Database
If IsNull(Me.Assignedto) Or Me.Assignedto = "" Then
MsgBox "Please enter or select someone to assign this to. ", vbInformation, "Required information..."
Me.Assignedto.SetFocus
Exit Sub
End If
If IsNull(Me.DateAssigned) Or Me.DateAssigned = "" Then
MsgBox "Please enter a date. ", vbInformation, "Required information..."
Me.DateAssigned.SetFocus
Exit Sub
End If
If MsgBox("You are about to update your records. Continue? ", vbYesNo + vbQuestion + vbDefaultButton1, "Record update confirmation...&quot = vbNo Then Exit Sub
Set Db = CurrentDb()
SQL = "SELECT tblTempRef.RefNo FROM tblTempRef ORDER BY tblTempRef.RefNo"
Set Rs = Db.OpenRecordset(SQL, dbOpenDynaset)
If Rs.RecordCount = 0 Then
Rs.Close
MsgBox "No reference numbers found... ", vbInformation, "Required information..."
GoTo Exit1
End If
Do Until Rs.EOF
Db.Execute "UPDATE [Daily Metrics] SET [Daily Metrics].DocumentsCurrentlyAssignedto = " & Me.Assignedto & ", [Daily Metrics].DateAssigned = '" & Me.DateAssigned & "' WHERE ((([Daily Metrics].[Ref No])=" & Rs!RefNo & &quot)"
Rs.MoveNext
Loop
Rs.Close
Db.Execute "Delete tblTempRef.RefNo FROM tblTempRef"
Me.sbfmTempRef.Requery
MsgBox "All records updated successfully. ", vbInformation, "Success!"
DoCmd.Close acForm, Me.Name

Exit1:
Exit Sub

Err1:
MsgBox Err.Number & " " & Err.Description, vbInformation, "Update query error..."
Resume Exit1
End Sub

Like a champ!
Hope it'll work at your end!
Gord
ghubbell@total.net

 

[Kobayashi]

Good afternoon Gord,

Thanks again, but there is still a problem. This time with the ) syntax surrounding the WHERE ((([Daily Metrics].[Ref No])=" & Rs!RefNo & &quot)"? The error message is saying that there are too many ) in the statement?

HELP!!!

Regards,

Adrian

 

[ghubbell]

Good Morning Adrian!
That's pretty odd as the code above was copy/pasted out of a compiled and tested db...here: I've removed the Access created brackets and tested again here with Access 2000. Works fine:

Db.Execute "UPDATE [Daily Metrics] SET [Daily Metrics].DocumentsCurrentlyAssignedto = " & Me.Assignedto & ", [Daily Metrics].DateAssigned = '" & Me.DateAssigned & "' WHERE [Daily Metrics].[Ref No]=" & Rs!RefNo & ""

Does your 97 Db compile properly? The error may not be valid if the Db is not compiled. give it another go?! Gord
ghubbell@total.net

 

[Kobayashi]

Gord,

I've compiled and removed all of the ) but now I'm getting this error message:
3075 Syntax error (missing operator) in query expression '[Daily Metrics].[Ref No]='.

???

Adrian

 

[ghubbell]

Adrian,
Please make me another little zip copy of exactly where you stand at this moment. Please leave me one or two records to test with. We'll beat this devil! Gord
gord@ghubbell.com

 

[Kobayashi]

Gord,

All I have done is paste the code you last supplied into my procedure, so you already have what I have.
I have zipped a copy of the Daily Metrics table though, which I will email to you so you have a 100 or so records to test with.
Thanks again for your continued help!

Regards,

Adrian

 

[Kobayashi]

Gord,

Apologies for the delay in expressing my gratitude.
The following code that you kindly took the trouble to create and send to me works absolutely brilliantly!
Oh, I actually removed the Docmd.close... part at the end as I wanted to keep the form open for additional updations.
Many thanks again!

For anyone who is interested, the following code updates two fields in an underlying table to whatever selection you choose in these (combo boxes) for each and every record, criteria of the records being the selected autonumber field.
I probably haven't described the function of this code very well, but it is great, so please give it a go!

Private Sub CmdUpdate_Click()
On Error GoTo Err1
Dim SQL As String, Rs As Recordset, Db As Database
If IsNull(Me.Assignedto) Or Me.Assignedto = "" Then
MsgBox "Please enter or select someone to assign this to. ", vbInformation, "Required information..."
Me.Assignedto.SetFocus
Exit Sub
End If
If IsNull(Me.DateAssigned) Or Me.DateAssigned = "" Then
MsgBox "Please enter a date. ", vbInformation, "Required information..."
Me.DateAssigned.SetFocus
Exit Sub
End If
If MsgBox("You are about to update your records. Continue? ", vbYesNo + vbQuestion + vbDefaultButton1, _
"Record update confirmation...&quot = vbNo Then
Exit Sub
End If
Set Db = CurrentDb()
SQL = "SELECT tblTempRef.RefNo FROM tblTempRef ORDER BY tblTempRef.RefNo "
Set Rs = Db.OpenRecordset(SQL, dbOpenDynaset)
If Rs.RecordCount = 0 Then
Rs.Close
MsgBox "No reference numbers found... ", vbInformation, "Required information..."
GoTo Exit1
End If
Do Until Rs.EOF
If IsNull(Rs!RefNo) Then GoTo StepOver
Db.Execute "UPDATE [Daily Metrics] SET DocumentsCurrentlyAssignedto = '" & Me.Assignedto & "', DateAssigned = #" & Me.DateAssigned & "# WHERE [Ref No]=" & Rs!RefNo
StepOver:
Rs.MoveNext
Loop
Rs.Close
Db.Execute "Delete tblTempRef.RefNo FROM tblTempRef"
Me.sbfmTempRef.Requery
MsgBox "All records updated successfully. ", vbInformation, "Success!"
DoCmd.Close acForm, Me.Name

Exit1:
Exit Sub

Err1:
MsgBox Err.Number & " " & Err.Description, vbInformation, "Update query error..."
Resume Exit1
End Sub

 

[ghubbell]

Thank you Adrian, and of course you're welcome! It's great to hear it's been successful and it's also great to have it all here for everyone to use and enjoy. That's what makes it all worthwhile! Till later,
;-) Gord
gord@ghubbell.com