Creating a query that returns the last record in a table

[bookenz]

I want to create a query that returns the last entered record in table. How do I do this. Not sure how to use "Max" in query design.

 

[Golom]

You need a field (or fields) in the table that uniquely identify the last record entered, then

Select * From tbl
Where Fld = (Select MAX(Fld) From tbl)

Where "Fld" is the field that whose MAX value is in the last record.

 

[Remou]

Hi
Max in SQL view:
[tt]SELECT Max([Field1]) AS Expr1
FROM tblTable;[/tt]

Max in Code:
[tt]vMax = DMax("Field1", "tblTable", "OptionalCriteria")[/tt]

For last record, you will need a date or other such field that increments. Is this what you meant?

 

[bookenz]

Thanks kindly for your speedy replies. I sort of 'get' your SQL suggestions however I think it's time for me to ellaborate on the real problem:

I have 2 tables: tblCustomer, and tblCompany.
and 2 forms: frmCustomer, and frmAddCompany

In frmCustomer I have a combo box called cboCompany. cboCompany requires the user to select a company (the company that the customer belongs to (row source: "SELECT tblCompany.Company_ID, tblCompany.Company_Name FROM tblCompany ORDER BY tblCompany.Company_Name;").

I have allowed the user to type in a company that is not in the list which invokes the 'cboCompany_Not_in_list' event procedure:

Private Sub cboCompany_NotInList(NewData As String, Response As Integer)

Dim strSQL As String
Dim i As Integer
Dim Msg As String
Dim CompanyName As String

'exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add a new company?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Company not found")
If i = vbYes Then
strSQL = "Insert Into tblCompany ([Company_Name]) values ('" & NewData & "')"
CurrentDb.Execute strSQL, dbFailOnError
Response = acDataErrAdded

DoCmd.OpenForm "frmAddCompany"

Else
Response = acDataErrContinue
Exit Sub
End If
End Sub

As you can see the procedure adds the new company to tblCompany and then opens 'frmAddCompany'. Now 'frmAddCompany' is based on a query (qryCompany) with the following criteria under Company_ID: [Forms]![frmCustomerRequest]![cboCompany]. This is so that only the new company record is displayed (allowing the user to fill in other details relating to the new company).

However, this does not work. in fact frmAddCompany displays the previously entered company. It is as though cboCompany is not updated with the new record when VBA calls frmAddCompany.

I have a command button labelled "Add Company Details" on frmCustomer. If the user enteres a new company into cboCompany and then clicks the "Add Company Details" button then frmAddCompany displays the correct record (being the new company, or the last record).

How can I update or refresh cboCompany in VBA so that when the line:

DoCmd.OpenForm "frmAddCompany"

is called the correct (last record) record is displayed.

Thankyou in anticipation of this long winded problem...

 

[Remou]

Hi
How about:

DoCmd.OpenForm "frmAddCompany", , , "Company_Name ='" & NewData & "'"

 

[bookenz]

Thanks Remou. Just tried your suggestion. Doesn't seem to work though. Same problem. frmAddCompany displays with no record (until the sub is exited then it displays the new record when i click on the "Display Company records" button.)...crazeh stuff.

 

[bookenz]

I can temporarily solve this by using the 'max' method (as above) and telling qryCompany to select the last record in tblCompany, but, I assume this is a cheap fix and programmatically unsound.

 

[Remou]

Hi
I pasted the code from above and tested it. It worked for me, so could the problem may be somewhere else? Perhaps you could you try it with a fake company table containing only company name, which is what I did.

 

[bookenz]

cheers mate. some guy answered on the other thread. and it worked. so prob solved.