ALTER COLUMN Help

[moscat via AccessMonster.com]

I previously submitted a post but was never answered. I must be because I
stated it worked but wanted input how it could be simplified. However, I
moved on without the simplification and I am moving forward writing it for
each field.

What I need to do is to have a Form where I set the default value of the
field in the table for several fields. I only found that the way to do it is
using ALTER TABLE...ALTER COLUMN. In my form, I am using an unbounded text
box and I named it 'CField1'. At the beginnning I tried the code shown below
and it works, but only if the text entered in CField1 has no spaces or
doesn't contain the " or ? characters. Is there a way to go around this. I
noticed that in the table, I can use the question mark as long as I have it
enclosed with double quotations as "?". In the case of the ", I am using it
as the inch symbol like 3/4". This is the code I am using:

Private Sub btnSubmit_Click()

Dim cnn As ADODB.Connection
Dim strSQL, strDef As String

Set cnn = CurrentProject.Connection

If Me.CField1 = "None" Then
strDefault = Empty
strSQL = "ALTER TABLE MyTbl ALTER COLUMN Field1 _
varchar(50) Default"
'strSQL = strSQL & strDef
cnn.Execute strSQL, , dbFailOnError
Set cnn = Nothing
ElseIf Not (IsNull(Me.CField1)) Then
strDefault = Me.CField1
strSQL = "ALTER TABLE MyTbl ALTER COLUMN Field1 _
varchar(50) Default "
strSQL = strSQL & strDef
cnn.Execute strSQL, , dbFailOnError
Set cnn = Nothing
End If

End Sub

Thank you for your help. I am stuck for now.

 

[Dale Fye]

Why change the default value of a field, when you can change the default
value of a control instead.

If you are doing what I think, you want to change the default value of
certain fields once you have entered data into that field. If so,
use the controls AfterUpdate event to change that controls Default value.
Something like the following works for a date/time field that I have on one
of my forms. This will be retained as long as that form is open.

It won't make the change to the default value permanent, but it is a far
sight easier than changing the default value of a field in the database.

HTH
Dale

 

[moscat via AccessMonster.com]

Thanks for replying Dale. The situation in my case is that for one record
there are over 50 fields, and in some instances, the information being
inputed for certain fields repeats. In my case, I don't have data/time fields.


Therefore, the form I have for changing the default values of the fields in
the table also retrieves the current default values in the table so the user
is aware what are the current default values in place in the table. The idea
is to give them the choices in selecting what values to change, so when a new
record is added in a different input form, the common fields/values are
automatically inserted. In other words is the same as copying a particular
record, and then revise the fields.

Why change the default value of a field, when you can change the default
value of a control instead.

If you are doing what I think, you want to change the default value of
certain fields once you have entered data into that field. If so,
use the controls AfterUpdate event to change that controls Default value.
Something like the following works for a date/time field that I have on one
of my forms. This will be retained as long as that form is open.

It won't make the change to the default value permanent, but it is a far
sight easier than changing the default value of a field in the database.

HTH
Dale

I previously submitted a post but was never answered. I must be because I
stated it worked but wanted input how it could be simplified. However, I

[quoted text clipped - 41 lines]

Thank you for your help. I am stuck for now.

 

[Douglas J. Steele]

Dale's point, I believe, is that since you should never be updating tables
directly, setting the default value for the text box on the form should be
adequate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying Dale. The situation in my case is that for one record
there are over 50 fields, and in some instances, the information being
inputed for certain fields repeats. In my case, I don't have data/time
fields.


Therefore, the form I have for changing the default values of the fields
in
the table also retrieves the current default values in the table so the
user
is aware what are the current default values in place in the table. The
idea
is to give them the choices in selecting what values to change, so when a
new
record is added in a different input form, the common fields/values are
automatically inserted. In other words is the same as copying a particular
record, and then revise the fields.

Why change the default value of a field, when you can change the default
value of a control instead.

If you are doing what I think, you want to change the default value of
certain fields once you have entered data into that field. If so,
use the controls AfterUpdate event to change that controls Default value.
Something like the following works for a date/time field that I have on
one
of my forms. This will be retained as long as that form is open.

It won't make the change to the default value permanent, but it is a far
sight easier than changing the default value of a field in the database.

HTH
Dale

I previously submitted a post but was never answered. I must be because I
stated it worked but wanted input how it could be simplified. However, I

[quoted text clipped - 41 lines]

Thank you for your help. I am stuck for now.

 

[Martin]

If this is the case, how will the code look like?

Dale's point, I believe, is that since you should never be updating tables
directly, setting the default value for the text box on the form should be
adequate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying Dale. The situation in my case is that for one record
there are over 50 fields, and in some instances, the information being
inputed for certain fields repeats. In my case, I don't have data/time
fields.


Therefore, the form I have for changing the default values of the fields
in
the table also retrieves the current default values in the table so the
user
is aware what are the current default values in place in the table. The
idea
is to give them the choices in selecting what values to change, so when a
new
record is added in a different input form, the common fields/values are
automatically inserted. In other words is the same as copying a particular
record, and then revise the fields.

Why change the default value of a field, when you can change the default
value of a control instead.

