Error 5941 With Odd Source (1 Viewer)

[Good Looking Bloke]

I am helping with a word manipulating Access app written by someone the company lost contact with. This is not my strong area at all, however, most of the time I can sort it out. It's been working well for a while now. Then in the last week, it's suddenly spinning odd errors. Some I have simply rewritten the same code using ADO or some other alternative, but this one is proving elusive and difficult.

The code generating the error is

If blnChallenge Then
startRange.ConvertToTable (vbTab)
doc.Tables(doc.Tables.Count).Columns(1).Width = 1.85 * 28.35
doc.Tables(doc.Tables.Count).Columns(2).Width = 11 * 28.35
doc.Tables(doc.Tables.Count).Columns(3).Width = 6 * 28.35


The line generating the error is

doc.Tables(doc.Tables.Count).Columns(2).Width = 11 * 28.35

which is adding confusion to my confusion. I mean why not error on Column 1?

Adding to my confusion in one instance the code ran past this with no issue, generating a different error later, only to generate the 5941 error description 'The requested member of the collection does not exist' again.

Any available suggestions welcome.

 

[SHANEMAC51]

which is adding confusion to my confusion. I mean why not error on Column 1?

maybe there are less than 2 tabs in the first line of the text that is being converted, for example, if there is 1 tabulator, then a table of 2 columns is created

 

[SHANEMAC51]

you have 1 tabulator in all lines

 

[Good Looking Bloke]

As I said this is not my area. I am not following what you mean by this?

I also don't understand why it has been working and suddenly decided not to?

Thanks

 

[Gasman]

What is the error message?
Perhaps a pic of it.
Another person just posted and error number, but when they posted a pic of the actual message, it was a lot clearer.?

Why do some people think some know the error numbers off by heart. perhaps common ones like Type Mismatch, but come on?

 

[SHANEMAC51]

As I said this is not my area. I am not following what you mean by this?

I also don't understand why it has been working and suddenly decided not to?

Thanks

some kind of problem with the selection of text for conversion to a table via a tabulator

 

[Gasman]

Adding to my confusion in one instance the code ran past this with no issue, generating a different error lat

That code only happens if blnChallenge is true?

I can see now that you mentioned the error text right at the bottom. Apologies

Could it be that you have only created one column with that data?,

Generally (except for the latest MS bugs) if a program that had worked flawlessly for years, suddenly stops working, the data is at fault?

I'll harp back to my Mantra 'Walk through the code with F8'

 

[arnelgp]

so this is Word VBA (doc.table.count).
the question is what is the constant 28.35? is it the page width (minus the margin?).

 

[Good Looking Bloke]

The question is what is the constant 28.35? is it the page width (minus the margin?).

Yes I think this is pages width

How I read the code, is it is opening a word document from Access and manipulating the properties of the word document. I did a tiny bit of word code years back but it was with bookmarks and forms inside word. Never manipulated it from Access. I am simply last one standing so this is falling to me.

Could it be that you have only created one column with that data?,

I didnt create this code.

Generally (except for the latest MS bugs) if a program that had worked flawlessly for years, suddenly stops working, the data is at fault?

The data input hasn't changed the contents does but the data input and formats hasn't changed. Just these sudden oddities in the code with no clear cause.

For example, some DAO recordset that has worked suddenly started throwing an error. The SQL was fine. The data was fine. I tried rewriting it various ways and it wouldn't stop creating the error. Then I rewrite it as ADO and suddenly the error stops.

Thinking the code is older and perhaps corrupted over time, I imported everything and reset references and so on, however, this hasn't fixed it.

It's very odd.

 

[SHANEMAC51]

so this is Word VBA (doc.table.count).
the question is what is the constant 28.35? is it the page width (minus the margin?).

72 points=2.54 cm, so 1cm=28.35 points, a unit of measurement in WORD

 

[arnelgp]

this code:
doc.Tables.Count

refers to the Total number of tables in the word doc.
so:

doc.Tables(doc.Tables.Count)

refers to the "last" table in your doc.

you should add Breakpoint and enter this:

doc.Tables(doc.Tables.Count).Select (it will highlight the table).
now you can see which table is being referred in the code.

 

[arnelgp]

which is adding confusion to my confusion. I mean why not error on Column 1?

a table will always have Column 1, so just a guess, the Faulty column may not exists since that is what error 5941 for?

 

[Good Looking Bloke]

Well, I am confused, however, the issue is fixed...at least for today.

I am confused because although I added a fix, the fix isn't used in the code.

What I mean is, I modified to the below:


With doc.Tables(doc.Tables.Count)
.Select
If .Columns.Count < 3 Then
Do Until .Columns.Count = 3
.Columns.Add BeforeColumn:=.Columns(.Columns.Count)
Loop
End If

End With

doc.Tables(doc.Tables.Count).Columns(1).Width = 1.85 * 28.35


doc.Tables(doc.Tables.Count).Select

If iRings = 5 Or iRings = 6 Then

doc.Tables(doc.Tables.Count).Columns(2).Width = 9 * 28.35
doc.Tables(doc.Tables.Count).Columns(3).Width = 8 * 28.35

Else

doc.Tables(doc.Tables.Count).Columns(2).Width = 11 * 28.35
doc.Tables(doc.Tables.Count).Columns(3).Width = 6 * 28.35

End If


However even though I added it when I F8 through the code it is skipping over:
If .Columns.Count < 3 Then
Do Until .Columns.Count = 3
.Columns.Add BeforeColumn:=.Columns(.Columns.Count)
Loop
End If
as it sees 3 columns anyway.

This has fixed the error however as I read it, given it is skipping adding any columns it isn't actually doing anything at all?

 

[GPGeorge]

This probably can't be emphasized enough. When a process works for an extended period of time and then stops working (whatever that actually means in any given situation), look at the data. In criminal investigations, the mantra is "Follow the money". In our world, the mantra should be "Follow the data". In light of the report that it suddenly fixed itself, "Well, I am confused, however, the issue is fixed...at least for today. I am confused because although I added a fix, the fix isn't used in the code." I would say, yeah, follow the data fer sure.

 

[Good Looking Bloke]

This probably can't be emphasized enough. When a process works for an extended period of time and then stops working (whatever that actually means in any given situation), look at the data. In criminal investigations, the mantra is "Follow the money". In our world, the mantra should be "Follow the data". In light of the report that it suddenly fixed itself, "Well, I am confused, however, the issue is fixed...at least for today. I am confused because although I added a fix, the fix isn't used in the code." I would say, yeah, follow the data fer sure.

Thanks. I did that.

Last month the same report ran. I reloaded the same data that ran fine and guess what? Error 5941.

This is real test as the data is loaded the report run. The next set of data loaded overwrites the previous data etc.