Solved Setfocus to newly added record in subform (1 Viewer)

Mike Krailo

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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.
1592873353669.png

Code:
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
 

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
3,471
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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.
1592876339088.png
 

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
3,471
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
OK, I just used sendkeys as the last line of code to get the job done.
SendKeys ("^{DOWN 2}")
 

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
3,471
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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.
 
Last edited:

AccessBlaster

Custom title here:
Local time
Today, 09:11
Joined
May 22, 2010
Messages
3,099
Maybe try setting the focus on the subform object first. If that works move forward.

Code:
JobListSubForm.SetFocus
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:11
Joined
Feb 19, 2002
Messages
30,597
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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

error reading drive A:
Local time
Tomorrow, 01:11
Joined
May 7, 2009
Messages
11,130
Try this code:
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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

AWF VIP
Local time
Today, 17:11
Joined
Jul 21, 2014
Messages
557
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

AWF VIP
Local time
Today, 17:11
Joined
Jul 21, 2014
Messages
557
Are you sure that the name of the subform control containing form JobListSubForm is also called 'JobListSubForm' ?
 

Micron

AWF VIP
Local time
Today, 12:11
Joined
Oct 20, 2018
Messages
3,471
(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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:11
Joined
Feb 19, 2002
Messages
30,597
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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.

Code:
   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

AWF VIP
Local time
Today, 17:11
Joined
Jul 21, 2014
Messages
557
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

Active member
Local time
Today, 12:11
Joined
Mar 28, 2020
Messages
158
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.
 

Users who are viewing this thread

Top Bottom