DLookup

[Joe Z]

I was able to use a past thread to get pretty close, but not quite there. I'm trying to enable the user to select a table to view, by keying in the archive table name. My code is as follows:

Public Sub OpenArchiveTable()

Dim strArchiveTableDate As String
Dim archivetableexists As String
Dim strArchiveTableName As String

strArchiveTableDate = InputBox("Enter Date of Report - mmddyyyy")
strArchiveTableName = "PD_Grade_Changes_" & strArchiveTableDate
archivetableexists = Nz(DLookup("Name", "MSysObjects", "MSysObjects.Name =" & strArchiveTableName))

If Len(archivetableexists) > 1 Then
DoCmd.OpenTable strArchiveTableName, acViewNormal, acReadOnly
Else
MsgBox "Table Does Not Exist"
End
End If

End Sub

The problem is in the following lines, but I can't seem to get it right:

archivetableexists = Nz(DLookup("Name", "MSysObjects", "MSysObjects.Name =" & strArchiveTableName))

How do I get the criteria to read my "strArchiveTableName" variable name?

Thanks!

 

[ReAn]

Try:

archivetableexists = Nz(DLookup("[Name]", "MSysObjects", "MSysObjects.[Name] = '" & strArchiveTableName & "'"))

 

[Joe Z]

Wow, that was quick! Your revised line worked perfectly! Thanks again

Joe

 

[ReAn]

I have found in all the domain appregation, that field names work better if surrounded by [].

Also you just forgot quotes.

 

[Pat Hartman]

I have found in all the domain appregation, that field names work better if surrounded by [].

- The square brackets are only necessary when your names do not conform to accepted VB standards or duplicate the names of functions or properties. In this case "Name" is the name of a property and so should not be used as a column name. Access is entirely too flexible in what it allows for names. VBA is a real programming language and so is more rigid in its naming standards.