Next Try

[gblack]

OK Guys&Gals,
I’ve been trying to get an answer in some way, shape, or form, via my last few posts, but I think I’m on the wrong track… So, I’m gonna back up and ask a broader question.

I have a bunch of fields in a table all named sequentially: MM1YY, MM2YY, MM3YY….MM12YY.

I also have this table linked to a form, where all of these fields need to be updated, based upon the Value entered in the first field… MM1YY.

Meaning if the user enters 08/05 for MM1YY, I want MM2YY, MM3YY, MM4YY…etc… To be updated 09/05, 10/05, 11/05… for all remaining fields. (BTW: They enter the value as a string, not a date field)

Without going into database design (as I am not the person who created the database), I was wondering if I can programmatically auto fill these values. I mean… I know I can. I already did so with a couple of case statements that loops through each possibility and fills in the value for each field… But what I want is a better way of doing this, where I don’t have to write out all the the names of the fields by hand. My thought was, it’s fine if there are 12 fields, but what if there were 25?...Or more?

I’d want to be able to loop through and post the values to each of the sequential fields in the form, without having to hard code each field name into the code, but I can’t seem to figure out how?

I’ve looked up a bunch of stuff on the subject but seem to be running into dead ends… I thought using collections might be the answer, but that seems to just give me an array which can't update the values in the table… maybe a record set, which updates the table, might work… however, at this point, I am lost as to how to reference each field and place the value in it.

Right now my code works and it looks like this:

Private Sub MM1_YY_LostFocus()

Dim strDate, strCell As String
Dim intMM, intYY, intCount, i As Integer

strDate = Me.MM1YY
intMM = CInt(Left(strDate, 2))
intYY = CInt(Right(strDate, 2))

Do While intCount < 13

If intMM < 12 Then
Select Case intMM
Case 1
strCell = "02/0" & intYY
Case 2
strCell = "03/0" & intYY
Case 3
strCell = "04/0" & intYY
Case 4
strCell = "05/0" & intYY
Case 5
strCell = "06/0" & intYY
Case 6
strCell = "07/0" & intYY
Case 7
strCell = "08/0" & intYY
Case 8
strCell = "09/0" & intYY
Case 9
strCell = "10/0" & intYY
Case 10
strCell = "11/0" & intYY
Case 11
strCell = "12/0" & intYY
End Select
Else
strCell = "01/0" & intYY + 1
intMM = 0
intYY = intYY + 1
End If
intMM = intMM + 1
Select Case intCount
Case 2
Me.MM2YY = strCell
Case 3
Me.MM3YY = strCell
Case 4
Me.MM4YY = strCell
Case 5
Me.MM5YY = strCell
Case 6
Me.MM6YY = strCell
Case 7
Me.MM7YY = strCell
Case 8
Me.MM8YY = strCell
Case 9
Me.MM9YY = strCell
Case 10
Me.MM10YY = strCell
Case 11
Me.MM11YY = strCell
Case 12
Me.MM12YY = strCell
End Select
intCount = intCount + 1
Loop
Me.Refresh

End Sub

Even though it works, it seems like it is a lot of lines for the little that it actually does… BTW: I know with the first case statement, there’s probably a more efficient way of writing that as well… but I was working so hard on the one aspect, I kind of got a bit lazy on the other…while I wait for an answer, maybe I’ll figure out something better than a case statement for that section…

So… Maybe you bigger brained people can answer: what is a better way of doing this?

Thanks,
Gary

 

[FoFa]

Use a function Something like:
FUNCTION FigureThisDumbNumber(OrgNum as string, HowMany as Integer) as string
DIM OldNum as Int, OldYr as integer
DIM OrgDate as date, NewDate as Date
OldNum = CINT(LEFT(OrgNum,INSTR(OrgNum,"/")-1))
OldYr = CINT(Mid(OrgNum,INSTR(OrgNum,"/")+1))
if OldNum < 1 then
FigureThisDumbNumber = 0
Exit Function
End If
OldDate = DateSerial(OldYr, OldNum, 1) ' Set to first day of month
NewDate = Dateadd("m",HowMany,OldDate)
FigureThisDumbNumber = cstr(month(NewDate)) & "/" & Right(Cstr(Year(NewDate)),2)
exit function

Then you can use it many different ways.
Default value of a control could be =FigureThisDumbNumber(Forms!MyForm!FirstControl,1)
=FigureThisDumbNumber(Forms!MyForm!FirstControl,2)
=FigureThisDumbNumber(Forms!MyForm!FirstControl,3)

Or you could in VBA
MyControl2 = FigureThisDumbNumber(Forms!MyForm!FirstControl,1)
MyControl3 = FigureThisDumbNumber(Forms!MyForm!FirstControl,2)
etc.

Just one way.

 

[Pat Hartman]

But what I want is a better way of doing this,

You can't really get there without talking about table structure and field definitions.

