UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Isopentable Function, Any Version    
 
   
Raas
post Jun 5 2019, 12:32 PM
Post#1



Posts: 610
Joined: 27-January 07
From: Northern Arizona


I can get the function to work, at least I think. But when I execute the code to check to see if a table is already open, I get "true" then it is actually close, and I get "false" when it is open. I also print out the Boolean and get a 0 when it is open and empty when closed. Doesn't make any sense. Any thoughts about what I'm missing in my thinking here?

PUBLIC Function ISOPENTABLE (strname As String, strType As String) As Boolean
Dim isOpen As Variant
If SysCmd(acSysCmdGetObjectState, strType, strName) <> 0 then
isopen = true
End if
End Function

A debug.print returns as indicated above.

Thanks,
Go to the top of the page
 
orange999
post Jun 5 2019, 12:39 PM
Post#2



Posts: 1,921
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I found this in M$oft documentation

CODE
Sub AllTables()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllTables collection.
For Each obj In dbs.AllTables
   If obj.IsLoaded = True Then
    ' Print name of obj.
    Debug.Print obj.Name
   End If
Next obj
End Sub

This post has been edited by orange999: Jun 5 2019, 12:40 PM

--------------------
Good luck with your project!
Go to the top of the page
 
theDBguy
post Jun 5 2019, 01:04 PM
Post#3


Access Wiki and Forums Moderator
Posts: 75,621
Joined: 19-June 07
From: SunnySandyEggo


Hi. Just curious... Why do we need this function? It's highly recommended you shouldn't let your users directly access table or queries.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post Jun 5 2019, 03:59 PM
Post#4


UtterAccess VIP
Posts: 4,693
Joined: 5-June 07
From: UK


CODE
PUBLIC Function ISOPENTABLE (strname As String, strType As String) As Boolean
Dim isOpen As Variant

If SysCmd(acSysCmdGetObjectState, strType, strName) <> 0 then
     isopen = true
End if
End Function


I think your code is slightly off.
strType neds to be an integer; the value passed in can be an access constant, eg actable, acform, acquery etc
also you aren't returning a value for isopentable.


Try this syntax:

CODE
Function IsOpen(strName As String, Optional objType As Integer = acForm)
    IsOpen = (SysCmd(acSysCmdGetObjectState, objType, strName) <> 0)
End Function


for a table I would call
isopen("tablename",actable)

for a query I would call
isopen("qyeryname",acquery)

for a form I would call
isopen("formname" ) acform is not required as it's the default


I use this to wait for a form to close

CODE
docmd.openform "frmName"

while isopen("frmName")
  doEvents
wend


it holds a program exection while a popup form is open - but you can use it without the popup being opened in dialog mode, so you can continue to do other things, if need be, because of the doEvents within the loop.

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
Raas
post Jun 5 2019, 04:16 PM
Post#5



Posts: 610
Joined: 27-January 07
From: Northern Arizona


DBGuy: Maybe I'm doing it incorrectly, but what I have is a form with a combo box that looks up foods. If this is a new food, I go to the NotinList event. That's where this is used. I open up a new food form so the user can input their nutritional values along with the other information. It's then saved to the main food table, but they also want the new foods to be logged separately as well. I'm opening a recordset in the food log table, and inserting the new food into it, then returning to the main input form to continue. I could just close the log table before returning to the main form, and reopen every time there's a new food, but I wanted to see about checking if the table was still open and doing a bypass of the rst.open command and also not have to have the recordset closed after every log posting.

Like I said, maybe there's a better way that I don't know about.
Go to the top of the page
 
theDBguy
post Jun 5 2019, 04:42 PM
Post#6


Access Wiki and Forums Moderator
Posts: 75,621
Joined: 19-June 07
From: SunnySandyEggo


Maybe. Normally, the NotInList event can be coded to accept the new item after the event fires. You may have to post the NotInList event code for us to see if there's anything there that's missing or odd.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
orange999
post Jun 5 2019, 05:54 PM
Post#7



Posts: 1,921
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Raas,
You might get some ideas from this free youtube video.

--------------------
Good luck with your project!
Go to the top of the page
 
June7
post Jun 5 2019, 06:03 PM
Post#8



Posts: 619
Joined: 25-January 16



Don't open tables or queries. Open forms.


--------------------
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Raas
post Jun 5 2019, 08:56 PM
Post#9



