How to check/update a row of values based on a single column header?(Access)

Question

HI

i have database with many tables. In the first table i have a field called status.

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

other tables could have same idno with different fields.

I want to check the status for each id no and if it is yes then for that id number in all tables for all null and blank fields i want to update them as 111111.

I am looking for a sample vba code for this which i can adapt.

Thanks

Answer 1

This question is probably related to:

http://stackoverflow.com/questions/233026/how-to-run-a-loop-of-queries-in-access

Answer 2

no i dont want to update the idno field i want to update all the other fields

for eg if the 2nd table is like this

idno   salary   expense
111     456       469     
225     42      
345               456

i want the the field salary for id no 345 for which status is yes in first table to be updated by 111111. like this i need a loop as there are many tables in the database

Answer 3

Here is some largely untested code. Hopefully it will give you 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

Answer 4

Hi Remou thanks for the help

but wat does If Left(tdf.Name, 4) <> "Msys" this line mean 'msys'

Answer 5

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 can go into SQL View for a query, paste this in, and then run the query, or assign it to a string and use CurrentDb.Execute or CurrentProject.Connection.Execute, depending on your DAO/ADO preference.