L
Les
Hi, is it possible to select the last record entered, if so how ?
Thanks in advance
Thanks in advance
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Allen Browne
How do you define "last"?
Is it the highest AutoNumber value?
Or is there a date/time field?
Perhaps something like this:
Dim rs As DAO.Database
Dim strSql As String
strSql = "SELECT TOP 1 Table1.* ORDER BY [ID] DESC;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Is it the highest AutoNumber value?
Or is there a date/time field?
Perhaps something like this:
Dim rs As DAO.Database
Dim strSql As String
strSql = "SELECT TOP 1 Table1.* ORDER BY [ID] DESC;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
J
Jerry Whittle
Depends on how you define the last record. Database tables don't really store
records in any order. It might look like it; however, you can't depend upon
it. You also can't really depend on autonumbers to increment in an orderly
fashion.
What I do in such situations is create a field called something like
dtAdded. I make the default value of this field Now(). That way when a new
record is added, the current date and time is added to the field. This field
doesn't need to show up on any forms or reports.
Now to find the last record entered using a query, it looks something like:
SELECT TOP 1 tblTable.*
FROM tblTable
ORDER BY dtAdded DESC;
records in any order. It might look like it; however, you can't depend upon
it. You also can't really depend on autonumbers to increment in an orderly
fashion.
What I do in such situations is create a field called something like
dtAdded. I make the default value of this field Now(). That way when a new
record is added, the current date and time is added to the field. This field
doesn't need to show up on any forms or reports.
Now to find the last record entered using a query, it looks something like:
SELECT TOP 1 tblTable.*
FROM tblTable
ORDER BY dtAdded DESC;
L
Les
Hi Allen, i have tried to adapt the code i got on another site but have made
a mistake somewhere, could you help me please
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strInfo As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CIANumberRequest. * order by [dtAdded]
DESC", dbOpenForwardOnly, dbReadOnly)
While strInfo = ""
strInfo = strInfo & rst!Requestor & vbTab
strInfo = strInfo & rst!RequestorDept & vbTab
strInfo = strInfo & rst!Amount
Wend
GetReqText = strInfo
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage, True
End If
End Sub
--
Les
a mistake somewhere, could you help me please
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strInfo As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CIANumberRequest. * order by [dtAdded]
DESC", dbOpenForwardOnly, dbReadOnly)
While strInfo = ""
strInfo = strInfo & rst!Requestor & vbTab
strInfo = strInfo & rst!RequestorDept & vbTab
strInfo = strInfo & rst!Amount
Wend
GetReqText = strInfo
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage, True
End If
End Sub
--
Les
Allen Browne said:How do you define "last"?
Is it the highest AutoNumber value?
Or is there a date/time field?
Perhaps something like this:
Dim rs As DAO.Database
Dim strSql As String
strSql = "SELECT TOP 1 Table1.* ORDER BY [ID] DESC;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Les said:Hi, is it possible to select the last record entered, if so how ?
Thanks in advance
A
Allen Browne
There are several things wrong, e.g. no SELECT in the SQL statement, and the
loop makes no sense.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
loop makes no sense.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Les said:Hi Allen, i have tried to adapt the code i got on another site but have
made
a mistake somewhere, could you help me please
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strInfo As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CIANumberRequest. * order by [dtAdded]
DESC", dbOpenForwardOnly, dbReadOnly)
While strInfo = ""
strInfo = strInfo & rst!Requestor & vbTab
strInfo = strInfo & rst!RequestorDept & vbTab
strInfo = strInfo & rst!Amount
Wend
GetReqText = strInfo
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage,
True
End If
End Sub
--
Les
Allen Browne said:How do you define "last"?
Is it the highest AutoNumber value?
Or is there a date/time field?
Perhaps something like this:
Dim rs As DAO.Database
Dim strSql As String
strSql = "SELECT TOP 1 Table1.* ORDER BY [ID] DESC;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Les said:Hi, is it possible to select the last record entered, if so how ?
Thanks in advance
L
Les
Hi Allen, i am not a programmer but a dabbler and really battling with this,
can you help me with the code ?
--
Les
can you help me with the code ?
--
Les
Allen Browne said:There are several things wrong, e.g. no SELECT in the SQL statement, and the
loop makes no sense.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Les said:Hi Allen, i have tried to adapt the code i got on another site but have
made
a mistake somewhere, could you help me please
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strInfo As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CIANumberRequest. * order by [dtAdded]
DESC", dbOpenForwardOnly, dbReadOnly)
While strInfo = ""
strInfo = strInfo & rst!Requestor & vbTab
strInfo = strInfo & rst!RequestorDept & vbTab
strInfo = strInfo & rst!Amount
Wend
GetReqText = strInfo
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage,
True
End If
End Sub
--
Les
Allen Browne said:How do you define "last"?
Is it the highest AutoNumber value?
Or is there a date/time field?
Perhaps something like this:
Dim rs As DAO.Database
Dim strSql As String
strSql = "SELECT TOP 1 Table1.* ORDER BY [ID] DESC;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Hi, is it possible to select the last record entered, if so how ?
Thanks in advance
L
Les
Hello Allen, i have made the following changes and it works ok, with the
exception of the loop, it just keeps looping ??
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strReq As String, strReqD As String, strAmnt As String, strSql As
String
Dim strGams As String, strProj As String
strSql = "SELECT * FROM tbl_CIANumberRequest ORDER BY dtAdded DESC"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql, dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF '<========= JUst keeps looping ??
strReq = rst!Requestor
strReqD = rst!RequestorDept
strAmnt = rst!Amount
strGams = rst!gAMSNo
strProj = rst!ProjectNo
Wend
' GetReqText
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage, True
End If
End Sub
--
Les
exception of the loop, it just keeps looping ??
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strReq As String, strReqD As String, strAmnt As String, strSql As
String
Dim strGams As String, strProj As String
strSql = "SELECT * FROM tbl_CIANumberRequest ORDER BY dtAdded DESC"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql, dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF '<========= JUst keeps looping ??
strReq = rst!Requestor
strReqD = rst!RequestorDept
strAmnt = rst!Amount
strGams = rst!gAMSNo
strProj = rst!ProjectNo
Wend
' GetReqText
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage, True
End If
End Sub
--
Les
Allen Browne said:There are several things wrong, e.g. no SELECT in the SQL statement, and the
loop makes no sense.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Les said:Hi Allen, i have tried to adapt the code i got on another site but have
made
a mistake somewhere, could you help me please
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strInfo As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CIANumberRequest. * order by [dtAdded]
DESC", dbOpenForwardOnly, dbReadOnly)
While strInfo = ""
strInfo = strInfo & rst!Requestor & vbTab
strInfo = strInfo & rst!RequestorDept & vbTab
strInfo = strInfo & rst!Amount
Wend
GetReqText = strInfo
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage,
True
End If
End Sub
--
Les
Allen Browne said:How do you define "last"?
Is it the highest AutoNumber value?
Or is there a date/time field?
Perhaps something like this:
Dim rs As DAO.Database
Dim strSql As String
strSql = "SELECT TOP 1 Table1.* ORDER BY [ID] DESC;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Hi, is it possible to select the last record entered, if so how ?
Thanks in advance
A
Allen Browne
Les, I don't know what the loop is meant to do.
If you just want the data from the first record, you could remove the While
and Wend lines so there is no loop.
If you do need to loop through other records as well, you need an
rst.MoveNext in the loop, but I still don't understand the point of the
loop.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
If you just want the data from the first record, you could remove the While
and Wend lines so there is no loop.
If you do need to loop through other records as well, you need an
rst.MoveNext in the loop, but I still don't understand the point of the
loop.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Les said:Hello Allen, i have made the following changes and it works ok, with the
exception of the loop, it just keeps looping ??
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strReq As String, strReqD As String, strAmnt As String, strSql As
String
Dim strGams As String, strProj As String
strSql = "SELECT * FROM tbl_CIANumberRequest ORDER BY dtAdded DESC"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql, dbOpenForwardOnly, dbReadOnly)
While Not rst.EOF '<========= JUst keeps looping ??
strReq = rst!Requestor
strReqD = rst!RequestorDept
strAmnt = rst!Amount
strGams = rst!gAMSNo
strProj = rst!ProjectNo
Wend
' GetReqText
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject, strmessage,
True
End If
End Sub
--
Les
Allen Browne said:There are several things wrong, e.g. no SELECT in the SQL statement, and
the
loop makes no sense.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Les said:Hi Allen, i have tried to adapt the code i got on another site but have
made
a mistake somewhere, could you help me please
Function GetReqText() As String
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strInfo As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_CIANumberRequest. * order by
[dtAdded]
DESC", dbOpenForwardOnly, dbReadOnly)
While strInfo = ""
strInfo = strInfo & rst!Requestor & vbTab
strInfo = strInfo & rst!RequestorDept & vbTab
strInfo = strInfo & rst!Amount
Wend
GetReqText = strInfo
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Function
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Sub SendMail()
Dim strBody
strBody = GetReqText()
If Len(strBody) = 0 Then
strBody = "Nothing shipped today"
Else
Dim strmessage As String, stDocName
Dim stremail As String
Dim strsubject As String
strmessage = strBody
stDocName = "rpt_CIAEmail"
stremail = "[email protected]"
strsubject = "CIA Number Request"
DoCmd.SendObject , stDocName, , stremail, , , strsubject,
strmessage,
True
End If
End Sub
--
Les
:
How do you define "last"?
Is it the highest AutoNumber value?
Or is there a date/time field?
Perhaps something like this:
Dim rs As DAO.Database
Dim strSql As String
strSql = "SELECT TOP 1 Table1.* ORDER BY [ID] DESC;"
Set rs = dbEngine(0)(0).OpenRecordset(strSql)
Hi, is it possible to select the last record entered, if so how ?
Thanks in advance