Refer to a Form

[BBryan]

Hi,
Could you please help me.
I was looking to use a combo box to filter on a sub form.
The form I have is called - DrawingNo(AddDrawingMultiLines) fm. (I know now I should not have used parentheses and a space in my name and I didn't want to change my form name because I would have to do a lot of changing).
The name of the form is not recognized. Is there a way to Refer to this form with the parentheses and space.
for this ---- Form_DrawingNo(AddDrawingMultiLines) fm.RecordSource = LSQL

The Compile error comes up - method or data member not found
I have tried
Form_[DrawingNo(AddDrawingMultiLines) fm].RecordSource
and "Form_[DrawingNo(AddDrawingMultiLines) fm].RecordSource"
and Form_"DrawingNo(AddDrawingMultiLines) fm".RecordSource.
I can't figure out how to write this.

This is the code





_______________
Private Sub cboSelected_AfterUpdate()
'Call subroutine to set filter based on selected DrawingNoID
SetFilter
End Sub
______________________________________
Sub SetFilter()

Dim LSQL As String
LSQL = "select * from DrawingNo tbl"
LSQL = LSQL & " where DrawingNoID = '" & cboSelected & "'"
Form_DrawingNo(AddDrawingMultiLines) fm.RecordSource = LSQL
End Sub

 

[JamesMcS]

It would be

forms![Form_DrawingNo(AddDrawingMultilines) fm]

But I've got to say that's a terrible naming convention, I can see all sorts of problems coming up in the future. It would definitely be worth spending a day sorting it out.

 

[boblarson]

You refer to subforms in this way:

Forms!YourMainFormName.YourSubFormControl.Form.Recordsource = "xxxxx"

The YourSubformControl part is the name of the control on the main form which HOUSES the subform. It is NOT the name of the subform itself (UNLESS the subform control and the subform are named the exact same thing, which is okay). If the subform is named differently from the subform control you MUST use the subform control name and not the subform name.

The other part - .FORM. has to remain EXACTLY as shown as that tells Access that you want a property, method, or event from the subform itself and not the subform control.

If the code is on the main form then you don't need the full form reference you can use

Me.YourSubformControl.Form.Recordsource = "xxxx"

See here for more about subforms.

 

[JamesMcS]

Whoops failure to read post properly=wrong advice given... dummy.... sorry all!

 

[boblarson]

Whoops failure to read post properly=wrong advice given... dummy.... sorry all!

Don't worry about it. It happens to me all of the time

Oh, and BBryan -

You really should NOT use special characters in your object or field names. You should not have ( ) in there or anything other than an underscore _

You should also try not using spaces in the names as that will simplify matters for you immensely.

 

[JamesMcS]

I knew the right answer and everything... back of the class for me...

But still, bank holiday weekend! First days off in 3 weeks for me, can't wait. Have an excellent one all!

 

[boblarson]

But still, bank holiday weekend! First days off in 3 weeks for me, can't wait. Have an excellent one all!

You too! I'm looking forward to it as well (I still have about 6 hours to go until mine starts) as we are taking a mini-vacation (the wife and me -withOUT the kids for a change).

 

[JamesMcS]

Ah, we have yet to have that decision to make... just plain old lie ins and barbecues for us.. counting the seconds.... Enjoy it then! Weather is going to be nice Sunday and Monday round our parts, hope the same at least for you!

 

[BBryan]

Thanks for your reply.
(I changed all my forms names To somthing better - I see now that it was bad naming)

I still think I am doing something wrong

My Form is 3 Levels Deep - On the 2nd level I have the ComboBox to filter for the 3rd Level

1st level Form - ProjectDSfm
2nd level Form - JobNoDSfm
3rd level Form - DrawingNoDSfm

I changed to

Sub SetFilter()
Dim LSQL As String

LSQL = "select * from DrawingNo tbl"
LSQL = LSQL & " where DrawingNoID = '" & cboSelected & "'"

Forms![ProjectDSfm]![JobNoDSfm]![DrawingNoDSfm].Form.RecordSource = LSQL

End Sub

I now am getting a run time error 2580--
the record source 'Select * from Drawing tbl where DrawingNoID = '25" specified on this form or report does not exist.

 

[boblarson]

Okay, the form reference for the 3rd level from the 2nd level would be (if the names you gave are the subform ontrol names) if the code is on the 2nd level subform:

LSQL = "select * from DrawingNo tbl"
LSQL = LSQL & " where DrawingNoID = '" & Me.cboSelected & "'"

Me.JobNoDSfm.Form.Recordsource = LSQL

 

[BBryan]

Hi Bob,
I have Rechecked to see if I had the subform Control names right by the clicking on the boarder (On this 2nd level I have JobNoDSfm) and code is coming from the Combo Box I have on second level

I Copied the new code from your reply and it still gives me a error - Complile error Data or member Not found

LSQL = "select * from DrawingNo tbl"
LSQL = LSQL & " where DrawingNoID = '" & Me.cboSelected & "'"

Me.JobNoDSfm.Form.Recordsource = LSQL

 

[boblarson]

Hi Bob,
I have Rechecked to see if I had the subform Control names right by the clicking on the boarder (On this 2nd level I have JobNoDSfm) and code is coming from the Combo Box I have on second level

I Copied the new code from your reply and it still gives me a error - Complile error Data or member Not found

LSQL = "select * from DrawingNo tbl"
LSQL = LSQL & " where DrawingNoID = '" & Me.cboSelected & "'"

Me.JobNoDSfm.Form.Recordsource = LSQL

Can you post a copy of your database (with any sensitive data replaced with bogus test data)? It might clear things up a bit quicker.

 

[BBryan]

Sure. It is 17.5 MB. I am now sure how to send it

 

[boblarson]

Okay, first of all.

See how far down it goes when you run Compact and Repair and then ZIP the file (if you are on WinXP or higher you can right click on the file and select SEND TO > COMPRESSED FOLDER). And then see how big it is. But you really do need to run Compact and Repair first.

 

[BBryan]

Here is the database

 

[BBryan]

Hi Bob,
I just looked I don't know if I sent the dtatbase.
I went to Go advanced and manage attachments
upload from your computor
then close window and send. is that right.

 

[boblarson]

Hi Bob,
I just looked I don't know if I sent the dtatbase.
I went to Go advanced and manage attachments
upload from your computor
then close window and send. is that right.

What size was the file after running compact/repair and then zipping? It would need to be 2Mb or less to upload.

And then to upload it is:

Manage Attachments
Select the file to upload using Browse Button
and then Click the UPLOAD link
Then close the window. If it uploaded properly before you close the dialog there should be a link that appears just under the places where you select/type the file paths.

 

[BBryan]

Hi Bob,
The file was 3.10 MB after compacing and ziping it down.
It is the smallest I could get it.
Is there any other way to send at this size?

 

[boblarson]

You can try emailing it to me, but I might not get to it right away. I can't get my email at work and I am going to be unavailable most of the weekend (holiday). But I can try to get to it. I'll PM you with an email address you can use.