VBA enounted a problem while attempting to access a property or method...blah blah...

[Groundrush]

My db works well, it has been compressed/repaired & the code complied without any problems until I convert it to an Mde

The problem I have is that for some reason three of my sub forms will not function properly without getting this on click warning

The expression on Click you entered as the event property setting produced the following error:The expression you entered refers to an object that is closed or doesn't exist.

Visual Basic for Applications (VBA) encountered a problem while attempting to access a property or method. The problem may be one of the following:
A reference is missing.
For help restoring missing references, see the Microsoft Knowledge Base article 283806.
An Expression is misspelled.
Check all expressions used in event properties for correct spelling.
A user-defined function is declared as a sub or as a private function in a module.
Expressions can resolve a user-defined function only if the function is declared as one of the following:
- A public function in a module
- A public or private function in a code module of the current form or report
Security in Access is set to Medium or High and the Microsoft Jet 4.0 SP8 update is not installed.
A more recent verion of Jet 4.0 must be installed for Access to function properly when security is set to Medium or High. To obtain the latest version of Microsoft Jet, go to Windows Update.

I don't understand why there is a problem if it works fine without converting it to a Mde.

The 3 subforms do have one thing in common though

they have a cmd button that moves the focus to another object on the form & then the subforms visibility is set to false.

[Private Sub CmdClose_Click()

 On Error GoTo 0 'Err_cmdClose_Click
 If DCount("*", "tblTaskScheduleRates", "[SRLINKTask] = " & Me.Parent.TaskID) = 0 Then
        Me.Parent.frmFlashRates.Visible = True
        Me.Parent.cmdViewRates.Visible = True
        
        End If
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Parent.cmdSaveRecord.SetFocus
    Me.Parent.frmTaskScheduleRatesSubForm.Visible = False
    Me.Parent.frmTaskScheduleRatesViewSubForm!lstAssigned.Requery
    Me.Parent!lstRevisedCost.Visible = True
   
        
Exit_cmdClose_Click:
    Exit Sub

Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click
       

End Sub

Private Sub CmdClose_Click()
Me.Parent.cmdAssignTask.SetFocus
Me.Parent.frmTimeRecordAssignSubForm.Visible = False
Me.Parent.frmTaskMessageViewSubForm!lstMessages.Requery
Me.Parent!frmSumTimeRecordsSubForm!lstAssigned.Requery
Me.Parent!lstRevisedCost.Visible = True

If DCount("*", "qrySubbyFlash", "[TaskID] = " & Me.Parent!TaskID) > 0 Then
Me.Parent!cboTaskStatus = Me.Parent!cboTaskStatus.ItemData(1)
Else
Me.Parent!cboTaskStatus = Me.Parent!cboTaskStatus.ItemData(0)
End If

If DCount("*", "qryOperativeFlash", "[TTLINKTask] = " & Me.Parent!TaskID) > 0 Then
Me.Parent!cboTaskStatus = Me.Parent!cboTaskStatus.ItemData(1)
End If


End Sub

Private Sub cmdSave_Click()

If DCount("*", "tblTaskMessage", "[TMLINKTask] = " & Me.Parent.TaskID) = 0 Then
        Me.Parent.frmFlashMessage.Visible = True
        Me.Parent.cmdViewMessages.Visible = True
 End If
 Me.Parent.cmdNotes.SetFocus
 Me.Parent.frmTaskMessageSubForm.Visible = False
 Me.Parent.frmTaskMessageViewSubForm!lstMessages.Requery
 Me.Parent!lstRevisedCost.Visible = True
       
End Sub

it works just fine as a normal database but for some reason it causes problems as an Mde.

any ideas?

thanks

 

[RoyVidar]

I'll start with saying that I don't know much about mdes, and the following comments are "general", and can probably pass as "any idea"

What I usually look at first when encountering this error, is form corruption. Do you know which click event it is reacting on?

In this case, however, I'd take a look at your form/control referencing first.

1 - You are mixing dot/bang syntax - I don't know whether that might cause any problems - both of the following are toggling the visible property of a control on the parent form, why are they different, in one case you use bang, in the other dot

Me.Parent.frmTaskScheduleRatesSubForm.Visible = False *)
Me.Parent!lstRevisedCost.Visible = True

Here you're mixing in one reference

Me.Parent.frmTaskScheduleRatesViewSubForm!lstAssigned.Requery *)

*) see #2 too

2 - You're using incorrect referencing of subform properties. For instance

Me.Parent.frmFlashRates.Visible = True

should contain the Form keyword - for instance like this

Me.Parent!frmFlashRates.Form.Visible = True

Some say this is only for form properties and methods, but I will include the Form keyword for all subform/subreport referencing.

See for instance http://support.microsoft.com/?kbid=209099 or http://www.mvps.org/access/forms/frm0031.htm for more info

If it's corruption, try /decompile - use the step by step approach here http://www.granite.ab.ca/access/decompile.htm, note only that between 2 and 3, close all instances of Access before compacting (ensure you open without running any code)

 

[Groundrush]

Thank you for your reply

I have now tidyied up my code, re compacted & repaired & even exported everything into a new db

tested it as a Mdb & it works just fine as before but still throws up the error messages after converting it to an Mde

Normally an Mde can not be created if there are errors in the code, only after compiling & correcting errors you are then able to convert.

I have been able to convert without any error messages, but just find that using it as an Mde it throws up errors

I'm using 2003 by the way.

 

[Pauldohert]

Is it happening on particluar lines - or doesn't it like the sub in general.

If its a line what line(s) is it???

 

[Groundrush]

OK, after a bit of backtracking I was able to work out that my problem had something to do with my attempt at improving the form performance by removing all the source object details from the sub forms

