Results 1 to 12 of 12
  1. #1
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134

    loop through continuous form

    Dear Gents,
    Um trying to loop through my continuous form to print every item in the form with its qty


    for example:
    first record code 1001 and its qty 5
    first record code 1002 and its qty 6
    first record code 1003 and its qty 2
    first record code 1004 and its qty 3

    so i need to print these 4 products with its quantities after each others

    i had a try but for sure that was wrong

    Code:
    Dim i As Integer
    Dim db As Database
    Dim rs As Recordset
    
    
    Set db = CurrentDb
    Set rs = Me.RecordsetClone
    For i = 0 To rs.RecordCount - 1
        MsgBox qty
        rs.MoveNext
        Next i
        rs.Close
        Set rs = Nothing
        db.Close
    in the previous code i only tried to show by msgbox the value of the item qty.

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    You don't need to declare and close the database. And I think you need rs!qty, not just qty.

    Try something like this:

    Code:
    Dim rs as DAO.Recordset
    
    Set rs = Me.RecordsetClone
    
    If Not (rs.BOF and rs.EOF) Then 'make sure we have records to loop through
        rs.MoveFirst 'make sure we're on the first record
        Do While Not rs.EOF 'keep looping until we reach the end of the recordset
            Debug.Print rs!qty 'open the debug window with Ctrl+G to see this output
            rs.MoveNext 'don't forget to move to the next record or your app will get hung up an an infinite loop
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    For your review: http://allenbrowne.com/ser-29.html

  3. #3
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Thanks for your fast response , But unfortunately i tried this code but it loop with the same number .. say we will work on the previous example
    first record code 1001 and its qty 5
    first record code 1002 and its qty 6
    first record code 1003 and its qty 2
    first record code 1004 and its qty 3

    that was the output:
    5
    5
    5
    5

    actually i need the output to be:
    5
    6
    2
    3

    I need the qty of every product to print it's barcode.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you seem to have one record with 8 fields. That's not normalized. Is it because that's the way you get the data, or did you develop it that way?
    You would need to reference 4 different recordset fields if this is the case, and maybe add a line wrap between each.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Did you correct your reference to rs!qty instead of just qty?

  6. #6
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Quote Originally Posted by kd2017 View Post
    Did you correct your reference to rs!qty instead of just qty?
    I got an error when i do it rs!qty
    Click image for larger version. 

Name:	1.JPG 
Views:	17 
Size:	21.9 KB 
ID:	35817

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Oh I'm sorry I misunderstood. You are trying to count records grouped by [code]! I thought qty was a field in your form. I apologize.

    Is there any reason the DCount function wont work for you? https://www.techonthenet.com/access/...ain/dcount.php
    https://support.office.com/en-us/art...a-11a64acbf3d3

    Or an aggregate query...

    I will be helpful to us here if you can post your table structure and give us a big picture overview of what you're trying to achieve.

  8. #8
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Quote Originally Posted by kd2017 View Post
    Oh I'm sorry I misunderstood. You are trying to count records grouped by [code]! I thought qty was a field in your form. I apologize.

    Is there any reason the DCount function wont work for you? https://www.techonthenet.com/access/...ain/dcount.php
    https://support.office.com/en-us/art...a-11a64acbf3d3

    Or an aggregate query...

    I will be helpful to us here if you can post your table structure and give us a big picture overview of what you're trying to achieve.
    Actually , I have a continuous form , this form contains records of my products as shown below
    Click image for larger version. 

Name:	dfd.JPG 
Views:	13 
Size:	165.4 KB 
ID:	35818

    I need to loop on every product and print it's barcode qty
    so item code 2 it's quantity is 9 .. So i need to be printed 9 times after each others
    then the code again run again to the next record which is code 3 it's quantity is 40 .. So it will be printed 40 times after the 9 labels of the previous record .. And so on ,, I hope you understood me now ,

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Nope, I'm still confused. What do you mean by "printed 40 times"? Printed where? You just want it printed to the debug window, msgbox, textfile? Or you want the record repeated 40 times on the form?

    At any rate this code will loop through each record in the forms recordset and then loop [rs!Qty] times on each record.
    As I don't know the exact field names you're working with, and you said rs!qty threw an error before, I can't guarantee this will be error free.

    Code:
    Dim rs as DAO.Recordset
    Dim i as Long
    
    Set rs = Me.RecordsetClone
    
    If Not (rs.BOF and rs.EOF) Then 'make sure we have records to loop through
        rs.MoveFirst 'make sure we're on the first record
        Do While Not rs.EOF 'keep looping until we reach the end of the recordset
            For i = 1 to rs!Qty
                Debug.Print rs!Code
            Next i
            rs.MoveNext 'don't forget to move to the next record or your app will get hung up an an infinite loop
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    For your googling curiosity a loop within a loop is called a "nested loop"

  10. #10
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Quote Originally Posted by kd2017 View Post
    Nope, I'm still confused. What do you mean by "printed 40 times"? Printed where? You just want it printed to the debug window, msgbox, textfile? Or you want the record repeated 40 times on the form?

    At any rate this code will loop through each record in the forms recordset and then loop [rs!Qty] times on each record.
    As I don't know the exact field names you're working with, and you said rs!qty threw an error before, I can't guarantee this will be error free.

    Code:
    Dim rs as DAO.Recordset
    Dim i as Long
    
    Set rs = Me.RecordsetClone
    
    If Not (rs.BOF and rs.EOF) Then 'make sure we have records to loop through
        rs.MoveFirst 'make sure we're on the first record
        Do While Not rs.EOF 'keep looping until we reach the end of the recordset
            For i = 1 to rs!Qty
                Debug.Print rs!Code
            Next i
            rs.MoveNext 'don't forget to move to the next record or your app will get hung up an an infinite loop
        Loop
    End If
    
    rs.Close
    Set rs = Nothing
    For your googling curiosity a loop within a loop is called a "nested loop"
    What i mean by printed is to be printed on barcode labels after each others .. i will be connected with a barcode printer which will take an order from that database to print how many qty from every product .. Unfortunately it gives me the same error as before ..

  11. #11
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Okay I see now what you want to do. The code I gave you should work as long as the field names are correct. The error code you posted before is telling me that [qty] is not a field name of your table. I've already asked for you to post your table structure. You'll need to post the full code you are using and a view of your table design for help with this error. I don't have experience printing labels so I won't be much good direct help in that regard.

  12. #12
    shod90 is offline Competent Performer
    Windows 8 Access 2016
    Join Date
    Jan 2016
    Posts
    134
    Quote Originally Posted by kd2017 View Post
    Okay I see now what you want to do. The code I gave you should work as long as the field names are correct. The error code you posted before is telling me that [qty] is not a field name of your table. I've already asked for you to post your table structure. You'll need to post the full code you are using and a view of your table design for help with this error. I don't have experience printing labels so I won't be much good direct help in that regard.
    Thanks man !!! it worked fine now .. the issue was in the column name in my table

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  2. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  3. Replies: 14
    Last Post: 06-28-2014, 10:52 AM
  4. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  5. Replies: 2
    Last Post: 01-01-2014, 02:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums