Code not running because form has not finished closing. 1

[puforee]

I have a Form open with a sub-form. The sub-form data is connected, through queries, to a table. I want to update the data in the table. This will require running several queries to update the table. I have the code to do the table updates. Of course it won't run as long as the form sub-form is open. It has the table locked.

I created code, attached to a button on the Form Sub-Form, that runs a macro that closes the form Sub-Form and opens a dialog form. The Open event on the dialog form runs the table update queries. The problem is..it fails with 3211 Error. Even though I closed, with a macro, the Form Sub-form that had the table locked...it still thinks it is locked. If I run the code (queries) separately, without opening the Form Sub-form, the code works fine.

I must assume the Form Sub-Form has not released the lock on the table.

How can I check (looping check) to validate the Form Sub-form is closed and the Table is unlocked before running the update code/queries?

Thanks

 

[dhookom]

Provide your code, record source, and update queries.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Duane,
Sorry it has taken this log to get back to you. I had to find time to give you all the info.

In my DB I am tracking user licenses for a software tool over a broad range of customers. Initially this information is moved into Access, via code, from spreadsheet Tabs. Each customer table is named based on each spreadsheets tab name. This all works good.

My major, Microsim Work Form, allows my users to view/edit each table through a separate form. The edit is controlled by password (PW). The users can also show a report. The reports are not PW protected. These are individually selected by buttons on the form.

The number of Items/buttons will grow over time.

At the top of the Microsim Work Form is a sub-form that shows total licenses based on several categories: Total Licenses, MT Active Licenses, MT Inactive Licenses, Non MT Active Licenses, and Non MT Inactive Licenses.

These values come from a table that was created by several queries that gather data from each customers table. Although all the data in the customer tables in not the same...the data for this sub-form is. I have added a "Refresh License Information" button on the sub-form and this is where the trouble starts. It took about 1 minute to realize I could not update the table supplying the License data from this button. The table was already being used by the sub form.

So, I decided, when the refresh button is pushed, to close the Form and Sub-form and open a Update_License_Dialor_Box form that would run the code.

So, clicking the refresh button calls a macro that Closes the Microsim Work Form and sub-form without saving. Then the Update_License_Dialor_Box form is opened with the same macro. Open form Update_License_Dialor_Box, in read only mode. This works.

My assumption was that by the time the Update License Dialog Box macro ran...the Microsin Work Form would be closed. I had applied the refresh code to the Udate License Dialog Box Open event. Key word - assumed.

I will show the refresh code below but the result is the same as if I still had the Microsim Work Form/Sub-Form still open. It looks like I need some sort of code to validate it is close before the code runs.

So:
Run macro to close Microsim Work Form.
Run macro to open Update_License_Dialor_Box form.
From Update_License_Dialor_Box open event run

'I was told to use this code to ensure the dialog form was fully loaded and visible
'before the main code runs.

DoCmd.SelectObject acForm, Me.Name
Me.Repaint
DoEvents