If you are doing what I think, you want to change the default value of
certain fields once you have entered data into that field. If so,
use the controls AfterUpdate event to change that controls Default value.
Something like the following works for a date/time field that I have on
one
of my forms. This will be retained as long as that form is open.

It won't make the change to the default value permanent, but it is a far
sight easier than changing the default value of a field in the database.

HTH
Dale

I previously submitted a post but was never answered. I must be because I
stated it worked but wanted input how it could be simplified. However, I
[quoted text clipped - 41 lines]

Thank you for your help. I am stuck for now.

 

[Douglas J. Steele]

Forms!frmInvoice!PaymentMethod.DefaultValue = """Cash"""

Note that you need quotes around the value, even if it's numeric, hence the
three double quotes before and after.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

If this is the case, how will the code look like?

Dale's point, I believe, is that since you should never be updating
tables
directly, setting the default value for the text box on the form should
be
adequate.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for replying Dale. The situation in my case is that for one
record
there are over 50 fields, and in some instances, the information being
inputed for certain fields repeats. In my case, I don't have data/time
fields.


Therefore, the form I have for changing the default values of the
fields
in
the table also retrieves the current default values in the table so the
user
is aware what are the current default values in place in the table. The
idea
is to give them the choices in selecting what values to change, so when
a
new
record is added in a different input form, the common fields/values are
automatically inserted. In other words is the same as copying a
particular
record, and then revise the fields.



Dale Fye wrote:
Why change the default value of a field, when you can change the
default
value of a control instead.

If you are doing what I think, you want to change the default value of
certain fields once you have entered data into that field. If so,
use the controls AfterUpdate event to change that controls Default
value.
Something like the following works for a date/time field that I have on
one
of my forms. This will be retained as long as that form is open.

It won't make the change to the default value permanent, but it is a
far
sight easier than changing the default value of a field in the
database.

HTH
Dale

I previously submitted a post but was never answered. I must be
because I
stated it worked but wanted input how it could be simplified.
However, I
[quoted text clipped - 41 lines]

Thank you for your help. I am stuck for now.

 

[moscat via AccessMonster.com]

I modified my code to match your suggestion to something like this:

Dim stDocName, strDef as String
stDocName="MyForm"

If Me.CField1 = "None" Then
Forms(stDocName).Controls("Field1").DefaultValue = Empty
ElseIf Not (IsNull(Me.CField1)) Then
strDefault = Me.CField1
Forms(stDocName).Controls("Field1").DefaultValue = """" & strDef & """"
End If

CField1 is the value entered in the unbound control. I have a control button
that when clicked it will execute the code above. I have already included
opening and closing the form to edit the fields default values.

The problem I am having is the sometimes the value entered are something like:

3/4" Plywood
30" & 36"

When these values are inputed, the field displays #Name? when opening the
form. I tried adding or removing a quotation mark to the code line and
"_strDef" is stored in the default value.

How can I work around this?

Forms!frmInvoice!PaymentMethod.DefaultValue = """Cash"""

Note that you need quotes around the value, even if it's numeric, hence the
three double quotes before and after.

If this is the case, how will the code look like?

[quoted text clipped - 52 lines]

 

[Douglas J. Steele]

To solve that, you need to double up any occurrence of double quotes inside
the string:

Forms(stDocName).Controls("Field1").DefaultValue = """" & Replace(strDef,
Chr$(34), Chr$(34) & Chr$(34)) & """"

I've used Chr$(34) to represent the double quote because I find that a bit
more legible than Replace(strDef, """", """""")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I modified my code to match your suggestion to something like this:

Dim stDocName, strDef as String
stDocName="MyForm"

If Me.CField1 = "None" Then
Forms(stDocName).Controls("Field1").DefaultValue = Empty
ElseIf Not (IsNull(Me.CField1)) Then
strDefault = Me.CField1
Forms(stDocName).Controls("Field1").DefaultValue = """" & strDef & """"
End If

CField1 is the value entered in the unbound control. I have a control
button
that when clicked it will execute the code above. I have already included
opening and closing the form to edit the fields default values.

The problem I am having is the sometimes the value entered are something
like:

3/4" Plywood
30" & 36"

When these values are inputed, the field displays #Name? when opening the
form. I tried adding or removing a quotation mark to the code line and
"_strDef" is stored in the default value.

How can I work around this?

Forms!frmInvoice!PaymentMethod.DefaultValue = """Cash"""

Note that you need quotes around the value, even if it's numeric, hence
the
three double quotes before and after.

If this is the case, how will the code look like?

[quoted text clipped - 52 lines]

Thank you for your help. I am stuck for now.

 

[moscat via AccessMonster.com]

Thank You. You make it look so easy.

To solve that, you need to double up any occurrence of double quotes inside
the string:

Forms(stDocName).Controls("Field1").DefaultValue = """" & Replace(strDef,
Chr$(34), Chr$(34) & Chr$(34)) & """"

I've used Chr$(34) to represent the double quote because I find that a bit
more legible than Replace(strDef, """", """""")

I modified my code to match your suggestion to something like this:

[quoted text clipped - 35 lines]