Automate my vba code with a module?

[cbrace09]

Hello,

I have the following code on my form "Customers Home". Anyone have an idea how I could do this without VBA? I'm entry level with access so nothing to overly complicated please.

Thank you in advance!!!

'This section will convert values to ALL CAPS
Private Sub Fname_LostFocus()
Me.Fname = UCase(Me.Fname)
End Sub
Private Sub Lname_LostFocus()
Me.Lname = UCase(Me.Lname)
End Sub
Private Sub Title_LostFocus()
Me.Title = UCase(Me.Title)
End Sub
Private Sub Email_LostFocus()
Me.Email = UCase(Me.Email)
End Sub
Private Sub Address1_LostFocus()
Me.Address1 = UCase(Me.Address1)
End Sub
Private Sub Address2_LostFocus()
Me.Address2 = UCase(Me.Address2)
End Sub
Private Sub City_LostFocus()
Me.City = UCase(Me.City)
End Sub
Private Sub Notes_LostFocus()
Me.Notes = UCase(Me.Notes)
End Sub
'End Section

' This section will make entries to the Updated and Updated By field when a field value is changed AND force ALL CAPS in specific fields.
Private Sub Fname_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub Lname_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub Title_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub Email_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub WorkPhone_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub WorkExt_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub MobilePhone_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub FaxPhone_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub CompanyID_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub CustomerID_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub Company_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now()
End Sub
Private Sub Address1_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub Address2_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub City_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub State_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub Postal_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub CustomerType_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub CreditLine_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub JoinMailingList_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub Billable_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub PreferedCustomer_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub OKToEmailDocuments_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub Active_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub Notes_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
End Sub
Private Sub Attachments_Dirty(Cancel As Integer)
Me.UpdatedBy.Value = Forms![Home]![cbo_User]
Me.LastUpdated.Value = Now
' End Section

 

[RuralGuy]

Since Access is not case sensitive, why are you wasting time converting everything to UPPERCASE?

 

[cbrace09]

This mdb stores and creates legal documents. Most of our clients are used to all caps in their documents. The goal here is to ensure the generated report is stored and printed with all caps.

On second though, maybe I should just incorporate the ALL CAPS to the reports....?

 

[WIS]

' This section will make entries to the Updated and Updated By field when a field value is changed AND force ALL CAPS in specific fields.

Try Form BeforeUpdate - this will replace all your code. If one or more ctrls are changed then the BeforeUpdate will be triggered on exiting the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
... your two lines of code
End If
End Sub

If you go to tbl Design mode and put ">" in the Format section for any string(text) data type it will force all to Upper case (however, if you are using A2003 SP3 without the "hotfix" it may(will) show a blank, a "bug" in A2003 SP3.

 

[RuralGuy]

For the record, the FORM BeforeUpdate event *only* fires when the FORM is Dirty so there is no need to test for Me.Dirty.

 

[cbrace09]

Again, thanks for the posts.

As for the Before_Update right...as posted that will not work with the dity parameter.

Also, the input mask ">" doesn't work. Now, if I knew how many characters would be entered into each field, this method would work with something like >LLLLL (corey - would be COREY)

So, am I to understand that the code I originaly posted is the way to go?

Thanks in advance!

 

[RuralGuy]

The FORM BeforeUpdate *will* work to time stamp the record. You just do not need to test for Dirty since it will always be true.

 

[RuralGuy]

I would probably use a generic KeyPress procedure in all of the controls on the form to deal with the UpperCase issue.

 

[SOS]

On second though, maybe I should just incorporate the ALL CAPS to the reports....?

That would be my solution.

 

[cbrace09]

I tried the Key Press function but when in use, as soon as a single character is entered, the cursor reverts back to the beginning position in the field.

I also attempted to code the fields in the reports. This yielded an error message saying I could not define this variable....or something along those lines.

I have ended up just putting the code behind the Form_BeforeUpdate parameter and that seems to work just fine. It is a bit of a mess with all that code but it works!

Thanks for all the advice guys, I truly do appreciate it. Maybe someday I will be as knowledgeable as you pros out there!

 

[SOS]

I also attempted to code the fields in the reports. This yielded an error message saying I could not define this variable....or something along those lines.

You could have done it in the query underlying the report and just take each field:

MyField1New:UCase([MyField1])

MyField2New:UCase([MyField2])

MyField3New:UCase([MyField3])


what's more simple than that?

[

 

[cbrace09]

ALRIGHT! that works great! Thank you so much! I didn't even think about doing it that way.

 

[SOS]

ALRIGHT! that works great! Thank you so much! I didn't even think about doing it that way.

No problem - glad to have been of assistance.