Access 2010 - display last ID (auto number)

[gawai]

hi,
i want to display last ID in my form field, below code does not work. any solution ?

Private Sub Form_Load()
Dim db As Database
Dim rs As DAO.Recordset


Set db = CurrentDb()
selectQuery = "SELECT ID FROM myTable"
Set rs = CurrentDb.OpenRecordset(selectQuery)


rs.MoveLast
lastRec = rs("ID")



Me.fldRptNo.Value = lastRec
MsgBox lastRec




'get newly inserted record ID (autonumber)
'rs.Move 0, rs.LastModified
'newID = rs("ID")


rs.Close
Set rs = Nothing


End Sub

 

[BrianMH]

You may need to recalculate the form to get it to display.

 

[gawai]

You may need to recalculate the form to get it to display.

the input fields are unbound.
what will i recalculate in the form ?

 

[BrianMH]

Does the message box come up with the value but it just doesn't populate the textbox?

 

[gawai]

Error :

Run-time Error '94':

Invalid user of Null

for line
MsgBox lastRec

 

[BrianMH]

Try this.

Private Sub Form_Load()
    Dim db As Database
    Dim rs As DAO.Recordset




    Set db = CurrentDb()
    selectQuery = "SELECT max(ID) as ID FROM myTable"
    Set rs = CurrentDb.OpenRecordset(selectQuery)




    rs.MoveLast
    lastRec = rs!ID
    Me.fldRptNo.Value = lastRec
    MsgBox lastRec
    rs.Close
    Set rs = Nothing




End Sub

 

[gawai]

thanks.
it works but after i changed to :

Set db = CurrentDb()
selectQuery = "SELECT max(ID) as lastID FROM TblHT"
Set rs = CurrentDb.OpenRecordset(selectQuery)


rs.MoveLast
lastRec = rs!lastID
Me.fldRptNo.Value = lastRec
MsgBox lastRec

 

[BrianMH]

Out of curiosity in your original code did you change myTable to TblHT?

 

[gawai]

Not really but ID was repeated in your sql stmt.
I changed to lastID

SELECT max(ID) as lastID
Instead of
SELECT max(ID) as ID

 

[BrianMH]

Yeah I get that, I'm just saying your original errors may have been due to you not referencing the table name correctly if you hadn't changed myTable to TblHT.