Programmatically set a form's default value

  • Thread starter Thread starter Jay
  • Start date Start date
J

Jay

Hi all -

I'm using a button to open a form with the button's Click event procedure.
Is there an effective VBA technique to set the default value property for the
form's <DamageYear> field to the variable 'strYearOfDamage' ?

The following Click event procedure correctly passes the argument
'strYearOfDamage' to the form's Open event procedure, but I'm having trouble
with the syntax for assigning its value as the default value for the
<DamageYear> field.

Private Sub btnDamages_Click()
On Error GoTo Err_btnDamages_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmE_09_DetailedDamageCategories"
strYearOfDamage = Trim(CStr(Me.reportingYear)) '"2000", "2001", "2002", etc.

stLinkCriteria = "[VesselNumber]=" & "'" & Me![A_VesselNumber] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , acDialog, _
strYearOfDamage

Exit_btnDamages_Click:
Exit Sub

Err_btnDamages_Click:
MsgBox Err.Description
Resume Exit_btnDamages_Click

End Sub
- - - - - - - -
Thanks in advance,
Jay
PS: this post will be unattended for about 8hrs.
 
Hi Jay

Each editable control on a form has a DefaultValue property. For a text
field, the value should be enclosed in quotes:

Me.DamageYear.DefaultValue = """" & strYearOfDamage & """"

Note: Two consecutive quotes within a text string are interpreted as one
quote character in the string.
So """" = "
 
Hi Graham -

Thanks for the response. The statement you suggested produces a 'compile
error: method or data member not found', with the DefaultValue keyword
highlighted as the error source. I've applied your suggestion as follows:

1. Statement in btnDamages_Click event that passes the damage year value as
the OpenArgs argument:
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , _
acDialog, strYearOfDamage

2. Statement in the module of the newly opened form that produces the
compile error:
Private Sub Form_Open(Cancel As Integer)
defaultYear = Nz(OpenArgs, "")
Me.DamageYear.DefaultValue = """ & defaultYear & """ '<---Error here
End Sub

Note that the application has a valid reference to the Microsoft DAO 3.6
Library and other standard references.

I've worked around the issue by setting the default year in the table field
(instead of the form control) as follows, but I'd still like to understand
how to set it for the form control:

Private Sub btnDamages_Click()
On Error GoTo Err_btnDamages_Click

intDamageYear = Me.reportingYear
CurrentDb.TableDefs!tblE_09_DetailedDamageCategories. _
Fields("DamageYear").DefaultValue = intDamageYear

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmE_09_DetailedDamageCategories"
strYearOfDamage = strDamageYear

stLinkCriteria = "[VesselSurveyNumber]=" & "'" & Me![A_VesselSurveyNumber]
& "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , acDialog

Exit_btnDamages_Click:
Exit Sub

Err_btnDamages_Click:
MsgBox Err.Description
Resume Exit_btnDamages_Click

End Sub
- - - - - - - -
Jay




Graham Mandeno said:
Hi Jay

Each editable control on a form has a DefaultValue property. For a text
field, the value should be enclosed in quotes:

Me.DamageYear.DefaultValue = """" & strYearOfDamage & """"

Note: Two consecutive quotes within a text string are interpreted as one
quote character in the string.
So """" = "

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jay said:
Hi all -

I'm using a button to open a form with the button's Click event procedure.
Is there an effective VBA technique to set the default value property for
the
form's <DamageYear> field to the variable 'strYearOfDamage' ?

The following Click event procedure correctly passes the argument
'strYearOfDamage' to the form's Open event procedure, but I'm having
trouble
with the syntax for assigning its value as the default value for the
<DamageYear> field.

Private Sub btnDamages_Click()
On Error GoTo Err_btnDamages_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmE_09_DetailedDamageCategories"
strYearOfDamage = Trim(CStr(Me.reportingYear)) '"2000", "2001", "2002",
etc.

stLinkCriteria = "[VesselNumber]=" & "'" & Me![A_VesselNumber] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , acDialog, _
strYearOfDamage

Exit_btnDamages_Click:
Exit Sub

Err_btnDamages_Click:
MsgBox Err.Description
Resume Exit_btnDamages_Click

End Sub
- - - - - - - -
Thanks in advance,
Jay
PS: this post will be unattended for about 8hrs.
Click to expand...
Click to expand...
 
Hi Jay

Remember I said:
That was *four* quotes in a row - one to open the string, two for the quote,
and one to close the string.

Your line of code should read:
Me.DamageYear.DefaultValue = """" & defaultYear & """"

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jay said:
Hi Graham -

Thanks for the response. The statement you suggested produces a 'compile
error: method or data member not found', with the DefaultValue keyword
highlighted as the error source. I've applied your suggestion as follows:

1. Statement in btnDamages_Click event that passes the damage year value
as
the OpenArgs argument:
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , _
acDialog, strYearOfDamage

2. Statement in the module of the newly opened form that produces the
compile error:
Private Sub Form_Open(Cancel As Integer)
defaultYear = Nz(OpenArgs, "")
Me.DamageYear.DefaultValue = """ & defaultYear & """ '<---Error here
End Sub

Note that the application has a valid reference to the Microsoft DAO 3.6
Library and other standard references.

I've worked around the issue by setting the default year in the table
field
(instead of the form control) as follows, but I'd still like to understand
how to set it for the form control:

Private Sub btnDamages_Click()
On Error GoTo Err_btnDamages_Click

intDamageYear = Me.reportingYear
CurrentDb.TableDefs!tblE_09_DetailedDamageCategories. _
Fields("DamageYear").DefaultValue = intDamageYear

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmE_09_DetailedDamageCategories"
strYearOfDamage = strDamageYear

