Subforms one-to-one

[SGooding]

I'm not really experienced with the advanced functions in Access. I'm using Access 97 to create a database that will create and track work orders through various queues- ie maintenance, accounting etc. At the end of the process the work order is 'complete' and can eventually be archived.

The problem I'm having is that to be able to have some functionality for security I've created a subform with a one to one relationship with the main form. I realize that this flies in the face of convention, but I needed to limit access to the accounting table and this was the only way I could think of to make that happen. I've violated another rule by trying to retain a calculated amount in a table field. I have to maintain this because one element in calculation may change with time- the curRate. This is the current labor rate being coded out for any maintenance work completed. It changes one or maybe twice per year so I need to lock in the total offset for the work order charge.

I want the labor charge portion of the account coding to happen automatically so I've set up the following code in the onCurrent event in the subform. As some additional information this first line of account coding may be different depending on the type of workorder. There can be up to 6 lines of coding for each workorder. If I can get this to work I may have another 2 or three lines of account coding happen automatically to reduce the amount of manual keying occuring in Accounting

Private Sub Form_Current()

'Sets up the first line of code as the total offset for the workorder

Dim lngHours As Long
Dim curRate As Currency
Dim curAmt As Currency

lngHours = Forms![frmAccountingWorkorders]![Fab_Workorders]![TotalHours]
curRate = Forms![frmAccountingWorkorders]![LaborRate]
curAmt = lngHours * curRate

If Forms![frmAccountingWorkorders]![WoType] = 1 Then
Forms![frmAccountingWorkorders]![frmAccountCoding]![Loc1] = 23
Forms![frmAccountingWorkorders]![frmAccountCoding]![DeptChrg1] = 0
Forms![frmAccountingWorkorders]![frmAccountCoding]![Prod1] = 3000
Forms![frmAccountingWorkorders]![frmAccountCoding]![Account1] = 110203
Forms![frmAccountingWorkorders]![frmAccountCoding]![Amount1] = curAmt * -1
Else
Forms![frmAccountingWorkorders]![frmAccountCoding]![Loc1] = 23
Forms![frmAccountingWorkorders]![frmAccountCoding]![DeptChrg1] = 0
Forms![frmAccountingWorkorders]![frmAccountCoding]![Prod1] = 3000
Forms![frmAccountingWorkorders]![frmAccountCoding]![Account1] = 7900501
Forms![frmAccountingWorkorders]![frmAccountCoding]![Amount1] = curAmt * -1

End If

End Sub


This code works great if I only move forward through the records and fully code them at the time I am reviewing them. If I try to backup and look at a previous workorder I get an error message: 'The changes you requested were not successful because they would create duplicate values in the index, primary key, or relationship etc' Since it is often the case that one or more work orders needs to be passed over for coding later I need some way to prevent this error from happening.

I'm hoping someone can help me - Thanks!

 

[LittleSmudge]

First off, storing your calculated table is NOT a problem because in this case you have a solid justification for doing so. ( You must either store the current value of the curRate OR the total in each record - as there is no significant difference in data size then which one is up really up to you ).

As for your main problem -
What are the Primary Key fields in the table that underlies this form?

It seems from the code and the error that you have somthink else that causes a New Record to be created - or old records to have their primary key altered - or old records have fields altered that cannot accept dumplicate entry.

Check the table definitions - are any fields set with indexes 'No Duplicates' that don't need it?


Also
,

Why, in the code you have posted, are you setting the Amount1 field and then immediately setting it to something different ?

        Forms![frmAccountingWorkorders]![frmAccountCoding]![Account1] = 110203
        Forms![frmAccountingWorkorders]![frmAccountCoding]![Amount1] = curAmt * -1

'ope-that-'elps.

G LS

 

[SGooding]

Sorry, I forgot to include that information. The main form pulls from query qryWorkorder which is using an autonumber as the primary key in field 'WONumber'. In a later version of this database I would like to add coding that will create a workorder number that has meaning for the user but have left that issue for later.

The subform pulls from qryAcctingCodes and has a field called WONumber3 that is linked as a child to the masterfield 'WONumber' from the main form. The only field in tblWorkorder that is indexed with No Duplicates is the 'WONumber' field. The tblAcctingCodes has only one indexed with No Duplicates field - 'WONumber 3'.

For the field values - I'm probably not going about this in the most efficient manner but here's what I'm trying to do:

Forms![frmAccountingWorkorders]![frmAccountCoding]![Account1] = 110203
Forms![frmAccountingWorkorders]![frmAccountCoding]![Amount1] = curAmt * -1

Depending on the type of workorder (1,2 or 3) the field [Account1] should be either an Account Receivable account- 0110203 (workorder type 1) or a variance account 07900501 (types 2 or 3). The If statement determines the type of workorder and then sets the Account coding. The Amount in field [Amount1] is based on the total hours charged by the rate currently used. I want this to calculate and come through as a negative number so that when Accounting keys in the offsetting account(s), the subtotal on the Accounting Codes Subform will total to zero when the coding is complete. We use a double entry accounting system and this way the entire accounting entry is stored in the database plus this provides a check for the Accounting user to verify that the total amount of the workorder has been correctly charged out either internally to a department or externally to a vendor account. This might be way more information than you wanted - I hope it answered your question. Please let me know if you need more info- I'm really stuck.

Thanks for your help!

 

[LittleSmudge]

Firstly, appologies for the Red Herring, Account1 and Amount1 ARE two different fields. I just read them as being the same thing ( cc = m )

As for your real problem, What happens when you open the form from scratch?
Can you open the form to an existing record okay, and can you change records in the 'forward' direction okay, or does that cause a problem too?


G LS

 

[SGooding]

No problem- I wasn't sure if I had created an issue with something in those two lines.

When I open the form to a record for the first time (ie there is no corresponding record in the subform) everything works great. Once I've moved out of that record and the subform now has a record that ties to the main form, I can't return to that record without getting the 'duplicate value in the primary key' error. It seems like I need something in my code that says- 'if a corresponding record already exists in the subform- pull that record- otherwise use the code below to calculate the value for the first set of fields'. I'm just not sure how to accomplish that using code or if I need to approach this from a different direction.

Thank you!

 

[SGooding]

Solution!!!!! I think I found my problem! I had the subform Data Entry property set to Yes, so everytime the form was activated it was trying to create a new record. If a record had already been created the error message would pop up since this has a one-to-one relationship.

Thanks for looking at my issue- I appreciate your time!