Default value

[Gonepostal]

I want to make the default value of a field to equal the value in the ID field and append .jpg to it so that it will automatically populate the field with a value such as 123.jpg where 123 equals the ID field. Is this possible through the properties section or will I need to program it?

Thanks for any help.
Ed

 

[Ken Snell [MVP]]

You cannot do this directly in the table's properties. You can do it on a
form by setting the Default Value property of a textbox that will display
the value:

=[ID] & ".jpg"

Note that the default value will be displayed in this case *only when*
you're creating a new record.

--

Ken Snell
<MS ACCESS MVP>

I want to make the default value of a field to equal the value in the ID
field and append .jpg to it so that it will automatically populate the field
with a value such as 123.jpg where 123 equals the ID field. Is this
possible through the properties section or will I need to program it?

Thanks for any help.
Ed

 

[Douglas J. Steele]

Just to tag along on Ken's correct response.

Remember that simply having a file name (123.jpg) doesn't actually mean that
Access will be able to find it, as Access will append the currnet directory
to that file name, and you don't always know where the current directory is
pointing.

You're better off putting a complete file path. If your intent is that the
jpg files will be in the same folder as the MDB, you should be able to use
=CurrentProject.Path & "\" & [ID] & ".jpg" (assuming Access 2000 or newer).
For previous versions of Access, you can use Left(CurrentDb.Name,
Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & [ID] & ".jpg"

 

[Gonepostal]

Thanks to both of you for you quick answers.

Just to tag along on Ken's correct response.

Remember that simply having a file name (123.jpg) doesn't actually mean
that Access will be able to find it, as Access will append the currnet
directory to that file name, and you don't always know where the current
directory is pointing.

You're better off putting a complete file path. If your intent is that the
jpg files will be in the same folder as the MDB, you should be able to use
=CurrentProject.Path & "\" & [ID] & ".jpg" (assuming Access 2000 or
newer). For previous versions of Access, you can use Left(CurrentDb.Name,
Len(CurrentDb.Name) - Len(Dir(CurrentDb.Name))) & [ID] & ".jpg"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

You cannot do this directly in the table's properties. You can do it on a
form by setting the Default Value property of a textbox that will display
the value:

=[ID] & ".jpg"

Note that the default value will be displayed in this case *only when*
you're creating a new record.

--

Ken Snell
<MS ACCESS MVP>

I want to make the default value of a field to equal the value in the ID
field and append .jpg to it so that it will automatically populate the
field with a value such as 123.jpg where 123 equals the ID field. Is
this possible through the properties section or will I need to program
it?

Thanks for any help.
Ed

 

[peregenem]

You cannot do this directly in the table's properties. You can do it on a
form by setting the Default Value property of a textbox that will display
the value:

=[ID] & ".jpg"

Note that the default value will be displayed in this case *only when*
you're creating a new record.

To avoid storing data redundantly, consider this alternative structure:

CREATE TABLE Widgets (
ID INTEGER NOT NULL PRIMARY KEY
)
;
CREATE TABLE WidgetsWithPics (
ID INTEGER NOT NULL PRIMARY KEY
REFERENCES Widgets (ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
pic_filename VARCHAR(64) NOT NULL
)
;
CREATE VIEW WidgetsPics (ID, widget_pic_filename)
AS
SELECT Widgets.ID,
IIF(WidgetsWithPics.pic_filename IS NULL,
CSTR(Widgets.ID) & '.jpg',
WidgetsWithPics.pic_filename)
AS widget_pic_filename
FROM Widgets
LEFT JOIN WidgetsWithPics
ON Widgets.ID = WidgetsWithPics.ID
;
INSERT INTO Widgets (ID) VALUES (1)
;
INSERT INTO Widgets (ID) VALUES (2)
;
INSERT INTO WidgetsWithPics (ID, pic_filename)
VALUES (1, 'Blah.gif')
;
SELECT ID, widget_pic_filename FROM WidgetsPics
;