Bit of Data-maintenance from Lovina-Bali

[Rich_Lovina]

Hi there, restoring some code I've had running tks to Forum for over a year, I've come accross 2 small problems:

1. If the code in my form is
Me.[Inits] = StrConv(Me.[Inits], vbUpperCase)
And original data is entered in lowercase, the code only displays in uppercase whereas on viewing in a table, the result is still lowercase; so how do I make it permanently Uppercase for the table (and export purposes)?

2. I have a field in my form & defined in the table design as a Yes/No field & I want the following to occur, ( but my code below is as used for a text field) :

Private Sub SECT_IT_AfterUpdate()
If Not IsNull(Me.SECTION_CC) Then
Sect_IT = YES and Me.GLOBAL_CC = Yes
End If
End Sub

I want this to occur when either editing or adding a record on the form?

Thanks in advance and Hi from sunny Lovina in Northern Bali where the laptop is just holding up.

 

[Tim K.]

Do you want to change the stored records to uppercase? You may use a update query with the StrConv() or use code to loop thru all the record and converse all to uppercase.

Or just put > (greated sign) in the Format property of the fields you want to make them look uppercase.

Or if you want the change the text justed insert in the a text box right away, apply the code below.

Private Sub Text0_AfterUpdate()
Me.Text0 = StrConv(Me.Text0, vbUpperCase)
End Sub


For 2nd question, just use the use the code below.

Private Sub SECT_IT_AfterUpdate()
If Not IsNull(Me.SECTION_CC) Then
Sect_IT = True and Me.GLOBAL_CC = True
End If
End Sub

 

[AnnPhil]

Update field

Is it possible to use an update query to update existing blank fields with data from another table? If not how would you do this?
I have a table that has 1000 records. I need to update one field in about 900 records. The value for the field is stored in another table, how can i get those values into the table.

 

[Tim K.]

Yes. But you must have a unique field in both tables so you can compare what to update with what. How are both tables related? What field(s) do you want to update and taking from what field(s)?

 

[AnnPhil]

Yes both tables have a unique field to tie them together. I have one field that i want to update with the values from the other table. Because they have a common field that links them together there should be a way to do this.

 

[Tim K.]

Check this update query SQL out.

UPDATE tblA INNER JOIN tblB ON tblA.ID = tblB.ID SET tblA.Cost = [tblb].[Cost]
WHERE (((tblA.ID)=[tblB].[ID]));

tblA and tblB have ID field as a unique id. It updates Cost field in tblA with Cost in tblB.

Back up your tables before doing this.

 

[AnnPhil]

Thanks Tim, it worked great!!!

 

[Rich_Lovina]

Dear Tim
Tks yr suggestions. I'll try the update approach, as from what you write there's no simple code to make the Strconv permanent from the textbox...it just appears to make the change to the text, i.e. in the form only.
Thanks part 2...had forgotten.