Solved Setfocus to newly added record in subform

[Mike Krailo]

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

 

[Deleted member 147267]

Not sure where you think it is "being told to go" but it looks to me as if it's behaving as it should. If you want it to be the first record, try a requery instead of refresh. That usually puts the focus on the first subform record. Or state where you think it should be.
I'd say you might have underlying issues with normalization as it appears you are repeating several customer type fields in a job table, and that would be incorrect. Only the custID should be in the job table (aside from the job details of course).

 

[Mike Krailo]

This is for a client and I told him the same thing but that's the way he wants it. As far as the focus, I want the focus to go to the JobNumber field for the newly created potential job.
Forms![CustomerForm].Form![JobListSubForm]![JobNumber].SetFocus
But that line of code does nothing to bring the focus where it needs to go. It's somewhere that is not visible to me. The requery does indeed put the focus on the already created record but I would like it to go on the newly created record to start entering the JobNumber.

 

[Deleted member 147267]

Maybe someone will understand what you've got better than me. It looks to me that the job field is disabled and if that's the case you can't set focus there. If it's not, then it's just formatted gray I guess. Plus, there is data in the job field (or so it appears) and it even looks like there are 2 of them - both with data - so I don't get the issue, unless maybe it's because your pic doesn't reflect the problem. If it will help to know where the focus is and you can't see it, try going through the motions and then in the immediate window type
?Screen.ActiveControl.Name
and hit enter. It should tell you which control is active.

 

[Mike Krailo]

OK, I just used sendkeys as the last line of code to get the job done.
SendKeys ("^{DOWN 2}")

 

[Deleted member 147267]

I think you'd find that the general consensus is that SendKeys can be flaky and in this case it might be a bit of a hack as opposed to figuring it out. You could post a db copy for analysis if you're so inclined.

 

[Mike Krailo]

You are right, it definitely is a hack and worked for all of about 1 minute before failing to do the same thing it did before. I'll keep working on it.
UPDATE: I don't know how it happened, but all the tab stops got screwed up from when I previously set them. I had to set them back to proper order and now it's working with the sendkeys hack. As long as it works, I'm ok with it.

 

[AccessBlaster]

Maybe try setting the focus on the subform object first. If that works move forward.

JobListSubForm.SetFocus

 

[Pat Hartman]

This is for a client and I told him the same thing but that's the way he wants it.

Does he examine your table design daily? How would he know if you did this correctly by normalizing the tables? You can show whatever you want on the forms. Forms can be based on queries that join multiple tables. However, when you create a form that joins the table you want to update to a reference table, it is best to lock the controls that show the lookup data to prevent updates.

 

[Mike Krailo]

I'll keep that in mind Pat. I may post the current table structure in a new thread so you can take a look at it in the table section.

 

[arnelgp]

Try this code:

Private Sub NewLeadBtn_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim bm As Variant
'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

DoCmd.SetWarnings False

   
StrSQL =  _ &
"SELECT CustomerTable.CusID, CustomerTable.Street, CustomerTable.City, " _ &
"CustomerTable.ST, CustomerTable.Zip, Date() AS OrdDate FROM CustomerTable " _ &
"WHERE (((CustomerTable.CusID)=" & Me!CusID & "));"

Set db = CurrentDb
Set rst = db.OpenRecordset(StrSQL)

If Not (rst.BOF And rst.EOF) Then
    rst.MoveFirst
    With Me.Recordset
        .AddNew
        !CusID = rst("CusID")
        !Street = rst("Street")
        !City = rst("City")
        !ST = rst("ST")
        !Zip = rst("Zip")
        !LeadDate = Date
        .Update
        bm = .LastModified
        .bookmark = bm
    End With
    rst.Close
End If
Set rst = Nothing
Set db = Nothing
Forms![CustomerForm]![JobListSubForm].SetFocus
Forms![CustomerForm]![JobListSubForm].[Form]![JobNumber].SetFocus
End Sub

 

[Mike Krailo]

No that won't work arnelgp because the sql is actually inserting customer address info into the JobTable. And it has already been pointed out how that might not be a good idea but I believe he is just storing that in there for historical reasons. Now instead of doing the insert, I could open up every field in the job table and then do like you suggested. I might try that because even though I have it working now with the sendkeys, it is not the best way to do it (hack). Thanks for your input.

 

[cheekybuddha]

You turn the Warnings off and fail to turn them back on again.

You are probably encountering an error which is never being shown.

 

[cheekybuddha]

Are you sure that the name of the subform control containing form JobListSubForm is also called 'JobListSubForm' ?

 

[Deleted member 147267]

(edited)
I guess I missed the point of your second pic. Seems that all you need to do is 'goto record' after setting the focus to the control? You'd just need to know the record count for the form after the new record is created and subtract 1. If that's not what you're after, then for me, this thread has gone awry after post 10.

 

[Pat Hartman]

Also, creating "empty" records leads to "empty" records in the tables when the user does not complete them. Change the code to go to a new record. Then in the BeforeInsert event of the subform, copy whatever data you want into the record. Using this technique, you won't leave empty records since you won't be dirtying them before the user does. Your form cannot have the proper validation code if it allows you to add "empty" records. Once you correct the logic error in your current process, you can fix your validation code to prevent empty records from being added

 

[Mike Krailo]

Thank you very much for the advice to all who have contributed. I will do some more experiments as soon as I get a chance. Been a busy day today and I have one last meeting till I can relax.

 

[Mike Krailo]

I'll take Pat Hartman's advice soon enough to avoid the blank records. For now I got rid of the sendkeys stuff and put this at the end of the code and it works fine.

   Me.JobListSubForm.SetFocus
   DoCmd.RunCommand acCmdRecordsGoToLast
   Me!JobListSubForm!JobType.SetFocus  'Put's the focus on JobType control for the new record (MDK) 6/25/2020

 

[cheekybuddha]

Remember to get rid of the DoCmd.SetWarnings False (or make sure you turn them back on at the end of your procedure)

 

[Mike Krailo]

David, you might have missed the posted code in post#1 but it does have the DoCmd.SetWarnings True near the bottom. I think you were looking at Arnelgp's code which I'm not using. Thank you for your concern though. Very thoughtful.