DCount Function

[Guest]

I am developing an application that will manage a database which will be used
for building an organization chart. On my data entry form I have a Delete
button for what else?....deleting unwanted or former employees. However, one
of the crteria for allowing this delete is to insure that it will not leave
any orphans. In other words, if Bob Jones reports to John Smith and John
Smith gets deleted, Bob Jones becomes an orphan.

Now with that said, I have tried to use the DCount function to search the
database to see if that person's "Name" show's up in the "ReportsTo" column.
Here is the code:

Dim intOrphan As Integer, strName As String

strName = Me.txtName.Value
intOrphan = DCount("*", "dbo_OrgData", "[ReportsTo] =" & strName)

If I insert the person's actual name such as:
intOrphan = DCount("*", "dbo_OrgData", "[ReportsTo] = 'John Smith'")

it will work fine, however, using the varialbe "strName", I get the
following error:

Syntax error (missing operator) in query expression '[ReportsTo] =John
Smith'.

It interprets the value of the variable "strName" (which is John Smith) but
it will not use it to query the database.

Can anyone tell me what is going on here?

Thanks,

Mike

 

[fredg]

I am developing an application that will manage a database which will be used
for building an organization chart. On my data entry form I have a Delete
button for what else?....deleting unwanted or former employees. However, one
of the crteria for allowing this delete is to insure that it will not leave
any orphans. In other words, if Bob Jones reports to John Smith and John
Smith gets deleted, Bob Jones becomes an orphan.

Now with that said, I have tried to use the DCount function to search the
database to see if that person's "Name" show's up in the "ReportsTo" column.
Here is the code:

Dim intOrphan As Integer, strName As String

strName = Me.txtName.Value
intOrphan = DCount("*", "dbo_OrgData", "[ReportsTo] =" & strName)

If I insert the person's actual name such as:
intOrphan = DCount("*", "dbo_OrgData", "[ReportsTo] = 'John Smith'")

it will work fine, however, using the varialbe "strName", I get the
following error:

Syntax error (missing operator) in query expression '[ReportsTo] =John
Smith'.

It interprets the value of the variable "strName" (which is John Smith) but
it will not use it to query the database.

Can anyone tell me what is going on here?

Thanks,

Mike

Well when you hard code 'John Smith' its not the same as John Smith.
In the first instance John Smith is a string (enclosed in single
quotes). In the second instance it's not a string (within the DCount
function syntax).

IntOrphan = DCount("*", "dbo_OrgData", "[ReportsTo] =""" & strName &
"""")

The above will enclose the variable value within double quotes
"[ReportsTo] = "John Smith"" (using a single quote you will get an
error if the value contains a single quote, i.e. O'Brien).

You can avoid even declaring strName, and because the Value property
is the default property you don't have the explicitly state it.
Just use:

Dim intOrphan As Integer
IntOrphan = DCount("*", "dbo_OrgData", "[ReportsTo] =""" & Me.txtName
& """")

 

[John Spencer]

You need the text delimiters around the string.

intOrphan = DCount("*", "dbo_OrgData", "[ReportsTo] ='" & strName & "'")

Could you use the relationships window to set up a self-join and enforce
referential integrity? I think it might be possible, although I think
you would have to always fill in the ReportsTo field.

I've never tested enforcing a self-join with Access, so the above is
speculation.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================