Access Update Query

[DMFisher]

I've created a query to return all values in a table where 'Type' is 'A'

[TABLE="width: 500"]
<tbody>[TR]
[TD]Type[/TD]
[TD]TypeNumber[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][XXX][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][YYY][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][ZZZ][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[apr pillai]

It cannot be done with update query. Need a VBA Routine to do it.

 

[DMFisher]

Do you know what the VBA would be for this? I haven't got a clue then it comes to it...

 

[stumac]

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?

 

[DMFisher]

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.

 

[stumac]

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.

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

 

[xenou]

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.

 

[DMFisher]

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.

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