Changing case on multiple records in subform

[Lloyd]

I have written code that takes a report number that user enters and then
grabs data from an oracle database and updates the access database. However
the data that comes in is in all uppercase. One of my subforms that the data
is imported to is a continuous forms view and generally contains multiple
records. After the data is added through my append query, I am using the
below code to convert the last name and first name to title case. But it
only works on the first record. How do I get the code to cycle through the
other records in the subform

Forms![frmMainEntry]![subfrmPersons]![LastName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![LastName], 3)

Forms![frmMainEntry]![subfrmPersons]![FirstName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![FirstName], 3)

Thanks in advance....

 

[Marshall Barton]

I have written code that takes a report number that user enters and then
grabs data from an oracle database and updates the access database. However
the data that comes in is in all uppercase. One of my subforms that the data
is imported to is a continuous forms view and generally contains multiple
records. After the data is added through my append query, I am using the
below code to convert the last name and first name to title case. But it
only works on the first record. How do I get the code to cycle through the
other records in the subform

Forms![frmMainEntry]![subfrmPersons]![LastName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![LastName], 3)

Forms![frmMainEntry]![subfrmPersons]![FirstName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![FirstName], 3)

That would be better done in the form's record source query
so the form would not have to be bothered with it.

 

[Lloyd]

Marshall,

Do you mean using strconv in the query to do the conversion on the front
end? If this is what you mean, I tried but I couldnt figure out how to get
it to work in an append query, only figured it out in a update query??

I have written code that takes a report number that user enters and then
grabs data from an oracle database and updates the access database. However
the data that comes in is in all uppercase. One of my subforms that the data
is imported to is a continuous forms view and generally contains multiple
records. After the data is added through my append query, I am using the
below code to convert the last name and first name to title case. But it
only works on the first record. How do I get the code to cycle through the
other records in the subform

Forms![frmMainEntry]![subfrmPersons]![LastName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![LastName], 3)

Forms![frmMainEntry]![subfrmPersons]![FirstName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![FirstName], 3)

That would be better done in the form's record source query
so the form would not have to be bothered with it.

 

[Marshall Barton]

Just use the expression:

StrConv([LastName], 3)

as a calculated field in the Select part of the append
query.
--
Marsh
MVP [MS Access]

Do you mean using strconv in the query to do the conversion on the front
end? If this is what you mean, I tried but I couldnt figure out how to get
it to work in an append query, only figured it out in a update query??

That would be better done in the form's record source query
so the form would not have to be bothered with it.>> Lloyd wrote:

I have written code that takes a report number that user enters and then
grabs data from an oracle database and updates the access database. However
the data that comes in is in all uppercase. One of my subforms that the data
is imported to is a continuous forms view and generally contains multiple
records. After the data is added through my append query, I am using the
below code to convert the last name and first name to title case. But it
only works on the first record. How do I get the code to cycle through the
other records in the subform

Forms![frmMainEntry]![subfrmPersons]![LastName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![LastName], 3)

Forms![frmMainEntry]![subfrmPersons]![FirstName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![FirstName], 3)

 

[Lloyd]

Ok, I'm a little slow today. I understand what your saying about modifying
the forms record source. I did a quick test, I place to 47 fields in the
query and then tried to modify just the last name. I changed it to LastName:
strconv([LastName],3)

I then got a circular error. I know I could change the expression name, but
then I would have to go in a modify all my forms to look at the new expresion
name.

Thats why I thougth it would be easier to run some code after the append
query is run to update the back end tables.

Or am I doing something wrong in my expression?

I have written code that takes a report number that user enters and then
grabs data from an oracle database and updates the access database. However
the data that comes in is in all uppercase. One of my subforms that the data
is imported to is a continuous forms view and generally contains multiple
records. After the data is added through my append query, I am using the
below code to convert the last name and first name to title case. But it
only works on the first record. How do I get the code to cycle through the
other records in the subform

Forms![frmMainEntry]![subfrmPersons]![LastName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![LastName], 3)

Forms![frmMainEntry]![subfrmPersons]![FirstName] =
StrConv(Forms![frmMainEntry]![subfrmPersons]![FirstName], 3)

That would be better done in the form's record source query
so the form would not have to be bothered with it.

 

[Marshall Barton]

Ok, I'm a little slow today. I understand what your saying about modifying
the forms record source. I did a quick test, I place to 47 fields in the
query and then tried to modify just the last name. I changed it to LastName:
strconv([LastName],3)

I then got a circular error. I know I could change the expression name, but
then I would have to go in a modify all my forms to look at the new expresion
name.

Thats why I thougth it would be easier to run some code after the append
query is run to update the back end tables.

That usually(?) works in a query.

I never heard of a circular reference error in a query. Are
you sure you don't have a form control named X with an
expression that includes X?

You should run the Select query by itself to make sure the
query works or if it has a problem. Don't mess with the
form until the query does what you need.

 

[Douglas J. Steele]

Ok, I'm a little slow today. I understand what your saying about
modifying
the forms record source. I did a quick test, I place to 47 fields in the
query and then tried to modify just the last name. I changed it to
LastName:
strconv([LastName],3)

I then got a circular error. I know I could change the expression name,
but
then I would have to go in a modify all my forms to look at the new
expresion
name.

Thats why I thougth it would be easier to run some code after the append
query is run to update the back end tables.

That usually(?) works in a query.

I never heard of a circular reference error in a query. Are
you sure you don't have a form control named X with an
expression that includes X?

