copy value of one field to another field in same table

[dbnDavid]

Suppose I have a table in which there are two fields called, let's say,
Sender and Receiver. Sometimes (eg. when the value of a third field is
"holiday") the same person is both Sender and Receiver. How can I automate
this, so that when "Joe Bloggs" is entered for Sender on a "holiday"
occasion. Access will copy that name to the Receiver field? (My real data is
more complicated, but this gives the bones of what I'm trying to do.)

I've tried creating a form based on a query that selects for "holiday", and
where "holiday" is the default value for its field. I then tried to create a
macro that will copy the Sender control value to the Receiver control,
getting it to run from the On Lost Focus properties on the Event tag for the
Sender control.

But I know very little about macros and when I tried it I got an error
message saying "The object doesn't contain the Automation object 'Queries'".
Where can I go from here?

 

[tina]

if you're trying to set the value of the Receiver field in the current
record in a form bound to the table, then you don't need to use a query at
all. just add a macro to the AfterUpdate event of the Sender field, as

Condition: [Sender] Is Not Null And [3rdField] = "holiday"
Action: SetValue
Item: [Receiver]
Expression: [Forms]![FormName]![Sender]

hth

 

[dbnDavid]

Thanks, that was helpful, but only partly. I added the macro as you
suggested, and the data gets updated in the Receiver control and in the
underlying table if I'm editing an EXISTING record in the Sender control, but
I now get an error message ("can't save this record at this time") when I
close the form. If, on the other hand, I enter a NEW record in the Sender
control the Receiver control doesn't update, and the table doesn't either.
The form then either hangs or I get an additional error message saying that
the Receiver field is a required field (which it needs to be) and cannot have
a null value.

if you're trying to set the value of the Receiver field in the current
record in a form bound to the table, then you don't need to use a query at
all. just add a macro to the AfterUpdate event of the Sender field, as

Condition: [Sender] Is Not Null And [3rdField] = "holiday"
Action: SetValue
Item: [Receiver]
Expression: [Forms]![FormName]![Sender]

hth

Suppose I have a table in which there are two fields called, let's say,
Sender and Receiver. Sometimes (eg. when the value of a third field is
"holiday") the same person is both Sender and Receiver. How can I automate
this, so that when "Joe Bloggs" is entered for Sender on a "holiday"
occasion. Access will copy that name to the Receiver field? (My real data is
more complicated, but this gives the bones of what I'm trying to do.)

I've tried creating a form based on a query that selects for "holiday", and
where "holiday" is the default value for its field. I then tried to create a
macro that will copy the Sender control value to the Receiver control,
getting it to run from the On Lost Focus properties on the Event tag for the
Sender control.

But I know very little about macros and when I tried it I got an error
message saying "The object doesn't contain the Automation object 'Queries'".
Where can I go from here?

 

[tina]

well, maybe there's some problem with the syntax in the macro - i haven't
used macros for things like this in years. it should work just fine with a
little VBA code, instead of a macro. just add the following code to the
Sender control's AfterUpdate event procedure, as

If Not IsNull(Me!Sender) And _
Me!ThirdFieldName = "holiday" Then
Me!Receiver = Me!Sender
End If

if you've never created an event procedure before, click the
CreateEventProcedure link at
http://home.att.net/~california.db/instructions.html, for illustrated
instructions.

hth

Thanks, that was helpful, but only partly. I added the macro as you
suggested, and the data gets updated in the Receiver control and in the
underlying table if I'm editing an EXISTING record in the Sender control, but
I now get an error message ("can't save this record at this time") when I
close the form. If, on the other hand, I enter a NEW record in the Sender
control the Receiver control doesn't update, and the table doesn't either.
The form then either hangs or I get an additional error message saying that
the Receiver field is a required field (which it needs to be) and cannot have
a null value.

if you're trying to set the value of the Receiver field in the current
record in a form bound to the table, then you don't need to use a query at
all. just add a macro to the AfterUpdate event of the Sender field, as

Condition: [Sender] Is Not Null And [3rdField] = "holiday"
Action: SetValue
Item: [Receiver]
Expression: [Forms]![FormName]![Sender]

hth

Suppose I have a table in which there are two fields called, let's say,
Sender and Receiver. Sometimes (eg. when the value of a third field is
"holiday") the same person is both Sender and Receiver. How can I automate
this, so that when "Joe Bloggs" is entered for Sender on a "holiday"
occasion. Access will copy that name to the Receiver field? (My real

data
is

more complicated, but this gives the bones of what I'm trying to do.)

I've tried creating a form based on a query that selects for

"holiday",
and

where "holiday" is the default value for its field. I then tried to

create
a

macro that will copy the Sender control value to the Receiver control,
getting it to run from the On Lost Focus properties on the Event tag

for
the

Sender control.

But I know very little about macros and when I tried it I got an error
message saying "The object doesn't contain the Automation object 'Queries'".
Where can I go from here?

 

[dbnDavid]

I'm happy to try this route, but it's still not working.

The code I've now got in the VBA window looks like this (I'm using the real
field names):

Private Sub PrimaryClientID_AfterUpdate()
If Not IsNull(Me!ClientID) And _
Me!IsDirectClnt = "Yes" Then
Me!Agent = Me!ClientID
End If

End Sub

How does that look to you? ClientID and Agent are both text fields. The
IsDirectClient field is a Yes/No data type. I also tried typing the "Yes"
without the quotes.

The name of the form is frmClientsDirect.

But what I do notice as a probable problem is that first line:

Private Sub PrimaryClientID_AfterUpdate()

I don't have any field in any table that is called PrimaryClientsID. I used
to have, at an earlier design stage. But since then I have resaved and
recopied the database several times. I also tried just now to clear the decks
by creating a fresh Access database, re-importing all my tables, forms,
queries, etc from the previous version. But the VBA editor still comes up
with PrimaryClientsID and I can't figure out how to change it.

well, maybe there's some problem with the syntax in the macro - i haven't
used macros for things like this in years. it should work just fine with a
little VBA code, instead of a macro. just add the following code to the
Sender control's AfterUpdate event procedure, as

If Not IsNull(Me!Sender) And _
Me!ThirdFieldName = "holiday" Then
Me!Receiver = Me!Sender
End If

if you've never created an event procedure before, click the
CreateEventProcedure link at
http://home.att.net/~california.db/instructions.html, for illustrated
instructions.

hth

Thanks, that was helpful, but only partly. I added the macro as you
suggested, and the data gets updated in the Receiver control and in the
underlying table if I'm editing an EXISTING record in the Sender control, but
I now get an error message ("can't save this record at this time") when I
close the form. If, on the other hand, I enter a NEW record in the Sender
control the Receiver control doesn't update, and the table doesn't either.
The form then either hangs or I get an additional error message saying that
the Receiver field is a required field (which it needs to be) and cannot have
a null value.

if you're trying to set the value of the Receiver field in the current
record in a form bound to the table, then you don't need to use a query at
all. just add a macro to the AfterUpdate event of the Sender field, as

Condition: [Sender] Is Not Null And [3rdField] = "holiday"
Action: SetValue
Item: [Receiver]
Expression: [Forms]![FormName]![Sender]

hth


Suppose I have a table in which there are two fields called, let's say,
Sender and Receiver. Sometimes (eg. when the value of a third field is
"holiday") the same person is both Sender and Receiver. How can I automate
this, so that when "Joe Bloggs" is entered for Sender on a "holiday"
occasion. Access will copy that name to the Receiver field? (My real data
is
more complicated, but this gives the bones of what I'm trying to do.)

I've tried creating a form based on a query that selects for "holiday",
and
where "holiday" is the default value for its field. I then tried to create
a
macro that will copy the Sender control value to the Receiver control,
getting it to run from the On Lost Focus properties on the Event tag for
the
Sender control.

But I know very little about macros and when I tried it I got an error
message saying "The object doesn't contain the Automation object
'Queries'".
Where can I go from here?

 

[Tom Wickerath]

Hi David,

Try changing:
Me!IsDirectClnt = "Yes" Then

to
Me!IsDirectClnt = True Then (or Me!IsDirectClnt = -1 Then)

Since IsDirectCInt is a boolean, it will never be equal to "Yes" (or Yes).

But what I do notice as a probable problem is that first line:

Private Sub PrimaryClientID_AfterUpdate()

I don't have any field in any table that is called PrimaryClientsID.

Do you have a control named PrimaryClientsID? The name of each control is
displayed as the first entry on the Other tab of the Properties dialog. To
view this dialog, press the F4 key in form design view, or click on View >
Properties.

Alternatively, in the code module associated with this form, click on the
left-hand dropdown arrow just below the toolbar. Do you see an entry in the
list with this name? If so, then you have this control on your form.

Make sure that Event Procedure is shown for the After Update property on the
Event tab of the Properties dialog.


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I'm happy to try this route, but it's still not working.

The code I've now got in the VBA window looks like this (I'm using the real
field names):

Private Sub PrimaryClientID_AfterUpdate()
If Not IsNull(Me!ClientID) And _
Me!IsDirectClnt = "Yes" Then
Me!Agent = Me!ClientID
End If

End Sub

How does that look to you? ClientID and Agent are both text fields. The
IsDirectClient field is a Yes/No data type. I also tried typing the "Yes"
without the quotes.

The name of the form is frmClientsDirect.

But what I do notice as a probable problem is that first line:

Private Sub PrimaryClientID_AfterUpdate()

I don't have any field in any table that is called PrimaryClientsID. I used
to have, at an earlier design stage. But since then I have resaved and
recopied the database several times. I also tried just now to clear the decks
by creating a fresh Access database, re-importing all my tables, forms,
queries, etc from the previous version. But the VBA editor still comes up
with PrimaryClientsID and I can't figure out how to change it.

well, maybe there's some problem with the syntax in the macro - i haven't
used macros for things like this in years. it should work just fine with a
little VBA code, instead of a macro. just add the following code to the
Sender control's AfterUpdate event procedure, as

If Not IsNull(Me!Sender) And _
Me!ThirdFieldName = "holiday" Then
Me!Receiver = Me!Sender
End If

if you've never created an event procedure before, click the
CreateEventProcedure link at
http://home.att.net/~california.db/instructions.html, for illustrated
instructions.

hth

Thanks, that was helpful, but only partly. I added the macro as you
suggested, and the data gets updated in the Receiver control and in the
underlying table if I'm editing an EXISTING record in the Sender control, but
I now get an error message ("can't save this record at this time") when I
close the form. If, on the other hand, I enter a NEW record in the Sender
control the Receiver control doesn't update, and the table doesn't either.
The form then either hangs or I get an additional error message saying that
the Receiver field is a required field (which it needs to be) and cannot have
a null value.

:

if you're trying to set the value of the Receiver field in the current
record in a form bound to the table, then you don't need to use a query at
all. just add a macro to the AfterUpdate event of the Sender field, as

Condition: [Sender] Is Not Null And [3rdField] = "holiday"
Action: SetValue
Item: [Receiver]
Expression: [Forms]![FormName]![Sender]

hth


Suppose I have a table in which there are two fields called, let's say,
Sender and Receiver. Sometimes (eg. when the value of a third field is
"holiday") the same person is both Sender and Receiver. How can I automate
this, so that when "Joe Bloggs" is entered for Sender on a "holiday"
occasion. Access will copy that name to the Receiver field? (My real data
is
more complicated, but this gives the bones of what I'm trying to do.)

I've tried creating a form based on a query that selects for "holiday",
and
where "holiday" is the default value for its field. I then tried to create
a
macro that will copy the Sender control value to the Receiver control,
getting it to run from the On Lost Focus properties on the Event tag for
the
Sender control.

But I know very little about macros and when I tried it I got an error
message saying "The object doesn't contain the Automation object
'Queries'".
Where can I go from here?

 

[dbnDavid]

Thanks a lot Tom. That worked perfectly.

I'm very pleased to learn about the boolean in Yes/No fields. And, yes,
there were a whole slew of controls still retaining old names from discarded
tables. I'm now renaming all controls with an identifying prefix (cbo, txt,
etc) so as to pick up straightaway what I'm looking at. Now I see why people
recommend that.

 

[tina]

your VBA code was fine, after making the correction that Tom posted. note
that the Boolean issue is probably what made the macro not work, as well.
changing the macro condition to

Condition: [ClientID] Is Not Null And [IsDirectClnt] = True

or

Condition: [ClientID] Is Not Null And [IsDirectClnt]

should work (providing there was not another syntax error in the macro
solution i posted, that is! <g>).

hth

I'm happy to try this route, but it's still not working.

The code I've now got in the VBA window looks like this (I'm using the real
field names):

Private Sub PrimaryClientID_AfterUpdate()
If Not IsNull(Me!ClientID) And _
Me!IsDirectClnt = "Yes" Then
Me!Agent = Me!ClientID
End If

End Sub

How does that look to you? ClientID and Agent are both text fields. The
IsDirectClient field is a Yes/No data type. I also tried typing the "Yes"
without the quotes.

The name of the form is frmClientsDirect.

But what I do notice as a probable problem is that first line:

Private Sub PrimaryClientID_AfterUpdate()

I don't have any field in any table that is called PrimaryClientsID. I used
to have, at an earlier design stage. But since then I have resaved and
recopied the database several times. I also tried just now to clear the decks
by creating a fresh Access database, re-importing all my tables, forms,
queries, etc from the previous version. But the VBA editor still comes up
with PrimaryClientsID and I can't figure out how to change it.

well, maybe there's some problem with the syntax in the macro - i haven't
used macros for things like this in years. it should work just fine with a
little VBA code, instead of a macro. just add the following code to the
Sender control's AfterUpdate event procedure, as

If Not IsNull(Me!Sender) And _
Me!ThirdFieldName = "holiday" Then
Me!Receiver = Me!Sender
End If

if you've never created an event procedure before, click the
CreateEventProcedure link at
http://home.att.net/~california.db/instructions.html, for illustrated
instructions.

hth

Thanks, that was helpful, but only partly. I added the macro as you
suggested, and the data gets updated in the Receiver control and in the
underlying table if I'm editing an EXISTING record in the Sender

control,
but

I now get an error message ("can't save this record at this time") when I
close the form. If, on the other hand, I enter a NEW record in the Sender
control the Receiver control doesn't update, and the table doesn't either.
The form then either hangs or I get an additional error message saying that
the Receiver field is a required field (which it needs to be) and

cannot
have

a null value.

:

if you're trying to set the value of the Receiver field in the current
record in a form bound to the table, then you don't need to use a

query
at

all. just add a macro to the AfterUpdate event of the Sender field, as

Condition: [Sender] Is Not Null And [3rdField] = "holiday"
Action: SetValue
Item: [Receiver]
Expression: [Forms]![FormName]![Sender]

hth


Suppose I have a table in which there are two fields called, let's say,
Sender and Receiver. Sometimes (eg. when the value of a third field is
"holiday") the same person is both Sender and Receiver. How can I automate
this, so that when "Joe Bloggs" is entered for Sender on a "holiday"
occasion. Access will copy that name to the Receiver field? (My

real
data

is
more complicated, but this gives the bones of what I'm trying to do.)

I've tried creating a form based on a query that selects for "holiday",
and
where "holiday" is the default value for its field. I then tried

to
create

a
macro that will copy the Sender control value to the Receiver control,
getting it to run from the On Lost Focus properties on the Event

tag
for

the
Sender control.

But I know very little about macros and when I tried it I got an error
message saying "The object doesn't contain the Automation object
'Queries'".
Where can I go from here?

 

[Tom Wickerath]

Hi David,

Glad I could help. I am a big proponent of using good naming conventions,
and avoiding the use of Access or JET reserved words for anything that one
assigns a name to in Access (examples: Name, Date). For one thing, you should
never experience a circular reference error in a calculated control, if you
have taken the time to rename your controls so that they do are not named the
same as a control source.

Here is a quote I would like to share with you, which comes from the bottom
of this page:
http://www.xoc.net/standards/rvbanc.asp

"Using a naming convention requires a considerable initial effort on your
part. The payoff comes when either you or another programmer has to revisit
your code at a later time."

Here are some KB articles worth bookmarking:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266

And two other good articles as well:

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp


Good Luck on your project!

Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[dbnDavid]

Thanks very much tina & Tom. I've learned a lot, and I'm grateful for the
encouragement to start dipping my toes into VBA.

 

[tina]

you're welcome, and i do encourage you to begin shifting away from macros to
VBA. the power of VBA is so far beyond that of macros, that you'll be
astounded by what you can do with it!

 

[dbnDavid]

Problem is that I'm finding it very difficult to teach myself anything much
about VBA. The VBA Help files are very little use as they assume (a) that you
already know something about how code works - which I don't really - and (b)
that you can figure out what all those confusing panels and panes are in the
interface. I'm finding it a bit like landing suddenly in China and trusting
that one day you'll somehow understand all the signs.

But I'm kind of picking it up one word at a time!

 

[tina]

comments inline.

Problem is that I'm finding it very difficult to teach myself anything much
about VBA.

The VBA Help files are very little use as they assume (a) that you
already know something about how code works - which I don't really - and (b)
that you can figure out what all those confusing panels and panes are in the
interface. I'm finding it a bit like landing suddenly in China and trusting
that one day you'll somehow understand all the signs.

But I'm kind of picking it up one word at a time!

here's one thing that may help you: pick a macro object in your database
window, then from the menu bar click Tools | Macros | Convert Macros to
Visual Basic. (it really means convert them to VBA.) checkmark both boxes in
the dialog that opens, then click Convert. Access creates a new standard
module (look in the Modules pane of the database window) with all the
"macro" VBA code in it. the generated code is not necessarily efficient; but
you can learn a lot just by comparing it to the actions in the macros you
wrote. having a "starting point" with a frame of reference can help you to
not feel so lost in this alien land. <g> also, make liberal use of the F1
key. in the VBA window, put your cursor on any command or action, etc, that
you're not familiar with, and press F1 to read up on the associated Help
topic. also read the Help on things that you think you *are* familiar with,
especially references (such as Me.Form); you'll be surprised what you might
learn. in the Help window, explore the See Also, Applies To, Properties,
Methods, Events, and Example options at the top of the window under the
topic header. not all of the options are available for all topics, but you
learn a lot by reading these related subjects. also, read and study any code
that you come across - on a website, posted in the newsgroups, in manuals,
etc. you can copy/paste into a db module and, again, use F1 to help you
figure out what the various things do, like Do...Loop, For...Next,
If...Then...Else, etc, etc, etc.

it's a long learning curve, but you'll be surprised how quickly you'll begin
to be able to substitute code for your macros; and sooner than you think,
you'll be experimenting with things that you simply couldn't do with a
macro. it gets to be lots of fun!

hth

 

[dbnDavid]

Thanks tina. I liked both the advice and the spirit of it!

 

[tina]

you're very welcome, good luck!