problem with this code

[pungentSapling]

Does anyone see the problem with this code.... I get an error that says that the form frmUserSub does not exist......yet I assure you that it does.


Private Sub cboFindDrawing_Change()
Dim strRecSource As String
Dim strLink As String
strLink = Me.txtLinkRef.Value
strRecSource = "Select * from [tblSubForm] Where [LinkRef] = '" & strLink & "' AND [DrawingNo] = '" & cboFindDrawing.Value & "'"
Forms![frmUserSub].RecordSource = strRecSource
'Forms!frmUserSub.Refresh
End Sub




Please help

Thanks
P

 

[IfButOnly]

!

Is frmUserSub the current form, subform or parent?

If current, try Me!RecordSource = strRecordSource

If subform, try Forms!MainformName!frmUserSub.RecordSource = strRecordSource

if parent, try Parent.RecordSource = strRecordSource

Let us know if there is still a problem.

 

[pungentSapling]

frmUserSub is a subform and is not current

i tried
Forms!frmUser![frmUserSub].RecordSource=....


but now it says it can not find the FIELD frmUserSub

do I have to reference the area in the main form that contains the sub form?

 

[IfButOnly]

Is frmUser the current form?

 

[pungentSapling]

yes frmUser is the active form.....
I have a combo box in frmUser that I want to use to narrow the record source of frmUserSub. ie:Only the records related to the the drawing number selected in the cboBox should come up in the subform.


But i can not seem to reference the subform. I have checked the names .....they are fine

 

[IfButOnly]

Hang on - Subforms don't have a RecordSource field, do they? Subforms work off the record source of the main form.

I would suggest you need to reselect the record source of your main form or use a list box instead of a subform.

 

[Rich]

Base the subform on a query or the SQL, reference the combo in the criteria of the relevant field in the query, re-query the subform on the after update event of the combo

 

[pungentSapling]

Thanks for the help.... I changed the record surce to a query,
I tried to requery the from but I get the same problem. An error message tells me that the form (subForm... in this case)does not exist or that it is closed.
The form does exist. I tried making a new form.... but got the same result. I can not reference the subForm from the code in the main form.

 

[Rich]

Me.subformName.Requery

 

[pungentSapling]

here is what I have now........It almost seems to work but I get runtime error 2001 "you have cancelled the prevoius operation" message. Does anyone know what causes this error? I have searched and come up with very little.....is there a problem with my sql statement?

Thanks for the help I can now reference the subform.




Private Sub cboFindDrawing_Change()

Dim strRecSource As String
Dim strLink As String
Dim strDrawing As String
strLink = Me.txtLinkRef.Value
strDrawing = Me.cboFindDrawing.Value
strRecSource = "SELECT * FROM [tblSubform] WHERE [LinkRef]='" & strLink & "' AND [DrawingNo]= '" & strDrawing & "'"

MsgBox strRecSource
Me.chfrmUserSub.Form.RecordSource = strRecSource
End Sub

 

[pungentSapling]

ok I have the subform so that it only shows the records that are related to the number selected in the combo box in the main form.....and you can change the number in the combo box and it requeries nicely..........problem is that when the form loads there is nothing in the combo box....hence no sub records in the subform.. I need it to show all of the subrecords if nothing is selected in the combo box on the main form. But since I can not change the recordsource for the sub form I can not figure out how to do this. Any Help?

 

[pungentSapling]

I am having absolutely no luck with this.......
Maybe I need to start from the beginning again.



How do I change the recordSource of a subform...it has to be possible.

I will make two queries... one with no criteria one with a single criteria(the value inside the cboBox on my main form)
I want to toggle the record source of the sub from between these two queries based on if the cbo box is holding a value or is null.


thanks
sorry I am so thick.

 

[WayneRyan]

Forms![MainForm]![SubForm].RecordSource = "qryTest"

hth,
Wayne

 

[pungentSapling]

i have resolved the problem.... thanks for all of your input.

for anyone interested:


Private Sub cboFindDrawing_Change()

Dim strRecSource As String
Dim strLink As String
Dim strDrawing As String
strLink = Me.txtLinkRef.Value
strDrawing = Me.cboFindDrawing.Value

If IsNull(Me.cboFindDrawing.Value) Then
strRecSource = "tblSubForm"
Else
strRecSource = "SELECT * FROM [tblSubform] WHERE [DrawingNo]= '" & strDrawing & "'"
End If


MsgBox strRecSource

Forms![frmUser]![chfrmUserSub].Form.RecordSource = strRecSource
Me.chfrmUserSub.Form.Requery

End Sub



something odd was happening when I included the linkRef in the sql....linkRef is the link field between the parent and child forms.... Once I got rid of that it worked fine.