Search in Form and Case ID

[jl2886]

First question:
I have an assigned ID that is based on the month and year and then the numbered case. For example,
If a new policy came in in March of 07 and he was the 67th person who was entered for March of 07, then his case number would be 307-067
I would like to know if I can program visual basic to automatically assign a case a number when creating a new case using a form. AND I only want it to create the id once, not again when I update the form with other information at a later date. If that isn't possible, would it be better to create two forms, one that adds a new case, and one that edits a case. And in the one with the edit, just lock the case number.

Next Question: I have the database in excel currently. Once I am done building the access database, I want to transfer the table to access. When I do that, should I make the assigned id (the one spoken about above), the primary key?

Next Question:
When I want to edit a case at a later date, I want to be able to either click on the case in a query and the actual table and for it to automatically open the form to enter in the edited information. How do I this?

Next Question:
If I open a form, I want to be able to search by case-id(the one spoken about in question one). How do I do this? If I didn't want the case-id to be automatically assigned, then I could aways do an AfterUpdate of Case-id for every other variable in the form. Is there another way?

[MMcCarthy]

First question:
I have an assigned ID that is based on the month and year and then the numbered case. For example,
If a new policy came in in March of 07 and he was the 67th person who was entered for March of 07, then his case number would be 307-067
I would like to know if I can program visual basic to automatically assign a case a number when creating a new case using a form. AND I only want it to create the id once, not again when I update the form with other information at a later date. If that isn't possible, would it be better to create two forms, one that adds a new case, and one that edits a case. And in the one with the edit, just lock the case number.

This would depend on how new records are triggered. If you use a separate form to add a new record then something like the following should work in the open event of the form.

Private Sub Form_Load()
Dim assID as Long
 
   assID = DMax("CLng(Right([ID],3))", "TableName")
   Me!ID = Month(Date()) & Format(Year(Date()), "yy") & "-" & Format(assID + 1, "000")
 
End Sub

Next Question: I have the database in excel currently. Once I am done building the access database, I want to transfer the table to access. When I do that, should I make the assigned id (the one spoken about above), the primary key?

Yes as long as it's value is unique.

Next Question:
When I want to edit a case at a later date, I want to be able to either click on the case in a query and the actual table and for it to automatically open the form to enter in the edited information. How do I this?

You don't. However, you can represent the table or query as a datasheet form and program the corresponding form to open in the On Current event.

Next Question:
If I open a form, I want to be able to search by case-id(the one spoken about in question one). How do I do this? If I didn't want the case-id to be automatically assigned, then I could aways do an AfterUpdate of Case-id for every other variable in the form. Is there another way?

Have a look at the following tutorial

Example Filtering on a Form

Mary