Access Update Query

DMFisher

New Member
Joined
Apr 22, 2016
Messages
35
I've created a query to return all values in a table where 'Type' is 'A'

TypeTypeNumber
A34
A35
A36
A37
A45
A[XXX]
A[YYY]
A[ZZZ]

<tbody>
</tbody>

Can update queries update the blank 'TypeNumber' fields in sequential order based on the record above it?
E.g.: 'XXX' = 46, YYY = 47, ZZZ = 48
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Are the values you have (XXX, YYY, ZZZ) actually stored as that in the table or are they 0's or Nulls?

Do you want them to increment starting from the highest number already in the table?
 
Upvote 0
Are the values you have (XXX, YYY, ZZZ) actually stored as that in the table or are they 0's or Nulls?

Do you want them to increment starting from the highest number already in the table?


The values XXX,YYY,ZZZ are blank values.. they were just to show you were the numbers should go.

If it is going from the Queries, then it will just be an increment starting from the highest number.
If it is going from the table, it will be the increment from the highest number from the records where the 'Type' field is the same as the record being updated.
 
Last edited:
Upvote 0
Something like the following will work - there is no error handling in it so you need to make sure you enter the correct values under the editable values.

Always run things like this on a copy of your database and check you are happy with the result before running on your live data.

Code:
Sub UpdateBlanks()
Dim RS1 As Recordset
Dim T As String ' Table
Dim TF As String 'Type Field
Dim UF As String 'Update Field
Dim TV As String 'TypeValue
Dim UN As Long

[B][COLOR=#ff0000]'Edit Values Below[/COLOR][/B]
T = "MyTable" 'Change to your table
TF = "Type" 'Type field name
UF = "TypeNumber" ' Field that contains number
TV = InputBox("Enter " & TF & " Value to update blanks for")
UN = Nz(DMax(UF, T, TF & " = '" & TV & "'")) + 1

Set RS1 = CurrentDb.OpenRecordset("Select " & UF & " from " & T & " where " & UF & " Is Null AND " & TF & " = '" & TV & "'")
    With RS1
        Do Until .EOF = True
        .Edit
        .Fields(0) = UN
        .Update
        .MoveNext
        UN = UN + 1
        Loop
    End With
Set RS1 = Nothing
MsgBox "Complete"
End Sub
 
Upvote 0
It might be worth asking why you need these values because they seem unnecessary. For instance, an autonumber field would also work to create order / sequence/ unique IDs.
 
Upvote 0
something like the following will work - there is no error handling in it so you need to make sure you enter the correct values under the editable values.

Always run things like this on a copy of your database and check you are happy with the result before running on your live data.

Rich (BB code):
sub updateblanks()
dim rs1 as recordset
dim t as string ' table
dim tf as string 'type field
dim uf as string 'update field
dim tv as string 'typevalue
dim un as long

'edit values below
t = "mytable" 'change to your table
tf = "type" 'type field name
uf = "typenumber" ' field that contains number
tv = inputbox("enter " & tf & " value to update blanks for")
un = nz(dmax(uf, t, tf & " = '" & tv & "'")) + 1

set rs1 = currentdb.openrecordset("select " & uf & " from " & t & " where " & uf & " is null and " & tf & " = '" & tv & "'")
    with rs1
        do until .eof = true
        .edit
        .fields(0) = un
        .update
        .movenext
        un = un + 1
        loop
    end with
set rs1 = nothing
msgbox "complete"
end sub

amazing!!! Thankyou so much
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top