Single Field Form - Best Practice (1 Viewer)

[HillTJ]

Hi, I need to build a form that will enable me to save 'default' values, such as a filename & path, maybe a company name, logo etc in one place only to be referenced as required. That is, only one point of reference. This will become the "Default location" rather than having, say the file name & path "Hard Coded" & thus, difficult to amend. I know this may be contrary to Database Principles & may limit the flexibility of the application, but in this "restricted" case it's what I'd prefer. Is there a "Best Practice" way of achieving this? Appreciate the advice.

 

[arnelgp]

place it info in a table, say, tblDefaults

tblDefaults (table)
Entity (string)
value (string)

use dlookup on the Load event of the form to populate your textbox/combo for default value:

me.textCountry = dlookup("value", "tbldefaults", "entity='country'")

you can even convert the "value" to any scalar datatype:

me.phi = val(dlookup("value","tbldefaults", "entity='phi'"))

 

[isladogs]

I often use the same idea as you suggest so clients can 'personalise' the app for their default values.

Using the table as suggested by arnelgp, the thing you need to be aware of is that your form controls have to be unbound which significantly increases the amount of code required. Also each default has to be a string even if its a number value though arnel explained how to deal with that.

The alternative is to have a single record table with a separate field for each default item. This also means you can use different datatypes as required. However you could use up with a lot of fields with a spreadsheet type structure. However you can then use a bound form.

Both methods have advantages and disadvantages. I've used both methods. Use whichever suits your needs better

EDIT
Forgot to say...Value is a reserved word in Access so shouldn't be used as a field name. Instead use ItemValue or similar

 

[HillTJ]

Guys, thank you. I got the idea. With arnelgps approach i can have a whole batch of default values. Excellent. Would this approach work for a logo?, say to put on each form?

 

[arnelgp]

it will, eg:

Private Sub Form_Load()
Me.auto_Header0.Caption = dlookup("Value", "tblDefaults","Entity='Form." & Me.Form.Name & ".Caption'" )
end sub

so if your form name is "Form1", your record should look like this:

Entity: "Form.Form1.Caption"
Value" "the caption here"

 

[isladogs]

My understanding of your logo question was different to arnel.

Enter the full path to the logo in the item value field for an entity called LogoPath or similar. Then use an image control to display the logo

 

[CJ_London]

Would this approach work for a logo?

not as a object/picture unless you have a second column for that data type. however you could store the logo in the a directory (perhaps the same directory as the current db) and store the path as your defaultvalue.

one way to make your app a little bit faster rather than looking up the value each time using dlookup is to put it in a public function with a static variable - something like

function pictureFilePath() as string
static fPath as string

if fPath="" then fPath=dlookup("defaultvalue","tblDefaults","DefaultType='PicturePath'")
picturefilepath=fPath
end function

this means it is only looked up the first time it is required in any session.

or on first opening the form, put the table into a recordset

function getDefault(dType as String) as String
static rs as dao.recordset

if rs is nothing then set rs=currentdb.openrecordset("SELECT DefaultType, DefaultValue FROM tblDefaults")

rs.movefirst
rs.findfirst "DefaultType='" & dType & "'"
getDefault=rs!DefaultValue
'needs code to handle if dType not found
end function

 

[theDBguy]

Would this approach work for a logo?, say to put on each form?

You know, the next time, if I have to tackle something like this, I was thinking of using the Image Gallery. I wonder...

 

[HillTJ]

Guys, excellent. the dBguy, you are correct I was referring to an image file. arnelgp, I shall use your approach for a 'caption' & have a look @ the suggested approaches for the image file. I've just got to digest what I have to do!!. Appreciate it & i hope this thread is of use to other Newby's. I do get the feeling that the questions I ask are almost "standard" Newby questions once you get past the very basics?

 

[theDBguy]

Guys, excellent. the dBguy, you are correct I was referring to an image file.

Hi. If you need us to elaborate on how to use the Image Gallery, just let us know. Cheers!

 

[HillTJ]

theDBguy, yes. Please do.

 

[isladogs]

As DBG appears to be offline, I'll answer on his behalf.

To add one or more images to the Image Gallery, from the Design menu click Insert Image then Browse to your image. Repeat as necessary.
Small thumbnails of each image appear in the gallery.

To use an image from the gallery, select an image then draw out a rectangle on the form or report. The image is automatically inserted. Resize as necessary.

The gallery is simple to use and means you can distribute your file to include all those images. However, as the gallery images are embedded in your file, I recommend only using it for small image files to avoid file bloat.

If your app is going to be used by multiple clients each with their own logo, the image gallery isn't suitable. Instead store the file path as I suggested back in post #6 in an Images subfolder. The client then overwrites the supplied logo in the folder with their own image

 

[HillTJ]

Guys thanks. I've just added arnelgp's recommendation from post 2 & it works! With encouragement from the forum, I make progress & will investigate the gallery concept but I think isladogs is correct in post 12 & I'll have to store a file path (using arnelgp's lookup). Thanks again.

 

[theDBguy]

theDBguy, yes. Please do.

Hi. Glad to hear you already have a solution. Just to add one more point to Colin's post about using the Image Gallery, it allows you to reuse the image on multiple forms or reports. If you update the image in the gallery, all the forms and reports using this image will automatically display the new image.


Good luck with your project.

 

[theDBguy]

As DBG appears to be offline, I'll answer on his behalf.
...
If your app is going to be used by multiple clients each with their own logo, the image gallery isn't suitable. Instead store the file path as I suggested back in post #6 in an Images subfolder. The client then overwrites the supplied logo in the folder with their own image

Hi Colin. Thanks for jumping in. The above quote is exactly what I wanted to play with if I get another opportunity on a future project. I think the company logo is a perfect use for the Image Gallery because it should be just a small image and will probably be used in multiple forms and reports. I would like to distribute the app with a generic or empty logo and then give the client a "Preference or Settings" form where they could select their own logo image. This would then be stored in the Image Gallery so all the forms and reports displaying the logo will automatically display the new image. I have already experimented with this concept a little bit, and I think I can make it work. I'm just waiting on an opportunity to try it out on a real project. Cheers!

 

[CJ_London]

is there a size limit/type for an image in the gallery? Just thinking if there is and the app requires a quality image on reports (invoices and the like) you may be back to filepath

 

[theDBguy]

is there a size limit/type for an image in the gallery? Just thinking if there is and the app requires a quality image on reports (invoices and the like) you may be back to filepath

Hi CJ. The Image Gallery is actually an Attachment field. So, the only size limitation is the file size limit of the database itself (2GB).

 

[HillTJ]

I got an ideal project, Just got to get my head around the concept & have a go!

 

[isladogs]

I've tried using both file path and the image gallery for logos and other set images with multiple clients. Both methods work.
However, in my experience clients found it easy to overwrite a default logo file in a specified folder but were less confident about replacing images in the image gallery.
Also, I can't remember whether image gallery files can be modified in an ACCDE file.

 

[theDBguy]

Also, I can't remember whether image gallery files can be modified in an ACCDE file.

Good question. I think they can but I'll have to check when I get a chance.