I am using Access97 and have created an employee database. I am having trouble creating a method in which to get a new number for an employee and open a form in add mode with the new number already populated and then remove that number from the list of available numbers. This is how I currently do it. 1) Open switchboard and select Get New Employee ID. 2) Select from which group of employees I need a number (e.g Finance =1000-1999 HR= 2000 to 2999, IRM= 3000-3999, etc.) This brings up a list (Report based on a query) of available numbers for that range. I then write down the first number, close the report, go to the Available numbers table and delete the number manually, Then open up the Employee form and put in the number manually. I would rather have an option of when I click on the correct group of employees that a function would grab the first number from the selected query and then open the Employees form with the employee number populated and remove that number from the available nuumbers table. I have no problem going the other way. When I check the removal box and then select the purge button the employee gets removed from the active employees, their data transferred to the removed employees table and then their number appended to the available numbers table. I just don't know enough yet to figure out how to grab the first item in a query and use that item in the form I want to use and then delete that number from the other table. Sorry for writing a book. Thanks.
I dont know too much about access, but Im sure you can input sql commands somewhere, heres the sql i would use
SELECT employee_ID
FROM available_numbers
LIMIT 0, 1
the LIMIT 0, 1 just picks out the first entry for you
If you dont understand sql then i can look at access and find out how to implement if if you would like
Thanks for your help. Because of your help I was pointed in a new direction and I figured out how to insert the SQL code into access97. Here’s how to do it. You open your query and then to the right of the view box (the one that toggles between build and view) is a down arrow where you can select SQL View. I had to play with the code as access97 did not like the Limit 0,1 statement. EIN is the Employee Id Number. Here is the final code that I used:
INSERT INTO Temp ( EIN ) 'This is a temporary table used to test if the query worked
SELECT TOP 1
AvailableEIN
FROM AvailableEIN
GROUP BY AvailableEIN
HAVING (((Min(AvailableEIN.AvailableEIN)) Between 1000 And 1999));
It returns the desired value! Hooray!
Now the next part of the question. How do I open up my Employee form in add mode and insert the selected value from above into it?
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.