Determining if an ATTACHMENT field is null

[mminieri]

I have 1 TABLE and 1 FORM with about 30 PAGES (tabbed) on that form. One of the tabbed PAGES contains only an ATTACHMENT field. For any selected record, I make all tabs THAT HAVE DATA visible, otherwise the remain hidden. This is done by checking for NULL on a text box within each tabbed PAGE. The expression used for that are fine, but that expression does not work for ATTACHMENT fields. Anyone have an concise, elegant solution? Thanks

LARGER IMAGE VIEW LINK = http://www.minieriassociates.com/Overview.jpg

[NeoPa]

Can you tell us how your attachment is configured? Is it a Bound Control?

Also, what type of Control is it? Is there a new one in 2016 called Attachment or is the technical name different? I can find no such control in the main list in 2010.

PS. This question is a follow-on from an earlier question you can find at Subform (tabbed page) visible on main form ONLY if subform has data.

[mminieri]

Greetings NeoPa .... This is the NEW control in 2016 called ATTACHMENT. 2016 has a paperclip in the toolbar for placing such a control in a form/subform/page. Using it creates a box one can size. Clicking in that box will then show a small paperclip left of the box LABEL. Clicking THAT paperclip will open a traditional UPLOAD/ATTACHMENT dialogue. While I believe it allows multiple attachments in each box (has Vertical scroll arrows next to the label) I use each box for only 1 attachment (because image files will display the image and I want all of them shown). Documents will display an ICON for the doc file type. I have 3 such boxes on a page, and 3 page, for a total of 9 boxes but using only the 1st box on each page for checking if null. Some research shows some examples of how to determine if null ... most using a complicated "recordcount" function. I'm looking for an example expression that even an Access Moron (me) can understand.

[NeoPa]

Greetings. It sounds like I can't be a great deal of help on this one.

What I would say though, if you feel up to it, is to look at some examples of when it's populated and when it's not and see if you can determine some sort of consistent and reliable setting of any of its properties that will show which state it's in. Then you can use this new understanding to check for that property in your code.

Good luck :-)

PS. Maybe Access Newbie, but not Access Moron. You don't come across as that at all.

[PhilOfWalton]

This seems to work.

Option Compare Database
Option Explicit
 
Function TestAttachments()
 
    Dim MyDb As Database
    Dim Rs As Recordset
    Dim RsChild As Recordset2
 
    Set MyDb = CurrentDb
    Set Rs = MyDb.OpenRecordset("SELECT Cafe.* FROM Cafe")
    Set RsChild = Rs.Fields("Attachment").Value
 
    If RsChild.RecordCount <= 0 Then
        MsgBox "No Attachments"
    Else
        MsgBox "Attachments"
    End If
 
End Function
 

I've often wondered what Recordset2 was for.

Phil

[NeoPa]

To check for an empty recordset it's generally safer to check for (.BOF AND .EOF) as .RecordCount is often found to be unset until all the records have been buffered. Large recordsets particularly can show as zero when they are anything but empty. One to remember.

[PhilOfWalton]

Yes, your right

changing

If RsChild.RecordCount <= 0 Then
 

to

If RsChild.BOF And RsChild.EOF Then
 

works as well

Phil

[mminieri]

Thanks Gentlemen,

Advanced apology for some ignorance here. Start with more info;

the accdb is named INCIDENT_REPORTS

the one and only table is named INCIDENT_REPORTS_MAIN

the FORM is called INCIDENT_REPORT