In my experience, you need to include the name of the table, even if the
field name is unique.

LastName: strconv([MyTable].[LastName],3)

 

[Marshall Barton]

Ok, I'm a little slow today. I understand what your saying about
modifying
the forms record source. I did a quick test, I place to 47 fields in the
query and then tried to modify just the last name. I changed it to
LastName:
strconv([LastName],3)

I then got a circular error. I know I could change the expression name,
but
then I would have to go in a modify all my forms to look at the new
expresion
name.

Thats why I thougth it would be easier to run some code after the append
query is run to update the back end tables.

That usually(?) works in a query.

I never heard of a circular reference error in a query. Are
you sure you don't have a form control named X with an
expression that includes X?

In my experience, you need to include the name of the table, even if the
field name is unique.

LastName: strconv([MyTable].[LastName],3)

That may very well explain why it doesn't always work.
Thanks for the enlightening info.

 

[Lloyd]

Lloyd wrote:
Ok, I'm a little slow today. I understand what your saying about
modifying
the forms record source. I did a quick test, I place to 47 fields in the
query and then tried to modify just the last name. I changed it to
LastName:
strconv([LastName],3)

I then got a circular error. I know I could change the expression name,
but
then I would have to go in a modify all my forms to look at the new
expresion
name.

Thats why I thougth it would be easier to run some code after the append
query is run to update the back end tables.


That usually(?) works in a query.

I never heard of a circular reference error in a query. Are
you sure you don't have a form control named X with an
expression that includes X?

In my experience, you need to include the name of the table, even if the
field name is unique.

LastName: strconv([MyTable].[LastName],3)

That may very well explain why it doesn't always work.
Thanks for the enlightening info.

I will give it another try tomorrow, but one question since I am using
strconv in my query for the form, doesnt that just display it on the form
correctly, but on the backend of the database it still remains in caps?

If that is the case, I need to find a way to get the backend data converted
to title case on those fields as other apps use the data from the backend and
my problem then moves to the other apps. I figured if I could learn a way to
convert those fields after they are imported by cycling through the records
on the subform and changing the first and last name to title case so I dont
have to do this in the other apps/reports that use this data.

I'm just not sure how to write the code that goes to the specific fields in
each record on the subform. I have the code that does the conversion as it
works, but it only works on the first record and doesnt change the case on
the other records on the subform.....

Any thoughts would be appreciated.

 

[John W. Vinson]

I will give it another try tomorrow, but one question since I am using
strconv in my query for the form, doesnt that just display it on the form
correctly, but on the backend of the database it still remains in caps?

Can you just run an Update query *on the table*? The form doesn't have to be
involved in the least; just run an update query updating the field to
StrConv([fieldname], 3) to permanently change the case *in the table*.

 

[Lloyd]

I will give it another try tomorrow, but one question since I am using
strconv in my query for the form, doesnt that just display it on the form
correctly, but on the backend of the database it still remains in caps?

Can you just run an Update query *on the table*? The form doesn't have to be
involved in the least; just run an update query updating the field to
StrConv([fieldname], 3) to permanently change the case *in the table*.

John,

I wasnt sure if I could run the update query while the record was still
open. I'll give it a try and see if it works.

 

[Lloyd]

I will give it another try tomorrow, but one question since I am using
strconv in my query for the form, doesnt that just display it on the form
correctly, but on the backend of the database it still remains in caps?

Can you just run an Update query *on the table*? The form doesn't have to be
involved in the least; just run an update query updating the field to
StrConv([fieldname], 3) to permanently change the case *in the table*.

I created a make table query to bring in the oracle data, then I ran an
update query on that temp table and then used the append query to bring the
data into access...thanks!

 

[John W. Vinson]

I wasnt sure if I could run the update query while the record was still
open. I'll give it a try and see if it works.

You'll probably get an error "the table was already open by another user". But
it's worth a try.

The ideal would be to do the conversion using the update query, once, with the
form closed; and put code in the form textbox's AfterUpdate event to
propercase the field as soon as it's entered:

Private Sub txtFieldname_AfterUpdate()
If Len(Me!txtFieldname) > 0 Then
Me!txtFieldname = StrConv(Me!txtFieldname, vbProperCase)
End If
End Sub

So long as all input is done through the Form this should cover all the cases
(old data will have been updated using the query, new data will be converted
as it is added).

 

[Marshall Barton]

I will give it another try tomorrow, but one question since I am using
strconv in my query for the form, doesnt that just display it on the form
correctly, but on the backend of the database it still remains in caps?

Can you just run an Update query *on the table*? The form doesn't have to be
involved in the least; just run an update query updating the field to
StrConv([fieldname], 3) to permanently change the case *in the table*.

I wasnt sure if I could run the update query while the record was still
open. I'll give it a try and see if it works.

What do you mean bt "while the record was still open"?

An Update query could/should updata every record in the
table and you only need to do that once (immediately after
the import).

 

[Marshall Barton]

I will give it another try tomorrow, but one question since I am using
strconv in my query for the form, doesnt that just display it on the form
correctly, but on the backend of the database it still remains in caps?

Can you just run an Update query *on the table*? The form doesn't have to be
involved in the least; just run an update query updating the field to
StrConv([fieldname], 3) to permanently change the case *in the table*.

I created a make table query to bring in the oracle data, then I ran an
update query on that temp table and then used the append query to bring the
data into access...thanks!

In that case, I would try using a one time update query to
fix all existing data in the final destination table. Then
use StrConv in either the make table or append query to deal
with all new data.