Copied Macro running Hooey now

[deeda67]

I'm not exactly sure where to post this so I thought I'd try here. I have a database that is set up with three queries to, when in a form, click the department and then Access automatically assigns the next project number to the form. I copied this database so I had a backup copy. The backup copy works but the new one doesn't. I haven't changed the set up to the queries or the macro, only added other aspects (not related to this form or macros or queries) to the database. The only thing I noticed is I turned the maco off in order for my staff to do some updates, when it was turned back on, it doesn't work - don't know if that messed with it or not. Now, it adds 1, then 2, then 3, then 4, and so on instead of adding just one to the DEPTID.

Basically, where should I look to see what the problem is? I've completely recopied all the queries and the macro from the one that works back into this one and it still doesn't work so somthing is messing with it but I don't know what it could be!!!

 

[FoFa]

Work backwards, track this field backwards to see where it is going astray.
Best I can tell you right now.

 

[deeda67]

OK, I've done everything I possibly can. I changed Project Name to Project Number in one query and not the other, but then it adds the correct number and puts it in the table but it doesn't show on the form. These are the queries I have in order they run on the macro. Anything jump out at anyone?

New Query:

SELECT DISTINCT DEPTID.DESC, DEPTID.ID, DEPTID!ID+1 AS NEWID, [Project table].[Project Number], " " & [NEWID] AS NEWREC, [Project table].Department INTO DEPTINTERIM
FROM DEPTID INNER JOIN [Project table] ON DEPTID.DESC = [Project table].Department
WHERE (((DEPTID.DESC)=[Forms]![Assign Project Number]![Combo26]) AND (([Project table].[Project Number]) Is Null))


New Project Number Query:

UPDATE DEPTINTERIM, [Project table] SET [Project table].[Project Number] = [NEWREC]
WHERE ((([Project table].[Project Number]) Is Null) AND (([Project table].Department)=[Forms].[Assign Project Number].[Combo26]));

QryCalc:

UPDATE DEPTID INNER JOIN [Project table] ON DEPTID.DESC = [Project table].Department SET DEPTID.ID = DEPTID.ID+1
WHERE ((([Project table].[S&A Contact]) Is Null) AND ((DEPTID.DESC)=[Forms]![Assign Project Number]![Combo26]));

 

[FoFa]

If it adds it to the table. THEN you close form and reopen it, does it show it then?
You may have to requery your form after you run the query.

 

[deeda67]

Well I got it to work again, but I have no idea why. All I did was go back into each query and choose the Project Number field again (it wasn't changed I just reclicked it) and then rechose New Project Number query in my first instance of the macro and it works. The only thing it does now is after you add one project and then go to add another, it gives you a blank record - but if you just add again, it gives you the correct next number. When I go back in the table, it has added a blank record, but all the project numbers after that are added correctly.