selecting a single record

[Paul Martin]

Can someone please help me with a data updating problem in my Access
database?



The problem is this. I have a table with a load of address data in it. It
used to just have data for addresses as follows: -



Billing Address1

Billing Address2

Billing City

Billing State/Province

Billing Post Code

Billing Country



I have now added the following fields to the "customers' main table"



Shipping Address1

Shipping Address2

Shipping City

Shipping State/Province

Shipping Post Code

Shipping Country



The contents of the table are viewed in a justified form one record at a
time. I want to create a query, or a number of queries in a macro, run from
a button in the form that copies the shipping fields in to the billing
fields for JUST the record that is being viewed in the form.


I have been all round the houses and have no problem creating queries that
write the date to assorted location be it the fields in this table or to a
new table, I've even written all the shipping data in a make table query,
written it back into the billing fields and then deleted the table all run
by a macro initiated by my command button but everything I have done works
for all the records and I can't figure out how to make it do it for just the
record being viewed in the form.


I have tried the help file but I guess I'm not asking the right question as
I haven't found the answer.

I would be grateful for any pointers in the right direction


Paul Martin

 

[Guest]

Hi Paul,

I think something like this should work for you:

Private Sub cmdCopyFields_Click()
On Error GoTo ProcError

[Shipping Address1] = Nz([Billing Address1])
[Shipping Address1] = Nz([Billing Address2])
' etc.

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Guest]

PS. Here is a slight variation, just in case you have the Allow Zero Lenght
property for the fields in question set to No:

Private Sub cmdCopyFields_Click()
On Error GoTo ProcError

[Shipping Address1] = Nz([Billing Address1], Null)
[Shipping Address1] = Nz([Billing Address2], Null)
' etc.

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub


Disallowing zero lenght strings is usually a good thing to do. Here is more
information on this topic:

See the topic: "Fields: Allow Zero Length"
http://allenbrowne.com/bug-09.html

Also, I forgot to mention that you will be better off in the long run to
avoid spaces or other special characters for anything that you assign a name
to in Access (fields, tables, queries, forms, reports, macros, modules and
controls on forms and reports).

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



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Paul,

I think something like this should work for you:

Private Sub cmdCopyFields_Click()
On Error GoTo ProcError

[Shipping Address1] = Nz([Billing Address1])
[Shipping Address1] = Nz([Billing Address2])
' etc.

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Can someone please help me with a data updating problem in my Access
database?



The problem is this. I have a table with a load of address data in it. It
used to just have data for addresses as follows: -



Billing Address1

Billing Address2

Billing City

Billing State/Province

Billing Post Code

Billing Country



I have now added the following fields to the "customers' main table"



Shipping Address1

Shipping Address2

Shipping City

Shipping State/Province

Shipping Post Code

Shipping Country



The contents of the table are viewed in a justified form one record at a
time. I want to create a query, or a number of queries in a macro, run from
a button in the form that copies the shipping fields in to the billing
fields for JUST the record that is being viewed in the form.


I have been all round the houses and have no problem creating queries that
write the date to assorted location be it the fields in this table or to a
new table, I've even written all the shipping data in a make table query,
written it back into the billing fields and then deleted the table all run
by a macro initiated by my command button but everything I have done works
for all the records and I can't figure out how to make it do it for just the
record being viewed in the form.


I have tried the help file but I guess I'm not asking the right question as
I haven't found the answer.

I would be grateful for any pointers in the right direction


Paul Martin

 

[Paul Martin]

Tom

Many thanks for your help with my database problem. I have used the version
2 of you code, altered the field names to the ones actually in my database
and then linked the code to a command button on my form. When I try to run
the command I get this message

