Windows 7 64bit 
Access 2010 64bit
Former FoxPro Programmer struggling with Access 2010
To find the simplest thing takes days to find among all the stupid stuff I cannot find how to take the last record in the table get the records value (numeric) which is a serial number in a SQL Query and add 1 to it and append the next record and put that value in it. In Foxpro it was simple:
go bottom
store empno +1 to mempno
append blank
replace empno with mempno
Done
I have been going in circles and because Access has auto numbering fields (I cannot use them, I have to do no harm to the other programmers data). I do not have the luxury of doing anything with the structure of the SQL Database I cannot find the answer I have answered no to the error message (Was this information helpful) so many times was this helpful my mouse is broken. I have tried writing macros but they are extreamly limited I am not that familiar with VB but I have to get my head around it.
Here is the code for a append query that almost works but I can not get the last record number field value so I can add 1 to it:
SQL Query:
INSERT INTO dbo_EmployeeCloseOut ( LocationNo, EmployeeCloseOutNo, CloseDate, RegisterNo )
SELECT 1 AS Expr3, [dbo_EmployeeCloseOut]![EmployeeCloseOutNo]+1 AS Expr1, Date()+Time() AS Expr2, [Drawer Rec].[Register No]
FROM [Drawer Rec] LEFT JOIN EmployeeCloseOut ON [Drawer Rec].[Register No] = dbo_EmployeeCloseOut.RegisterNo;
I do not have the ability to change the target database structure and I have to adhear to their numbering sequence. Usually there are four records in the drawer rec file to add to the employeecloseout file and I want to link the two by EmployeeCloseOutNo, so when I get the number I have to place it in both tables.
Thanks for looking at this.
Reply With Quote
