General discussion

Locked

Access Query for next record

By Goliathkutaa ·
Ok, here's what I got.

I have a form used for Data Entry to add records to an Access DB. The table I am adding to has 2 primary keys, taskNumber and taskYear. Both are manually entered. What I am trying to do is when the user presses the Add button, and there is an error saying you cannot go to that record, I want to query the DB and find the next available taskNumber.

Can this be done????

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by rboyer In reply to Access Query for next rec ...

When you say the numbers are manually entered, what type of number is it? If it is just a TaskNumber, just make it an Autonumber and choose a starting number. Does the number increase or decrease? You can make a query the searches a field by < or > then choose a paramenter, ex: query Tasknumber enter data enter] and it will give anything higher than the number entered.
I would just make it Autonumber or you could be asking for trouble down the road.

Collapse -

by Goliathkutaa In reply to

The TaskNumber will have multiple duplicates; because each year the number begins again at one. With an autonumber it would just continue to count up. The database needs to be able to have records from previous years also entered, but they don't have them onhand to start from... can you think of anything else???

Collapse -

by dryflies In reply to Access Query for next rec ...

you can do a query to find the maximum tasknumber in the table and then increment it. here is a query I used to find the oldest registered player in our Little league
SELECT Max([Registered Players].[LEAGUE AGE]) AS [MaxOfLEAGUE AGE]
FROM [Registered Players];
while this will not work if you reuse task numbers, it works well if you want to increment the max task number every time.

Collapse -

by Goliathkutaa In reply to

Thanks. I have the query working now; but I don't know how to run the query and set the taskNumber to the result of the query...

Collapse -

by Goliathkutaa In reply to Access Query for next rec ...

Thanks guys,

I was able to get the query to work, but it wouldn't allow me to set a text box to the result of the query. So, I made a listbox and just disabled it, with a label to inform the user that this was the next available Task Number.

Thanks again.

Collapse -

by Goliathkutaa In reply to Access Query for next rec ...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums