Using an Attachment field to set a default logo for forms and reports.

[gemadan96]

I have a database to manage membership. In it is a table called Settings. This table has various vield for setting default values for location, city, state, zip, and logo. Logo is an attachment field. I want the users of the database to be able to attach a logo and then have that logo used on the various forms and reports.

I have created VBA functions to set the get the values from the settings table and use them in the various forms and reports such as:

Function GetDefaultLocation() As String


GetDefaultLocation = Nz(DLookup("Location", "Settings"), 0)
End Function

Is it possible to create a similar function for GetLogo() that will utilize the Settings Attachment field to display the logo on other forms and reports? I cant use a peth to a logo as this will be used by different people on different computer systems for different organizations.

[June7]

Yes, I just tested. DLookup on an Attachment field worked.

Why use a function? You can have the DLookup expression in ControlSource property.

[gemadan96]

Can you give me an example of how you would use the DLookup function to pull to display the logo?

[gemadan96]

The idea behind the VBA function is that for the other fields in the settings tables thos values are used as the default values for various controls throughout the database.

For example the default value for the Location control on the Member Detail form is set to =GetDefaultLocation()

[June7]

The DLookup would be the same as you have for the Location. Just reference the attachment field name.

Could not use in DefaultProperty because embedded files cannot be replicated that way and why would you want to? Why do you need to actually save the location or other data from Settings to other tables?

[gemadan96]

The values in Location might be site A, Site B, etc. When adding members to the database they are assigned to a location.

I've tried using the Dlookup with a control on the form to display the attachment and get no results. I get no error, just a blank box.

[June7]

Must be an Image control. It works for me.

If the Location or image will be different for each member, then the DLookup expressions need filter criteria, otherwise it will always grab value from the first record.

Or include the settings table in RecordSource by joining in query, assuming the settings table record ID is saved in the members record as a foreign key.

[gemadan96]

The location is not the issue and is working exactly as it is intended. It may be different for each member. The logo will be the same for each form and report as there will beonly one record in the settings table. The logo will be different for each organization that utilizes this database on their own computer. Organization A will have a copy of the database with their own data and logo and Organization B will have a copy of the database with their own data and logo. Organization A may have location 1, 2, &3, and Organization B may have Location 4, 5, & 6. The locations within an organization will share the logo. Organizations will not share data or logos. I want each organization to incorporate their own logo in the database. I have everthing setup to add the logo in the Settings table. Now I need the logo, as an attachment in the database to be used on forms and reports in the header.

If you got it to work how did you do it? I have setup both an image and an attachment control on a form. In the properties of the control, on the Data tab I have entered the following for Control Source: =DLookUp("Logo","Settings","ID=1"). I don't get an error and I don't see the logo. So how is your DLookUp written, and where did you enter it?

[June7]

I did the same thing. I put an Image control in the header section of a form and set the ControlSource property to DLookup expression. What image type is the logo? Didn't work with PDF. It does with JPG or PNG.

[gemadan96]

The image is a PNG. Attached is a screen capture for the properties of the control. I'm not sure what else to do to get this to work

[June7]

That looks like what I did in my test. Since I can't replicate issue, if you want to provide database for analysis, follow instructions at bottom of my post.

Alternative is to include the Settings table in the form RecordSource. If the Settings table has only one record, don't even need a JOIN clause. The Settings record will join with every record in the other table (a Cartesian join). This will make the Attachment field available on every record and DLookup would not be needed.

The only other approach is to programmatically set the Picture property of Image control. This was the method required before Access 2007.

[gemadan96]

Here's the database. So far I've only attempted this on the control panel form that opens when the database is opened. The logo should appear in the upper right corner.

[June7]

I get an 'unrecognized database format' error when I try to open your db. First time ever seen that with an mdb. Could be the upload is corrupted.

Why is this an mdb anyway? The Image control does not have ControlSource property in mdb format. I assumed you are using an accdb format because of your member profile.

[gemadan96]

Try this one. It is Access 2010 so it should be an ACCDB file.

The odd thing is I tried it in this file with an Attachment Control and it works in the Control Panel form. However I cant get it to work in any other forms or reports.

[June7]

Now I can't replicate what I did earlier with my db and I'm not sure what I did (didn't save it).

The only option I can suggest is to include the Settings table in the RecordSource of each form. I got that to work with the Rank Details form.

I copy/pasted the Image control from Control Panel to Settings. Also, dragging from the field list works.

Frankly, I think logos on every form is overkill, I don't do it.