The idea was that it only fires up the queries behind the sub forms when I need them rather than all of them firing up when the form is first opened

for example

I have a command button that uses this code below

Me.frmInvoiceTotalSubForm.SourceObject = "frmInvoiceTotalSubForm"
Me.Form!frmInvoiceTotalSubForm.Visible = True

The code works well without any problems until I convert it to an Mde

 

[Pauldohert]

I think that may count as changing the design - which you cannot do in an mde.

Try changing it so it just loads the recordsource when needed - this will help with the performance , but should be allowed with an mde.

 

[Groundrush]

I think that may count as changing the design - which you cannot do in an mde.

Try changing it so it just loads the recordsource when needed - this will help with the performance , but should be allowed with an mde.

That's what the above code is supposed to do

but it only works witohut a glitch in a mdb.

unless there is another way of doing it.

 

[boblarson]

Me.frmInvoiceTotalSubForm.SourceObject = "frmInvoiceTotalSubForm"

is changing the design as you are setting the sourceobject. Just set the subform's recordset to nothing at first and the set the RECORDSOURCE and requery when you want to load the subform.

 

[Groundrush]

Me.frmInvoiceTotalSubForm.SourceObject = "frmInvoiceTotalSubForm"

is changing the design as you are setting the sourceobject. Just set the subform's recordset to nothing at first and the set the RECORDSOURCE and requery when you want to load the subform.

Sorry Bob but I have no idea what you mean by setting the recordset of the subform to nothing

and setting the recordsource when loading the subform

I only get the option to clear the SourceObject like in my example above.

 

[boblarson]

 

[Groundrush]

Bob,

Thank you very much for your detailed explanation, I'm going to enjoy trying out your suggestion on my database

I take it that this method will also increase the form loading speed when used on an say a LAN network or similar & only requery when I need it to?

 

[boblarson]

I take it that this method will also increase the form loading speed when used on an say a LAN network or similar & only requery when I need it to?

Yes, it will for the initial load. It definitely helps the user's perception when they load initially as it would usually take much longer for the initial display. But, when they go to load the subform it will probably need some getting used to it not loading quite as fast as it would if it were loaded initially. It's a bit of a trade-off, but generally it is better to only pull the stuff you need when you need it.

 

[Pauldohert]

I think that may count as changing the design - which you cannot do in an mde.

Try changing it so it just loads the recordsource when needed - this will help with the performance , but should be allowed with an mde.

Thanks I'll do that!

 

[Groundrush]

Bob,

I looked & tried out your suggestion but found a few different things about my sub forms that are not relevant to yours which is causing a few problems

One of them being that some of them do not have a query or table in the record source but the list boxes within them have qrys embedded in their row source instead.

SELECT qryInvoiceTotal3.TaskID, qryInvoiceTotal3.ConPrefix, qryInvoiceTotal3.JobNo, qryInvoiceTotal3.[Date Issued], qryInvoiceTotal3.ResName, qryInvoiceTotal3.MINo, Format([TotalCost2],"Currency") AS TotalCost3
FROM qryInvoiceTotal3
WHERE (((qryInvoiceTotal3.TaskID)=[forms]![frmCallLoggingEdit]![txtTaskID]))
ORDER BY qryInvoiceTotal3.[Date Issued];

Also the sub forms that do have tables as the record source are only to be used as data entry sub forms & not to be used to view records with so they may not require any change.

My first test run with the sub forms that have list boxes have had the name of the subform in the source object left in place

Private Sub cmdInvoiceCosts_Click()

Dim strSQL As String

Me.frmInvoiceTotalSubForm.Form.RecordSource = strSQL
Me.frmInvoiceTotalSubForm.Form.Requery
Me.frmInvoiceTotalSubForm.Visible = True
Me.frmMaterialViewSubForm.Visible = False
Me.frmSubbyAssignedSubForm.Visible = False
Me.frmTimeRecordAssignSubForm.Visible = False
Me.frmSumTimeRecordsSubForm.Visible = False
Me.frmTaskMessageSubForm.Visible = False
Me.frmTaskMessageViewSubForm.Visible = False
Me.frmSchedRSubForm.Visible = False
Me.frmSchedRSubForm.Visible = False

End Sub

The sub form does open ok but I have no idea if the qry behind the Listbox is firing up on the Form load or when I click on the cmd button.

 

[Pauldohert]

The sub form does open ok but I have no idea if the qry behind the Listbox is firing up on the Form load or when I click on the cmd button.

Nevermind

 

[Groundrush]

Nevermind

I need to improve the form opening speed & I don't think I'n going to achieve this with the current suggestions

my sub forms are not the same.

 

[Pauldohert]

The data can be loaded when needed whther its contained in a subform as the recordsource or a list on the subform as the rowsource.


When you open/makevisible the subform you want to do either

1) Set the recordsource of the form


Me.frmInvoiceTotalSubForm.Form.RecordSource = strSQL

2) Set the rowsource of the listbox

Me.frmInvoiceTotalSubForm.Form!Lisboxname = strSQL

(synatx here may be off)

Youi have to set the strSQL to what is currently in the rowsource or recordsource in the properties of the form/listbox leaving the rowsource/recordsource in the properties of the listbox/form totally blank.


The SourceObject should never be referenced or changed. -

You should be able to tell if you code is working - 1) put a meaggebox after each line Msgbox "Me.frmInvoiceTotalSubForm.Form!Lisboxname = strSQL has jst run" etc


If any data appeers it shoulkd be working - beacuse the rowsource or recordsource would be balnk until the code kicks in.

 

[Groundrush]

Thanks, will have another look