Replies

Unfortunately that doesn’t work.
Been doing a different project, so only just got back to this.

Application.Wait is not available in MS ACCESS, at least not in 365 Click to Run

So you have to call it from Excel via the Excel Object

oXL.Application.Wait (Now + TimeValue(“00:00:10”))

But for some reason you then hit the same old object lag time runtime issue as before.

Looks to me at the moment, that the only solution is to use the Inputbox Pause.

Strangely, on a local PC, this only gets triggered once.

When I ran it on a Cloud based file and database via the client, it actually triggered the need to put the same pause into the error trap.
I did add a counter in that to make sure it didn’t loop for ever.
So far, it has never needed more than 3

This appears to be either a local bug, or maybe a new bug in Access.

I may dig out an old laptop running Office 2010 and see if it was an issue back then.

 

RetiredGeek wrote:

Andrew,

I’ve not seen this before but it may be a timing issue.

Instead of a break point try adding this code:

 Dim x As String
 x = InputBox(“Press any key to continue”, “Test”)

in it’s place. If the code runs after you press enter it’s a timing issue that was somehow introduced, maybe an update to excel? You can look in the Windows Update settings to see what updates were installed and when and if you find one for Excel you might try uninstalling it.
HTH

Good shout!

Here’s still weird(ish) as code below

Set oxlB = oXL.Workbooks.Open(strTFolder & strTFile)

strPause = InputBox(“Workbook Opened” & vbLf & “Do you want to Proceed”, “Continue?”, “Yes”)
If strPause <> “Yes” Then
       oxlb.close SaveChanges:=False
       oxl.Quit 
Exit Sub

Set oxlS = oxlB.Worksheets(“Title”)

Using the above , it executes correctly.

 

If I use

intPause = MsgBox(“Workbook Opened” & vbLf & “Do you want to proceed”, vbYesNo + vbQuestion + vbDefaultButton1, “Continue…”)
if intPause = vbNo 
  ‘etc

It still fails….
I suspect MsgBox may pause all processing, whilst InputBox doesn’t.

It’s a work around for now… Cheers…

I wonder why there is now a timing issue, that didn’t exist before 14th Dec?

 

The issue appears to be the code fails to recognise the range object if run directly from a command button when asked to clear or change any data on any sheet.
But as I said, placing a code break before the first WITH statement, but after the workbook open line everything works fine on pressing continue.

To test if the object actually exists I added some debug.print lines

placing this debug.print code before the with statement

<pre class=”bbcode_code”> ‘Open the Template
Set oxlB = oXL.Workbooks.Open(strTFolder & strTFile)
Set oxlS = oxlB.Worksheets(“Title”)
Debug.Print “oxl = ” & oXL.Name
Debug.Print “oxlB = ” & oxlB.Name
Debug.Print “oxlS = ” & oxlS.Name
For Each oxlName In oxlB.Names
If Left(oxlName.Name, 3) = “inf” Then
Debug.Print oxlName.Name & ” References: ” & oxlName.RefersTo
End If
Next
DoEvents
With oxlS
‘Clear
‘On Error Resume Next
Set oxlRC = .Range(“infClientID”)
oxlRC.ClearContents
Set oxlRC = .Range(“infClient”)
oxlRC.ClearContents

Generates this output.

oxl = Microsoft Excel
oxlB = IPM3rdPartyExpensesSummaries117.xlsm
oxlS = Title
infAssignmentType References: =Title!$C$11
infBookMark References: =Title!$A$2
infClient References: =Title!$C$7
infClientID References: =Title!$B$7
infCurrency References: =Title!$B$9
infCurrencyName References: =Title!$C$9
infDates References: =Title!$C$8
infFor References: =Title!$C$10
infHomeCountry References: =Title!$C$13
infHostCountry References: =Title!$C$14
infReports References: =Title!$G$5
infReportSelection References: =Title!$A$3:$C$24
infServices References: =Title!$C$16:$C$38
infServicesStart References: =Title!$C$16
infSupplier References: =Title!$C$12
infUpdated References: =Title!$C$5

So, it recognises the Excel Application Object, and the workbook object, and the range objects.
BUT, unless there is a breakpoint in the code, it crashes out at the line

<b>oxlRC.ClearContents</b>

It appears to assign the correct Range to the Object variable <b>oxlRC</b>

It also fails if the line is changed to <b>oxlRC = “”</b>

<i>There is NO protection set on the Workbook, or Worksheets in it.</i>
And the error only appears to be when changing data, not referencing the objects.

<pre class=”bbcode_code”> Set oxlRC = .Range(“infClientID”) ‘Works OK
oxlRC.ClearContents ‘Fails with the error

I even changed the code from the original to use a range object <b>oxlRC </b>, but it still comes up with the <b>Application Defined Error 50290</b>

Very very Bizarre.

Yep..
That’s what I meant re Advanced.

A criteria Calculation that returns True or False in the second Row of Criteria that covers the necessary combined condition.

I might even play around with both ideas and see which one I like best.
But autofilter springs to mind because it means NOT needing an addition hidden extract sheet. :cheers:

I think I see where you are coming from Rory.
Sorry for my misunderstanding.
It had been a LOOOONNG day and I was tired.

Tell me if I am wrong……

If going for AutoFilter method, which would be my preferred route

Loop through all the cells in the relevant column
and if any individual ones match the criteria
Then add the exact cell to the array, unless it is already there, so the result will be an array of all the cells I want.

Makes sense now I am more awake, if that is what you were saying.

