Subform / Query problem.. driving my crazy

[spacemunkee]

Hi all first post

I have a table which contains technical information about subcontractors, including their performance (good,bad etc) which is chosen using a combo box when adding new records.

I have decided to create a search form that contains a combo box with the same performance values within so that the user can basically search through subcontractors which have the same performance level.

In the search form i have placed the combo box with the name cboSearch
Row source: SELECT [tblSubcontractors].[Performance] FROM [tblSubcontractors] GROUP BY [Performance]

The on change command uses the following event:

Private Sub cboSearch_Change()
DoCmd.OpenQuery "qryPerformance"
Me.cbosearch = ""
End Sub

I have produced a query (qryPerformance) which contains all of the field from the tblSubcontractors with the criteria [Forms]![frmPerformance]![cboSearch]

I then tested the query from the search form using the combo box which produced the desired results in a datasheet. I have used the sub form wizard to place the qryPerformance in to the search form. However the problem that i am having is when i open the search form and then select a value from the combo box the results pop up as a data sheet (in the background, not in the sub form) as they did before and the subform is left unupdated.

I cant see what im missing but im sure its going to be obvious.. i just want the results of the query to be as a subform in the search form, which i can then browse through.


Thanks for any help

 

[Dennisk]

you should use DOCMD.openquery to run action queries only. However did you change your query to a select query for testing?

 

[spacemunkee]

it is currently running as a select query with all the code as i pasted in first post .. my knowledge is very basic as i have only been using access for just over a week

 

[spacemunkee]

Ive spent a good 8 hours on this today to no avail i have even tried to reproduce the same effect using various other methods such as without using any querys just form properties, but these would only link to one result (eventhough i know there are 2 or 3 with the same performance level)

Anyone have any ideas?

 

[Dennisk]

what do you want the query qryPerformance to do for you. do you want that query as the recordsource for a form?

 

[spacemunkee]

yes, im currently using the query to take the combo box value and to use it to find exact matches in tblSubcontractors. The query runs fine and opens up the correct records as a data sheet, but i want these to be visible in a sub form (single form view) on the same form as the combo box. Then i was going to add controls allowing the user to click through the results obtained. Its just i cant get the query to reproduce the results as a sub form, they just keep popping up as a data sheet within access in the background.

 

[Wiz47]

yes, im currently using the query to take the combo box value and to use it to find exact matches in tblSubcontractors. The query runs fine and opens up the correct records as a data sheet, but i want these to be visible in a sub form (single form view) on the same form as the combo box. Then i was going to add controls allowing the user to click through the results obtained. Its just i cant get the query to reproduce the results as a sub form, they just keep popping up as a data sheet within access in the background.

Are you using ...

Me.YourSubformName.Requery

That should put the data in the subform on the form.

 

[boblarson]

Actually, Wiz47, remember that to refer to a subform requires a bit different syntax:

Forms!YourMainFormNameHere.YourSubformCONTAINERnameHere.Form.Requery

Remember to refer to the subform CONTAINER name on the main form. If you placed it via the wizard, it will be the same name as your subform, but if you do it manually it will not be the same (it will be something like ActiveXCtl3) and you must rename the control, or refer to it's actual name in the code.

And to refer to a control on a subform use:

Forms!YourMainFormNameHere.YourSubformCONTAINERnameHere.Form.YourControlNameHere

 

[Wiz47]

Actually, Wiz47, remember that to refer to a subform requires a bit different syntax:

Forms!YourMainFormNameHere.YourSubformCONTAINERnameHere.Form.Requery

Remember to refer to the subform CONTAINER name on the main form. If you placed it via the wizard, it will be the same name as your subform, but if you do it manually it will not be the same (it will be something like ActiveXCtl3) and you must rename the control, or refer to it's actual name in the code.

And to refer to a control on a subform use:

Forms!YourMainFormNameHere.YourSubformCONTAINERnameHere.Form.YourControlNameHere

That's true Bob, the great rule of referencing forms.subform.subform of subform

 

[spacemunkee]

I think i have solved it

The query has now been deleted and i am using the following:

In my combo box -
SELECT [tblSubcontractors].[Performance] FROM [tblSubcontractors] GROUP BY [Performance]

In the after update code-
Private Sub Combo61_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Performance] = '" & Me![Combo61] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Sub form -

Very long winded but..
SELECT [tblSubcontractors].[Project ID], [tblSubcontractors].[Subcontractor], [tblSubcontractors].[Type of work], [tblSubcontractors].[Cost], [tblTechnicalInfo].[Start Date], [tblSubcontractors].[Duration (Days)], [tblSubcontractors].[Performance], [tblSubcontractors].[Notes], [tblSubcontractors].[End Date] FROM [tblSubcontractors]

It seems to work though. Thanks for the help Just need to start working on the controls.