Using the 'SetValue' Action

J

JC

I am trying to use the SetValue action to set the value
of a field when I change values of several fields on a
Form.

I have the following as seperate fields;

ProposalType - Year - No - Operator

I can show them on a form by concatenating them using
the '&' operator, this works fine.

I now want to write the completed proposal number back to
a proposals text field using the SetValue Action. I have
looked at Microsoft knowledgebase article 292042 and have
followed the steps, however I get the following error
message?

'The object doesn't contain the automation object
ProposalDB, the component doesn't make the property or
method available for automation operations.'

If someone understands this and can help, my headache
will go!

Happy New Year

JC
 
K

Ken Snell

You need to give us more info: what are the controls on the form, to which
fields are they bound, what are the values in the controls when you want to
write values to fields, what is the resulting value to write to the field,
what are the actions that you've tried to use in the macro, etc.

Show examples with actual data if possible.
 
J

JC

Ken,

Thanks for your reply, here is the information;

This is what concatenates all of the fields together to
give me what I want. They are from one database called
ProposalDB and are all text fields.

=[ProposalType] & '-' & [ProposalYear] & '-' &
[ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The form has a text box at present with the result of the
above being shown through the control source.

An example of the result is;

1-04-0001-VS Issue 04

I have tried putting an after update event procedure in
place called CreateProposalNo. This is configured as
follows;

SetValue
Item [ProposalDB]![proposal]
Expression [ProposalType] & '-' & [ProposalYear] & '-
' & [ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The control source in the individual fields on the form
is set to the actual field name.

The user selects the [ProposalType] which is a lookup
combo box, manually enteres the [ProposalYear] and
[ProposalNo], the [Client ID] is another lookup combo box
and the [ProposalIssueNo] is a manual entry. As the
fields are completed the text box at the bottom of the
creen displays the result. I just want this written back
into a field as a complete string.

I hope this is enough data.

Thanks

JC
 
K

Ken Snell

Here is what you want the macro to be (as it's being run from the one form
and no other forms are involved):

Action: SetValue
Control Name: Proposal
Expression: NameOfTextboxWithConcatenatedString

As to which AfterUpdate event you use, I'm not clear as to which one that
would be. Your user is entering data into many controls, so none of them can
be used (unless you somehow ensure that the last control filled in is always
the last one filled in).

However, let me suggest that you not store the concatenated result into a
field anyway. Store the individual pieces (ProposalType, ProposalYear,
ProposalNo, and ClientID) in separate fields. You then can display the
concatenated result at any time via a calculated field in a query or a
textbox with a control source expression. Keeping them separate makes it
MUCH EASIER to do searching based on proposal year, or on client, etc., as
you won't need to parse the information back apart in order to find the
desired info.

--
Ken Snell
<MS ACCESS MVP>


JC said:
Ken,

Thanks for your reply, here is the information;

This is what concatenates all of the fields together to
give me what I want. They are from one database called
ProposalDB and are all text fields.

=[ProposalType] & '-' & [ProposalYear] & '-' &
[ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The form has a text box at present with the result of the
above being shown through the control source.

An example of the result is;

1-04-0001-VS Issue 04

I have tried putting an after update event procedure in
place called CreateProposalNo. This is configured as
follows;

SetValue
Item [ProposalDB]![proposal]
Expression [ProposalType] & '-' & [ProposalYear] & '-
' & [ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The control source in the individual fields on the form
is set to the actual field name.

The user selects the [ProposalType] which is a lookup
combo box, manually enteres the [ProposalYear] and
[ProposalNo], the [Client ID] is another lookup combo box
and the [ProposalIssueNo] is a manual entry. As the
fields are completed the text box at the bottom of the
creen displays the result. I just want this written back
into a field as a complete string.

I hope this is enough data.

Thanks

JC


-----Original Message-----
You need to give us more info: what are the controls on the form, to which
fields are they bound, what are the values in the controls when you want to
write values to fields, what is the resulting value to write to the field,
what are the actions that you've tried to use in the macro, etc.

Show examples with actual data if possible.

--
Ken Snell
<MS ACCESS MVP>




.
Click to expand...
Click to expand...
 
J

JC

Thanks for your help Ken,

I am trying to program a button to open a word document
with the filename = to the created proposal number. This
is why I thought it would be easier to put the data into
a field and then just play with the one field value?

If you have any ideas on this it would be appreciated.

Thanks

JC
-----Original Message-----
Here is what you want the macro to be (as it's being run from the one form
and no other forms are involved):

Action: SetValue
Control Name: Proposal
Expression: NameOfTextboxWithConcatenatedString

As to which AfterUpdate event you use, I'm not clear as to which one that
would be. Your user is entering data into many controls, so none of them can
be used (unless you somehow ensure that the last control filled in is always
the last one filled in).

However, let me suggest that you not store the concatenated result into a
field anyway. Store the individual pieces (ProposalType, ProposalYear,
ProposalNo, and ClientID) in separate fields. You then can display the
concatenated result at any time via a calculated field in a query or a
textbox with a control source expression. Keeping them separate makes it
MUCH EASIER to do searching based on proposal year, or on client, etc., as
you won't need to parse the information back apart in order to find the
desired info.

--
Ken Snell
<MS ACCESS MVP>


Ken,

Thanks for your reply, here is the information;

This is what concatenates all of the fields together to
give me what I want. They are from one database called
ProposalDB and are all text fields.

=[ProposalType] & '-' & [ProposalYear] & '-' &
[ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The form has a text box at present with the result of the
above being shown through the control source.

An example of the result is;

1-04-0001-VS Issue 04

I have tried putting an after update event procedure in
place called CreateProposalNo. This is configured as
follows;

SetValue
Item [ProposalDB]![proposal]
Expression [ProposalType] & '-' & [ProposalYear] & '-
' & [ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The control source in the individual fields on the form
is set to the actual field name.

The user selects the [ProposalType] which is a lookup
combo box, manually enteres the [ProposalYear] and
[ProposalNo], the [Client ID] is another lookup combo box
and the [ProposalIssueNo] is a manual entry. As the
fields are completed the text box at the bottom of the
creen displays the result. I just want this written back
into a field as a complete string.

I hope this is enough data.

Thanks

JC


-----Original Message-----
You need to give us more info: what are the controls
Click to expand...
on
the form, to which
fields are they bound, what are the values in the controls when you want to
write values to fields, what is the resulting value to write to the field,
what are the actions that you've tried to use in the macro, etc.

Show examples with actual data if possible.

--
Ken Snell
<MS ACCESS MVP>

I am trying to use the SetValue action to set the value
of a field when I change values of several fields on a
Form.

I have the following as seperate fields;

ProposalType - Year - No - Operator

I can show them on a form by concatenating them using
the '&' operator, this works fine.

I now want to write the completed proposal number
Click to expand...
back
to
a proposals text field using the SetValue Action. I have
looked at Microsoft knowledgebase article 292042 and have
followed the steps, however I get the following error
message?

'The object doesn't contain the automation object
ProposalDB, the component doesn't make the property or
method available for automation operations.'

If someone understands this and can help, my headache
will go!

Happy New Year

JC


.
Click to expand...
Click to expand...


.
Click to expand...
 
K

Ken Snell

No need to concatenate in a field/control. Use an expression in your
"filename" argument that is opening the Word document; that expression would
look something like this (assuming that the filename argument of whatever
command you're using is named Filename):

Filename:=[ProposalType] & '-' & [ProposalYear] & '-' & [ProposalNo] & '-' &
[ClientID] & ' Issue ' & [ProposalIssueNo]

--
Ken Snell
<MS ACCESS MVP>

JC said:
Thanks for your help Ken,

I am trying to program a button to open a word document
with the filename = to the created proposal number. This
is why I thought it would be easier to put the data into
a field and then just play with the one field value?

If you have any ideas on this it would be appreciated.

Thanks

JC
-----Original Message-----
Here is what you want the macro to be (as it's being run from the one form
and no other forms are involved):

Action: SetValue
Control Name: Proposal
Expression: NameOfTextboxWithConcatenatedString

As to which AfterUpdate event you use, I'm not clear as to which one that
would be. Your user is entering data into many controls, so none of them can
be used (unless you somehow ensure that the last control filled in is always
the last one filled in).

However, let me suggest that you not store the concatenated result into a
field anyway. Store the individual pieces (ProposalType, ProposalYear,
ProposalNo, and ClientID) in separate fields. You then can display the
concatenated result at any time via a calculated field in a query or a
textbox with a control source expression. Keeping them separate makes it
MUCH EASIER to do searching based on proposal year, or on client, etc., as
you won't need to parse the information back apart in order to find the
desired info.

--
Ken Snell
<MS ACCESS MVP>


Ken,

Thanks for your reply, here is the information;

This is what concatenates all of the fields together to
give me what I want. They are from one database called
ProposalDB and are all text fields.

=[ProposalType] & '-' & [ProposalYear] & '-' &
[ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The form has a text box at present with the result of the
above being shown through the control source.

An example of the result is;

1-04-0001-VS Issue 04

I have tried putting an after update event procedure in
place called CreateProposalNo. This is configured as
follows;

SetValue
Item [ProposalDB]![proposal]
Expression [ProposalType] & '-' & [ProposalYear] & '-
' & [ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The control source in the individual fields on the form
is set to the actual field name.

The user selects the [ProposalType] which is a lookup
combo box, manually enteres the [ProposalYear] and
[ProposalNo], the [Client ID] is another lookup combo box
and the [ProposalIssueNo] is a manual entry. As the
fields are completed the text box at the bottom of the
creen displays the result. I just want this written back
into a field as a complete string.

I hope this is enough data.

Thanks

JC



-----Original Message-----
You need to give us more info: what are the controls on
the form, to which
fields are they bound, what are the values in the
controls when you want to
write values to fields, what is the resulting value to
write to the field,
what are the actions that you've tried to use in the
macro, etc.

Show examples with actual data if possible.

--
Ken Snell
<MS ACCESS MVP>

message
I am trying to use the SetValue action to set the value
of a field when I change values of several fields on a
Form.

I have the following as seperate fields;

ProposalType - Year - No - Operator

I can show them on a form by concatenating them using
the '&' operator, this works fine.

I now want to write the completed proposal number back
to
a proposals text field using the SetValue Action. I
have
looked at Microsoft knowledgebase article 292042 and
have
followed the steps, however I get the following error
message?

'The object doesn't contain the automation object
ProposalDB, the component doesn't make the property or
method available for automation operations.'

If someone understands this and can help, my headache
will go!

Happy New Year

JC


.
Click to expand...


.
Click to expand...
Click to expand...
 
J

JC

Thanks Ken
-----Original Message-----
No need to concatenate in a field/control. Use an expression in your
"filename" argument that is opening the Word document; that expression would
look something like this (assuming that the filename argument of whatever
command you're using is named Filename):

Filename:=[ProposalType] & '-' & [ProposalYear] & '-' & [ProposalNo] & '-' &
[ClientID] & ' Issue ' & [ProposalIssueNo]

--
Ken Snell
<MS ACCESS MVP>

Thanks for your help Ken,

I am trying to program a button to open a word document
with the filename = to the created proposal number. This
is why I thought it would be easier to put the data into
a field and then just play with the one field value?

If you have any ideas on this it would be appreciated.

Thanks

JC
-----Original Message-----
Here is what you want the macro to be (as it's being
Click to expand...
run
from the one form
and no other forms are involved):

Action: SetValue
Control Name: Proposal
Expression: NameOfTextboxWithConcatenatedString

As to which AfterUpdate event you use, I'm not clear
Click to expand...
as
to which one that
would be. Your user is entering data into many
Click to expand...
controls,
so none of them can
be used (unless you somehow ensure that the last
Click to expand...
control
filled in is always
the last one filled in).

However, let me suggest that you not store the concatenated result into a
field anyway. Store the individual pieces
Click to expand...
(ProposalType,
ProposalYear,
ProposalNo, and ClientID) in separate fields. You then can display the
concatenated result at any time via a calculated field in a query or a
textbox with a control source expression. Keeping them separate makes it
MUCH EASIER to do searching based on proposal year, or on client, etc., as
you won't need to parse the information back apart in order to find the
desired info.

--
Ken Snell
<MS ACCESS MVP>


Ken,

Thanks for your reply, here is the information;

This is what concatenates all of the fields together to
give me what I want. They are from one database called
ProposalDB and are all text fields.

=[ProposalType] & '-' & [ProposalYear] & '-' &
[ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The form has a text box at present with the result
Click to expand...
of
the
above being shown through the control source.

An example of the result is;

1-04-0001-VS Issue 04

I have tried putting an after update event procedure in
place called CreateProposalNo. This is configured as
follows;

SetValue
Item [ProposalDB]![proposal]
Expression [ProposalType] & '-' & [ProposalYear] & '-
' & [ProposalNo] & '-' & [ClientID] & ' Issue ' &
[ProposalIssueNo]

The control source in the individual fields on the form
is set to the actual field name.

The user selects the [ProposalType] which is a lookup
combo box, manually enteres the [ProposalYear] and
[ProposalNo], the [Client ID] is another lookup
Click to expand...
combo
box
and the [ProposalIssueNo] is a manual entry. As the
fields are completed the text box at the bottom of the
creen displays the result. I just want this written back
into a field as a complete string.

I hope this is enough data.

Thanks

JC



-----Original Message-----
You need to give us more info: what are the
Click to expand...
controls
on
the form, to which
fields are they bound, what are the values in the
controls when you want to
write values to fields, what is the resulting value to
write to the field,
what are the actions that you've tried to use in the
macro, etc.

Show examples with actual data if possible.

--
Ken Snell
<MS ACCESS MVP>

message
I am trying to use the SetValue action to set the value
of a field when I change values of several fields on a
Form.

I have the following as seperate fields;

ProposalType - Year - No - Operator

I can show them on a form by concatenating them using
the '&' operator, this works fine.

I now want to write the completed proposal number back
to
a proposals text field using the SetValue Action. I
have
looked at Microsoft knowledgebase article 292042 and
have
followed the steps, however I get the following error
message?

'The object doesn't contain the automation object
ProposalDB, the component doesn't make the
Click to expand...
property
or
method available for automation operations.'

If someone understands this and can help, my headache
will go!

Happy New Year

JC


.



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

setvalue action changes record focus 5
setting the argument in SETVALUE Action of macro 6
Where did the SetValue action go? 5
'SetValue macro action' problem 4
SetValue action in Macro? 8
Access SetValue Action 1
Recording the date and time when a record is modified in Access 2
Error when using Setvalue command 2

Top