Solved

MS Access Default value

Posted on 2011-03-13
25
390 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Outlook Free & Paid Tools
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

707 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

14 Experts available now in Live!

Get 1:1 Help Now