Error 2465 – Microsoft Office Access can’t find the field `I’ referred to in
your expression.

The code as used is………
----------------------------------------------
Private Sub Command59_Click()
On Error GoTo ProcError

[Address1] = Nz([DeliveryAddress1], Null)
[Address2] = Nz([DeliveryAddress2], Null)
[City] = Nz([DeliveryCity], Null)
[StateOrProvince] = Nz([DeliveryStateOrProvince], Null)
[PostalCode] = Nz([DeliveryPostalCode], Null)
[Country/Region] = Nz([DeliveryCountry/Region], Null)

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub

------------------------------------------

Any clues as to where I have gone wrong

Thanks
Paul

PS. Here is a slight variation, just in case you have the Allow Zero
Lenght
property for the fields in question set to No:

Private Sub cmdCopyFields_Click()
On Error GoTo ProcError

[Shipping Address1] = Nz([Billing Address1], Null)
[Shipping Address1] = Nz([Billing Address2], Null)
' etc.

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub


Disallowing zero lenght strings is usually a good thing to do. Here is
more
information on this topic:

See the topic: "Fields: Allow Zero Length"
http://allenbrowne.com/bug-09.html

Also, I forgot to mention that you will be better off in the long run to
avoid spaces or other special characters for anything that you assign a
name
to in Access (fields, tables, queries, forms, reports, macros, modules and
controls on forms and reports).

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



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Paul,

I think something like this should work for you:

Private Sub cmdCopyFields_Click()
On Error GoTo ProcError

[Shipping Address1] = Nz([Billing Address1])
[Shipping Address1] = Nz([Billing Address2])
' etc.

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Can someone please help me with a data updating problem in my Access
database?



The problem is this. I have a table with a load of address data in it.
It
used to just have data for addresses as follows: -



Billing Address1

Billing Address2

Billing City

Billing State/Province

Billing Post Code

Billing Country



I have now added the following fields to the "customers' main table"



Shipping Address1

Shipping Address2

Shipping City

Shipping State/Province

Shipping Post Code

Shipping Country



The contents of the table are viewed in a justified form one record at
a
time. I want to create a query, or a number of queries in a macro, run
from
a button in the form that copies the shipping fields in to the billing
fields for JUST the record that is being viewed in the form.


I have been all round the houses and have no problem creating queries
that
write the date to assorted location be it the fields in this table or
to a
new table, I've even written all the shipping data in a make table
query,
written it back into the billing fields and then deleted the table all
run
by a macro initiated by my command button but everything I have done
works
for all the records and I can't figure out how to make it do it for
just the
record being viewed in the form.


I have tried the help file but I guess I'm not asking the right
question as
I haven't found the answer.

I would be grateful for any pointers in the right direction


Paul Martin

 

[Guest]

Hi Paul,

The code displays an immediate update to the user if you reference the name
of the controls, rather than the name of the bound fields. I had set up a
quick demo. using the Orders form in the sample Northwind database. They use
the same name for controls and fields. If you reference the fieldnames, then
you will not see an immediate update, unless you either navigate off of the
record, and then come back, or you issue a requery command.

Hmmm.....the only way I can reproduce your Error 2465 is if I do not have a
field or text box with the name indicated. Do you have Name Autocorrect (aka
Name Autocorrupt) enabled? This "feature" is enabled by default, and found
under Tools > Options, on the General tab.

Try adding the lowercase "txt" prefix to the names of your text box
controls, if they are currently the same name as the underlying fields. Then
reference the controls by control name in the code, like this:

txtAddress1 = Nz(txtDeliveryAddress1, Null)
txtAddress2 = Nz(txtDeliveryAddress2, Null)

Also, not that it would cause the error, but it appears to me as if you
*might* have the controls backwards, ie, taking the first two lines as an
example:

[Address1] = Nz([DeliveryAddress1], Null)
[Address2] = Nz([DeliveryAddress2], Null)

You are saying that the Address should equal the DeliveryAddress. It just
seems to me like it should be reversed, as in:

[DeliveryAddress1] = Nz([Address1], Null)
[DeliveryAddress2] = Nz([Address2], Null)

but that would not explain the error in any case.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom

Many thanks for your help with my database problem. I have used the version
2 of you code, altered the field names to the ones actually in my database
and then linked the code to a command button on my form. When I try to run
the command I get this message

Error 2465 – Microsoft Office Access can’t find the field `I’ referred to in
your expression.

The code as used is………
----------------------------------------------
Private Sub Command59_Click()
On Error GoTo ProcError

[Address1] = Nz([DeliveryAddress1], Null)
[Address2] = Nz([DeliveryAddress2], Null)
[City] = Nz([DeliveryCity], Null)
[StateOrProvince] = Nz([DeliveryStateOrProvince], Null)
[PostalCode] = Nz([DeliveryPostalCode], Null)
[Country/Region] = Nz([DeliveryCountry/Region], Null)

ExitProc:
Exit Sub

ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub

------------------------------------------

Any clues as to where I have gone wrong

Thanks
Paul

 

[Paul Martin]

Tom

