Record Navigation Issue in VBA with SQL - Please Help

[sanders720]

This program works great, until the clumsy coding at the end. All I want to do is move to the last record just created by this program. Currently, the form stays at the beginning of the table.

I think I'm mixed up on which recordset I'm in and how the syntax to move around is done. Perhaps it's overcomplicated.

Look for +++---+++--- below, which shows where this program ceases to work.

Thanks in advance for your help with this issue...


Private Sub cmdAddITJobNumber_Click()
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim rs As Recordset
Dim X As Long

sql1 = "SELECT Max ([IT Job Number]) AS Expr1 from tblITJobPurchases"
Debug.Print sql1
Set rs = CurrentDb.OpenRecordset(sql1)
X = rs!expr1 + 10
Me.[cboIT Job Number].Value = X

sql2 = "INSERT INTO tblITJobPurchases ([IT Job Number]) VALUES (" & Me.[cboIT Job Number] & &quot"
Debug.Print sql2
CurrentDb.Execute sql2

DoCmd.Close
DoCmd.OpenForm "frmITJobPurchases"
Forms!frmITJobPurchases![cboIT Job Number].Value = X

rs.Close
Set rs = Nothing

+++---+++---

sql3 = "SELECT [IT Job Number] from tblItJobPurchases"
Set rs = CurrentDb.OpenRecordset(sql3)
Debug.Print X; " Do Until Match"

Do Until rs.EOF
Debug.Print tables!tblITJobPurchases![IT Job Number].Value
If tables!tblITJobPurchases![IT Job Number].Value = X Then
Exit Do
Else
rs.MoveNext
End If
Loop

rs.Close
Set rs = Nothing




End Sub

 

[scking]

I believe it would be much easier to resolve if the code were simplified.

========Complex===============
sql1 = "SELECT Max ([IT Job Number]) AS Expr1 from tblITJobPurchases"
Debug.Print sql1
Set rs = CurrentDb.OpenRecordset(sql1)
X = rs!expr1 + 10
Me.[cboIT Job Number].Value = X
--------Simplified-------------
Me.[cboIT Job Number].Value = DLookup("[IT Job Number]", "tblITJobPurchases&quot

Since you don't explain your design very much I need to assume that the tblITJobPurchases is probably what? Are you attached to this table with the form. If so then a simple assignment to a bound control would completely replace the code for the insert.

Are you closing the form you are working in and reopening it? Now I don't understand that part. I believe you would only need this. And probably not it if you had it as a bound control.

Me.[cboIT Job Number].Requery

The last part, where you are reading through every record to find what you just loaded is again confusing to me. I'm not sure why you are doing it but you could really speed it up using the .FindFirst method of the recordset.

I suggest you provide us a little feedback about your code and we could be of much more assistance. Frequently, with a little explanation, you will get a lot more responses to your questions. If you are doing what I suspect then the code could be reduced to about 3 lines without using three recordsets.

Steve King


Growth follows a healthy professional curiosity