Solved

MS Access Default value

Posted on 2011-03-13
25
393 Views
Last Modified: 2012-05-11
i have a form with a subform. the subform is continuous form. what i need is on one field to hvae the next new record to be the prev value + 1.

i find things like this

Private Sub YourNumericControl_AfterUpdate()
If Not IsNull(Me.YourNumericControl.Value) Then
  YourNumericControl.DefaultValue = Me.YourNumericControl.Value
End If
End Sub

Open in new window


Problem is this will only work one time because this is a after update event!

0
Comment
Question by:Blueice13085
  • 13
  • 12
25 Comments
 
LVL 75
ID: 35125066
You are probably going to have to use Dmax() on that field to get the previous or Max value ... and use the Form BeforeInsert event to set the default value ... which might look like this:

Private Sub Form_BeforeInsert (Cancel As Integer).

Me.YourNumericControl = Nz (DMax("[YourTableFieldName]","[YourTableOrQueryName]"),0) + 1

End Sub

mx
0
 

Author Comment

by:Blueice13085
ID: 35125106
Not getting this to work getting errors
!
Private Sub Form_BeforeInsert(Cancel As Integer)

    Me.INV___KEY_TAG = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1

End Sub

Open in new window

0
 
LVL 75
ID: 35125137
And what might that Error number / description be ?

Meanwhile ...
Is INV___KEY_TAG a text box on your form ?  IF so, try this syntax

 Me.("INV___KEY_TAG") = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1

If INV___KEY_TAG is just a field name, try this:

 Me![INV___KEY_TAG] = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1

mx
0
 

Author Comment

by:Blueice13085
ID: 35125156
Also the number is not like 1,2,3,4. its going to be like 172105,172106,172107,172201,172202. etc.... these numbers are from a invoice book which has 50 invoices and the next invoice book be change the order or invoice number sounds like this code uses biggest number and not by ID field
0
 
LVL 75
ID: 35125163
What is the Error you are getting ?

mx
0
 

Author Comment

by:Blueice13085
ID: 35125167
and this is a field so it should look like
Me![INV / KEY TAG] = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1
but also comes up with a error
0
 
LVL 75
ID: 35125173
"but also comes up with a error "
Please .... *what* is the error ?
0
 

Author Comment

by:Blueice13085
ID: 35125175
using
Private Sub Form_BeforeInsert(Cancel As Integer)
[INV / KEY TAG] = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1
End Sub
 shows no error's but the field [INV / KEY TAG] doesnt change!
0
 

Author Comment

by:Blueice13085
ID: 35125179
just takes be to VBA
0
 

Author Comment

by:Blueice13085
ID: 35125183
guessing it has to do with the Me![INV / KEY TAG] when i take out the Me! and just use [INV / KEY TAG]= no error, but also no change in the field!
0
 
LVL 75
ID: 35125188
When you use Me ..... What ... is the Error ?

mx
0
 

Author Comment

by:Blueice13085
ID: 35125199
also seen something like this online

 =DLookUp("[Field]","Table","[ID]=Forms![Form1]![ID]-1")

Open in new window


any thoughts on using this?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75
ID: 35125202
Until you tell me what Error you are getting, I cannot help further, sorry.

mx
0
 

Author Comment

by:Blueice13085
ID: 35125229
trying this i get runtime error 2001

Private Sub START_MILES_AfterUpdate()
[START TIME] = Now()
[INV / KEY TAG] = DLookup("[INV / KEY TAG]", "[drivers_log (sub)]", "[ID (SUB)]=Forms![drivers_log (sub)]![ID (SUB)]+1")
End Sub

using what you had gave me i get this
runtime error 13 Type Mismatch
0
 

Author Comment

by:Blueice13085
ID: 35125249
Your code

Private Sub Form_BeforeInsert(Cancel As Integer)
 Me![INV / KEY TAG] = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1
End Sub

Open in new window


My error
Run-time error '13':
Type Mismatch

End, Debug, help
0
 
LVL 75
ID: 35125255
What is the data type of [ID (SUB)]  ?

Also, based on your comment @ http:#a35125156 ... this scheme is not going to work anyway.

And this:

DLookup("[INV / KEY TAG]", "[drivers_log (sub)]", "[ID (SUB)]=Forms![drivers_log (sub)]![ID (SUB)]+1") ...

That's not really going to work ... and probably why you are getting the errors ...

0
 

Author Comment

by:Blueice13085
ID: 35125270
Alright so what can i do then for this to work? any thing!
0
 

Author Comment

by:Blueice13085
ID: 35125287
would this work? http://allenbrowne.com/ser-24a.html and how would i be able to add 1 to the end number!
0
 
LVL 75
ID: 35125296
Well ... maybe something like this, using the RecordsetClone:


Private Sub Form_BeforeInsert (Cancel As Integer).
    With Me.RecordsetClone
         .MoveLast    ' got the the last record on the Continuous form ...
         Me![[INV / KEY TAG] = ![INV / KEY TAG]   ' get the value from the RecordsetClone
    End With
End Sub

mx
0
 
LVL 75
ID: 35125302
TYPO in last post ...


Private Sub Form_BeforeInsert (Cancel As Integer).
    With Me.RecordsetClone
         .MoveLast    ' got the the last record on the Continuous form ...
         Me![INV / KEY TAG] = ![INV / KEY TAG]   ' get the value from the RecordsetClone
    End With
End Sub
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 35125308
Sorry ... need to increment by ONE


Private Sub Form_BeforeInsert (Cancel As Integer).
    With Me.RecordsetClone
         .MoveLast    ' got the the last record on the Continuous form ...
         Me![INV / KEY TAG] = Nz( ![INV / KEY TAG] ,0 ) + 1  ' get the value from the RecordsetClone
    End With
End Sub
0
 

Author Comment

by:Blueice13085
ID: 35125314
tried this and soon as i past this in this line goes RED

Me![[INV / KEY TAG] = ![INV /
KEY TAG]   ' get the value from the RecordsetClone
0
 
LVL 75
ID: 35125324
"tried this and soon as i past this in this line goes RED"

See my corrected post @ http:#a35125308

mx
0
 

Author Comment

by:Blueice13085
ID: 35125329
i could also use this on a afterupdate of a different field correct? and this seems to work great so far THANKS for all of your help
0
 
LVL 75
ID: 35125337
"i could also use this on a afterupdate of a different field correct?"

Well ... you originally said "Default Value" ... which only applies to a New Record ... and the Before Insert event triggers as soon as any Bound Field is edited on a New Record.

But ... whatever works is fine I guess ...

I have to sign off now for a while ...

mx
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Two list boxes - best structure 3 32
Acc 2010 pause execution 10 9
Access 2003, sort table via code 5 7
Running Total in Access 4 17
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

895 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

18 Experts available now in Live!

Get 1:1 Help Now