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.