Solved

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

Posted on 2014-02-08
11
72 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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
 
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 27

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

756 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