The tabctl/page on that form is called ATTACH13 ''''its also the tbl column heading

The ATTACHMENT field is called ATTACH1

The event trigger is the result of a selection of a record using a combobox AFTER UPDATE property

My attempt to apply PhilOfWalton's example is;

Option Compare Database
Option Explicit
 
Function TestAttachments()
 
    Dim INCIDENT_REPORTS As Database
    Dim Rs As Recordset
    Dim RsChild As Recordset2
 
    Set INCIDENT_REPORTS = CurrentDb
    Set Rs = INCIDENT_REPORTS.OpenRecordset("SELECT* FROM ATTACH1")
    Set RsChild = Rs.Fields("ATTACH1").Value
 
    If RsChild.BOF And RsChild.EOF Then
        MsgBox "No Attachments"
    Else
        MsgBox "Attachments"
    End If
 
End Function

This brings up INVALID INSIDE PROCEDURE for the first line OPTION COMPARE DATABASE so I haven't progressed very far.

[PhilOfWalton]

Please ignore the last post. I hit the Tab key by mistake and the post was created.

Suppose your Combo Box is called MyCombo.

The code then becomes

Private Sub MyCombo_AfterUpdate()
 
    Dim MyDb As Database         ' You can call it anything you like
    Dim Rs As Recordset
    Dim RsChild As Recordset2
 
    Set MyDb = CurrentDb
    Set Rs = INCIDENT_REPORTS.OpenRecordset("SELECT* FROM 
        INCIDENT_REPORTS_MAIN")         ' This is your table name
    Set RsChild = Rs.Fields("ATTACH1").Value
 
    If RsChild.BOF And RsChild.EOF Then
        MsgBox "No Attachments"
    Else
        MsgBox "Attachments"
    End If
 
End Sub
 

Note ALL Modules should start with

Option Compare Database
Option Explicit
 

The original code I sent you was for a "Stand Alone" Module.

Hope this resolves your problem.

I would add in passing that your table structure looks most unusual.
You appear to be limited to 4 vehicles, and assuming PERS is persons, 4 persons. I don't know what the other pages represent, but normally there would be a table of vehicles and a table of persons.
If I am correct, I suggest you read up on Database Normalisation. There are many articles, but I have attached a simple guide.

Phil

[NeoPa]

MMinieri:
This brings up INVALID INSIDE PROCEDURE for the first line OPTION COMPARE DATABASE so I haven't progressed very far.

That indicates that you have copied and pasted the suggested code at a point in your existing module that is within an existing procedure. That would be a mistake. The suggested code was designed to be the whole, or at least the starting part, of a module in its own right.

Simply take the top two declarative lines and, after ensuring your existing module matches them already, drop them. Then take the rest and drop it into your existing module outside of any of the existing procedures. What was posted for you was a procedure in its own right (plus the two declarative lines you can ignore now) and cannot be embedded within any other.

As for Normalisation, we also have a very popular and helpful article on this site (Bytes.com) that many, many people have found helpful called Database Normalisation and Table Structures.

[mminieri]

Thanks for the replies. I have never created or used a MODULE so did not recognize that. Solve my last ("Invalid Procedure") issues by adding the MS 2016 in the DAO library reference. Normalization is not important at present, but I agree.

From PHil's last code - at LINE 8 - I now get the ERROR 91 "not defined" hangup.

[PhilOfWalton]

Sorry, my mistake.

Line 8 opens a recordset in the current database
Line 3 defines MyDb as the current database, so line 8 should be

   Set Rs = MyDb.OpenRecordset("SELECT* FROM INCIDENT_REPORTS_MAIN")
 

I had copied the code from an earlier posting, and omitted to change INCIDENT_REPORTS to MyDb.

Again apologies

Phil

[NeoPa]

MMinieri:
Normalization is not important at present, but I agree.

While I won't argue with you, I will certainly point out that such an approach can be very dangerous to you, in as much as it can lead to seriously increasing your levels of work and frustration going forward.

Trying to build a working and robust database without an understanding of normalisation is a bit like trying to build a pyramid without an understanding of trigonometry. It won't stand the test of time. You have been warned!

[mminieri]

NeoPa ... The "warning" is appreciated. My intent is that once the functionality is such that is will "DEMO" to my client and they approve, I can do the work to breakout the TABLES.

Phil ... The coding produces the message, thanks. No in place of the MsgBox, my original purpose is to display the TAB for the ATTACH13 (also ATTACH46 and ATTACH70)IF those pages are not NULL by checking the first box (ATTACH1, ATTACH4 and ATTACH7 on each of those pages. My changes to IF / THEN statements must be incorrect though there is no error, but it does not function as intended. My current coding is;

  Dim MyDb As Database
    Dim Rs As Recordset
    Dim RsChild As Recordset2
     Set MyDb = CurrentDb
    Set Rs = MyDb.OpenRecordset("SELECT* FROM INCIDENT_REPORTS_MAIN")
    Set RsChild = Rs.Fields("ATTACH1").Value
     If RsChild.BOF And RsChild.EOF Then
        Me.ATTACH13.Visible = False
    Else
        Me.ATTACH13.Visible = True
    End If
 

[PhilOfWalton]

I think you're on the right track, but if you read your code

Set RsChild = Rs.Fields("ATTACH1").Value
 

checks whether there are any attachments to ATTACH1

Then depending on the result, you try to show or hide a totally different page (ATTACH13).

Obviously all the code must refer to the same Page.

Phil

[mminieri]

Please read the note to you in P17. There are 3 "pages" (tabbed subforms on the main form) and EACH page has 3 "ATTACHMENT" boxes. It is the page (ATTACH13) that contains the box (ATTACH1) that I am trying to make visible.

[twinnyfo]

Gents,

I applaud all y'all's hard work on this, and I am learning a bit about Attachment fields--mostly that I am glad I avoid them when I can.

I prefer to use a text field with a Path\Folder\Filename of the image I want and then simply use an image control with a linked image which is set to the filename at run time. I know this is not always possible, but even in cases in which the DB is in a networked environment, the files can be saved in that same networked environment.

Concerning usage for the task at hand, simply see if the text field is null. If it is, then there is no image and make the page invisible.

This method also reduces the amount of DB bloat tha can occur from saving images within the DB. It's typically not a big deal until you get into larger DBs, but I found that it can slow things done and increase the overall size of your BE.

Please note, this post is not intended to "hijack" or "derail" the discussion thus far. If OP so desires, we can also start a new thread along these lines.

Even though you offered a penny for my thoughts, I put my two cents in....

:-)

[PhilOfWalton]

OK. I'm lost.
You have a Tab Control with 31 pages on it, including ATTACH13, ATTACH46 & Attach79.

You refer to a Box (ATTACH1). As far as I know, there is no control called a "Box"

So what sort of control is ATTACH1, and where is it?

Phil

[mminieri]

Sorry Phil ..... was being lazy and using a shorthand. When I said BOX ..... I mean the "ATTACHMENT" field (new 2016 control by that name) which looks like a box. It is the FORM that has 31 Tab Controls on it, including ATTACH13, etc. It is that Tab Control that has "fields" ATTACH1, ATTACH2 and ATTACH3 on it.

twinnyfo .... always open to a better way and may have to pursue your method. Can you point me to info on that technique please?

[twinnyfo]

mminieri,

Just in case you're interested, someone just posted an article on this very same question!

[PhilOfWalton]

Sorry, I' still struggling.

Please confirm that there are 31 Tab Controls, each with ONE Page on them.

Please confirm that on TAB Control "ATTACH13" You have a Page. I can see that the Page's Caption is "ATTACH13", but what is the name of the Page?

If I can't get any further, would you like me to have a look at your Db, If so please send me a Private Message.

Phil

[twinnyfo]

The opening images indicate one (1) Tab Control with Thirty-One (31) Pages on that One Control. The Pages, rather than requiring reference through the Tab Control, can be referenced Directly. Thus Me.ATTACH13 refers to the

PAGE

named ATTACH13.

I think there is something fundamentally irregular about this approach. One of the earliest posts (perhaps from a previous thread) indicated "Sub-Forms", yet, based upon the images, I see no Sub-Forms embedded in the main form. Were this a simple case of Sub-Forms, any time a related table did not have a related record, anything on that Sub-Form would be Null--easy breezy. Hide the page.

However, in this case, we are using the Current Record, and we are looking at the content of Attachment Fields. So, one of my first questions would be, Let's say we can get this Page to go invisible if there is no attachment. What if someone wanted to Add an attachment? They can't because the Page is invisible.

So, from the outset I have been confused, but have been biding some time, waiting to see where this would go.

I have a Tab Control in my work DB that I make various pages visible and invisible based on what we are doing in the DB. I don't have to, but I do, because if there is no data to show, then just hide the thing. However, I don't query the data to see if I am going to hide it or not. If I am moving to the next stage of the process, I clear the data and hide the form (Me.pgName.Visible = False). I know the exact same situation is not here, so it may not apply so easily.

However, this is a long thread that seems to be spinning its wheels--not to mention the structural challenges already mentioned. And, each time the table is queried, it looks like it is just pulling all records, rather than the specific record which is currently on the form (unless, of course, there is only one record, which would be highly unlikely).

As I look at things, this should be the answer:

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rst2 As DAO.Recordset2
    Dim strSQL As String
    strSQL = "SELECT * FROM INCIDENT_REPORTS_MAIN " & _
        "WHERE IncidentID = " & Me.IncidentID
    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset(strSQL, dbOpenDynaset)
    Set rst = rst2!ATTACH1.Value
    Me.ATTACH13.Visible = Not (rst.EOF And rst.BOF)
    Set rst = Nothing
    Set rst2 = Nothing
    Set db = Nothing
 

This assumes the following:
There is a Primary Key for this Table called IncidentID (must have a primary key for this to work)
ATTACH1 is the name of the Attachment Control on the Page as well as the name of the Field
ATTACH13 is the name of the Page on the Tab Control

I've tried this with one of the few Attachment fields I use and all works fine.

However, I think that this Form has too many convolutions which are very difficult to follow, mainly because there are no proper naming conventions set up anywhere (yeah, I know, I harp on this a lot). But, if the Form were named frmIncidentReports and there was a Tab Control named tbctlIncidentViewer and the Pages of that Tab Control were named pgVeh1, pgAttach1, pgEtc and the Attachment Control on the Page were named atchCrashImage, rather than using the default naming convention of MS Access which simply names everything identical to the Control Source, then it would be a lot easier for us to understand the design of the form without having to ask.

Then Me.atchCrashImage would refer to the Attachment Control--not the field. In the current state, the ambigous names cause confusion for the MS Access engine, as well as those trying to solve this issue.

Hope this hepps!

[PhilOfWalton]

@Twinnyfo.

I am relieved that I am not the only one who is confused.

I believe there is one Tab control with 31 pages, but I have been unable to get those pages in three rows as in Image 2 & 3.

Also notice that the "Help" Page is in a different position between those two images.

Phil

[mminieri]

twinnyfo ..... your code above WORKED! My Super Thanks. I only needed to change "IncidentID" to "IRnumber".

Perhaps this will help with understanding. I have not shown the FULL PAGE of the FORM (anywhere).... only the relevant portion, to save space here.

LARGER VIEW - http://www.minieriassociates.com/Null_Attach.jpg

IMAGE (A) here is the revised IMAGE 1 in the original post.

[PhilOfWalton]

Glad that you have got that working.

The mysterious Recordset2 seemed to be the key.

Phil

[twinnyfo]

@Phil (and others)

The Recordset2 object enables the DB engine to use the parent table’s attributes (such as Attachment Fields (and I think, even Multi-value fields). This allows you to query the contents of the Attachments field and determine if there are any files included.

Glad I could hepp!