Cycling through records not fields using a recordset?? 1

[emaynard]

This is going to be a long one but here goes...

I'm having a problem....

I've been working on this database for about a week now and I'm almost finished, but can't get it to do one last thing I need it to.

The individual I'm creating this for wants to track how many technicians she needs at a glance. So when a job is open...I've made it where she can click on a "calendar" tab and it will show here a small sort of spreadsheet starting with week 1 - week 8. (i hope you can all picture this). The rows are the different type of techs needed (i.e. leads, install, security, etc) and the columns represent week 1, week 2, etc.

Now here is where it gets tricky. When looking at the calendar it shows the spreadsheet where she can enter the numbers for the techs needed. But when she opens the db next week, the numbers need to roll back. (i.e. Week 8 becomes week 7, week 7 becomes week 6, and so forth). I've made it where week 1, dumps into a "past week" field and keeps accumulating so she can keep track of the total techs needed for the entire job.

I have done the code to make the numbers roll back, but my problem is that it is only happening for the first record and no others. Instead of cycling through each record, it is only cycling through each column of the first record. I'm new to using recordsets so any help would be appreciated. The code I've used for this is below...I would appreciate any help anyone can offer. Thanks!!

emaynard

'---------------------------------------------
Option Compare Database
Option Explicit
'---------------------------------------------
Dim intWeek As Integer
Dim intCount As Integer

Public Function CalendarFunction()

intWeek = DateDiff("w", #12/30/2002#, Date)
intCount = 0      '(only used until database is finished, 
                  ' then will be removed)

'If current week = count then run transfer

    If intCount = intWeek Then  
        DoCmd.OpenForm "sbfCalendar"
        Forms!sbfCalendar.Visible = False
             TransferDates
        intCount = intCount + 1
        DoCmd.close
        Exit Function
      Else:  'do nothing
    End If

'If db hasn't been run in a week – run twice to catch up 
    If (intWeek - intCount = 2) Then 
         MoveDates
         MoveDates
         intCount = intCount + 2
         Exit Function
         Else: Exit Function
    End If

End Function
'-----------------------------------------
Public Function TransferDates()

Dim int1 As Long
Dim int2 As Long
Dim int3 As Long
Dim int4 As Long
Dim int5 As Long
Dim int6 As Long
Dim int7 As Long
Dim int8 As Long
Dim intpw As Long

'Set values equal to variables

Forms!sbfCalendar!intpastWeeks.SetFocus
intpw = Forms!sbfCalendar!intpastWeeks.Value + Forms!sbfCalendar!intwk1.Value

Forms!sbfCalendar!intwk1.SetFocus
int1 = Forms!sbfCalendar!intwk2.Value

Forms!sbfCalendar!intwk2.SetFocus
int2 = Forms!sbfCalendar!intwk3.Value

Forms!sbfCalendar!intwk3.SetFocus
int3 = Forms!sbfCalendar!intwk4.Value

Forms!sbfCalendar!intwk4.SetFocus
int4 = Forms!sbfCalendar!intwk5.Value

Forms!sbfCalendar!intwk5.SetFocus
int5 = Forms!sbfCalendar!intwk6.Value

Forms!sbfCalendar!intwk6.SetFocus
int6 = Forms!sbfCalendar!intwk7.Value

Forms!sbfCalendar!intwk7.SetFocus
int7 = Forms!sbfCalendar!intwk8.Value

Forms!sbfCalendar!intwk8.SetFocus
int8 = "0"

'Transfer values between variables

intpw = intpw + int1
int1 = int2
int2 = int3
int3 = int4
int4 = int5
int5 = int6
int6 = int7
int7 = int8
int8 = "0"

'Place values back into form

Forms!sbfCalendar!intpastWeeks.SetFocus
Forms!sbfCalendar!intpastWeeks.Value = intpw

Forms!sbfCalendar!intwk1.SetFocus
Forms!sbfCalendar!intwk1.Value = int1

Forms!sbfCalendar!intwk2.SetFocus
Forms!sbfCalendar!intwk2.Value = int2


Forms!sbfCalendar!intwk3.SetFocus
Forms!sbfCalendar!intwk3.Value = int3


Forms!sbfCalendar!intwk4.SetFocus
Forms!sbfCalendar!intwk4.Value = int4


Forms!sbfCalendar!intwk5.SetFocus
Forms!sbfCalendar!intwk5.Value = int5


Forms!sbfCalendar!intwk6.SetFocus
Forms!sbfCalendar!intwk6.Value = int6

Forms!sbfCalendar!intwk7.SetFocus
Forms!sbfCalendar!intwk7.Value = int7


Forms!sbfCalendar!intwk8.SetFocus
Forms!sbfCalendar!intwk8.Value = int8

End Function
'------------------------------------------------------
Public Function MoveDates()

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "sbfCalendar", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

  Do Until rst.EOF
     TransferDates
     rst.MoveNext
  Loop

End Function

 

[JerichoJ]

Ok,

The reason that it is only working for the first record is that you have to realize that the "TransferDates" function doesn't know *anything* about the rst you have declared in "MoveDates". Looping through each record of the recordset in "MoveDates" does nothing to effect what records are current on your hidden form, or what records the "TransferDates" function is acting upon.

You can remove the "MoveDates" function. It isn't doing anything for you. Even though what you have setup isn't the right way to do what you want, there is a way to go ahead and make it work.

Your "TransferDates" function is acting on the current record that the form is looking at. What you haven't done in your code is get the form to change the current record to be anything other than the first record. You should put the code in "TransferDates" in a loop. That loop needs to change to the next record at the end of the loop and then check to make sure we aren't on a new (empty) record before performing your transfers.

Dim rst As DAO.RecordSet

Set rst = Forms!sbfCalendar.RecordSetClone
'Or you might have to add a reference to .form such as:
'  Set rst = Forms!sbfCalendar.Form.RecordSetClone
'  I can never remember off the top of my head

Do While Not rst.EOF
  'Your code after your Dim Statements
  'Must be altered to refer to the actual
  'field names within the recordset instead
  'of the control names on the form

  intpw = rst("intpastWeeks") + rst("intwk1")
  int1 = rst("intwk2")
  int2 = rst("intwk3")
  'etc, etc

  'remember, if the name of your field in your
  'table isn't "intwk2" then you need to change
  'this to the actual name of that field
  
  rst.MoveNext
Loop
rst.Close
Set rst = Nothing

One other way to do this is to never open the form at all. There really isn't any need to open the form. Just have your code grab the recordset that the form normally points to. Meaning, if your forms recordsource property is some query named "qryWhatever", then you would do the following:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = Currentdb()
Set rst = db.OpenRecordset("qryWhatever", dbOpenDynaset)
Do While Not rst.EOF
  intpw = rst("intpastWeeks") + rst("intwk1")
  int1 = rst("intwk2")
  int2 = rst("intwk3")
  'etc, etc

  rst.MoveNext  
Loop
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

This way you never have to open the form hidden. You don't have to do all of that Forms! stuff, it reduces the code significantly, and doesn't hard code the name of the form which could change some day causing all of the code to have to be altered.

 

[emaynard]

I ended up taking out the TransferDates code and used the MoveDates, but doing the same thing you said. Thanks for your help and quick response!