VB array with embedded loops

[marnieg]

It's been awhile since I have coded vbscript with Access and need some help. First I will give my issue in pseudo code and then my guess at how to code it

I have a database lets call dbtrack with 3 tables tblParameter, tblBallot, tblDist

I want to read each entry in tblDist sorted by two fields called strloc, strpre

When the strloc or strpre changes I read the tblParameter table to get a field intValue

I then want to create either a single or multiple entries in tblBallot for each tblDist based on a calculation of a field in the tblBallot plus the intValue

So here is my thinking on the coding. The user will be hitting a command button to envoke this code.

strSQLQuery = "SELECT * FROM tblDist ORDER BY strloc, strpre ASC"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSQLQuery, conn, 3, 3

strloc1 = rs.strloc
strpre1 = rs.strpe - is this how you references fields from array?
strloc_old = ""
strpre_old = ""

' need some type of looping through array
Do until rs.EOF ??? is this correct

' checking if next record has changed values
if strloc1 <> strloc_old or strpre1 <> strpre_old then

' read parameter table
intVal = "Select intVal from tblParameter where tblParameter.loc = strloc1 and tblParameter.pre = strpre1"
end if

intHigh = rs.High
intLow = rs.Low
intnew = intLow + intVal


'Now test if intnew less than inthigh insert another
Do until intnew > inthigh
' Now insert a record into tblBallot with new value
strSQLQuery = "INSERT INTO tblBallot (loc,pre,high,low) VALUES (" & strloc1 & ", " & strpre1 & ", " & intLow & ", " & intnew &")"
intLow = intnew
intnew = intnew + intVal
if intnew > intHigh then
intdiff = intNew - intHigh
' insert final record for the value up to high
strSQLQuery = "INSERT ...."
end if
Loop

Loop


Please recommend any sites or books for advanced vbscript using MS Access

Thanks

 

[WayneRyan]

marnieg,

I'm not really trying to following the logic, but this
should get you started

Dim rs As DAO.Recordset

strSQLQuery = "SELECT * FROM tblDist ORDER BY strloc, strpre ASC"
Set rs = CurrentDb.OpenRecordset(strSQLQuery)

strloc_old = "" 
strpre_old = ""

While Not rs.EOF And Not rs.BOF
   '
   ' checking if next record has changed values
   '
   ' Note: can also be referenced As [B]rs.Fields("strloc")[/B]
   '
   if [B]rs!strloc[/B] <> strloc_old or rs!strpe <> strpre_old then 
      ' read parameter table
      '
      ' DLookUp is easier, if the value doesn't exist, use the Nz function
      '                    intval = Nz(DLookUp(...), 0)
      '
      ' Split the DLookup over 3 lines for readability.
      '
      intVal = DLookUp("[intval]", _
                       "tblParameter", _
                       "loc = '" & strloc1 & "' And [pre] = '" & strpre1 & "'")
      '
      ' Since they changed values you should assign the new values here
      '
[B]      strloc_old = rs!strloc
      strpre_old = rs!strpe[/B]
   end if

   intHigh = rs.High
   intLow = rs.Low
   intnew = intLow + intVal


   'Now test if intnew less than inthigh insert another

   While intnew > inthigh
      '
      ' Now insert a record into tblBallot with new value
      '
      ' The CurrentDb.Execute is the way to execute Insert commands
      ' Note the use of single-quotes for string values
      '
      CurrentDb.Execute "INSERT INTO tblBallot (loc, pre, high, low) " & _
                        "VALUES ('" & strloc1 & "', '" & strpre1 & "', " & intLow & ", " & intnew &")"
      intLow = intnew
      intnew = intnew + intVal
      '
      if intnew > intHigh then
         intdiff = intNew - intHigh
         ' insert final record for the value up to high
         CurrentDb.Execute "INSERT ...."
      end if
      Wend
   rs.MoveNext ' Move to next record
Wend

hth,
Wayne

 

[marnieg]

Thank you for the coding example. Everything is working EXCEPT the While loop on the array. Any suggestions?
Thanks,

 

[marnieg]

Sorry, I figured out the problem. It was test data not coding error.

Thank you for your assistance.

 

[WayneRyan]

marnieg,

Glad to help ... hope to see you again.

Wayne