Using two .Browse at the same time

[Bluejay07]

Hello,

I am trying to create a macro in US payroll that searches for a specific earnings code, grabs the G/L Dist Code and applies that distribution code to other earnings codes for that employee.

Is it possible to have a .browse statement within a .browse statement for the same table? Here is what I was thinking but I don't know if this is possible"

UPEMPD.Browse "EARNDED = 'REG'" & "", 1
Do While UPEMPD.Fetch
   l_strEmp = UPEMPD.Fields("EMPLOYEE")
   l_strDistCode = UPEMPD.Fields("DISTCODE")
    
   UPEMPD.Browse "EMPLOYEE= " & l_strEmp" & "", 1    
   Do While UPEMPD.Fetch
      Select Case UPEMPD.Fields("EARNDED")
         Case OT1, OT2
           UPEMPD.Fields("DISTCODE") = l_strDistCode
           UPEMPD.update
      END Select
   Loop
Loop

If this is not possible, could someone suggest how I can accomplish this?

Thank you.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

No, Browse #2 will affect Fetch #1.

 

[ettienne]

Open UPEMPD twice.

 

[Bluejay07]

Thanks ettienne.

That's what I was think. Since I'm only affecting one table, do I need compositions?

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

That depends on the table, but give it a shot.

 

[Bluejay07]

Thanks for the response. I've come across an error (as soon as .update is trying to be executed) in which I can use help with.

Invalid Input. The Earning/Deduction "___" is invalid for employee 1001. An Earning/Deduction frequency must be the same as, or less frequent than, the Employee's pay frequency.

The frequency from the code is the same as the employee. Even recording a macro directly from accpac doesn't describe how it processes the frequency and I get the same error trying to run the Accpac macro, although I can change it in Accpac directly without any issues.

What do I need to add so that this frequency error does not occur.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

In the select case, if I set the payfrequency I no longer get the above error, altough the value does not update in the database. Here is my current code:

UPEMPD.Browse "EARNDED = REG" & "", 1
Do While UPEMPD.Fetch
   l_strEmp = UPEMPD.Fields("EMPLOYEE")
   l_strDistCode = UPEMPD.Fields("DISTCODE")
    
   UPEMPD2.Browse "EMPLOYEE= " & l_strEmp & "", 1
   Do While UPEMPD2.Fetch
      Select Case Trim$(UPEMPD2.Fields("EARNDED"))
         Case "OT1", "OT2"
           UPEMPL2.Fields("PAYFREQ").Value = 4
           UPEMPD2.Fields("DISTCODE").Value = "CALL"
           UPEMPD2.Update

      End Select
   Loop
Loop

I have also tried with some of the additional fields the macro added (ex. UPEMPL.update) but it didn't make any difference either. The compositions are also set based on the recorded macro.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

I suspect you need to compose with UPEMPL.
Use an SQL query with view CS0120 in the first loop to get your records and then have UPEMPL and UPEMPD composed for the second loop to update the details.

 

[Bluejay07]

I have changed the code to use CS0120 and it seems to be working the same as the .browse. (As previously mentioned) the compositions have been set and the database still will not update.

In addition, when I now get an error it is: "Employee Earning/Deduction Invalid Field Index 0/"

If at first you don't succeed, then sky diving wasn't meant for you!

 

[ettienne]

Here's what I would do: record a macro while you manually do the changes to an employee record. Use the same compositions and work out the kinks updating one record, then move on and add the outer loop using CS0120 to get the records.

 

[Bluejay07]

Thanks again for the response ettienne, although that is exactly what I have done and currently trying to do (with the exception of macro clean up and changing the accpac view names). I receive the error when UPEMPL.Update is reached. As mentioned above, the only thing the macro didn't record was the pay freqency and it seems to accept it if I manually set it to 4.
Going back about a year to a similar post, I have even tried adding UPEMPD2.Order = 3

If at first you don't succeed, then sky diving wasn't meant for you!

 

[DjangMan]

Run rvSpy as you step through that process on the employee maintenance screen and again when your macros runs - see what the differences are.

 

[Bluejay07]

Thanks for the suggestion.
Since I couldn't get the macro to run properly, I created a small program to perform the task instead. I made a few modifications and the changes are now being updated as expected in the database. I usually make a small program for these types of changes although I wanted to try to experiement with a macro this time. Unfortunatly it didn't work out so well. Maybe I'll try again later.

Thanks again.

If at first you don't succeed, then sky diving wasn't meant for you!