Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access Default value

Posted on 2011-03-13
25
Medium Priority
?
418 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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
 
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 - Microsoft MVP, Access and Data Platform) earned 2000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

721 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