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'

 

[Katerine]

Hi JamesMack,
There may be a simple way; it depends on if there's an existing criterion for which records to update. If so, you can just do an update query.

That is, if you want to update "Training Class" for all records where the "Training Date" (already in the tables) = 3/22/01. Or if you want to update for all records, period. Or if there's another table which contains the information concerning which records to update.

If, however, you want to update a whole bunch of records, but not all, and the ones you want to update appear to be random (that is, there's no defining criteria), then you're probably out of luck.

If you have any criteria in mind, I could probably help further if I had:
The table
The criteria (what field equals/is less than/is greater than/contains/etc. what value)
The field(s) you want to update and what you want to update them to.

Thanks!
Katie

 

[ghubbell]

Hi JamesMack,
First step is to make a copy of your table as a back up in the event we do something wrong...
What you need to use here is an "Update" query. As a rough example, create a query based off of your table of records and in design view, on the toolbar you'll see a button usually just to the left of the exclamation button. Drop this down and choose update query. You must have some criteria that is stored in your table like the date? So drag or double-click the date field to the grid. Perhaps the dates criteria has to be 03-22-2001. Type this in in the first criteria row underneath the date. What is it you're updating? Another field that perhaps should now say "Passed"? Select that field by draging or double-clicking it to the grid. In its "Update to" row (for example) you would type in "Passed".
This all adds up to saying "find me all the records from the table of choice where the date is what I ask for, and update my other field to the value I place in the "Update to" row. Before you hit the ! button which commits the update with only 1 warning (if you have warnings turned on), run the query in a datasheet view (top left button) and see if the quantity of records makes sense. Your update will not show at this point. If your happy, back to design view, then hit the exclamation button and your job is done.
That's it!
Gord
ghubbell@total.net

 

[JamesMack]

Thanks Katie and Gord,
If I could explain further.
The relevent table structure is as follows:

tblPersonalDetails
PersonnelNumber (primary key)
Name
Initials
Address
etc

tblPersTrainingObjectives
PersonnelNumber (composite key)
TrainingObjectiveNumber (composite key)
DateAchieved
Score

tblTrainingObjectives
TrainingObjectiveNumber (primary key)
Description

If 20 persons achieve training objective 12 on 22 Mar and 34 persons achieve training objective 87 on 25 Mar (not at all inconcievable), without an automated process for updating I am going to be very busy on the keyboard!

 

[Katerine]

Hi JamesMack,
Is there currently a way to tell the database which people achieve a training objective? I don't see one in the structure.

If there is a way that I'm not seeing, you should just be able to do a regular update query, as ghubbell says, using the existing criteria, and you can disregard the rest of this post

If there is no way in the database, and this is a one-time update (that is, you're bringing a new database up-to-speed), I would recommend creating a new, temporary table with just the PersonnelNumber, and type in the PersonnelNumber for each person who, say, achieves training objective 12 on 22 Mar.

Then create an update query which links the temp table to tblPersTrainingObjectives (make sure it's a one-to-one relationship), and set it to update "TrainingObjectiveNumber" to 12, "DateAchieved" to 3/22, where "PersonnelNumber" (in tblPersTrainingObjectives) is not null.

If, on the other hand, this is something you're going to do a lot, on an ongoing basis, I would highly recommend creating a form to make the updates as user-friendly as possible. I personally would set it up with a listbox where a user can CTRL-select any number of records, have a textbox for TrainingObjectiveNumber, and a textbox for DateAcheived, and have a button to go through each record selected and update with the values entered.

But that's a LOT of code!

 

[ghubbell]

I'm assuming you want to bump up the training objective number? If so as above using tblPersTrainingObjectives and enter something like this in your DateAchieved criteria:

[Enter the Date Achieved:]

and criteria for TrainingObjectiveNumber:

[Enter the achieved number:]

and in the update to in TrainingObjectiveNumber:

[Enter the number to update to:]

Every time you run the query it will ask you these three questions if you key in as shown. By the way, your TrainingObjectiveNumber primary must exist in TblTrainingObjective or you'll probably get some errors.
Write back if I'm still foggy on your intent... Gord
ghubbell@total.net

 

[JamesMack]

Thanks again for your response,
The training objectives are achieved only once by all but can be achieved at almost any time.(there are other objectives in a seperate table which have to be achieved annually). The database must record who has achieved which objectives, to what level and when.
The number is just a unique identifier for each training objective

 

[Kobayashi]

ghubbell,

Further to the above, can you please advise if there is a way to make an update query via a dedicated form, so that inexperienced users of a database will not inadvertently make errors?
I wish to update two fields in a table: Assignedto and DateAssigned, depending upon the criteria; Ref No. which is also the primary key. Also, I would like to be able to select more than one Ref No. and update the records in the table accordingly. I've tried using a MultiSelect list box but it is a bit clumsy so I would rather have a datasheet type control/form into which to enter the Ref No./s in the form.

Kind Regards

Adrian

 

[ghubbell]

Hi Adrian,
You could create a simple pop-up form, unbound, and with two text fields on it. You can name the text fields appropriately "txtAssignedto" and "txtDateAssigned". This is their names, not their control source. Now, if your Db is set up correctly, you could instead be using a combobox, at least for the field (txtAssignedto) whos row source would be data from a table of people? who this could be assigned to. Limit to list = yes.

Add a command button. Name it CmdUpdate, and in its on-click event in VB we'll make the query go. The code should look something like this:

Private Sub CmdUpdate_Click()
On Error GoTo Err1
If IsNull(Me.txtAssignedto) Or Me.txtAssignedto = "" Then
MsgBox "Please enter or select someone to assign this to. ", vbInformation, "Required information..."
Me.txtAssignedto.SetFocus
Exit Sub
End If
If IsNull(Me.txtDateAssigned) Or Me.txtDateAssigned = "" Then
MsgBox "Please enter a date. ", vbInformation, "Required information..."
Me.txtDateAssigned.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
DoCmd.SetWarnings False
DoCmd.OpenQuery "EnterTheNameOfYourUpdateQueryHere"
MsgBox "All records updated successfully. ", vbInformation, "Success!"
DoCmd.Close acForm, Me.Name

Exit1:
DoCmd.SetWarnings True
Exit Sub

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

Your query would of course be reading the values from the form, so in the "update to" row of your update query, you'd have like "Forms!NameOfThisForm!txtAssignedTo" and "Forms!NameOfThisForm!txtDateAssigned" in the correct positions.

Voila! Gord
ghubbell@total.net

 

[Kobayashi]

Gord,

Many thanks for your quick response. The code looks great, however, I want to update the Assigned to and Date Assigned fields in the table when a particular Ref No./s has/have been selected.
I presume this would entail a third unbound text box with it's row source being the Ref No. field in the underlying table.
This is the part that I am finding really difficult. Knowing how to input into the third text box on the form - called txtRefNo for example - more than one Ref No. and having all associated records in the table being updated with the text I type in the Assigned to and Date Assigned text boxes?
I hope I have made it clear? I can perform the action in SQL where the code would be something like:
UPDATE [TableName] SET [TableName].[Assignedto] = [form]."txtAssignedto", [TableName].[DateAssigned] = [Form].DateAssigned
WHERE [TableName].[Ref No]= ????This reference is the sticking point, as it has to be one or MORE Ref No.s;

Please help

In additon, and I know I'm asking a heck of a lot, especially it being the weekend, but I've also posted another post which you helped me with before, but which didn't quite fit the bill. The post is Re: Changing backcolor of ALL controls on a form.
I've kinda just started with this programming stuff after designing an Access application some time ago, and am intrigued! I am currently studying various VBA for Acces books and I am very happy with the progress I am making. I intend to ultimately obtain an MCSD.
However, I'm still a very long way away and do really appreciate the help I continue to receive by yourself and others on this site!

Always appreciative

Adrian

 

[BoxHead]

GORD ! ! ! ! !​

 

[ghubbell]

BoxHead! How do you do!! Been a long time and hi to all. I honor my pledge to look after threads I've participated in! (email notifications work well...!)

Ok Adrian,
There's a couple of ways to do this: best might be to make a list box with multi-select = extended, and a row source that is your table that contains the ref numbers. Have your users pick the ref or refs that they want to update. If you're not too familiar with this stuff there's some pretty good F1 help and code on multi-selecting from a list box, the list index property, and the selected property. In your code you're going to have to loop through the array of values you obtain from this running your update query on each ref number, something like:
Private Sub CmdUpdate_Click()
On Error GoTo Err1
Dim SQL As String, varListItem As Variant
If IsNull(Me.txtAssignedto) Or Me.txtAssignedto = "" Then
MsgBox "Please enter or select someone to assign this to. ", vbInformation, "Required information..."
Me.txtAssignedto.SetFocus
Exit Sub
End If
If IsNull(Me.txtDateAssigned) Or Me.txtDateAssigned = "" Then
MsgBox "Please enter a date. ", vbInformation, "Required information..."
Me.txtDateAssigned.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
With Me.MyListBox
For Each varListItem In .ItemsSelected
MsgBox varListItem & " " & .Column(0, varListItem)
'SQL = "UPDATE [TableName] SET [TableName].[Assignedto] = '" & Me.TxtAssignedto & "', [TableName].[DateAssigned] = '" & Me.TxtDateAssigned & "' WHERE [TableName].[Ref No]= '"& .Column(0, varListItem)
Next varListItem
End With
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

Now be warned! There's a message box line in there for testing but I didn't test your query... you'll probably have to play with the little quotes ( ' ) etc, depending on the values in your list box. You might also add a little something to catch a null value in the list box too.

Start with this and I'll find something to take care of your colors issue.
Gord
ghubbell@total.net

 

[Kobayashi]

Gord,

I have tried using a list box, and although the code I initially used didn't work - I've yet to try the above but certainly will - using a multi-select list box seemed a bit cumbursome, as I have to select approx. 20 items from the list box with ranges from 1-10,000. And if I have to select No. 87 for example and then 9999 it takes forever to scroll to the desired Number!
Is there any other way? Currently I do use an update query and inputting the numbers either in one criteria field as: 87 or 9999 or 7890 or ....., or on separate criteria lines, is a lot quicker that selecting them from the list box, only because of the vast amount of numbers involved, which increases daily.
Although just reading through what I've written it seems that I'm looking for a something that already exists, and works - the query table! The same layout on a form, where I can add command buttons and restrict/disable controls would be ideal?
Or, how about a text box where I can input a No., click a command button to add it to a list and then use the contents (Ref No.'s) of this list as the criteria?
It would be quicker if I could just type the numbers one after the other and then submit as the criteria, but I'll try anything.

Adrian

Adrian

 

[Kobayashi]

Gord,

I am having trouble - as you predicted - with the SQL part of the procedure. I removed the first apostrophe before 'SQL as this made the code just a note.
Here is the part of troublesome code that I have inputted:
MsgBox varListItem & " " & .Column(0, varListItem)
SQL = "UPDATE [Daily Metrics] SET [Daily Metrics].[DocumentsCurrentlyAssignedto]" _
= '" & DocumentsCurrentlyAssignedto & "', [Daily Metrics].[DateAssigned] = '" & _
Me.DateAssigned & "' WHERE [Daily Metrics].[Ref No]= '" & .Column(0, varListItem)
Next varListItem
End With
I'm getting a 'compile error, syntax error'.

Regards,

Adrian

 

[ghubbell]

Good morning Adrian!

Yes the message box was there just for your testing, and the code was commented as I don't have your data....

Look at this as one line:

SQL = "UPDATE [Daily Metrics] SET [Daily Metrics].[DocumentsCurrentlyAssignedto] = '" & Me.DocumentsCurrentlyAssignedto & "', [Daily Metrics].[DateAssigned] = '" & Me.DateAssigned & "' WHERE [Daily Metrics].[Ref No]= " & .Column(0, varListItem)

Notice the little quotes ( ' ) and their positions. Notice also that I've changed ]= '" & .Column(0, varListItem) to ]= " & .Column(0, varListItem)

This is what I mean that you'll have to play with these a little. As I recall, you may even require # in place of ' when using your date, if it's truly a date/time field.

Now to do a test, take the SQL string and paste it in to a new empty query in SQL view. Don't run it, but instead flip it to design view. Change the criteria to realistic numbers and dates. Flip it to datasheet view to see if it'll operate ok. Good? Flip back to SQL view and notice any changes Access may have made for you. Remember: string inputs usually require little quotes, while numbers don't. The date thing (#) should be resolved for you by Access.

To look at an alternative, sure, you can have a user key in or select values in to a field or fields in a perhaps continuous sub form or datasheet on your main form.

Easy: make the entries go to a temp table that is the source of the sub form. Loop the temp table, then delete its contents when the operation has run. Let me know if you'd like to try this approach instead. I know what you mean about list boxes especially with that many choices....unless...can you reduce the records in the list box by querying something?

Mondays! Gord
ghubbell@total.net

 

[Kobayashi]

Gord,

Never liked them (Mondays) much either!
I managed to get the code to work in the end. I kind of asked a favour of one of our programmers at work - much to his chagrin as their dept normally charge other internal depts $1000 an hour for their services! And after spending half an hour with an enthusiastic but troublesome programmer wannabe I think he'll be avoiding my area for a while! Nice fella though and I'll buy him a couple of pints on Friday - and he helped me to get it to work, without either of us really knowing how or what was wrong with what you gave me?
We did exactly what you advised above and created the query in the design grid of SQL and then changed it to the SQL view. Again, as you also suggested, I had to change the ' to # as it is a true date/time field.
We also changed the '" and "' to """ """ for some reason which was way beyond me? Any ideas why?
I think we also added a Docmd.execute statement of some description after the SQL code. Again, was this really necessary?
Anyway, it works, but to be honest I don't think it is the best way to achieve what I want - the number of items in the rowsource of the list box prevent this from being a practical option in this instance, but I will definately be saving the code because it will be great with smaller lists.
I'm unable to reduce the list via a query, so, after all your help that you've already given, any chance of option 2? Using the temp table definately seems to be the way to go!

 

[ghubbell]

A thousand bucks an hour?! Where do I sign up!!

Yes, the syntax in SQL strings used in Visual Basic can be an absolute riot. I've been at this for years and still can't remember all the rules. What the hey - it works now, so stash it in your 'library of Access brain teasers' because you know darn well you'll need it or pieces of it again. One really good thing to remember though is if you possibly can just call the saved query versus a SQL string in VB, it will run faster plus with the QBE grid, is usually much easier to maintain and understand. That's a fact!

Second option: Make a temp table "tblRefTemp". Name the field as your refno was. You should probably make a continuous form with a combobox with a row source that is from the real refnos table. Hopefully it won't be too slow to load up. (If it is we'll go to plan C!)

Place this form in the main as a sub, and we'll use the same update button on the main form. This time we'll loop the values you've loaded in the temp table then when we're done, empty it and clean off the form for another go. :

On Error GoTo Err1
Dim SQL As String, Rs As Recordset, Db As Database
'For Access 2000 or 2002 you need a reference to Microsoft DAO 3.6 Object Library: VB-Tools-References and check this lib. Make sure it will compile.
'if it won't, move its "priority" up or down till it does.
If IsNull(Me.txtAssignedto) Or Me.txtAssignedto = "" Then
MsgBox "Please enter or select someone to assign this to. ", vbInformation, "Required information..."
Me.txtAssignedto.SetFocus
Exit Sub
End If
If IsNull(Me.txtDateAssigned) Or Me.txtDateAssigned = "" Then
MsgBox "Please enter a date. ", vbInformation, "Required information..."
Me.txtDateAssigned.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 tblRefTemp.RefNo FROM tblRefTemp ORDER BY tblRefTemp.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 'loop the temp tables entries.
'******install your good working line in place of the line below notice the ending...
'Db.Execute "UPDATE [Daily Metrics] SET [Daily Metrics].[DocumentsCurrentlyAssignedto] = '" & Me.DocumentsCurrentlyAssignedto & "', [Daily Metrics].[DateAssigned] = '" & Me.DateAssigned & "' WHERE [Daily Metrics].[Ref No]= " & Rs!RefNo

Rs.MoveNext
Loop
Rs.Close

'done. now wipe the entries.
Db.Execute "Delete tblRefTemp.RefNo FROM tblRefTemp"

Me.YourSubFormsName.Requery 'this should clean the sub form

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

I wonder if you notice the 16 or so spaces to the right of all message box messages?! (Keeps your message in the middle of the message box when you display a message with an icon. (Bonus!)

Give it a go! Gord
ghubbell@total.net

 

[Kobayashi]

Gord,

I can't get the new code to work within what was my 'working' code.
Same old problem with the SQL syntax!
The first block of code below was my 'working' code, but obviously designed with the varlistitem in mind.
The second block is my code how it stands now.
Please have a look and see if you can fix?

SQL = "UPDATE [Daily Metrics] SET DocumentsCurrentlyAssignedto = " & _
' "= """ & DocumentsCurrentlyAssignedto & """, DateAssigned = #" & _
' Me.DateAssigned & "# WHERE [Ref No] = " & .Column(0, varListItem) & ";"


' dbs.Execute SQL


Do Until Rs.EOF 'loop the temp tables entries.
'******install your good working line in place of the line below notice the ending...
Db.Execute "UPDATE [Daily Metrics] SET [Daily Metrics].[DocumentsCurrentlyAssignedto]" _
= " & '" & Me.DocumentsCurrentlyAssignedto & """, DateAssigned = #" & _
Me.DateAssigned & "# WHERE [Ref No]= " & Rs!RefNo & ";"

Rs.MoveNext
Loop
Rs.Close

Thanks,

Adrian

 

[ghubbell]

Good morning Adrian!
Here's a couple to try: One line ok?! There's so many quotes and such that it will be easier to understand if it's spread out flat. BTW you can change your VB format to something like Arial size 8 or 9 using Tools-Options-Editor Format and on a 1024/768 screen you'll have real estate galore to make coding and reading a little easier.

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

Or

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

Depending on string, date or number data types, ' and # define how the data is read and how it is 'inserted' in to the line. You should be able to place a line break (red dot -left margin) on this line and check the values as they run by holding your cursor over each variable. The value should light up as a controltip text. Notice exactly how it is interpreted: with quote marks or not... No worries...We're so close it hurts!
Gord
ghubbell@total.net

 

[Kobayashi]

Good afternoon Gord,

It still doesn't like the first ' before the "? Also, where does the db.execute statement go now?
Code currently is as follows:
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 = "UPDATE [Daily Metrics] SET [Daily Metrics].[DocumentsCurrentlyAssignedto]" _
= "" & Me.Assignedto & "', DateAssigned = #" & _
Me.DateAssigned & "# WHERE [Ref No]= " & Rs!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 'loop the temp tables entries.
'******install your good working line in place of the line below notice the ending...


Rs.MoveNext
Loop
Rs.Close

'done. now wipe the entries.
Db.Execute "Delete tblTempRef.RefNo FROM tblTempRef"

Me.sbfmTempRef.Requery 'this should clean the sub form

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

Regards

Adrian