- Local time
- Today, 12:11
- Mar 28, 2020
Here is a screen shot of the form. If New Lead button is clicked, then a new job record is created in the subform but the focus is not where it is being told to go. See the code below.
Private Sub NewLeadBtn_Click() Dim JobNo As Long Dim db As DAO.Database 'Changed from Database to DAO.Database to get rid of compile error (MDK) 6/22/2020 Dim rst As Recordset Dim StrSQL As String If MsgBox(" New Lead? ", vbYesNo) = vbNo Then Exit Sub End If Refresh DoCmd.SetWarnings False StrSQL = "INSERT INTO JobTable ( CusID, Street, City, ST, Zip, LeadDate ) " _ & "SELECT CustomerTable.CusID, CustomerTable.Street, CustomerTable.City, " _ & "CustomerTable.ST, CustomerTable.Zip, Date() AS OrdDate FROM CustomerTable " _ & "WHERE (((CustomerTable.CusID)=" & Me!CusID & "));" DoCmd.RunSQL (StrSQL) StrSQL = "SELECT Max(JobTable.JobID) AS MaxOfJobID FROM JobTable " _ & "WHERE (((JobTable.CusID)=" & Me!CusID & "));" Set db = CurrentDb Set rst = db.OpenRecordset(StrSQL) rst.MoveFirst JobNo = rst!MaxOfJobID rst.Close Set rst = Nothing Set db = Nothing DoCmd.SetWarnings True Me.Refresh Forms![CustomerForm].Form![JobListSubForm]![JobNumber].SetFocus End Sub