Update Query duplicating data as well as updating in table.

[IainGreen]

I have a problem using an update query with a subform.

The subform 'IssuedTo' contains combo boxes with selection options (employee initials), representing members of staff assigned to a particular job number.

There can be any number of employees assigned to one job number.

All the 'IssuedTo' information is stored in a table called 'TabWays'.

The problem is that each time I run the update query after an 'Upon Close'event on the whoile form, the 'Issued To' data is duplicated as well as updated.

For example, if a job number has 'AJ' assigned to it before I go into the form and I add 'TM' to the job too, the 'TabWays' will be updated to AJ/AJ/TM - i.e. AJ is duplicated.


Any ideas - is it an IF statement on the code behind the 'Upon Close' event on the form or am I using the update query in totally the wrong way?

 

[hneal98]

Can you post the query and the if statement?

 

[IainGreen]

Here is the update query:

Field: IssueTo
Table: TabWay
Update To: [TabWay]![IssueTo] & [TabHWIssued]![IssuedTo] & "/"

The "/" is used to separate the different employee initials.

I was running just this query on close event for the whole form on which the subform containing the IssuedTo field is situated. I was not using an IF statement, I just wondered if that might be the answer.

How can I stop it duplicating upon closure of the form BUt still add the new data?

Thanks.

 

[JonFer]

Is [TabHWIssued] the table underlying your sub-form? If it is and it has one record for each employee's initials, I am guessing that you are not limiting the update to just the newly added employee.

 

[IainGreen]

[TabIssued] is the table underlying my subform.

I havent taken any measures to limit the update to just nely added employee.

So how do I do that?

 

[JonFer]

Before you go down that road, what about situations where you need to remove an employee - do you want this to occur automatically? What if two people have the same initials?

I would think through these questions as there are probably better options, depending on what you are using that field for.

Is the main form called to add one employee at a time to the issued table? Is that why you are using the On Close event?