default value for a form field

[Pwyd]

How would i make the default value for a subform, a value for the form that
that subform resides on?

i tried

=[Forms]![Main Record]![V_ID]

as the default value but it doesn't like that.

 

[Clifford Bass]

Hi,

That does work for me in Access 2007. In Access 2003 it seemed not to
work as well, only setting the default value to the value in the first record
that displayed when opening the main form. But I may have been doing
something odd with the test. Also, if this is a linking field you only need
to set up the linking. If it is something else, you might try doing
something like the following in the On Current event of the form:

[NameOfSubFormOnMainForm].Form![V_ID].Default = """" & [V_ID] & """"

You may wish to do that also for the V_ID field's After Update event.

Hope that helps,

Clifford Bass

 

[Arvin Meyer MVP]

The subform normally renders before the main form, so you have a timing
issue. In the main form's Current event, try something like:

Sub Form_Current()
Me.SubformControlName.Form!ControlName.DefaultValue = Me.ControlName
End Sub

 

[Clifford Bass]

Hi Arvin,

I would suggest quoting the thing as in my example. Otherwise when you
navigate to a new main record, it will give you an error if your main form
value can be null. This is because DefaultValue is a string and does not
allow nulls. It does however allow zero-length strings.

I do see from your example that mine was slightly off in that I typed
Default when I should have typed DefaultValue.

Thanks,

Clifford Bass

 

[Pwyd]

can either of you explain to me what the difference in usage between the !
and the . is between field and subfield names? I'd been told they're
"interchangeable" but i gather from your post that that is not precisely
correct?

The subform normally renders before the main form, so you have a timing
issue. In the main form's Current event, try something like:

Sub Form_Current()
Me.SubformControlName.Form!ControlName.DefaultValue = Me.ControlName
End Sub

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

How would i make the default value for a subform, a value for the form
that
that subform resides on?

i tried

=[Forms]![Main Record]![V_ID]

as the default value but it doesn't like that.

 

[Clifford Bass]

Hi,

They often are interchangeable. And legitimately so since it is an
allowed thing. The general format is:

[Collection name]![Object name].[Property or Method name]

The square brackets are optional if the name of the thing does not
contain spaces or certain other characters such as +, -, etc. Also, I do not
use square brackets around the build-in property/method names. So from my
corrected example:

[NameOfSubFormOnMainForm].Form![V_ID].DefaultValue = """" & [V_ID] & """"

There is an implicit item of "Me" (Arvin was explicit), which has a
default property of "Controls", which is a collection. Within that
collection is a subform control named "[NameOfSubFormOnMainForm]", which is
an object. "Form" is a property of that subform control. The "Form"
property contains an implicit property, again of "Controls", which is a
collection. In that collection is a control named "[V_ID]". Also, the
default property of a text box is its "Value" property.

In fully explicit form you would have:

Me.Controls![NameOfSubFormOnMainForm].Form.Controls![V_ID].DefaultValue =
"""" & [V_ID].Value & """"

There is one place that I do not follow the usage of the exclamation
(also called a "bang" for some reason) with collections. That is within
queries with tables and columns. The SQL standard is to use a period when
explicitly identifying a column with its table name (i.e.
tblSome_Table.Some_Column).

Hope that clarifies,

Clifford Bass

 

[Clifford Bass]

Hi,

Oops, I forgot to expand the [V_ID] completely. And, you can expand
the whole thing further since the Controls property itself also has a
property of Item. Item is actually its default property. So you could
actually have:

Me.Controls.Item("NameOfSubFormOnMainForm").Form.Controls.Item("V_ID").DefaultValue =
"""" & Me.Controls.Item("V_ID").Value & """"

Clifford Bass

 

[Arvin Meyer MVP]

Try setting the DefaultValue of a textbox to: = Null. You'll find it works.

 

[Clifford Bass]

Hi Arvin,

If I do this:

Private Sub Form_Open(Cancel As Integer)

Text6.DefaultValue = Null

End Sub

I get this:

Run-time error '94':

Invalid use of Null

If I change it to this:

Private Sub Form_Open(Cancel As Integer)

Text6.DefaultValue = ""

End Sub

I do not get any errors.

Clifford Bass

 

[Douglas J. Steele]

Try

Text6.DefaultValue = "Null"

Regardless of what the Default Value is (i.e. Text, Number, Date, etc.), the
DefaultValue property is a string.

 

[Clifford Bass]

Hi Douglas,

That was precisely my point--that it is a string. You need to make
sure you are assigning a string, whether explicit or implicit. And if you
simply assign a value of a field as the default value to another field, you
need to deal with the times when that value may be null. Now, maybe the
easiest method would be to use the Nz() function rather than what I
originally suggested, which was bad due to the use of Default instead of
DefaultValue.

Text6.DefaultValue = Nz([Text5], "")

The fatigue level was rather high last week so my thinking level was
such that I probably should not have been trying to help people. Perhaps I
should quit before I get myself into a bigger hole

Clifford Bass

 

[Douglas J. Steele]

Technically, I believe that should be

Text6.DefaultValue = Chr$(34) & Nz([Text5], "") & Chr$(34)

just as it should be

Text8.DefaultValue = Chr$(34) & Nz([Text7], 0) & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas,

That was precisely my point--that it is a string. You need to make
sure you are assigning a string, whether explicit or implicit. And if you
simply assign a value of a field as the default value to another field,
you
need to deal with the times when that value may be null. Now, maybe the
easiest method would be to use the Nz() function rather than what I
originally suggested, which was bad due to the use of Default instead of
DefaultValue.

Text6.DefaultValue = Nz([Text5], "")

The fatigue level was rather high last week so my thinking level was
such that I probably should not have been trying to help people. Perhaps
I
should quit before I get myself into a bigger hole

Clifford Bass

Try

Text6.DefaultValue = "Null"

Regardless of what the Default Value is (i.e. Text, Number, Date, etc.),
the
DefaultValue property is a string.

 

[Clifford Bass]

Hi Douglas,

You are right. And I really should quit. But nah... Here goes...
And it really should, for text values, be:

Text6.DefaultValue = Chr$(34) & Replace(Nz([Text5], ""), Chr$(34), Chr$(34)
& Chr(34)) & Chr$(34)

Or, slightly shorter version:

Text6.DefaultValue = """" & Replace(Nz([Text5], ""), """", """""") & """"

Clifford Bass