Open a from from sub form (1 Viewer)

[Teri Bridges]

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]

Is the main form set to Data Entry = Yes? Just checking...

 

[plog]

Is the text 'WhereCondition:" actualy in the DoCmd.OpenForm call? It shouldn't be:

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

 

[Teri Bridges]

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]

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]

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

 

[The_Doc_Man]

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]

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]

You appear to be missing a comma as shown in Plogs link.?
Look at the syntax.