1. I have the autocorrupt feature turned on
2. I have changed the order as you are right in that it is Address1 I need
to write to DeliveryAddress1
3. I found a space that shouldnt have been there in the Address2 control -
it had a space between Address and 2 in the Control Source in the from and
in the Field Name in the table but not in the code written.

Since I have changed that it works now.

To do exactly what i want it needs one extra bit of cleverness that is over
and above the original aim of the code. I have noticed that the code will
overwrite an existing delivery address and really I would like it to do one
of two things but i can't make my mind up as to what it is i want it to do.
The two options are: -

1. Don't update non blank records. Chances are that if there is already a
delivery address there its meant ot be so on the rare occasions it needs
changing then it wouldn't hurt me to edit the one that is there.
or
2. Ask me if i really meant to update the record and give me a Yes or No
option

Can you advise me how to do either of these or am I pushing my luck to far
now?

Anyway, thanks for your help so far.

Paul Martin
---------------------------------------------------------
For record the code is now.....

Private Sub Command59_Click()
On Error GoTo ProcError


[DeliveryAddress1] = Nz([Address1], Null)
[DeliveryAddress2] = Nz([Address2], Null)
[DeliveryCity] = Nz([City], Null)
[DeliveryStateorProvince] = Nz([StateOrProvince], Null)
[DeliveryPostalCode] = Nz([PostalCode], Null)
[DeliveryCountry/Region] = Nz([Country/Region], Null)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub

 

[Guest]

Hi Paul,

Since I have changed that it works now.

Glad to hear that you got it working!

I'd like to suggest that you consider renaming your command button from
"Command59" to something more reasonable, such as "cmdCopyFields". The title
of your ProcError section includes "Error in procedure
cmdCopyFields_Click...", so this seems like a better name to me.

1. Don't update non blank records.

Use an If...Then test to check for an existing value. Concatenate a zero
length string ("") to the field, and check the length:

Private Sub cmdCopyFields_Click()
On Error GoTo ProcError

If Len([DeliveryAddress1] & "") = 0 Then
[DeliveryAddress1] = Nz([Address1], Null)
End If

If Len([DeliveryAddress2] & "") = 0 Then
[DeliveryAddress2] = Nz([Address2], Null)
End If

' etcetera

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub

2. Ask me if i really meant to update the record and give me a Yes or No
option

Use the message box function, as in the following example:

Private Sub cmdCopyFields_Click()
On Error GoTo ProcError

Dim intResponse As Integer

intResponse = _
MsgBox("Do you really want to change the shipping information?", _
vbQuestion + vbYesNo, "Please Confirm Change Of Address...")

If intResponse = vbYes Then
[DeliveryAddress1] = Nz([Address1], Null)
[DeliveryAddress2] = Nz([Address2], Null)
[DeliveryCity] = Nz([City], Null)
[DeliveryStateorProvince] = Nz([StateOrProvince], Null)
[DeliveryPostalCode] = Nz([PostalCode], Null)
[DeliveryCountry/Region] = Nz([Country/Region], Null)
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Tom

1. I have the autocorrupt feature turned on
2. I have changed the order as you are right in that it is Address1 I need
to write to DeliveryAddress1
3. I found a space that shouldnt have been there in the Address2 control -
it had a space between Address and 2 in the Control Source in the from and
in the Field Name in the table but not in the code written.

Since I have changed that it works now.

To do exactly what i want it needs one extra bit of cleverness that is over
and above the original aim of the code. I have noticed that the code will
overwrite an existing delivery address and really I would like it to do one
of two things but i can't make my mind up as to what it is i want it to do.
The two options are: -

1. Don't update non blank records. Chances are that if there is already a
delivery address there its meant ot be so on the rare occasions it needs
changing then it wouldn't hurt me to edit the one that is there.
or
2. Ask me if i really meant to update the record and give me a Yes or No
option

Can you advise me how to do either of these or am I pushing my luck to far
now?

Anyway, thanks for your help so far.

Paul Martin
---------------------------------------------------------
For record the code is now.....

Private Sub Command59_Click()
On Error GoTo ProcError


[DeliveryAddress1] = Nz([Address1], Null)
[DeliveryAddress2] = Nz([Address2], Null)
[DeliveryCity] = Nz([City], Null)
[DeliveryStateorProvince] = Nz([StateOrProvince], Null)
[DeliveryPostalCode] = Nz([PostalCode], Null)
[DeliveryCountry/Region] = Nz([Country/Region], Null)

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdCopyFields_Click..."
Resume ExitProc
End Sub