'This is the refresh code and this is where the error occurs on the first query below.
DoCmd.SetWarnings (Off)
[highlight #FCE94F]DoCmd.OpenQuery "MT_Dev_Inv_MkTbl", acViewNormal, acEdit[/highlight]
DoCmd.OpenQuery "UAL_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "TUI_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "SSE_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "SI_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "Peters_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "MT_CLS_RM_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "MLO_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "LOT_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "InforSys_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "EXT_CUST_LT_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "CZK_Inv_APTbl", acViewNormal, acEdit
DoCmd.OpenQuery "ACN_Inv_APTbl", acViewNormal, acEdit
'This is for new items. DoCmd.OpenQuery "UpdateMS_", acViewNormal, acEdit
DoCmd.SetWarnings (WarningsOn)
DoCmd.Close

End Sub[/code]

On Update_License_Dialog_Box Close Event - Run Macro
Open Form, Microsim Work Form, view Form, Edit, Normal.

Can I give you any more information to help?

 

[dhookom]

Why are you opening all the queries? Are they action queries? It looks like you have table names that store data values.

Why do you have to open queries to get "Total Licenses, MT Active Licenses, MT Inactive Licenses, Non MT Active Licenses, and Non MT Inactive Licenses." Can these be generated from totals queries?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Duane,
The DB has several tables that do not have a common connection field so I have to query each table and pick out the key fields that are common fields between all tables by field name but not data. So, I analyze one table for required data and make a table with the results. The rest of the queries analyze the rest of the tables and append the result to the table that was just made. I end up with one large table from which I can run a totals query to get the data I showed you.

I will add to this message when I get to work tomorrow to explain more.

But, my big question is still...how do I cause the code to run after the main form is verified closed?

Thanks,

 

[dhookom]

I would expect you could get your counts/totals from a query and not need to build and add records to a table.

However you might want to run the code in the On Timer event of the [Update License Dialog Box] form. Set the Timer Interval to about 5 seconds and then your code should set it back to 0.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

I will try the on timer event. I guess the reason I need to run all the queries and then create/append them to a table is because I can't figure out how to use one query to gather the totals. Example: MT Active counts are a combining of information from more than one table. Those tables do NOT have any fields that could be used in a join. The same goes for the other groups of tables. The data I put in the built table allows me to determine the characteristics of each table loaded. Each record in each table contains information about MT or not MT and Active or Not active. This allows me to add things up. If you have an idea I am more than willing to listen.

I will let you know how the On Timer event goes.

Thanks,

 

[dhookom]

I would need to see a sampling/cross section of the queries and the final table.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

OK

The first query to run is a make table query (MT_Dev_Inv_MkTbl). MT stands for our org. that owns a group of licenses. Dev is the category of items in the table being queried. Inv indicates this query is pulling Inventory information from the table.

SELECT MicrosimDev.[MT Inventory Y/N], MicrosimDev.[Active Y/N], "MT_Dev" AS Tbl, 1 AS [Count] INTO MT_Active_Inventory
FROM MicrosimDev;

The rest of the queries are pulling data to append into the MT_Active_Inventory table created by the first query. Second is another MT INV table:

INSERT INTO MT_Active_Inventory ( [MT Inventory Y/N], [Active Y/N], Tbl, [Count] )
SELECT MicrosimMT.[MT Inventory Y/N], MicrosimMT.[Active Y/N], "Mt_CLS_RM" AS Tbl, 1 AS [Count]
FROM MicrosimMT;

Third Is a Non MT table being appended to the MT INV table:

INSERT INTO MT_Active_Inventory ( [MT Inventory Y/N], [Active Y/N], Tbl, [Count] )
SELECT MicrosimMT.[MT Inventory Y/N], MicrosimMT.[Active Y/N], "Mt_CLS_RM" AS Tbl, 1 AS [Count]
FROM MicrosimMT;

There are more queries doing the same thing: 5 more tables with MT Inventory information. And 2 more tables with Non-MT information and these are the tables that will increase in number the most.

MT INV Table. Not sure what to give you here but...

Fields in table:
MT Inventory Y/N Short Text (Yes I know I should use underlines)
Active Y/N Short Text (Yes I know I should use underlines)
Tbl Short Text
Count Number

Sample Data from table

MT Inventory Y/N	Active Y/N	Tbl	Count
Y	                 Y	      MT_Dev	  1
Y	                 Y	      MT_Dev	  1
Y	                 Y	      MT_Dev	  1
N	                 Y	      UAL	  1
N	                 Y	      UAL	  1

From here I can use the first two columns to determine if the data is from MT Inventory (Y) or Non-MT Inventory (N). And, if the licenses are Active (Y) or not (N). TBl shows where the data came from, (Useful if I am required to break down information further), and count allows my output Totals query to calculate the numbers.

Again, there is no common way to link the tables together in one query. All the fields in one table have nothing to do with tables in the other queries until I combine the as shown above.

Does this help?

 

[puforee]

By the way. I used the On Timer event and everything worked well. I got it down to 2 Seconds.

Thanks,

 

[dhookom]

I think you could change the append queries to simple totals queries and union the all together without creating a table.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Interesting. I have never use the union query. I will take a look at it.

I also answered you on the Forms Forum...about using ACCDE.

Thanks,

 

[puforee]

Duane, I have been delayed because of Jury Duty....I have investigated the Union query you recommended....it works great...except.... I use it to populate a report..no problem. within the report I am using conditional formatting to change font colors if Now() exceeds a date on the report. The conditional formatting is not working. I believe it is because the date format is not coming from the Union query. I also looked up formatting for a union query and the code is shown below.

SELECT [MT Inventory Y/N], [Active Y/N], [Name or Location], Format([Expiration_Date],'mm/dd/yyyy') As [Expiration Date], Format([Sent to_Sim Data],'mm/dd/yyyy') AS [Sent to MT], [Charge_Line], [Serial#], 1 AS [Count]
FROM MicrosimDev

UNION ALL SELECT [MT Inventory Y/N], [Active Y/N], [Name or Location], Format([Expiration_Date],'mm/dd/yyyy') As [Expiration Date], Format([Sent to_Sim Data],'mm/dd/yyyy') AS [Sent to MT], [Charge_Line], [Seral#] AS [Serial], 1 AS [Count]
From MicrosimMT

UNION ALL SELECT [MT Inventory Y/N], [Active Y/N], [Name or Location], Format([Expiration_Date],'mm/dd/yyyy') As [Expiration Date], Format([Sent to_Sim Data],'mm/dd/yyyy') AS [Sent to MT], [Charge_Line], [Serial#], 1 AS [Count]
From MicrosimInfosys

UNION ALL SELECT [MT Inventory Y/N], [Active Y/N], [Name or Location], Format([Expiration_Date],'mm/dd/yyyy') As [Expiration Date], Format([Sent to_Sim Data],'mm/dd/yyyy') AS [Sent to MT], [Charge_Line], [Serial#], 1 AS [Count]
From [MicrosimMT Seattle]

UNION ALL SELECT [MT Inventory Y/N], [Active Y/N], [Name or Location], Format([Expiration_Date],'mm/dd/yyyy') As [Expiration Date], Format([Sent to_Sim Data],'mm/dd/yyyy') AS [Sent to MT], [Charge_Line], [Serial#], 1 AS [Count]
From MicrosimP

UNION ALL SELECT [MT Inventory Y/N], [Active Y/N], [Name or Location], Format([Expiration_Date],'mm/dd/yyyy') As [Expiration Date], Format([Sent to_Sim Data],'mm/dd/yyyy') AS [Sent to MT], [Charge_Line], [Serial#], 1 AS [Count]
From MicrosimSI

UNION ALL SELECT [MT Inventory Y/N], [Active Y/N], [Name or Location], Format([Expiration_Date],'mm/dd/yyyy') As [Expiration Date], Format([Sent to_Sim Data],'mm/dd/yyyy') AS [Sent to MT], [Charge_Line], [Serial#], 1 AS [Count]
From MicrosimSSE;

Also, I cannot set the Report Expiration Date field to any format. The Format property dropdown is blank.

Can you add some coaching for this? Do I have to run a normal select query against the union query to try to get the date format recognized?

 

[dhookom]

You are forcing the date values to strings using Format(). You need to leave it as a date or convert the column to a date.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

OK. Before I added the format part, I ran the query without the Formatting. The tables (From) Items all have the Expiration_Date and Sent to_Sim Data formatted as short date. But when the report is opened, with the Union query as the source, the fields do not react as dates. When I apply the conditional formatting it does not work. And I cannot format the report fields as dates. I should have mentioned before that I had already tried the union query without the Format portion. Sorry.

 

[dhookom]

If the dates aren't right aligned they are considered text and you need to convert in the conditional format expression or in the record source.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

As short as your answer was it rates a star. You made me go all the way back to the spreadsheets to ensure the dates were set correctly. They were not. This also meant my tables were set wrong. The dates in the tables were set to m/d/yyyy. I set the all to Short Date...what I originally thought they were. Now everything comes out right and my Conditional Formatting works.

Sometimes it is the small things that cause the bigger problems. Failure to validate the data types I was using caused the problem. You can bet I won't make this mistake again.