Create unique Timecard Number for Employee 2

[JoeAtWork]

I have a subroutine for creating new timecards for employees. It works fine except that I can't get it to assign a unique timecard number (per employee). So far this particular employee has timecards 1 through 6. To find a unique timecard number for the employee, I keep looping until it finds a timecard number that doesn't exist. However, the loop always stops at 6, even though several timecards for that employee already exist as # 6.

The relevant code is below:

    Set linkCompany = Ses.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
    
    ' Timecard Header
    linkCompany.OpenView "CP0031", headerView
    Set headerFields = headerView.Fields
    
    ' Timecard Detail
    linkCompany.OpenView "CP0032", detailView
    Set detailFields = detailView.Fields
        
    headerView.Compose Array(detailView)
    detailView.Compose Array(headerView)
    
    
    ' Create a new TimecardNo
    lngTimecardNo = 1
    headerView.Browse "Employee = """ & txtEmployeeID & """ AND Timecard = """ & CStr(lngTimecardNo) & """", True
    [COLOR=red]'My loop to find a unique timecard number for this employee[/color]
    Do While (headerView.Fetch)
        lngTimecardNo = lngTimecardNo + 1
        headerView.Browse "Employee = """ & txtEmployeeID & """ AND Timecard = """ & CStr(lngTimecardNo) & """", True
    Loop
    
    ' No Timecard exists for that employee with this timecard number, so use it for a new
    ' timecard
    txtTimecardID = lngTimecardNo

Does anybody see something wrong with the code?

Also, does it matter if an employee has multiple timecards with the same number? I don't really care if I make a timecard 6 for employee "Joe Smith" in week 1, and another timecard 6 for same employee in week 2, the week he gets paid, so long as the cheque includes both timecards.

 

[jayconverse]

You've got a .Browse inside your .Fetch loop. You should only .Browse once, then you .Fetch to the end.

Jay Converse
IT Director
Systemlink, Inc.

 

[JoeAtWork]

Perhaps I misunderstand how Browse and Fetch work.

I thought Browse applies a filter, and Fetch will then return True if there are any records that match the filter. I realize there could be multiple matches, but for my purposes I only care if there's at least one.

I am trying to find a Timecard/Employee combination that doesn't exist yet. So I keep incrementing the lngTimecardNo variable to change the filter until the Fetch returns False.

Does Browse work like an ADO Recordset's FindFirst method? If so, what method can I call to start looking from the beginning again (e.g. "MoveFirst")?

 

[jayconverse]

.Browse is both a filter and a .FindFirst. I would .Browse for just the employee number, then .Fetch to the end of his records. You should also try your .Browse in Descending order, you might be able to pick up the last record.

But, at the end of the day, you're spending a lot of time on something that Accpac doesn't care about. Time card numbers don't matter, pay periods matter. After timecards are processed for a pay period, they're deleted anyway.

Jay Converse
IT Director
Systemlink, Inc.

 

[ettienne]

I always use timecard no. 1, as Jay says it does not matter - unless the user wants to see a timecard number.

 

[JoeAtWork]

I was beginning to wonder if time card numbers meant anything, considering I already had several of the same.

Should I be checking if a time card with the same Pay Period End already exists before I insert a new one?

Thanks for the help.
Joe

 

[ettienne]

Yes, the timecard for a certain pay period is unique.