Blueice13085
asked on
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
Problem is this will only work one time because this is a after update event!
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
Problem is this will only work one time because this is a after update event!
ASKER
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
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
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
ASKER
Also the number is not like 1,2,3,4. its going to be like 172105,172106,172107,17220 1,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
What is the Error you are getting ?
mx
mx
ASKER
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
Me![INV / KEY TAG] = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1
but also comes up with a error
"but also comes up with a error "
Please .... *what* is the error ?
Please .... *what* is the error ?
ASKER
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!
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!
ASKER
just takes be to VBA
ASKER
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!
When you use Me ..... What ... is the Error ?
mx
mx
ASKER
also seen something like this online
any thoughts on using this?
=DLookUp("[Field]","Table","[ID]=Forms![Form1]![ID]-1")
any thoughts on using this?
Until you tell me what Error you are getting, I cannot help further, sorry.
mx
mx
ASKER
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
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
ASKER
Your code
My error
Run-time error '13':
Type Mismatch
End, Debug, help
Private Sub Form_BeforeInsert(Cancel As Integer)
Me![INV / KEY TAG] = Nz(DMax("[INV / KEY TAG]", "[drivers_log (sub)]"), 0) + 1
End Sub
My error
Run-time error '13':
Type Mismatch
End, Debug, help
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 ...
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 ...
ASKER
Alright so what can i do then for this to work? any thing!
ASKER
would this work? http://allenbrowne.com/ser -24a.html and how would i be able to add 1 to the end number!
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Me![[INV / KEY TAG] = ![INV /
"tried this and soon as i past this in this line goes RED"
See my corrected post @ http:#a35125308
mx
See my corrected post @ http:#a35125308
mx
ASKER
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
"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
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
Private Sub Form_BeforeInsert (Cancel As Integer).
Me.YourNumericControl = Nz (DMax("[YourTableFieldName
End Sub
mx