Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Increment field based on another field 1

Status
Not open for further replies.
skyline666

skyline666

Programmer
Oct 22, 2007
141
GB
Hi,

I have a table called nRisk in a database in a Access 2003 Project, linked on SQL Server 2005. I have two fields in nRisk called RiskID and ProjectID. RiskID is a primary key field. Want I need is to have RiskID act like an autonumber field, but start again after each new ProjectID. So for example the first ProjectID is 1, then there RiskID goes from 1 to 8 for the 8 risks, then the next ProjectID is 2, and then the RiskID starts at 1 again. As RiskID is a primary key field, then to have no duplicate records the ProjectID can be added to the RiskID (for example 1-1, 1-2, 2-5 (ProjectID-RiskID)). If it helps, then the ProjectID can go before the RiskID field if the RiskID is going to be based on ProjectID.

Many thanks in advance,

Andrew
 
Sort by date Sort by votes
You can combined the two fields to create a unique key. How you get the next number depends on how many users you have. If very few people are doing data-entry, you can use DMax to get the next RiskID. Otherwise, you will need to do some checking to ensure that two people do not end up with the same RiskID.
 
Upvote 0 Downvote
Hi Remou,

Thanks, at the moment it would only be the 1 user. How would I go about combining the two fields, and how would I use DMax?

Many thanks,

Andrew
 
Upvote 0 Downvote
Here is an example of DMax. It assumes that RiskID and ProjectID are numeric:

[tt]NewRiskID=DMax("RiskID","tblTable","ProjectID=" & Me.ProjectID) + 1[/tt]

You can create a combined index in table design view. Choose View->Indexes and follow the instructions.
 
Upvote 0 Downvote
Ok, I have stuck the below code in VBA. Which option does it need to go in (ie: before update, enter etc) if I want the result to appear when I tab accross to the field? At the moment it is in Before Update but it doesn't do anything. I have also put in the ProjectID to display in RiskID, is the way that I did it correct, as I can't tell as nothing appears.

Code: 
Private Sub RiskID_BeforeUpdate(Cancel As Integer)

Dim RiskNextID As Long

    RiskNextID = DMax("RiskID", "nRisk", "ProjectID=" & Me.ProjectID) + 1
 
    RiskID = ProjectID & " - " & [RiskNextID]

End Sub

Also I can't find the indexes option, I think im in the wrong type of application, although I have no idea which im in or where to find out which im in.
 
Upvote 0 Downvote
Stupid me, it wouldn't work as I had the RiskID field as an int data type, changed it to varchar and it works fine now. Thanks for your help. Could you help me on another thread I have if I post the link? Its for the same table but different fields (although ProjectID is in both of these threads)
 
Upvote 0 Downvote
Actually it doesn't work now. I tried a new line and entered 1004 for ProjectID (although the ProjectID wouldn't cause the error) and tabbed across to RiskID, and got an error message saying "Invalid us of Null". I think I got this error as it is checking the RiskID from before but in the new format (ie: ProjectID - RiskID) and not just checking the RiskID. How do I check just the RiskID part in the new format?

Many thanks,

Andrew
 
Upvote 0 Downvote
Try:

[tt]Dmax("Val(Mid(RiskID,Instr(RiskID,'-')+1))","nRisk","ProjectID=" & 1)+1[/tt]
 
Upvote 0 Downvote
I got an error saying "Instr is not a recognized built-in function name".
 
Upvote 0 Downvote
What version of Access are you using? Are any references missing?
 
Upvote 0 Downvote
Im using Access 2003, as far as im aware all references are there, how do I check? Im using tables that have been made in SQL Server 2005 that have then been linked over if that makes a difference.
 
Upvote 0 Downvote
Also I couldn't do that indexes thing you said about before as I couldn't find it at all. I took a look on the help in Access and it the picture for indexes in the toolbar was a lightning strike with dashes down the left hand side of it, but I couldn't find that button at all.
 
Upvote 0 Downvote
I think I do have references for InStr as i typed in InStr on a new line n opened a bracket and it brought up the help popup bit that tells you what criteria to put in.
 
