Need help with code (1 Viewer)

[jdp103]

I am new to VB and need help with the following:

I have a form that is used for two types of requests (either Task or Project). The user selects which type of request it is by a dropdown box. What I need help is is this: there is also another field called Number next to the Type field. I would like the numbers to be automatically assigned the next available number based on the Type. For example, if it is Task, it will look up the last Task Number and assign the next number or if it is a Project, it will look at the last Project Number and assign the next one.

If there were only one type, than I could just use the autonumber feature, but since there two different types, I don't even know where to start!

Any help would be GREATLY appreciated!

 

[dcx693]

Why do you need to do this? If the Task and Project info is so different, then you could store the data in separate tables. If you want or need to keep it in one table, you can still assign autonumbers. You might wind up with this situation:

ID Type
1    Task
2    Project
3    Project
4    Task

but you can always filter out what you need via a query.

Do you need to have contiguous numbers in the tasks list and the project list ?

You can still calculate that via a query using the techniques listed on this site:
Roger's Access Library . Just pick your version of Access and look for the download which starts with the name: "NumberedQuery".

If it hasn't become clear, I'm a big fan of using autonumbers wherever possible.

 

[dcx693]

If you must keep the data in one table though, you can use the DMax function to find the maximum entry by Task or Project and just add 1 to it to get the next highest non-assigned number.

So, for example, if your form field where you choose Task or Project is called cboType, the field in the table holding the type is called "Type" and your table is called "tblStuff", then you can use this formula:
=Dmax("Number","tblStuff","[Type]=" & Me.cboType)+1
to get the next highest number.

The Dmax expression above assumes Type is a number. Generally, it's best to store numbers like 1,2 rather than "Type", "Project" because over the course of a database, it can waste space. Just have a table somewhere in the database that identifies 1 as Type and 2 as project and join that into whatever queries you create.

 

[jdp103]

Thanks for your help! I will try DMax and see if that works!! Thanks again!!!!