Solved

Access 2010 Form - Navigate to specific record & add new custom ID

Posted on 2014-02-08
11
66 Views
Last Modified: 2016-05-15
Hello-

I have an access database attached below and I want to be able to add new records and edit existing records. I have a form called frm_projects. On this form, I have a add project button and lookup project button. I need to have custom tracking IDs. For example I have multiple "categories" such as east, west, south, etc.. I need to be able to store a custom tracking ID such as EST01, if east is selected. The next record would then be EST02 if a new one was added.

Could someone give me some guidance on how to go about doing this?

Also, I need to figure out how to link the Project Information to the Savings Information.

Any help would be greatly appreciated!
Tool-ee11.accdb
0
Comment
Question by:tawathav
11 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 39844886
I can't download your db so can't see the table structure. But I'd suggest that you simply use the records ID and the category for tracking.

You will need a table category with fields: category ID;Category

In your project record you would have a catergory field that looks up to the category table.

So if you run a query where recordID=X and Catergory=East you should be able to track the record
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39846402
I would suggest you to use the category ID and not the values, and rather to split to 3 tables tblCategory, tblSubcategory, tblTertiaryCategory.

And to answer you first question
You should add new field in tbl_projects CustomID, and by Form_BeforeUpdate event you should write
   Dim MaxID As Long
   MaxID = Nz(DMax("Right(CustomID,3)", "tbl_projects", "Category='" & Me.CategoryCombo & "'"), 0)
   If IsNull(Me.CustomID) Then
      Me.CustomID = DLookup("Abbreviation", "tbl_categories", "Category='" & Me.CategoryCombo & "'") & Format(MaxID + 1, "000")
   End If

Open in new window

0
 

Author Comment

by:tawathav
ID: 39848049
I can't get this to work. Any other suggestions? Can you show me an example?
0
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
ID: 39848083
0
 

Author Comment

by:tawathav
ID: 39848153
Thank you Gozreh! Exactly what I wanted. Any idea how to lookup an initiative and navigate to the record for editing. Basically users will need to add new records and have the ability to edit existing records.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 10

Expert Comment

by:Gozreh
ID: 39848320
You can add a combobox with all records, and then set the bookmark to that record the user selected.

http://msdn.microsoft.com/en-us/library/office/ff835682.aspx
0
 

Author Comment

by:tawathav
ID: 39848376
Like on a pop up form or just on the form. I was looking to use the add initiative button. Thought this would easier for the end users. Maybe not...
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 39848452
on the form will be easier for you to design, but you can also do it with a dialog form, depends what you want
0
 
LVL 10

Expert Comment

by:Gozreh
ID: 40080453
If you got your answer please close this question !  if you still need assistance please ask !
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 41595352
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now