我有数据库有许多表。在第一个表,我有一个名为领域状态
。
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
其他表可能有相同的 IDNO
用不同的领域。
我想没有检查每个ID的状态,如果是肯定的,那么对于所有表的所有空和空白领域在该ID号我想更新他们为111111。
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.
我要寻找一个样本VBA code这个我能适应。
I am looking for a sample vba code for this which I can adapt.
感谢
下面是一些主要未经考验code。希望它会给你一个开始。
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