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
> Force Table Save Or Kill Table Save, Access 2016    
 
   
Tke_466
post Jul 20 2019, 09:33 AM
Post#1



Posts: 50
Joined: 25-January 07



I'm using some code to hide a column in a table(s) that is in a sub form. When I close the main form it is prompting me to save the table(s). I don't care to save the table or not (these are temporary tables that I'm building each time with vba). So I'm open to either option... ignoring the prompt or saving. I just need the prompt to go away. I've tried working with code in unload and close. Both kick the prompt out before the event. So I moved to just saving the table(s) after I hide the columns within my other routines. But I get an error.

CODE
Dim ctrl As Control
    Set ctrl = Me!Sub_Carbon.Form!ID
        ctrl.ColumnHidden = True
    Set ctrl = Me!Sub_Tech.Form!ID
        ctrl.ColumnHidden = True
    Set ctrl = Me!Sub_Lime.Form!ID
        ctrl.ColumnHidden = True
    Set ctrl = Me!Sub_Contacts.Form!ID
        ctrl.ColumnHidden = True

    Set ctrl = Me!Sub_ARCH_Carbon.Form!ID
        ctrl.ColumnHidden = True
    Set ctrl = Me!Sub_ARCH_Tech.Form!ID
        ctrl.ColumnHidden = True
    Set ctrl = Me!Sub_ARCH_Lime.Form!ID
        ctrl.ColumnHidden = True
    Set ctrl = Me!Sub_ARCH_Contacts.Form!ID
        ctrl.ColumnHidden = True
Set ctrl = Nothing


I'm testing this by going through and getting all of the tables I want to save. It's tied to a function that is reading all the table names I want. It works great until I get to the docmd statement.

CODE
rawArray = Split(tempTables, ",")
ReDim varArray(LBound(rawArray) To UBound(rawArray))

Dim index As Long
Dim tbl As String
For index = LBound(rawArray) To UBound(rawArray)
    varArray(index) = rawArray(index)
    DoCmd.Save acTable, rawArray(index)
Next index


What are my options??
Go to the top of the page
 
GroverParkGeorge
post Jul 20 2019, 11:03 AM
Post#2


UA Admin
Posts: 35,506
Joined: 20-June 02
From: Newcastle, WA


"...a column in a table(s) that is in a sub form."

Do you mean you have a subform BOUND to this table? And you are changing the visibility of the CONTROLS bound to fields in that table? I think that's what you are actually doing, right?

And, you are actually issuing the DoCmd.Save acTable, rawArray(index) statement which saves the TABLE itself, but not the changes in the controls in the subform, right?

So, if you want to close the subform without saving changes to that SUBFORM, you can do that using the third argument in DoCmd.Close

If that's a misreading of the situation, you can clarify and we'll go from there.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Tke_466
post Jul 20 2019, 12:18 PM
Post#3



Posts: 50
Joined: 25-January 07



Each subform is nothing more than the table (source object = Table.whatever). Attached is the prompt I'm getting. It wants me to save the table. Presumably because I hide a field. I don't need to save this change nor do I need to save the data in the table. But I'll save it to remove the prompt if that's what it takes. It doesn't look like it is asking me to save the subform. It seems so [censored] simple to fix but it's getting my goat at the moment.

Where do I issue the docmd.close? This prompt is firing off before unload and close form events. I am getting this clicking the X (close button) on the main form.

ETA: I stuck DoCmd.Close acTable, rawArray(index), acSaveYes in instead of docmd.save acTable, rawArray(index) (which by the way docmd.save acTable, rawArray(index) yeilds and error saying the object is not open). Anyways the docmd.close doesn't error but appears to do nothing fruitful as the prompt on close still exists.

This post has been edited by Tke_466: Jul 20 2019, 12:31 PM
Attached File(s)
Attached File  Picture1.jpg ( 90.58K )Number of downloads: 8
Attached File  Picture2.jpg ( 121.11K )Number of downloads: 5
 
Go to the top of the page
 
tina t
post Jul 20 2019, 04:34 PM
Post#4



Posts: 6,022
Joined: 11-November 10
From: SoCal, USA


you're not using a subform. a subform is a form object that is displayed in a special "subform control" which sits in another (main) form. don't confuse the subform control with the object it holds. a subform (or subreport) control is essentially a window-inside-a-window. the subform control will hold whatever object is successfully assigned as its' SourceObject - such as a table.

so what you're actually dealing with is an open table. you're manipulating the properties of that table - or perhaps it's more accurate to say a property of one or more fields in the table. it seems to be basically the same as if you open a table directly in datasheet view. if you hide a field (column) or make other changes to the structure, you'll get a prompt when you close the table object, asking if you want to save the changes you made to the table's structure.

i've never dealt with this issue because i don't use tables this way. my only suggestion is that you research how to programmatically save a table object that has been structurally changed. you might try searching on TableDefs and Fields.

i just did a test in an A2016 db: created one table with several fields and several records. created an unbound form, added a subform control, and set its' SourceObject to the table name. i added a command button on the form with the following code, as

Me!MySubformControlName.SourceObject = ""
DoCmd.Close , , acSaveNo

then i opened the form in Form view, manually hid a couple fields (columns) in the table there in the subform control. then, rather than clicking the X, i closed the form using the command button. no prompts appeared. i re-opened the form, and the hidden fields were again visible. i did another hide, and closed the form again with the command button. then i closed the db. i thought i might get a prompt when closing the db itself, but did not. apparently the structural changes were abandoned when the subform control's SourceObject was set to a zero-length string.

i have no idea if this is a good or bad way to do it. i can only say that it worked for me, in the simple test described above.

hth
tina
This post has been edited by tina t: Jul 20 2019, 04:35 PM

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Tke_466
post Jul 20 2019, 05:52 PM
Post#5



Posts: 50
Joined: 25-January 07



tina t:

Yes you are correct. I have to say this is my first time doing these things in this manor. I sensed that it wasn't really a subform, but I didn't know what else to call it. Thank you for the clarification. It may help me find the appropriate direction.

I tried to hide the field originally via tabledefs but I couldn't find a way to do it. Perhaps that is the best way to do it if it is possible then at that time I could save the table changes programatically.

I can see your method working no problem from a command button, but I want the option to hit the X.
Go to the top of the page
 
Tke_466
post Jul 20 2019, 05:56 PM
Post#6



Posts: 50
Joined: 25-January 07



OK I think I'm on the right path now... this works from a command button, so I think that I can mash some of this code into the code I use to build the tables originally and stash the remaining in the sub that utilizes that code. I think I'm seeing my mistake to how I was trying to create that property originally (thus sending me down this unforseen path)... IIRC I was setting to dbtext in the property rather than dbboolean.

CODE
Dim rawArray() As String
Dim varArray() As Variant
Dim dbs As DAO.Database
Dim fld As DAO.field
Dim prp As DAO.Property
Set dbs = CurrentDb

rawArray = Split(tempTables, ",")
ReDim varArray(LBound(rawArray) To UBound(rawArray))

Dim index As Long
For index = LBound(rawArray) To UBound(rawArray)
    varArray(index) = rawArray(index)
    Set fld = dbs.TableDefs(rawArray(index)).Fields("ID")
    Set prp = fld.CreateProperty("ColumnHidden", dbBoolean, True)
    fld.Properties.Append prp
    fld.Properties("ColumnHidden") = True
    DoCmd.OpenTable rawArray(index)
    DoCmd.Close acTable, rawArray(index), acSaveYes
    
Next index

Set prp = Nothing
Set fld = Nothing
Set dbs = Nothing

'Update Temp Sub Forms with new tables
Me.Sub_Carbon.SourceObject = "Table.TEMP_Carbon"
Me.Sub_Tech.SourceObject = "Table.TEMP_Tech"
Me.Sub_Lime.SourceObject = "Table.TEMP_Lime"
Me.Sub_Contacts.SourceObject = "Table.TEMP_Contacts"

'Update ARCH Sub Forms with new Tables
Me.Sub_ARCH_Carbon.SourceObject = "Table.ARCH_Carbon"
Me.Sub_ARCH_Tech.SourceObject = "Table.ARCH_Tech"
Me.Sub_ARCH_Lime.SourceObject = "Table.ARCH_Lime"
Me.Sub_ARCH_Contacts.SourceObject = "Table.ARCH_Contacts"

Me.Sub_Carbon.Form.Refresh
Me.Sub_Tech.Form.Refresh
Me.Sub_Lime.Form.Refresh
Me.Sub_Contacts.Form.Refresh

Me.Sub_ARCH_Carbon.Form.Refresh
Me.Sub_ARCH_Tech.Form.Refresh
Me.Sub_ARCH_Lime.Form.Refresh
Me.Sub_ARCH_Contacts.Form.Refresh
Go to the top of the page
 
Tke_466
post Jul 20 2019, 06:19 PM
Post#7



Posts: 50
Joined: 25-January 07



ARRGHHHHH I just figured it out. The reason I couldn't get the createproperty method to work I had my If statement in the wrong spot. This is why it's important to slow down and step through all the code and not rely on an error popping up. stupid stupid stupid. Now that I have it in this section of the code setting things properly it now works with out any extra junk. Forget setting controls and all that. And no prompts.

CODE
            If fldN = "ID" Then
                Set prp = fld.CreateProperty("ColumnHidden", dbBoolean, True)
                fld.Properties.Append prp
                fld.Properties("ColumnHidden") = True
            End If

This post has been edited by Tke_466: Jul 20 2019, 06:20 PM
Go to the top of the page
 
tina t
post Jul 20 2019, 08:29 PM
Post#8



Posts: 6,022
Joined: 11-November 10
From: SoCal, USA


good job! and thanks for posting the code you used, that helps all of us. :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th August 2019 - 02:29 AM