Upvote 0 Downvote
Where the tables are located should not make a difference. You will find references under Tools->References in the code window. If you do not see any references marked as missing, test Instr in a fresh database. It is a standard built-in function and has been available since at least Access '97.

Did you try my suggestion of choosing View->Indexes from the menubar when the table is in design view?

 
Upvote 0 Downvote
I was going to but there isn't an option for Indexes at all that I can find. Also the Instr works (it brings up what criteria I need to put in if i type Instr on a new line), but it foesn't work in that line of code.

Which reference would it be under as there are lots that are unticked. the ones that are ticked are:

"Visual Basics for Applications"
"Microsoft Access 11.0 Object Library"
"OLE Automation"
"Microsoft ActiveX Data Objects 2.1 Library
 
Upvote 0 Downvote
If Instr is working, then you do not have a missing reference, however, for the future, a missing reference is marked "MISSING:" and is ticked. I note that you do not have Microsoft DAO x.x Object Library referenced, it is likely that you will need it in the future.

What exact error did you get with the line I posted? Can you re-post the code with the line?

Regarding indexes, have a look here:
 
Upvote 0 Downvote
Here is the code that I have that you provided:

Code: 
Private Sub RiskID_GotFocus()

Dim RiskNextID As Long

    RiskNextID = DMax("Val(Mid(RiskID,Instr(RiskID,'-')+1))", "nRisk", "ProjectID=" & 1) + 1
    RiskID = Me.ProjectID & " - " & [RiskNextID]
   
End Sub

When I have this code the error is "Instr is not a recognized built-in function name". I decided to try out the Instr part of the code on a new line by itself, and the part "Instr(RiskID," works fine, as soon as I tried a single quote the help bit that comes up sayin what I need to enter disappears. I tried double quotes instead around the dash as can be seen in the below code, and vba liked it (as in the help bit stayed until I closed the brackets). But a different error came which said "Type mismatch".

Code: 
 RiskNextID = DMax("Val(Mid(RiskID,InStr(RiskID, " - ")+1))", "nRisk", "ProjectID=" & 1) + 1

 
Upvote 0 Downvote
Apologies. It should read:

RiskNextID = DMax("Val(Mid(RiskID,Instr(RiskID,'-')+1))", "nRisk", "ProjectID=" & Me.ProjectID) + 1
 
Upvote 0 Downvote
I have it working now, but with different code. Here is the code that I have now:

Code: 
Private Sub ProjectID_AfterUpdate()

    Dim intSeq As Integer
    'Me.RiskID = Me.ProjectID & "-01"

    If IsNull(DMax("sequenceno", "nrisk", "[ProjectID]=" & Me.ProjectID)) Then
    intSeq = "1"
    Else
    intSeq = DMax("sequenceno", "nrisk", "[ProjectID]=" & Me.ProjectID) + 1
    End If
    
    Me.SequenceNo = intSeq
    Me.RiskID = Me.ProjectID & "-" & Format(intSeq, "0000")
    
    Me.WBS.Requery
    
End Sub

I made a new column called SequenceNo which is an autonumber column, going from 1 to infinity. The code now checks this SequenceNo to see if there is any data in the table, if not assign 1 to intSeq. Then use the intSeq to go with the ProjectID to make the RiskID. Also note that I have put the code in ProjectID on AfterUpdate.

Many thanks for your help and ideas, I wouldn't of got it done without your help. Also feel free to use the code, as you did help after all lol.

Andrew

PS. The other thread I was on about I have sorted now, i'll post the thread link in next post if your interested to see what the problem was, and the answer.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TechieJr
  • Locked
  • Question
Filtering for null or empty field
Replies
8
Views
470
dhookom
dhookom
MattGreer
  • Locked
  • Question
Add Field Description to a Form 2
Replies
5
Views
154
Andrzejek
Andrzejek
caykamanj1966
  • Locked
  • Question
Hyperlink Text of a field
Replies
1
Views
112
caykamanj1966
caykamanj1966
caykamanj1966
  • Locked
  • Question
Delete Data from Field
Replies
1
Views
128
dhookom
dhookom
applestoapples
  • Locked
  • Question
Create duplicate rows based on Qty field
Replies
7
Views
133
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top