Multiple Loops - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Multiple Loops

Mike Ackland

11 months ago

Hi guys. firstly, I don't know if this is the right way to go about what I'm after.. but... having searched I'm still banging my head against the wall. So I'm seeking help.

I have a continuous form and on that form there is a Qty field which I've purposely set up as being null or blank as opposed to Zero. This is simply the way it has to be.

What I'm after is then to loop through the textbox fields of said form.
Then I need to loop through the tags of said textboxes for the Tag "NulltoZero" and then change the null to 0, and pop up a messagebox to state which ID's have been left blank, double checking the qtys are correct. Thus making every record completed.

Is using loops the right way to do this if so how? as I can't seem to get it working. Or am I barking up the wrong tree?

	Bill Carver @Reply 11 months ago
How about putting back nz and then hiding that field if the value is zero

Sami Shamma @Reply

11 months ago

Hi Mike.
I am not quite sure what you are trying to achieve, but this is definitely a convoluted way to go about it. Perhaps we could be of more help to you if you tell us what is it that you're trying to achieve rather than how you want to achieve it. Then we can suggest to you a more straightforward way to do what you want to do.

The answer to your question, if you know VBA well enough, is yes. You can loop through all the controls on a form and check their Tag property.

Mike Ackland OP @Reply

11 months ago

ok so basically a stock receipt will come in, and it's marked off against a PO. Qty's received is compared to qty's ordered.
Once stock is received, I'll then have nulls where no stock has arrived so the warehouseman can check this is in fact accurate as it'll stand out more easy for him to double check the non delivery lines. This is then confirmed with a msgbox with all the nulls (The ID's of products not delivered) , before they are changed to zero.

	Sami Shamma @Reply 11 months ago
So basically you want an apparent way to show which items of a purchase order do not show up on what was actually received. Is this correct?

	Mike Ackland OP @Reply 11 months ago
Exactly that Sami. my description is long winded I know, like the way I think the solution is. In my head the solution is .. loop though the controls on teh form, then find the controls marked with the tag "NulltoZero", create a messagebox using a string to concatenate the IDs and then change the field from a null to a zero. there's obviously a much easier solution which I'm just not seeing.

	Sami Shamma @Reply 11 months ago
Okay, it is time for you to post in design view the tables involved.

Mike Ackland OP @Reply

11 months ago

I have found the solution. Instead of looping through the fields of the form, I cloned the Recordset. If it found a null in the specified field name, it'll throw up a msgbox with the id and description of that record, and then change it to a Zero.
then a summary msgbox at the end toconfirm the number of items not received.
Here's the code I came up with...

Details

    Dim rs As DAO.Recordset

    Dim fieldName As String

    Dim nullCount As Long

    

    fieldName = "ReceiveQty" ' Replace with the actual field name

    nullCount = 0

    

    Set rs = Me.RecordsetClone

    

    If Not rs.EOF Then

        rs.MoveFirst

        Do While Not rs.EOF

            ' Check if the field is null

            If IsNull(rs.Fields(fieldName).Value) Then

                nullCount = nullCount + 1

                MsgBox "Null found in record ID: " & rs.Fields("ProductID").Value & " ; " & rs.Fields("Product").Value 

                rs.Edit

                rs.Fields(fieldName) = 0

                rs.Update

                

            End If

            rs.MoveNext

        Loop

    End If

    

  

    MsgBox "Total No. of undelivered lines '" & fieldName & "': " & nullCount, vbInformation, "Null Check"

    

    ' Clean up

    rs.Close

    Set rs = Nothing

Many thanks for you help! It toook me a while but I got there.

	Alex Hedley @Reply 11 months ago
Access Tip: Check a Form for Blank Controls Legacy Tip: https://599cd.com/tips/access/120720-check-for-blank/ Legacy Blog Post: https://599cd.com/blog/display-article.asp?ID=723 Video: https://www.youtube.com/watch?v=fe5D4iK0Pvk

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View