Whenever you have date information, it should be stored as a date data type. You can format the date so it only shows month and year but you should store a real date. Just use 1 as the day. That solves a lot of your problem since you can now use DateAdd() to add one month to the date and Access will take care of turning the year from December to January. And then there is the problem of the repeating group. If your tables were properly normalized, you wouldn't have twelve dates to deal with, you would only have one because the dates would be rows rather than columns.

 

[workmad3]

a more workable version of what you have though (although I agree with pat that you should probably look at table structures etc) is

Private Sub MM1_YY_LostFocus()

Dim strDate as String, strCell As String
Dim intMM as integer, intYY as integer, intCount as Integer, i As Integer

strDate = Me.MM1YY
intMM = CInt(Left(strDate, 2))
intYY = CInt(Right(strDate, 2))

Do While intCount < 13

If intMM < 12 Then
strCell = "0" & intMM + 1 & "0/0" & intYY
else

strCell = "01/0" & intYY + 1
intMM = 0
intYY = intYY + 1
End If
intMM = intMM + 1
me.Controls("MM" & intCount & "YY") = strCell
Loop
Me.Refresh
End sub

Hope this helps

 

[gblack]

Thanks!

WorkMad3 this is what I have been looking for!!!

Pat: The reason why I didn't want to get into the table structure is twofold, first…it’s not my database and the guy who asked me for help didn’t design it either. He just wanted his form to auto fill these dates. I didn’t want to get into any other issues with him, because it was evident to me that (working for the US Fed govt.) that in order to deal with that issue, it would have taken far more time than it was really worth.

Second, I really wasn’t interested in fixing that issue, honestly, I just wanted to know how I could loop through those objects I referred to in my post… that was my real question.

Since I had a work around, I was only interested in how I could fill these values via a loop vs. writing them all in a case statement… WorkMad3’s answer was exactly what I was looking for. The real answer to my question was: “me.Controls("MM" & intCount & "YY") = strCell” That’s all I wanted to know.

So thanks everyone! This really does help! BTW: Here’s the tweaked code...

Private Sub MM1_YY_LostFocus()

Dim strDate As String, strCell As String
Dim intMM As Integer, intYY As Integer, intCount As Integer, i As Integer

strDate = Me.MM1YY
intMM = CInt(Left(strDate, 2))
intYY = CInt(Right(strDate, 2))
intCount = 2
Do While intCount < 13

If intMM < 12 Then
strCell = "0" & intMM + 1 & "/0" & intYY
Else

strCell = "01/0" & intYY + 1
intMM = 0
intYY = intYY + 1
End If
intMM = intMM + 1
Me.Controls("MM" & intCount & "YY") = strCell
intCount = intCount + 1
Loop
Me.Refresh

End Sub

 

[workmad3]

one last tweak to that now I look at it again

Private Sub MM1_YY_LostFocus()

Dim strDate As String, strCell As String
Dim intMM As Integer, intYY As Integer, intCount As Integer, i As Integer

strDate = Me.MM1YY
intMM = CInt(Left(strDate, 2))
intYY = CInt(Right(strDate, 2))

for intcount = 2 to 12 'start the for loop

If intMM < 12 Then
strCell = "0" & intMM + 1 & "/0" & intYY
Else

strCell = "01/0" & intYY + 1
intMM = 0
intYY = intYY + 1
End If
intMM = intMM + 1
Me.Controls("MM" & intCount & "YY") = strCell

Next i 

Me.Refresh

End Sub

Just changes it to a for next loop, rather than a while loop running for a fixed amount of time. Its more a cosmetic change, but a for loop makes more sense than a while loop does, so seems to fit better.

 

[gblack]

Yep

That looks nice!

Thanks for the help!!!

G

 

[gblack]

Umm

except I don't think "i" is needed... Is it?

 

[gblack]

Last last tweak

actually I had to change it so that it wouldn't add a leading zero when the month was 10, 11, or 12... so here's my latest tweak:

Private Sub MM1_YY_LostFocus()

Dim strDate, strCell As String
Dim intMM, intYY, intCount As Integer

strDate = Me.MM1YY
intMM = CInt(Left(strDate, 2))
intYY = CInt(Right(strDate, 2))

For intCount = 2 To 12 'start the for loop

If intMM < 12 Then
If intMM = 9 Or Len(intMM) > 1 Then
strCell = intMM + 1 & "/0" & intYY
Else
strCell = "0" & intMM + 1 & "/0" & intYY
End If
Else

strCell = "01/0" & intYY + 1
intMM = 0
intYY = intYY + 1
End If
intMM = intMM + 1
Me.Controls("MM" & intCount & "YY") = strCell

Next

Me.Refresh

End Sub

 

[workmad3]

Try this as a more readable way of doing this

For intCount = 2 To 12 'start the for loop

If intMM >= 9 And intMM < 12 Then
strCell = intMM + 1 & "/0" & intYY
ElseIf intMM < 9 Then
strCell = "0" & intMM + 1 & "/0" & intYY
ElseIf intMM = 12 Then
strCell = "01/0" & intYY + 1
intMM = 0
intYY = intYY + 1
End If

intMM = intMM + 1
Me.Controls("MM" & intCount & "YY") = strCell

Next

Me.Refresh

End Sub