Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Variables in Module? (A2k (9.0.3821) SR-1)

    Using Variables in Module?

    A2k (9.0.3821) SR-1

    I have some code that reads rs1.

    rs1 has a record count field lngIssued.

    For each record in rs1, I want to read this field into a variable lngIssued and use it as a limit counter to generate x number of records in rs2.

    There are many records in rs1 and each one has a record count field with a different number of records to generate.

    The following code works, however it uses a hard coded record count lngIssued.

    I need a few extra commands to make this code run dynamically based on record count field in rs1

    Any syntax help would be appreciated

    Thanks, John

    Sub NormalizeIt_tbl_RPS_Owner_416(DenormTable As String, NormTable As String)

    Dim strPrefix As String
    Dim strStartNo As String
    Dim lngStartNo As Long
    Dim lngIssued As Long
    Dim lngCounter As Long
    Dim strNo As String

    strPrefix = "#"
    strStartNo = "1" ' starting number
    lngIssued = 100 ' limit count

    lngStartNo = CInt(strStartNo)

    Dim db As Database
    Dim rs1 As Recordset
    Dim rs2 As Recordset

    DoCmd.Hourglass True

    Set db = CurrentDb
    Set rs1 = db.OpenRecordset(DenormTable) 'table with denormalized format
    Set rs2 = db.OpenRecordset(NormTable) 'table with normalized format

    rs1.MoveFirst

    Do While Not rs1.EOF

    For lngCounter = 1 To lngIssued
    With rs2
    rs2.AddNew
    rs2!strSWIS = rs1!strSWIS
    rs2!strPropClass = "270" ' Mobile Home
    rs2!strSchoolCode = rs1!strSchoolCode
    rs2!lngRollYear = rs1!lngRollYear
    rs2!strOwnerLastName = rs1!strOwnerLastName
    Select Case lngStartNo
    Case Is < 10
    strNo = "000"
    Case 10 To 99
    strNo = "00"
    Case 100 To 999
    strNo = "0"
    Case Is > 999
    strNo = ""
    End Select
    rs2!strLocStreetNo = rs1!strLocStreetNo & " " & strPrefix _
    & strNo & lngStartNo

    rs2!strLocStreetName = rs1!strLocStreetName
    rs2!strPrint_Key = rs1!strPrint_Key
    rs2!strMailStreetAddress = rs1!strMailStreetAddress
    rs2!strMailCityStateZip = rs1!strMailCityStateZip
    rs2!strBlock = rs1!strBlock
    rs2!strLot = rs1!strLot
    rs2!strSection = rs1!strSection
    rs2!strSubSection = rs1!strSubSection
    rs2!strSubLot = rs1!strSubLot
    rs2!strSuffix = rs1!strSuffix
    rs2!dtmCCDateTime = rs1!dtmCCDateTime
    rs2!strLocStreetNameNo = rs1!strLocStreetName & " " & rs1!strLocStreetNo _
    & " " & strPrefix & strNo & lngStartNo
    rs2!strSource = "TOL"
    rs2.Update
    End With

    lngStartNo = lngStartNo + 1 'this works ok

    Next lngCounter

    strStartNo = "1"
    lngStartNo = CInt(strStartNo)

    rs1.MoveNext

    Loop

    DoCmd.Hourglass False
    Set db = Nothing
    Set rs1 = Nothing
    Set rs2 = Nothing
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Variables in Module? (A2k (9.0.3821) SR-1)

    First explain what the recordcount field in rs1 represents. Does this contain the number of records you want to create in rs2 for any given record in rs1? If so, then just move the code that populates lngIssued inside the Do While Not rs1.EOF loop and ahead of the For loop and populate it from the rs1!recordcount field. Then when you move to the next record in rs1, the value will change automatically. I don't understand why you're juggling converting the start number back and forth from strings to longs, though. You can always concatenate a number to a string, so there really isn't any reason to go to all that effort.
    Charlotte

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Variables in Module? (A2k (9.0.3821) SR-1)

    Hi Charlotte

    Yes!

    Wow! I love this stuff

    Talk about being on the 1-yard line and not knowing it.

    This is a building permit system that uses a parcel lookup file to populate individual permits when issued.

    The parcel lookup file initially comes from the assessor

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Variables in Module? (A2k (9.0.3821) SR-1)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> Does that mean you (and Sadie) owe me a lunch? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Variables in Module? (A2k (9.0.3821) SR-1)

    Any time you get to Western NY (Lewiston/Niagara Falls) it would be my pleasure.

    Let me know.

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question re code 'form'

    In general, i would put the interior loop into another procedure. But that is MY practice, and one that i have seen recommended as 'good practice' in various fora.

    What is the DB communities approach to this?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Question re code 'form'

    I think it depends on the situation. I don't usually recommend it here in the Lounge in answer to a straightforward question, even though I might very well use it in my own code. When someone posts code here and asks for help, I try to work with their structure as much as possible.

    Whenever practical, I put things like that in a separate loop, but I do that when I am writing loosely coupled code where all needed values are passed into the other procedure and a value or object is returned. If the code is tightly coupled and shares objects and/or variables, I don't split it out because I use local scope as much as possible. I use module level variables in forms, reports and classes, but I don't use global variables except with a gun to my head. <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>
    Charlotte

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Variables in Module? (A2k (9.0.3821) SR-1)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15> It's a bit out of my way from California. Maybe I'll take a rain check. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •