On database open check field for blanks and extract last 4 digits from other field?

[Shenlung]

Ok, new question.

I want to make the database (a simple table) loop through every record in the field "Last4SSno" and extract the last 4 digits from "SSno" if the "Last4SSno" field is blank, or if the 4 digits in Last4SSno does not match the last 4 digits of "SSno". I have google searched for hours, but I cannot even think of how to phrase a google query right now, and I'm getting a little fried on the idea. I would prefer to do this on database open, but if it has to be done some other way, that is acceptable as well, I'm just adept enough to modify the code to my own uses, it's just the *Private sub OnDBOpen* part and the *DoCMD.RunSQL Insert * in Last4SSno Right("SSno", 4) where "Last4SSno" isnull* part that I cannot seem to find help on(I think).

I could have the whole problem backwards, I just don't know anymore.

 

[Shenlung]

Reading through it again, the problem is not clear.

I need the field "Last4SSno" to be the last 4 digits of the field "SSno" for every record in the database (2300 records as of this morning). I have a rough idea of what I need it to do, I just need to figure out the first chunk:

Private Sub OnDBOpen()

Then the code:

    DoCmd.SetWarnings False
    If Last4SSno = "" Or Last4SSno = vbNullString Or Last4SSno <> Right("SSno", 4) Then
      strSQL = "UPDATE MedicalRecords SET Last4SSno = Right("SSno", 4)"
    DoCmd.RunSQL strSQL

Or something close to that. I'll have to make it loop, but since the primary key is the 9 digit social security number, that might get interesting. Maybe do a BoF with a "IfnotEoF Then NextRecord"? Not too clear on looping yet either, gonna research that part for now.

 

[spikepl]

1. Plain looping though all records in VBA is bound to be slower than running a query which is optimised by the machinery, and makes use of the indexes. So use a query when you can
2. Looping in Vba can be done a bit smarter by using the FindFirst/FindNext method etc instead of just blindly looping over all records and checking each. This works best if the items to be found are plain values for the involved fields and those fields are indexed. In your particular case, there might not be any difference.
3. If problem constructing the query, use the query designer to first make a normal SELECT query, just to pick the records you want to update. Once that works, then in the query designer change the query into an update query

 

[Shenlung]

Awesome, that solves the looping issue, I'll use the query designer to hunt out the records that are wrong, but I still don't know how to make it run silently in the background on startup. This needs to update automatically without user input, but I only need it to run once per session, I already have something similar implemented in my update queries I used for the primary userform (Set Last4SSno = Right( & Me.txtSSno & , 4)) or something to that effect... I'm not in my office right this second, so I can't look it up lol.

I was of the opinion that having it run at database open would work, but if that won't work I would love to find a solution that would work... A sub that runs only once every time the MDB is opened, what a novel idea

 

[spikepl]

you can run code from a macro that automagically gets executed at start up, if called Autoexec, or from the open event of a form, if you have a form that opens when db opens

 

[Shenlung]

Beautiful, That should work nicely. I'll do the Autoexec first and see if that works, I'll call the sub from my Private Sub Form_Activate() only if the autoexec doesn't work, as the form might be opened and closed a few times during the course of the day, and I'd rather not bog things down on this slow network any more than absolutely neccesary.

Spike, again, you have saved my day. Thank you!

I'll post the completed code here for posterity when it is done.