stLinkCriteria = "[VesselSurveyNumber]=" & "'" &
Me![A_VesselSurveyNumber]
& "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , acDialog

Exit_btnDamages_Click:
Exit Sub

Err_btnDamages_Click:
MsgBox Err.Description
Resume Exit_btnDamages_Click

End Sub
- - - - - - - -
Jay




Graham Mandeno said:
Hi Jay

Each editable control on a form has a DefaultValue property. For a text
field, the value should be enclosed in quotes:

Me.DamageYear.DefaultValue = """" & strYearOfDamage & """"

Note: Two consecutive quotes within a text string are interpreted as one
quote character in the string.
So """" = "

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jay said:
Hi all -

I'm using a button to open a form with the button's Click event
procedure.
Is there an effective VBA technique to set the default value property
for
the
form's <DamageYear> field to the variable 'strYearOfDamage' ?

The following Click event procedure correctly passes the argument
'strYearOfDamage' to the form's Open event procedure, but I'm having
trouble
with the syntax for assigning its value as the default value for the
<DamageYear> field.

Private Sub btnDamages_Click()
On Error GoTo Err_btnDamages_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmE_09_DetailedDamageCategories"
strYearOfDamage = Trim(CStr(Me.reportingYear)) '"2000", "2001",
"2002",
etc.

stLinkCriteria = "[VesselNumber]=" & "'" & Me![A_VesselNumber] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , acDialog, _
strYearOfDamage

Exit_btnDamages_Click:
Exit Sub

Err_btnDamages_Click:
MsgBox Err.Description
Resume Exit_btnDamages_Click

End Sub
- - - - - - - -
Thanks in advance,
Jay
PS: this post will be unattended for about 8hrs.
Click to expand...
Click to expand...
Click to expand...
 
Bingo ! Thank you very much Graham. The correct set of quotes made
everything function perfectly as you suggested from the start.
- - - - - -
Have a great day (or evening),
Jay


Graham Mandeno said:
Hi Jay

Remember I said:
That was *four* quotes in a row - one to open the string, two for the quote,
and one to close the string.

Your line of code should read:
Me.DamageYear.DefaultValue = """" & defaultYear & """"

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jay said:
Hi Graham -

Thanks for the response. The statement you suggested produces a 'compile
error: method or data member not found', with the DefaultValue keyword
highlighted as the error source. I've applied your suggestion as follows:

1. Statement in btnDamages_Click event that passes the damage year value
as
the OpenArgs argument:
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , _
acDialog, strYearOfDamage

2. Statement in the module of the newly opened form that produces the
compile error:
Private Sub Form_Open(Cancel As Integer)
defaultYear = Nz(OpenArgs, "")
Me.DamageYear.DefaultValue = """ & defaultYear & """ '<---Error here
End Sub

Note that the application has a valid reference to the Microsoft DAO 3.6
Library and other standard references.

I've worked around the issue by setting the default year in the table
field
(instead of the form control) as follows, but I'd still like to understand
how to set it for the form control:

Private Sub btnDamages_Click()
On Error GoTo Err_btnDamages_Click

intDamageYear = Me.reportingYear
CurrentDb.TableDefs!tblE_09_DetailedDamageCategories. _
Fields("DamageYear").DefaultValue = intDamageYear

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmE_09_DetailedDamageCategories"
strYearOfDamage = strDamageYear

stLinkCriteria = "[VesselSurveyNumber]=" & "'" &
Me![A_VesselSurveyNumber]
& "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , acDialog

Exit_btnDamages_Click:
Exit Sub

Err_btnDamages_Click:
MsgBox Err.Description
Resume Exit_btnDamages_Click

End Sub
- - - - - - - -
Jay




Graham Mandeno said:
Hi Jay

Each editable control on a form has a DefaultValue property. For a text
field, the value should be enclosed in quotes:

Me.DamageYear.DefaultValue = """" & strYearOfDamage & """"

Note: Two consecutive quotes within a text string are interpreted as one
quote character in the string.
So """" = "

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all -

I'm using a button to open a form with the button's Click event
procedure.
Is there an effective VBA technique to set the default value property
for
the
form's <DamageYear> field to the variable 'strYearOfDamage' ?

The following Click event procedure correctly passes the argument
'strYearOfDamage' to the form's Open event procedure, but I'm having
trouble
with the syntax for assigning its value as the default value for the
<DamageYear> field.

Private Sub btnDamages_Click()
On Error GoTo Err_btnDamages_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmE_09_DetailedDamageCategories"
strYearOfDamage = Trim(CStr(Me.reportingYear)) '"2000", "2001",
"2002",
etc.

stLinkCriteria = "[VesselNumber]=" & "'" & Me![A_VesselNumber] & "'"
DoCmd.OpenForm stDocName, acFormDS, , stLinkCriteria, , acDialog, _
strYearOfDamage

Exit_btnDamages_Click:
Exit Sub

Err_btnDamages_Click:
MsgBox Err.Description
Resume Exit_btnDamages_Click

End Sub
- - - - - - - -
Thanks in advance,
Jay
PS: this post will be unattended for about 8hrs.
Click to expand...
Click to expand...
Click to expand...
 
Hi Linq -

I typcially do capture the openargs values in the open event and pass them
to the load event before taking actions with their values. In this case
however, the statement Graham provided in his first post works perfectly in
the Form_Open event procedure (FYI).
- - - - - -
Thanks for the input,
Jay
 
Back
Top