At Wits End - Need help with Looping Macro

[ExoDus84]

Good Morning, everybody. I'm new to the site, and wanted to first introduce myself, and then ask for help on an issue that's been getting me stuck for a few days.

I'm having an issue looping a macro, given a condition. I have a form, frmMain, with a table as a recordsource (tbl_PDC_Parts) with a hidden text box (txtCount) on it that calculates the number of Null values in a table column (Rec_Location from tbl_PDC_Parts). The macro updates this column with data, and I need it to loop until all the values in the column are filled with data.

I then created a macro that runs my original macro, with the repeat expression: [Forms]![frmMain]![txtCount]>0

In VBA, I have the following, with the goal of updating the txtCount text box each time the macro runs, in order to make my repeat expression work:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.Updated = True
End Sub

This seems to work, as in it assigns all the values I need to the field, but it keeps looping infinitely, and it causes access to stall and crash eventually. :banghead: If I change the ending of the repeat expression to:

[Forms]![frmMain]![txtCount]=0, then nothing happens at all when I click the button on my form.

My goal is just to have this macro run until all values in column Rec_Location, from table tbl_PDC_Parts, are entered, and then stop.

Any help would be greatly appreciated!

 

[ExoDus84]

I've also tried doing this via VBA. I'm very new to VBA, keep in mind. This piece of code is kind of doing the same thing, but again, getting stuck and not exiting correctly. I put this on the click event of a command button.

   Me.txtCount.Requery
   Me.txtCount.SetFocus
 
    Do While Me.txtCount.Text > 0
     If Me.txtCount.Text = 0 Then
       MsgBox ("Finished")
       Exit Do
        End If
 
       DoCmd.RunMacro "mcr_Location_Update"
       Me.txtCount.Requery
    Loop

 

[MarkK]

What do you need to count? You don't need a loop to count records in a table that satisfy a condition. Consider a DCount() function like . . .

me.txtCount = DCount("*", "tblParts", "SomeField Is Null")

. . . so you can see there is no loop, and DCount() returns a count of the rows in the said table, where the indicated field (SomeField) has a value of null.
Also, it is common practice if you have some kind of summary information like this on a form that may change for each record, update it during the form's Current event. A code example of that might look like . . .

Private Sub Form_Current()
[COLOR="Green"]'  This routine runs when a new record becomes current in the form[/COLOR]
   Me.txtChildCount = DCount("*", "ChildTable", "ForeignKeyField = " & me.txtIDField)
End Sub

hth

 

[ExoDus84]

Thanks for your response, lagbolt.

What I'm basically trying to do is run a macro until there are no null values in a certain column. I'm thinking the DCOUNT function should work as well. What I need my code to do is this:

1) Run mcr_Location_Update macro. It will append some values, but not all.

2) Update the number of records that still contain null value in Rec_Location (table field of tbl_PDC_Parts). I'm doing this and assigning it to a hidden text box on my form, with the code: txtCount =Sum(IIf(IsNull([REC_LOCATION])=True,1,0))

3) Re-run the macro again, until there are no more records with a null value in Rec_Location. Then, exit the function.

That's what the VBA code I posted is trying to do, and it's updating the records...but it's not exiting the procedure,and causing access to stick. I'm sure it's a coding error.

In summary, no matter how I do it, run a macro that updates values in tbl_PDC_Parts, column Rec_Location, until there are no null values, and then stop.

 

[Adam McReynolds]

What do you want to change the null values to? As Lagbolt suggested DCount will give you a count of those records in quantity. If you want to find those records then DLookup will find records that satisfy criteria. I usually use "If" statements and then SQL to update records.

 

[MarkK]

Good post by Adam. Nothing to add.