Posts: 610
Joined: 27-January 07
From: Northern Arizona


I got the ISOPENTABLE function worked out.

Now for the next step. Is there any way to test a recordset to see if it's open?

As to Opening just forms: One solution doesn't fit all. In this case, it doesn't. That's why I'm asking the questions. Any help?

Thanks, again.
Go to the top of the page
 
theDBguy
post Jun 5 2019, 10:38 PM
Post#10


Access Wiki and Forums Moderator
Posts: 75,621
Joined: 19-June 07
From: SunnySandyEggo


Hi. All I know how to test if a recordset object is open is to use something like:
CODE
If rsobject Is Nothing Then

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
gemmathehusky
post Jun 6 2019, 05:04 AM
Post#11


UtterAccess VIP
Posts: 4,693
Joined: 5-June 07
From: UK


I would normally do this is a not in list event (using the isopen function)
This way you don't need recordsets/tables etc.

CODE
sub notinlist()  'not sure of the header

'confirm the user wants to add new item

domd.openfrom "NewItemManagerForm"  'the form you normally use to set the items up

'now wait for the form to close, I prefer this to opening the popup form as dialog, as you get more control over your entire app - so the doevents allows that.
'a dialog form also waits to close, though. If you don't hold the program execution, the requery fires too soon.

while isopen("NewItemManagerForm",acform) 'acform not necessary, but  ….
   doevents
wend

combobox.requery  'load any new items

end sub



as an alternative, just open your normal maintenance form to add the new items, and have a button on the form to requery the combobox. (or even double click the combobox), and run combobox.requery

--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 
diriutter
post Jun 6 2019, 09:21 AM
Post#12



Posts: 1
Joined: 18-February 19



What about asking user about saving new entry, open form to edit it and response = acDataErrAdded in notinlist event of "parent" form?

This triggers a refresh as well.
Go to the top of the page
 
Raas
post Jun 11 2019, 04:33 PM
Post#13



Posts: 610
Joined: 27-January 07
From: Northern Arizona


I guess I did the addition using a recordset since that was how I was "taught". Not to work directly in tables unless absolutely necessary. Use recordsets instead. So, I do, I try, anyway.
Go to the top of the page
 
theDBguy
post Jun 11 2019, 04:47 PM
Post#14


Access Wiki and Forums Moderator
Posts: 75,621
Joined: 19-June 07
From: SunnySandyEggo


Hi. No offense but when I tell people not to use tables directly, I usually mean use a form instead - not a recordset.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
Raas
post Jun 12 2019, 07:31 PM
Post#15



Posts: 610
Joined: 27-January 07
From: Northern Arizona


No offense taken! I'm "still" in the learning process, and I do things the way I know how, until someone or something can send me a better direction. I'll be revamping my project somewhat (actually start over) and try some of the suggestions given here.

When I started dabbling in Access, I was using forms for everything, then someone or more, said to use recordsets instead. So I had to learn recordsets, and thanks to UA, I was able to do that. So I used them almost exclusively, then I went to a hybrid use and blended them together, then flip-flopped. Back to the drawing board. Sometimes I get into a rut in one area and don't always think about the other areas. At 3/4 of a century old, maybe I'm just too old to be trying to learn all of this, but for me to stop learning and doing would mean I just as well go to the grave, so I'll keep trying, a little at a time and maybe I'll be 10% as smart as others are.

Thanks to all.
This post has been edited by Raas: Jun 12 2019, 07:38 PM
Go to the top of the page
 
gemmathehusky
post Jun 13 2019, 04:01 AM
Post#16


UtterAccess VIP
Posts: 4,693
Joined: 5-June 07
From: UK


I would say, In general use recordsets only sparingly, when no other approach is available.

for instance
-use forms bound to queries to present and manage data as a rule
-use action queries to update data, or append data
-use dlookups to read values

now - sometimes you need to read multiple lookups from the same record. In that case you can open a recordset containing just that record, and read all the values from the recordset,
alternatively, the action query (or process) you want to do can't be done with an action query - maybe the query you need is not updateable - in which case you can instead use a recordset of the items you want to manage and process them one at a time.

but in general, iterating recordsets is a slower process than a bulk query, and requires more careful attention to avoid logic errors.


--------------------
Dave (Male)

(Gemma was my dog)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    26th June 2019 - 07:07 PM