HI

i've database with lots of tables. Within the first table i've got a area known as status.

table 1
idno   name    status
111     hjghf     yes
225      hjgjj     no
345      hgj       yes

other tables might have same idno with various fields.

I wish to look into the status for every id no and when it's yes then for your id number in most tables for those null and blank fields i wish to update them as 111111.

I'm searching for an example vba code with this that we can adapt.

Thanks

Here's some largely untested code. Hopefully you'll have a start.

Sub UpdateNulls()
Dim strSQL As String
Dim rs As DAO.Recordset
For Each tdf In CurrentDb.TableDefs
    If Left(tdf.Name, 4) <> "Msys" And tdf.Name <> "Table1" Then
        strSQL = "Select * From [" & tdf.Name & "] a Inner Join " _
        & "Table1 On a.idno = Table1.idno Where Table1.Status = 'Yes'"

        Set rs = CurrentDb.OpenRecordset(strSQL)

        Do While Not rs.EOF
            For i = 0 To rs.Fields.Count - 1
                If IsNull(rs.Fields(i)) Then
                    rs.Edit
                    rs.Fields(i) = 111111
                    rs.Update
                End If
            Next
            rs.MoveNext
        Loop

    End If
Next
End Sub

Multi-table update syntax for MS Access:

UPDATE Table2
INNER JOIN Table1
  ON Table2.idno = Table1.idno
SET Table2.salary = 111111
WHERE Table1.status = 'yes'
AND Table2.salary Is Null

You are able to get into SQL View for any query, paste this in, after which run the query, or assign it to some string and employ CurrentDb.Execute or CurrentProject.Connection.Execute, based on your DAO/ADO preference.