Some sort of table corruption?

[GraeagleBill]

I have an split DB app that seems to have a corrupted table in the backend. The problem is the visibility of one of its fields when either the table or query of the table is opened directly, but not an issue when referenced as RecordSource for a form, report or DAO Recordset, there are NO fields "Hidden". One of the app's primary forms has the query of the table as its RecordSource and the data for the troublesome field displays as always:
Query: Form: The form, in design view, clearly reveals that the text box is bound to the troublesome table field "EmailWk".



I put together a general module to audit the table content for the troublesome field:

Option Compare Database
Option Explicit
Private Sub AuditQAsilomar()
Dim rs As DAO.Recordset


Set rs = DBEngine(0)(0).OpenRecordset("QAsilomar")


rs.MoveLast


If rs.RecordCount > 0 Then
    rs.MoveFirst
        While Not rs.EOF
            If Not IsNull(rs!EmailWk) Then Debug.Print rs!EmailWk
        rs.MoveNext
        Wend
End If


rs.Close
Set rs = Nothing


End Sub

The above audit displayed a multitude of email addresses in the IM that are contained in the DB.

I've tried new queries, copying the backend mdb, copying structure only followed by an append, simple copying of the table, even moving the position of the "EmailWk" field in the table's design view.

Once again, I checked the open table view to insure the field IS NOT HIDDEN.

What else can I try?

[ranman256]

this works for me:

try
if not IsNull(rs.fields("EmailWk").value ) then debug.print rs.fields("EmailWk").value

otherwise, it may be your recset.

dim db
dim rs

set db = currentdb

Set rs = db.OpenRecordset("QAsilomar")

[GraeagleBill]

Sorry, I don't understand your reply. As I stated in the OP, my audit code found and printed in the IM all the instances of the non-null "EmsilWk" fields in the query. Does your method do something different? Anyway, I get the same result with your statement:

if not IsNull(rs.fields("EmailWk").value ) then debug.print rs.fields("EmailWk").value

[GraeagleBill]

Based on multiple failures of new queries based on the troublesome table, I've come to the conclusion that I indeed have a seriously corrupted table. That being said, There's quite a bit of infomation in the table, 300 records at 27 fields each.

Seeing as how the code shown in the OP seems to find the fields of interest okay, I can envision a new table with identical structure together with two DAO Recordsets where I move each records worth of fields one at a time to the new table. Is there a way to accomplish the move using some code together with references to the Fields Collection, or am I faced with some grotesque looking code that moves each of the 27 fields one at a time?

[Micron]

What else can I try?

A C/R?

You know that any kind of Move with no records will raise an error, yes? So a MoveLast before checking that there are records isn't the way to do it.
Besides a c/r, could also import the table into a new db and check its behavior in datasheet view. If OK, might want to rename table in current db as tblMyTableOLD and then import from the test db and see if OK. If so, delete OLD.

[GraeagleBill]

import the table into a new db and check its behavior in datasheet view

Tried that in addition to simple c/r but that didn't work either, i.e., field continues to hide from any kind of view.

[Micron]

Then I'm stumped for now. Perhaps post the test db with the table for other sets of eyes?

[GraeagleBill]

Can you believe this! I have no idea how the squeeze occurred, but:

Paul spotted why the field doesn't display:


So the display issue wasn't anything other than a smoke-screen to the real problem, which has to do with queries based on "the table" that when used as RecordSource to a report fails to return the field values. Another OP if I can't find that, as it's a different issue.

[Micron]

Sometimes I/we overlook the obvious, especially when you can't actually see it. So this is your first experience with setting a field width to zero, I take it.

[GraeagleBill]

So this is your first experience with setting a field width to zero, I take it.

Yes, as the saying goes, "Down a rabbit hole" never to return.

It's professionally embarrassing when these kind of issues "Rear its ugly head".

[Micron]

Just do what I do - put it down to old age, regardless of what the number is. Better excuse than booze or drugs, yes?

[GraeagleBill]

put it down to old age

Well, that is the truth of the matter........... SIGH!