[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

MS Access Default value

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
Blueice13085
Asked:
Blueice13085
  • 13
  • 12
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What is the Error you are getting ?

mx
0
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"but also comes up with a error "
Please .... *what* is the error ?
0
 
Blueice13085Author Commented:
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
 
Blueice13085Author Commented:
just takes be to VBA
0
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
When you use Me ..... What ... is the Error ?

mx
0
 
Blueice13085Author Commented:
also seen something like this online

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

Open in new window


any thoughts on using this?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Until you tell me what Error you are getting, I cannot help further, sorry.

mx
0
 
Blueice13085Author Commented:
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
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Blueice13085Author Commented:
Alright so what can i do then for this to work? any thing!
0
 
Blueice13085Author Commented:
would this work? http://allenbrowne.com/ser-24a.html and how would i be able to add 1 to the end number!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"tried this and soon as i past this in this line goes RED"

See my corrected post @ http:#a35125308

mx
0
 
Blueice13085Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now