CAN I SUBSTITUTE VARIABLE FOR DOMAIN?

[Glint]

Hi Guys,
I have several tables that have a field I named "PostedBy" in my database.
More than half of the tables in the database have this field, which takes the
ID of a member. I have decided to delete duplicate records of members. But
before I do this, I want to know if that ID has been used in any other table,
especially in the PostedBy field. I found that a member could be deleted even
when his record has associated records in other tables; obviously someone did
not set the relationships properly.
So I need to search through over twenty tables, checking for the ID I want
to delete in their PostedBy field.
The first problem I had was how to retrieve those tables that have a
postedBy field. When I did not succeed, I created a table that has the names
of the tables that are affected.
The second problem was when I tried to go through each table with a domain
aggregate function DCount. Here is what I tried: ALLTbl2 is a table that has
the names (TName, the only field) of all tables that have a field named
PostedBy. SerialNumber is the ID of the member that I want to delete.

Dim DB As Database
Dim RS As DAO.Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT TName FROM ALLTbl2")
RS.MoveFirst
If DCount("*", TName, "[PostedBy]=" & SerialNumber) > 0 Then
MsgBox "This member exists in " & TName & "."
Exit Sub
Else
Do Until RS.EOF
RS.MoveNext
If DCount("*", TName, "[PostedBy]=" & SerialNumber) > 0 Then
MsgBox "This member exists in " & TName & "."
Exit Sub
Else
MsgBox "This record is deletable."
End If
Loop
End If

The error message I get is that "I entered an invalid argument in a domain
aggregate function."

Please show me a better way to do these.

 

[Ken Sheridan]

I think you need:

If DCount("*", RS.Fields("TName"), "[PostedBy]=" & SerialNumber) > 0 Then

Also I don't see any need to check the first record independently before
looping through the recordset, so:

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strTable as String

Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT TName FROM ALLTbl2")

With RS
.MoveFirst
Do Until .EOF
strTable = .Fields("TName")
If DCount("*", strTable, "[PostedBy]=" & SerialNumber) > 0 Then
MsgBox "This member exists in " & strTable & "."
Exit Sub
Else
MsgBox "This record is deletable."
End If
.MoveNext
Loop
End With

Ken Sheridan
Stafford, England

 

[Glint]

Thanks, Ken.
It works.
--
Glint

I think you need:

If DCount("*", RS.Fields("TName"), "[PostedBy]=" & SerialNumber) > 0 Then

Also I don't see any need to check the first record independently before
looping through the recordset, so:

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strTable as String

Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT TName FROM ALLTbl2")

With RS
.MoveFirst
Do Until .EOF
strTable = .Fields("TName")
If DCount("*", strTable, "[PostedBy]=" & SerialNumber) > 0 Then
MsgBox "This member exists in " & strTable & "."
Exit Sub
Else
MsgBox "This record is deletable."
End If
.MoveNext
Loop
End With

Ken Sheridan
Stafford, England

Hi Guys,
I have several tables that have a field I named "PostedBy" in my database.
More than half of the tables in the database have this field, which takes the
ID of a member. I have decided to delete duplicate records of members. But
before I do this, I want to know if that ID has been used in any other table,
especially in the PostedBy field. I found that a member could be deleted even
when his record has associated records in other tables; obviously someone did
not set the relationships properly.
So I need to search through over twenty tables, checking for the ID I want
to delete in their PostedBy field.
The first problem I had was how to retrieve those tables that have a
postedBy field. When I did not succeed, I created a table that has the names
of the tables that are affected.
The second problem was when I tried to go through each table with a domain
aggregate function DCount. Here is what I tried: ALLTbl2 is a table that has
the names (TName, the only field) of all tables that have a field named
PostedBy. SerialNumber is the ID of the member that I want to delete.

Dim DB As Database
Dim RS As DAO.Recordset
Set DB = CurrentDb
Set RS = DB.OpenRecordset("SELECT TName FROM ALLTbl2")
RS.MoveFirst
If DCount("*", TName, "[PostedBy]=" & SerialNumber) > 0 Then
MsgBox "This member exists in " & TName & "."
Exit Sub
Else
Do Until RS.EOF
RS.MoveNext
If DCount("*", TName, "[PostedBy]=" & SerialNumber) > 0 Then
MsgBox "This member exists in " & TName & "."
Exit Sub
Else
MsgBox "This record is deletable."
End If
Loop
End If

The error message I get is that "I entered an invalid argument in a domain
aggregate function."

Please show me a better way to do these.