Douglas J. Steele said:
See whether this works:
Private Sub Suffix_GotFocus()
If Len([Forms]![InPutFrm]!LocationSub2.Form!Suffix & "") = 0 Then
[Forms]![InPutFrm]!LocationSub2.Form!Suffix = _
Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"), 0) + 1
End If
End Sub
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Oops - I mean NewRecord, not NextRecord (I cut and pasted your code)
:
That's my life!
Yes there is a value, nothing changes - If I use the code with
NextRecord
the suffix is 0 and acts as described. Then if I remove the
NextRecord
and
the If references, the code adds 1 to the suffix but does it every
time
you
access the record.
I have tried this on the OnCurrent for the subform and on the GetFocus
of
the Suffix field and get the same results. Should I be putting is
somewhere
else?
:
That makes no sense at all, since it's running exactly what it was
running
before!
Does PartNumber have a value when the code's running? (I'm guessing
that
maybe it's looking up for PartNumber = '", and so finding nothing.)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
That stopped the old records from updating, but also the new
record -
it
stays at 0
:
Check the form's NewRecord property.
Private Sub Suffix_GotFocus()
If [Forms]![InPutFrm].NewRecord Then
[Forms]![InPutFrm]!LocationSub2.Form!Suffix = _
Nz(DMax("[Suffix]", "WhereIsItTbl", _
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"),
0)
+ 1
End If
End Sub
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Thanks Doug, actually both worked (though I had to change
Me!LocationSub2.Form!Suffix to
[Forms]![InPutFrm]![LocationSub2]![Suffix])
BUT - and there always seems to be a but - using either method,
on
either
the
Got Focus event of the field or the On Current event of the
subform,
the
suffix number increases everytime you select the record. I
need
the
number
to stay as assigned when the record is first created. Is there
an
event
that
writes to the table only when the record is new, or can the
coding
be
changed
to do this?
You have been a great help. (I hope to take a class in Access
in
April,
hopefully I will get a better understanding of this)
:
If you want to go the recordset route, try:
Private Sub Suffix_GotFocus()
Dim myRecordset As DAO.Recordset
Dim mySQL As String
mySQL = "SELECT Max(WhereIsItTbl.Suffix) AS MaxOfSuffix, " & _
"WhereIsItTbl.PartNumber, " & _
"Max([Suffix]+1) AS NextSuffix FROM WhereIsItTbl GROUP BY " &
_
"WhereIsItTbl.PartNumber " & _
"HAVING WhereIsItTbl.PartNumber = " &
[Forms]![InPutFrm]![PartNumber]
' There's no point having an ORDER BY when there's only going
to
be a
single
record!
Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If
myRecordset.Close
Set myRecordset = Nothing
End Sub
That having been said, the following should work just as well:
Private Sub Suffix_GotFocus()
Me!LocationSub2.Form!Suffix = Nz(DMax("[Suffix]",
"WhereIsItTbl",
_
"PartNumber = " & [Forms]![InPutFrm]![PartNumber]), 0) + 1
End Sub
You never did let me know wheter PartNumber is numeric or
text.
If
it's
text, then the recordset solution needs to be changed to
Private Sub Suffix_GotFocus()
Dim myRecordset As DAO.Recordset
Dim mySQL As String
mySQL = "SELECT Max(WhereIsItTbl.Suffix) AS MaxOfSuffix, " & _
"WhereIsItTbl.PartNumber, " & _
"Max([Suffix]+1) AS NextSuffix FROM WhereIsItTbl GROUP BY " &
_
"WhereIsItTbl.PartNumber " & _
"HAVING WhereIsItTbl.PartNumber = '" &
[Forms]![InPutFrm]![PartNumber]
&
"'"
Set myRecordset = CurrentDb.OpenRecordset(mySQL)
If myRecordset.EOF Then
Me!LocationSub2.Form!Suffix = 0 ' or whatever you want
Else
Me!LocationSub2.Form!Suffix = myRecordset!NextSuffix
End If
myRecordset.Close
Set myRecordset = Nothing
End Sub
and the non-recordset approach to
Private Sub Suffix_GotFocus()
Me!LocationSub2.Form!Suffix = Nz(DMax("[Suffix]",
"WhereIsItTbl",
_
"PartNumber = '" & [Forms]![InPutFrm]![PartNumber] & "'"),
0)
+ 1
End Sub
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
in
message
The subform control is called LocationSub2
The code I have, which is what you posted previously with
the
addition
of
the closure of the record set you posted.
Sorry that I just can't see this.
Thanks for your help
Private Sub Suffix_GotFocus()
Dim myRecordset As DAO.Recordset
Dim mySQL As String
mySQL = "SELECT Max(WhereIsItTbl.Suffix) AS MaxOfSuffix, " &
_
"WhereIsItTbl.PartNumber, " & _
"Max([Suffix]+1) AS NextSuffix FROM WhereIsItTbl GROUP BY "
& _
"WhereIsItTbl.PartNumber " & _
"HAVING WhereIsItTbl.PartNumber = " &
[Forms]![InPutFrm]![PartNumber] &
_
" ORDER BY Max(WhereIsItTbl.Suffix) DESC"
Set myRecordset = CurrentDb.OpenRecordset(mySQL)
myRecordset.Close
Set myRecordset = Nothing
End Sub
