Q: Find if table is open?

[Mark]

Is there a way to find out if a table is open? something similar to
currentproject.AllForms("MyFormName").IsLoaded

Thanks,
-Mark

 

[Douglas J. Steele]

I don't believe there is, but then you should never be working directly with
the tables: you should always be using forms.

 

[Mark]

Hi Doug,

Thanks for the response.Yeah, I knew someone was going to say that.

Basically, it's just a "helper database" that takes in a table and checks
some stuff then returns the results in a query. So, it's not a system of
record and it doesnt' matter if the table gets messed up because you can just
reimport the external data.

But one of the steps is to select the primary key and it requires me to open
the table for the user to look through to find it. I want to pause the code
until that table is closed. Since the user can import any arbitrary table,
there is no consisten file structure, so I can't really build a form around
it.

 

[George Nicholson]

DoCmd.OpenTable strTableName, acViewNormal
Do While MyIsLoaded(strTableName,acTable)
' pause code until table is closed
DoEvents
Loop

'(In a general module)
Public Function MyIsLoaded(strObjName As String, Optional lngObjType As
acObjecttype = acForm) As Boolean
' Returns True if strName is Open (non-zero), False(0) otherwise.
' Should return 0, not an error, if the object doesn't exist
' Default Object is Form
On Error Resume Next
MyIsLoaded = (SysCmd(acSysCmdGetObjectState, lngObjType, strObjName) <>
0)
End Function

 

[Dale Fye]

Rather than opening the table (which is editable) to allow the user to select
a PK value, you should either present them with a listbox, combo box, or
continuous form that displays the information they need to determine what PK
to use.

I "NEVER" open a table for my users. They can view and edit data in forms
but I NEVER let them actually open a table. This has too many possibilities
for grevious mistakes to occur.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Douglas J. Steele]

Why make them select the primary key? What if they select wrong?

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim idxCurr As DAO.Index
Dim fldCurr As DAO.Field
Dim strTableName As String
Dim strPK As String

strTableName = "Discs"
Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr.TableDefs(strTableName)
For Each idxCurr In tdfCurr.Indexes
If idxCurr.Primary = True Then
For Each fldCurr In idxCurr.Fields
strPK = strPK & fldCurr.Name & vbCrLf
Next fldCurr
End If
Next idxCurr

If Len(strPK) > 0 Then
MsgBox "The PK for " & strTableName & " is:" & vbCrLf & strPK
Else
MsgBox "There is no PK for " & strTableName
End If

 

[Mark]

I guess it helps to go into the reason for creating this.

Basically, I want the user to import a table (which will come from Excel and
will have headers). One column will contain the ID number, which isn't named
consistently, but is usually one of the first columns. The whole point of the
db is to find rows/fields that have invalid characters and spit out a table
with those invalid characters. Those then get sent to a group to fix.

Now, basically it's me using this, so I'm building this internally and not
for production. So I just wanted a simple thing and hence why I'm just using
message boxes, input boxes and not forms. The other people who would use this
are internal analysts and they'd know if they selected the wrong field.

With production ds, I do follow those general guidelines, but in this case,
I just wanted it quick and dirty.

 

[Mark]

Thanks! Works great!

-Mark

DoCmd.OpenTable strTableName, acViewNormal
Do While MyIsLoaded(strTableName,acTable)
' pause code until table is closed
DoEvents
Loop

'(In a general module)
Public Function MyIsLoaded(strObjName As String, Optional lngObjType As
acObjecttype = acForm) As Boolean
' Returns True if strName is Open (non-zero), False(0) otherwise.
' Should return 0, not an error, if the object doesn't exist
' Default Object is Form
On Error Resume Next
MyIsLoaded = (SysCmd(acSysCmdGetObjectState, lngObjType, strObjName) <>
0)
End Function