Select a record to display in another form

[noboffinme]

Hi

I have a db with 3 forms, main, project (a subform of main), & search (which has search results of the 'project' forms records).

I need to click on the 'tick' cmd button on the search form & select that record from the project form (which sits on the main form).

I've attached where I am so far but I can't display the record I've clicked on the search form to display on the project form, can anyone assist?

Also, could you please comment each step.

Thanks

 

[vbaInet]

This should be what you're after:

http://baldyweb.com/wherecondition.htm

 

[noboffinme]

Thanks vbaInet

I've changed that line but am getting an error with not finding the frm_project form.
-----------------------------------
Private Sub cmd_Select_Click()

Dim Request_ID As Integer
Dim rs As DAO.Recordset

Request_ID = Me.Request_ID

Set rs = Me.RecordsetClone

rs.FindFirst "Request_ID=" & Forms![frm_project]![Request_ID]

DoCmd.OpenForm "frm_project", , , Request_ID = " & me.frm_search"

Forms!frm_search.Bookmark = rs.Bookmark

rs.Close

Set rs = Nothing

End Sub
------------------------------
Not sure where I'm going wrong...

 

[vbaInet]

Completely scrap the recordset steps and just use the WHERE argument in the OpenForm method. What that code does is find a record and make it the current record when the form loads. Something like this:

Private Sub cmd_Select_Click()

    DoCmd.OpenForm "frm_project", , , "Request_ID = " &  Val(Me.Request_ID)

End Sub

If the return value of Me.Request_ID is of type Number then the above should work. If it's a string then that line should be:

DoCmd.OpenForm "frm_project", , , "Request_ID = [COLOR=Red][B]'[/B][/COLOR]" & Me.Request_ID & "[COLOR=Red][B]'[/B][/COLOR]"

Notice I removed the quotes used to wrap strings.

 

[noboffinme]

OK, so now the code reads as follows without the recordset lines;

----------------------
Private Sub cmd_Select_Click()
Dim Request_ID As Integer
Request_ID = Me.Request_ID
DoCmd.OpenForm "frm_project", , , "Request_ID = " & Val(Me.Request_ID)

End Sub

----------------------------

Val(Me.Request_ID) now picks up the value of the Request_ID but this isn't passed on to the frm_project form so the form opens but not to the correct record.

As I have the line "Request_ID = Me.Request_ID" & "Me.Request_ID" is picking up the frm_search Request_ID value, why doesn't the frm_project open to this record?

There is no error when this runs, just no record brought up on the frm_project form.

 

[vbaInet]

Does the field Request_ID exists in the record source of frm_project?

Also, I've noticed you've added the variable again? Are you using it later on in that sub?

 

[noboffinme]

Yes the frm_project form has a Request_ID field & I've now commented out the Request_ID = Me.Request_ID line, still no record brought up on the frm_project form?

 

[vbaInet]

I was just asking about the variable because when you're code is being run then it must read those two lines. Redundant really.

Let's see your db.

 

[noboffinme]

Hi vbaInet

I posted a stripped down version on the first post & when I use your answer in this db it works fine.

I think I need to look at why this isn't working in the full version (ie; what different), so thanks for your help & patience. : )

 

[vbaInet]

Could be something in the query. Good luck finding it.

You're welcome.

 

[noboffinme]

Here's the working example in case anyone needs it. Cheers

 

[vbaInet]

Thanks for posting back with your working solution.

 

[noboffinme]

Hi

I have one more part to this question that I'm stuck with.

Here's the code for the 'tick' control on the 'frm_search' form

The other 2 forms, 'frm_main' & 'frm_project' are setup as the 'frm_main' with the subform 'frm_project'.

I want the selected (tick) control on the 'frm_search' to open the 'frm_main' form at the selected 'Request_ID' on the 'frm_project' subform.

I have tried to complete this part as I am able to get the 'frm_project' form to go to the selected record thanks to vbaInet but I can't correctly refer to the 'Request_ID' control from the search form.

The DB is attached, thanks
-------------------------------
Private Sub cmd_Select_Click()
Dim Request_ID As Integer

'The below is the original code that brings up the correct record on the 'frm_projects' form
DoCmd.OpenForm "frm_project", , , "Request_ID = " & Val(Me.Request_ID)
'I intend to delete this above line once the below code is working

'I need to have the selected (selected by clicking the tick symbol on the search from) record open on the 'frm_main' form
'which holds the 'frm_project' form - I've tried the below to do this but there is an error in the syntax
'I've read several ways of referring to a control on a subform & tried many with no luck

'What's wrong with this reference to the 'Request_ID' control on the 'frm_project' subform?
'I would like to click on a tick on the 'frm_search' form & have that record open on the 'frm_main' form
'that holds the 'frm_project' subform

DoCmd OpenForm "frm_main" ,,, Forms!frm_main.frm_project.Request_ID = "Request_ID = " & Val(Me.Request_ID)

End Sub

 

[vbaInet]

There are a number of ways to refer to a control within a child container or a parent container. Here's a good link re this:

http://www.mvps.org/access/forms/frm0031.htm

You're opening frm_Main, not the subform. The WHERE argument of the OpenForm method affects the recordsource of the form you're opening, in your case frm_Main. So what you're trying to do doesn't follow.

 

[noboffinme]

Thanks

I've seen that form & what I've got from it & other sites is that to reference a subforms control, use Forms! first, the Form you want to Open second, the container subforms name that the control in held in third & then the control name, hence;

DoCmd OpenForm "frm_main" ,,, Forms!frm_main.frm_project.Request_ID = " & Val(Me.Request_ID)

OR

DoCmd OpenForm "frm_main" ,,, Me.frm_project.Form!Request_ID = " & Val(Me.Request_ID)

I have a syntax error in these but I'm not sure where.

 

[vbaInet]

I think you're missing my point.

Let me reiterate from my last post:

You're opening frm_Main, not the subform. The WHERE argument of the OpenForm method affects the recordsource of the form you're opening, in your case frm_Main. So what you're trying to do doesn't follow. or wouldn't work.

DoCmd OpenForm "frm_main" ,,, Forms!frm_main.frm_project.Request_ID = " & Val(Me.Request_ID)

Have a look at this (especially the second page that follows):

http://baldyweb.com/BuildSQL.htm

 

[noboffinme]

Hi

I'd like to know if anyone has the actual answer to selecting a record from a form on a subform that sits within another form.

Does anyone have the actual code answer to this? Pls check the last example db I've posted - I need to click on the 'tick' symbol of the 'frm_search' & have that record open on the 'frm_project' subform which sits as a subform on the 'frm_main' subform.

Appreciate the help so far, but links to other info is not helping, I would be able to work back from the answer better than trying to guess where I'm going wrong from other sites examples. Thanks