Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2001
    Posts
    175

    Question Unanswered: Loop Problem - URGENT

    Need from help with this as I am an amateur with loops.

    I have a main form called "ConsolNo" and a subform which is linked to the main form called "Bol Subform" (most of the time, the subform contains more that one record). From the information entered in these forms I then produce a report called "BOLreport" which contains the following code: (the report is based on a query called qrybol)

    Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    Dim x As Currency
    Dim xx As Currency
    Dim oceandb As Database
    Dim oceanrst As DAO.Recordset
    Dim endoffile As Boolean
    Dim varx As String
    Dim boltype As String
    Dim vary As Variant
    Dim varz As Variant


    endoffile = False

    Set oceandb = CurrentDb
    Set oceanrst = oceandb.OpenRecordset("qryBillsofLading")

    With oceanrst
    Do While endoffile = False

    varx = Me!ConsolNumber
    boltype = Me!TypeofBL
    vary = Me!BillsofLading

    If boltype = "Collect" Then
    If OceanFreight <> 0 Then
    OceanFreight.Visible = True
    Label150.Visible = True
    Text102.Visible = False
    x = OceanFreight
    Else: OceanFreight.Visible = False
    Label150.Visible = False
    Text102.Visible = False
    End If
    If BLFee <> 0 Then
    BLFee.Visible = True
    Label151.Visible = True
    Text103.Visible = False
    x = x + BLFee
    Else: BLFee.Visible = False
    Label151.Visible = False
    Text103.Visible = False
    End If

    ElseIf boltype = "Prepaid" Then
    If OceanFreight <> 0 Then 'OceanFreight = Prepaid
    Label150.Visible = True
    Text102.Visible = True
    OceanFreight.Visible = False
    xx = Text102
    Else: Label150.Visible = False
    Text102.Visible = False
    OceanFreight.Visible = False
    End If
    If BLFee <> 0 Then 'BLFee = Prepaid
    Label151.Visible = True
    Text103.Visible = True
    BLFee.Visible = False
    xx = xx + Text103
    Else: Label151.Visible = False
    Text103.Visible = False
    BLFee.Visible = False
    End If

    Me!Text109 = xx
    Me!Text96 = x

    End If

    oceanrst.MoveNext

    Loop

    End With

    End Sub

    When I run the code in visual basic, the variables "boltype" and "vary" should be changing as it goes through the loop but it just shows the first record. The ConsolNumber should remain the same but the others should change based on the the number of records

    What am I doing wrong.

    URGENT HELP NEEDED

  2. #2
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    Hi,

    As far as I cud understand, the following is going:
    you have used the code:


    Set oceanrst = oceandb.OpenRecordset("qryBillsofLading")

    With oceanrst
    Do While endoffile = False

    varx = Me!ConsolNumber
    boltype = Me!TypeofBL
    vary = Me!BillsofLading

    Soln:

    Where in this whole code have u used the recordset other than just moving to next rec.

    and will your loop ever end....... since you are not resetting the endoffile flag to true at any pt of time.


    Suggessting:

    Set oceanrst = oceandb.OpenRecordset("qryBillsofLading")

    Do

    varx = Me!ConsolNumber
    boltype = oceanrst!TypeofBL
    vary = oceanrst!BillsofLading
    ............
    ............
    ............


    Loop Until oceanrst.EOF = False


    forgive me, but I donot have a habit of using with.


    HTH:
    Rohit

  3. #3
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    I suggested to your other post that you don't use your endoffile variable, but use oceanrst.EOF for your loop breakout test. That property will automatically be set to True when you reach the last record of your recordset. See my last answer to your previous post for the details.

    I also didn't realize you were trying to use your loop in a report from your previous post.

    This isn't really a problem with loops - it's a problem within reporting in Access. There is a previous post on this topic I was involved in (I had a similar problem about 2- 3 weeks ago) I was having a problem getting more than one record into a report using VB code.

    The problem is your recordsource for the report. You can't set it to a recordset and loop through it because you'll only deal with the first record and there is no linkage between record navigation in a recordset and rows on a report. You'll need to pursue this avenue with someone who is an expert because in this area I need to learn a lot. It has something to do with setting the recordsource for the report to a query or a table or a string representing a query on a table.

    Anyone out there have any experience with producing reports and dynamically setting properties using VBA?
    Regards,
    Terry

  4. #4
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by spraguetr
    I suggested to your other post that you don't use your endoffile variable, but use oceanrst.EOF for your loop breakout test. That property will automatically be set to True when you reach the last record of your recordset. See my last answer to your previous post for the details.

    I also didn't realize you were trying to use your loop in a report from your previous post.

    This isn't really a problem with loops - it's a problem within reporting in Access. There is a previous post on this topic I was involved in (I had a similar problem about 2- 3 weeks ago) I was having a problem getting more than one record into a report using VB code.

    The problem is your recordsource for the report. You can't set it to a recordset and loop through it because you'll only deal with the first record and there is no linkage between record navigation in a recordset and rows on a report. You'll need to pursue this avenue with someone who is an expert because in this area I need to learn a lot. It has something to do with setting the recordsource for the report to a query or a table or a string representing a query on a table.

    Anyone out there have any experience with producing reports and dynamically setting properties using VBA?
    Previous post thread was:
    Only last record appears on report
    Regards,
    Terry

  5. #5
    Join Date
    Jan 2002
    Location
    UK
    Posts
    67
    Terry,

    It wud be more useful in case you can put teh link to the earlier post.

    Rohit

  6. #6
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by xzone
    Terry,

    It wud be more useful in case you can put teh link to the earlier post.

    Rohit
    Sorry, try this link, it should take you to the post I entered and eventually sorted out. Hope it helps.
    Only last record appears on report
    Regards,
    Terry

  7. #7
    Join Date
    Sep 2001
    Posts
    175
    Thanks for your advice guys! I checked the link but being a novice in this, I am lost as hell. Any other thoughts are welcome.

  8. #8
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84
    Originally posted by toukey1
    Thanks for your advice guys! I checked the link but being a novice in this, I am lost as hell. Any other thoughts are welcome.
    Being the novice I am with reports involving VBA code, I can't offer too much more. The problem is that you can't set the recordsource property of a report to a recordset and have all of the recordset data appear on the report. There is no linkage mechanism between the report and the recordset that allows you to traverse all of the records. The only way I solved my problem was to copy the contents of my recordset into a temporary table and to have that table set as the recordset for the report.

    It looks like you need to do some special formatting for each of the records that will be in the report and I have little experience with how to do that. Someone else in the forum must have some experience in this area. From the link I gave you, Rocky seemed to know where my problem was. Why don't you look him up and send private e-mail to him.
    Regards,
    Terry

Posting Permissions

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