OR, for the Advanced filter solution (depending upon which route I take)

Use a formula in the criteria row, that generates either TRUE or False for that row to match the requirement.
So, in my case, I would be looking at an OR() with the contents being contains any of the required values.

If my assumption is right, I agree.
Autofilter looks much simpler.

I will have a re-write to accommodate that method and see where it gets me.

Thanks for feedback.
Next time I will look at messages when I haven’t been up for 19 hours :o::D

That works with distinct values, BUT, when when there are several options all embedded needing wild cards it is the same problem I think.
Unless I have missed a bit of basic logic somewhere.

If I am just going to loop through the data then I may as well write my own filter extract tool.

But advanced filter does it fine.
It is just a bit of a pain building up the filter sequences criteria for an advanced filter because IF you want A or B or C in col 1 and X or Y in column 2 you need a 6 row advanced criteria

e.g

Col 1 = (A or B or C) And Col 2 = (X or Y) logic since OR logic in an advanced criteria needs a separate row so you need

Code:

COL 1       Col 2
----------------
A             X
B             X
C             X
A             Y
B             Y
C             Y

But it isn’t that simple because they are wild card searches
so actually the advanced filter is

Code:

COL 1       Col 2
----------------
*A*         *X*
*B*         *X*
*C*         *X*
*A*         *Y*
*B*         *Y*
*C*         *Y*

Be so easy in SQL eh….

Anyway, I have done it with advanced filter now.
Easy except for compiling the criteria range.

Anyone know if you can pass an array rather than a sheet range to advanced filter?

No, don’t tell me, I have done enough work today anyway.

Be too many fields zeddy, I would have to make them up on the fly for each field
So, if there were 10 wild card variants of 1 field I’d need an extra 4 dummy fields just for that one.
I reckon a lot easier to use an advanced filter.
Shame Microsoft put a daft 2 limit which they have never ever updated.
But then again.
It really ought to be in a database.
No matter how often you tell people that, they still want to use spreadsheets

I GUESS I WAS RIGHT!

They never considered that a requirement… :mad::( 37368-Homer-Good-Grief

I would suggest something like this on an open report button

This allows for ALL or NONE of the filter components to be entered

It is a bit more long winded, but more flexible

Code:

Dim strFilter as string, strThisPart as String
Dim conSPM = """"

'Build Filter Components from data on the form
'I could have done it in a shorter way, but this is easier to debug
'It is always worth considering an err trap as well

If Not IsNull(schoolname) Then
	strThisPart = " [School] = " & conSPM & schoolname & conSPM & " "
	'Build Filter either this is the first bit or we need to add
	If strFilter = "" Then
		strFilter = strThisPart
	Else
		strFilter = strFilter & " AND " & strThisPart
	End If
End If

If Not IsNull(txtstartdate) Then
	strThisPart = " [course date] >= #" & Format(txtstartdate,"mm/dd/yyyy") & "# "
	If strFilter = "" Then
		strFilter = strThisPart
	Else
		strFilter = strFilter & " AND " & strThisPart
	End If
End If

If Not IsNull(txtenddate) Then
	strThisPart = " [course date] <= #" & Format(txtenddate,"mm/dd/yyyy") & "# "
	If strFilter = "" Then
		strFilter = strThisPart
	Else
		strFilter = strFilter & " AND " & strThisPart
	End If
End If

'Now open the Report and pass it the filter
If strFilter = "" Then
       DoCmd.OpenReport ReportName:="attended training courses"
Else
       DoCmd.OpenReport ReportName:="attended training courses", WhereCondition:=strFilter 
End iF

This could have been done more quickly, but since you are new to VBA,
have a look at it and see if you can refine it.

OK, to follow this up, with a possible solution, that is working for me anyway…

I tried a different slant on design changes, which I should have thought of at the start.

I changed the design of the second level (subform) navigation form, on it’s own.
Tested it on its own, and it worked just fine

Then tested it via the top level Navigation form, and it was fine.

It appears that this may only be an issue, when you attempt to change the design of the sub navigation form, from the design screen of the main navigation form.

Maybe it will eventually get fixed, who know.

See attached image for example with working buttons.
Even when the formatting went wrong, the forms worked ok.
You just couldn’t see the text on the buttons…

36107-NavSubFormExampleImage

Thanks Wendell.
I had a feeling it was a bug.
I thought I might be pushing the envelope.
I will try doing a colour set on load and see if that fixed it.
If not I will just do an old school Menu Style form, and wait for Office 20??
So far the main navigation form has behaved exceptionally well.

Just as an additional to this already solved problem.
You could use SUMIF like this

Code:

=SUMIF(DatesOfDeposits,"",ChecksReceived)

This gives the same result, unless I have misread the question.

SUMPRODUCT is a wonderful function, especially with 2 conditions or more in older versions of Excel.
But SUMIF has been optimised for the calculation whereas (and I may well be corrected on this),
SUMPRODUCT is a volatile function.
This means that it recalculates even if none of the cells effecting it are changed.

On a bigger spreadsheet, this can have an impact on performance.

Feel free to shoot me down in flames on that one. :rolleyes:

Unfortunately the setting on the PC is not for exclusive.
I suspect it is something to do with user permissions, or maybe something on the thin client settings.
I reckon I am going to need to sit down with the Network administrator and look through the cases that show this behavior to see if we can find the cause.
If I do get to the bottom of it, I will post back here.

Thanks.
We’ll give it a try.

In case anyone is going to suggest printing it to adobe acrobat, which DOES work ok.
They do not have that.