Re: Problem with SQL and Recordset

Tech-Archive recommends: Fix windows errors by optimizing your registry

I have to leave now, but thank you for such a complete reply - will try
tomorrow first thing

Have a good evening
Sue


"Douglas J. Steele" wrote:

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
http://I.Am/DougSteele
(no private e-mails, please)


"Design by Sue" <DesignbySue@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:20B92F8F-BF54-4DBD-8EB2-17474B303F8B@xxxxxxxxxxxxxxxx
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

"Douglas J. Steele" wrote:

What's the current code in the module where you want to assign the value
to
Suffix?

(Also, is the subform control on your parent form named LocationSub or
something else?)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)




.

Relevant Pages

  • Re: Problem with SQL and Recordset
    ... Private Sub Suffix_GotFocus ... Dim myRecordset As DAO.Recordset ... Set myRecordset = CurrentDb.OpenRecordset ... Doug Steele, Microsoft Access MVP ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problem with SQL and Recordset
    ... "Douglas J. Steele" wrote: ... Doug Steele, Microsoft Access MVP ... Private Sub Suffix_GotFocus ... Set myRecordset = CurrentDb.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problem with SQL and Recordset
    ... Doug Steele, Microsoft Access MVP ... Private Sub Suffix_GotFocus ... Dim myRecordset As DAO.Recordset ... Set myRecordset = CurrentDb.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problem with SQL and Recordset
    ... Doug Steele, Microsoft Access MVP ... Private Sub Suffix_GotFocus ... Dim myRecordset As DAO.Recordset ... Set myRecordset = CurrentDb.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Problem with SQL and Recordset
    ... Private Sub Suffix_GotFocus ... Doug Steele, Microsoft Access MVP ... Dim myRecordset As DAO.Recordset ... Set myRecordset = CurrentDb.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)