CODEWITHSUNDEEP
ms-accessvbams-access-2010
smith22554
smith22554

Reputation: 21

How can I check if an attachment field is empty or not in Access?

I am having some trouble with MS Access 2010. I am trying to modify a database I downloaded, a template from Microsoft‘s web site, I am doing a little modifying to keep track of the food nutrition. I have some experience with basic from the old days of the 16 bit Atari days. I’m not totally green to VBA, just know enough to get in trouble. I have a Form that uses a select query to populate lower half of the form. I added an attachment field to the foods table which has over 8500 record that is the table for the query. My problem is to add VBA code in a module to see if there is a photo present or not. I want to be able to show an icon grayed out for no photo and a regular icon if there is a photo file. But that's for when I get it work to begin with. I call the function in the field properties:

Photo1: chkAttachment([Photo])

This one to start with gives me and error saying: "The multi-valued field '[Photo]' is not a valid in the expression 'chkAttachment([Photo])'. When I change it to:

Photo1: ChkAttachment([Photo].[FileName])

I get and #Error for the empty fields and a "Has Photo" for the one with a file in it.

The Following code is the function I am referencing To Show what I am trying to do

Public Function chkAttachment(fldPhoto As String) As String

On Error GoTo chkAttachment_Err

chkAttachment = ""

'Debug.Print fldPhoto
If fldPhoto = Null Then
    chkAttachment = "No Photo"
Else
    chkAttachment = "Has Photo"
End If

chkAttachment_Exit:
     Exit Function

chkAttachment_Err:
    MsgBox " It don't like Error number: " & Err.Number & " " & Error$
    Resume chkAttachment_Exit

End Function

The query won’t even call the code if the attachment is empty. I Googled the following question, and searched on this web site “Microsoft Access 2010 VBA how to query attachment field” without any luck. I have tried to use some code to count the number of files in the attachments, I found in the thread “How to query number of attachments from Attachment field in Microsoft Access?” from Aug 2011, but couldn’t figure out how to get it to work. When it comes to SQL, I’m in the dark with that part.

Thanks for any help that comes my way.

Steven

Upvotes: 1

Views: 10449

Answers (4)

Madman
Madman

Reputation: 1

I wanted to check if an attachment was made or not before running a query on a form. I made a text box in the form and under control source of the text box, went to expression builder and entered = [name of column which has the attachment].[AttachmentCount]. The text box gave me the count as 0(zero) if no attachment is made and 1 for 1 attachment. I could use the text box to condition run my query. All the above in Access 07.

Upvotes: 0

Terry S
Terry S

Reputation: 23

You can check it in SQL statement " Not (tblxx.Pic.FileData) Is Null " probably in the where clause. I did it this way using a recordset.

Upvotes: 1

parakmiakos
parakmiakos

Reputation: 3020

I believe this is causing the #ERROR in your field

If fldPhoto = Null Then

Correct checking for Null would be the following:

If IsNull(fldPhoto) Then

But then you might still get a Null exception while calling the function. So you might want to edit your overall code to the following:

If (fldPhoto = "") Then

and when calling the function :

Photo1: ChkAttachment(Nz([Photo].[FileName]))

Hope I helped

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123829

If you have an Attachment control named [attachPhoto] on your form and that control is bound to the [Photo] field (i.e., the Control Source of the Attachment control is Photo) then you can just check the value of

Me.attachPhoto.AttachmentCount

to see if the record has any attachments.

(Note that if you don't want the users to actually see the [attachPhoto] control you can just set its Visible property to No.)

Upvotes: 4

Related Questions