Select last record!

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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.
Click to expand...

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.
Click to expand...

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!
 
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!


.
Click to expand...

This is what I had tried so far:

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

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

'Where:
'"A/CNo" is a field in the table "Daily Record".
'"Daily Record" is the table bound to the form.
'"A/CNo is also the name of the Combo box.

End Sub

This is as far as I got.
Hope you have a simple solution.

Thanks

Dave
 
This is what I had tried so far:

Private Sub A_CNo_Change()
Dim dbs As Database, rst As Recordset
Click to expand...

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

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

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")
Click to expand...

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>
 
Back
Top