Understanding VBA

[lcook1974]

Hi All,
I am playing around with some VBA code because I want to understand how it works and just make my MS Access Databases more "efficient".

I really don't understand how varibles and constants get transferred around, like for a multi user database. I have front ends and back ends but no understanding of VBA code.

I did this little code to set all the First names in a table to upper case, but nothing is happening to the table.

I don't get any errors when I run it in the immediate window.

could someone help me out?

Sub UpperCase()
Dim db As Database
Dim rst As Recordset
 
'set Database to current and recordset to pull just the firstname column
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT CopyOfContact.First" & _
                           " FROM CopyOfContact " & _
                        "WHERE (((CopyOfContact.First) Is Not Null))")
If Not rst.EOF And Not rst.BOF Then
    For i = 0 To rst.Fields.Count - 1
        rst.MoveFirst
    Do Until rst.EOF
       
            If rst(i).Name = rst("First") Then
                rst.Edit
                rst.Fields("First") = UCase("First")
                rst.Update
            End If
            rst.MoveNext
    Loop
Next i
End If
Set db = Nothing
Set rst = Nothing

'Do While Not rst.EOF
     '    UCase (rst![First])
'rst.MoveNext
'Loop
End Sub

Thanks!
Larry

 

[SOS]

You don't need this part:

If rst(i).Name = rst("First") Then

Just go with this:

If Not rst.EOF And Not rst.BOF Then
    Do Until rst.EOF
                rst.Edit
                rst.Fields("First") = UCase(rstFields("First"))
                rst.Update
            End If
            rst.MoveNext
    Loop
 
End If
Set db = Nothing
Set rst = Nothing
End Sub

 

[Khalid_Afridi]

the code seems to be okay, this why it does not produce any error,
the most intrusting thing is happining here:

If Not rst.EOF And Not rst.BOF Then
    For i = 0 To rst.Fields.Count - 1
        rst.MoveFirst
    Do Until rst.EOF
       
            If rst(i).Name = rst("First") Then
                rst.Edit
                rst.Fields("First") = UCase("First")
                rst.Update
            End If
            rst.MoveNext
    Loop

Do your table fields name start with "First" ????

 

[lcook1974]

Thank you SOS!!!

 

[lcook1974]

@Khalid - Yes..they do, but I am looking to "re-vamp" the entire thing. I just need a better understanding of VBA to optimize my Database.

I think First and Last are keyword that I'll have to deal with when I re-design this thing.

Thank you for the reply!!!

Larry

 

[Khalid_Afridi]

Good luck larry

 

[lcook1974]

Thanks Khalid...I am sure you'll be seeing me hanging around a lot until I get a grip on VBA.

 

[Khalid_Afridi]

You are welcome my friend, I always eager to see good friends around me, SOS is one of them