Select last record!

[Dave]

Hi,

I'm having real trouble getting the 'On Change' event for
a combo box on a Form to select the last entered record in
a table that has the selected option in the combo box.
Once selected then populate other fields on the form with
the data from that record.

I tried using the SQL code
SELECT TOP 1
FROM
WHERE
but with no luck. Is there an easy way to do this.

Thanks

Dave

 

[John Vinson]

Hi,

I'm having real trouble getting the 'On Change' event for
a combo box on a Form to select the last entered record in
a table that has the selected option in the combo box.

The On Change event isn't actually appropriate for this: it fires
*with every keystroke* if you type in the combo. The combo's
AfterUpdate event will work better.

Once selected then populate other fields on the form with
the data from that record.

I tried using the SQL code
SELECT TOP 1
FROM
WHERE
but with no luck. Is there an easy way to do this.

Could you post your actual code? Note that SQL is one language, and
VBA is a different language; you can't mix them. The code as written
selects no fields from no table with no criteria... so it wouldn't do
much!

 

[John Vinson]

This is what I had tried so far:

Private Sub A_CNo_Change()
Dim dbs As Database, rst As Recordset

You need to define the dbs object, e.g.

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("SELECT TOP 1 " _
& "A/CNo FROM Dail Record " _

Since there are special characters, e.g. / and blank, in the field and
table names you must enclose them in square brackets.

& "WHERE A/CNo = A/CNo.text")

I'm not sure what this means. What is A/CNo.text? A/Cno is a field in
your table, and fields don't have Text properties!

Since it's a form control name, you again need brackets; to prevent
confusion (field, or control?) I'd rename the control to something
like cboACNo and pull the reference OUT of the quotes.

Also, for the Top 1 to work you must specify an Order By clause. I
don't have any idea what field in the table you want to use for
sorting, so I can't suggest what; but as a step on the road try

Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
strSQL = "SELECT TOP 1 [A/CNo] FROM [Dail Record]" _
& " WHERE [Dail Record].[A/CNo] = " & Me!cboACNo" _
& " ORDER BY <some field I don't know>"
Set rst = dbs.OpenRecordset strSQL, dbOpenSnapshot

<do something with rst, I don't know just what you want done>