By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,853 Members | 992 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,853 IT Pros & Developers. It's quick & easy.

Search in Form and Case ID

P: 83
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?
Apr 2 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. Dim assID as Long
  3.  
  4.    assID = DMax("CLng(Right([ID],3))", "TableName")
  5.    Me!ID = Month(Date()) & Format(Year(Date()), "yy") & "-" & Format(assID + 1, "000")
  6.  
  7. 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
Apr 2 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.