multiple default value in a field

[freelancer]

I want to add a default value to a field in a table based on a form I am
using. I am building a library database and would like to add each entry
via a form created for a specific type of record e.g If I want to create a
book entry I would select from my primary menu ‘create new book entry’ this
would open a form which has fields that relate specifically to a book [e.g.
ISBN etc]. I intend to create a form for each type of entry or record e.g.
book, journal, article, folder, other etc.
Within the main dbase table I have a ‘Cover Type’ field which indicates what
type of entry has been made as listed above. I want to be able to have the
form inset a de-facto type of entry to this field based on the specific new
entry I have created. Simply, if open the ‘create new book entry’ the value
in the ‘cover-type’ field will be book; similarly for the other types of
entry.
How can I have a default value based on a form rather than having one
default in a field in the main table?
I hope this makes sense and thanks, as ever, for the feedback.

 

[Allen Browne]

You do realize you can set the Default Value property of a control on a
form, independently of any DefaultValue for the field it is bound to?

Another alternative is to assign a value to the control in the BeforeInsert
event procedure of the form rather than use Default Value. This is a good
solution for a subform where the default depends on a value in the main
form.

If you want to do it programmatically, you could create a table to hold the
default values for controls on forms. Then OpenRecordset to get the values
to use for this form, and assign them.

This kind of thing (without error handling, checking bad values, etc.):

Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT tblDefault.* FROM tblDefault WHERE TheForm = """ &
Me.Name & """;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Do While Not rs.EOF
Me(rs!TheControl).DefaultValue = """" & rs!TheValue & """"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

 

[freelancer]

Terrifyingly simple. Thanks!

You do realize you can set the Default Value property of a control on a
form, independently of any DefaultValue for the field it is bound to?

Another alternative is to assign a value to the control in the BeforeInsert
event procedure of the form rather than use Default Value. This is a good
solution for a subform where the default depends on a value in the main
form.

If you want to do it programmatically, you could create a table to hold the
default values for controls on forms. Then OpenRecordset to get the values
to use for this form, and assign them.

This kind of thing (without error handling, checking bad values, etc.):

Dim rs As DAO.Recordset
Dim strSql As String
strSql = "SELECT tblDefault.* FROM tblDefault WHERE TheForm = """ &
Me.Name & """;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Do While Not rs.EOF
Me(rs!TheControl).DefaultValue = """" & rs!TheValue & """"
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to add a default value to a field in a table based on a form I am
using. I am building a library database and would like to add each entry
via a form created for a specific type of record e.g If I want to create a
book entry I would select from my primary menu ‘create new book entry’
this
would open a form which has fields that relate specifically to a book
[e.g.
ISBN etc]. I intend to create a form for each type of entry or record e.g.
book, journal, article, folder, other etc.
Within the main dbase table I have a ‘Cover Type’ field which indicates
what
type of entry has been made as listed above. I want to be able to have
the
form inset a de-facto type of entry to this field based on the specific
new
entry I have created. Simply, if open the ‘create new book entry’ the
value
in the ‘cover-type’ field will be book; similarly for the other types of
entry.
How can I have a default value based on a form rather than having one
default in a field in the main table?
I hope this makes sense and thanks, as ever, for the feedback.