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.
This is most likely associated with:
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.