Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access Default value

Posted on 2011-03-13
25
Medium Priority
?
420 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
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: …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

782 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