Open a from from sub form (1 Viewer)

Teri Bridges

Member
Local time
Today, 08:24
Joined
Feb 21, 2022
Messages
186
Hi, I have yet another question. I have a sub form that list courses by developer. So this form shows a list of courses assigned to the developer. What I want to do is open the main course form to the specific course when the user clicks the course ID in the sub form.

So what I am attempting is when the user looks at their assignments and sees course ID 3 they click on course ID 3 and the main form opens to Course ID 3

I can get the main course form to open using

DoCmd.OpenForm "frmCourseDetails", WhereCondition:="[CourseID]=" & Me!CourseID


But the main form opens to new record.

I do have the on load set on the main form to open in new record so I am sure it has something to do with that, but I am not sure what I need to do to fix it.

I do want the main form to open to new record if the user is not accessing through the sub form. I hope this makes sense.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:24
Joined
Oct 29, 2018
Messages
21,357
Is the main form set to Data Entry = Yes? Just checking...
 

plog

Banishment Pending
Local time
Today, 08:24
Joined
May 11, 2011
Messages
11,611
Is the text 'WhereCondition:" actualy in the DoCmd.OpenForm call? It shouldn't be:

 

Teri Bridges

Member
Local time
Today, 08:24
Joined
Feb 21, 2022
Messages
186
Is the main form set to Data Entry = Yes? Just checking...
No because if I do that I loose my search function. I don't think I can do what I want with the main form being set to on load new record. If I take the on load off it works fine. That's a bummer because it leaves my first record up and if folks are not paying attention they could accidently edit it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:24
Joined
Oct 29, 2018
Messages
21,357
No because if I do that I loose my search function. I don't think I can do what I want with the main form being set to on load new record. If I take the on load off it works fine. That's a bummer because it leaves my first record up and if folks are not paying attention they could accidently edit it.
Can you post a sample db to demonstrate the problem?
 

Teri Bridges

Member
Local time
Today, 08:24
Joined
Feb 21, 2022
Messages
186
Can you post a sample db to demonstrate the problem?
I did not know I could add a DB file, wow.
So if you go to frmcontactdetails select a contact go to the assignments tab click the course Id it opens the form to a new record not the specific record
 

Attachments

  • TeriV12.zip
    1.3 MB · Views: 165

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:24
Joined
Feb 28, 2001
Messages
26,998
The usual way to do this is,
You look at the combo/list box/selected sub-form record to get the desired field value
You use the parent form's .RecordSetClone to do a .FindFirst operation on that field value in the cloned recordset.
You pick up the bookmark from the parent's .RecordSetClone (after the successful search)
You use the bookmark to adjust the position of the parent's .Recordset.

Doing that from a sub-form is a bit trickier because you now have to prefix things with .Parent because nothing is local to you except the selected record. And doing it from a sub-form complicates matters because as a separate form, the sub-form context doesn't exactly overlap with the parent form context. Oh, they are reachable. But it is just rare to use a sub-form when a combo box in the parent's context would do just as well.

I mention it that way because you can create a combo box to do exactly that - find the correct record and reset the focus of the form. There is a wizard that would build that for you.
 

Teri Bridges

Member
Local time
Today, 08:24
Joined
Feb 21, 2022
Messages
186
The usual way to do this is,
You look at the combo/list box/selected sub-form record to get the desired field value
You use the parent form's .RecordSetClone to do a .FindFirst operation on that field value in the cloned recordset.
You pick up the bookmark from the parent's .RecordSetClone (after the successful search)
You use the bookmark to adjust the position of the parent's .Recordset.

Doing that from a sub-form is a bit trickier because you now have to prefix things with .Parent because nothing is local to you except the selected record. And doing it from a sub-form complicates matters because as a separate form, the sub-form context doesn't exactly overlap with the parent form context. Oh, they are reachable. But it is just rare to use a sub-form when a combo box in the parent's context would do just as well.

I mention it that way because you can create a combo box to do exactly that - find the correct record and reset the focus of the form. There is a wizard that would build that for you.
Oh my thank you, that is way over my head. I will keep at it and eventually I hope to understand.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:24
Joined
Sep 21, 2011
Messages
14,041
You appear to be missing a comma as shown in Plogs link.?
Look at the syntax.
 

Users who are viewing this thread

Top Bottom