error on access sub form - record doesn't exist?

[matthardwick]

Hi Everyone,

I have a problem with Access that I can't seem to solve:

I have a sub form recordset changing acording to the value of a drop down box.



I tried this code:

Private Sub comboPatronTypeSelect_Change()

    If Me.comboPatronTypeSelect.Value = "" Or Me.comboPatronTypeSelect.Value = Null Then
        ''''''''''''''''''''' do nothing ''''''''''''''''
    Else
        Me.lblSelectPatronType.Visible = False
    End If
    
    Debug.Print "patronType value: " & Me.comboPatronTypeSelect.Value
    
    'setup the variables.
    Dim strLicenceID_FK, strPatronType As String
    strLicenceID_FK = Me.licenceID.Value
    strPatronType = Me.comboPatronTypeSelect.Value
    
    ' setup the select for this patron type
    Dim strSQL As String
        strSQL = "SELECT * FROM ssel_web_erm_tblLicencePermissions WHERE licenceID_FK = '" & strLicenceID_FK & _
                "' AND patronType = '" & strPatronType & "'"
                Debug.Print strSQL
        
    Form_frmLicencePermissions_sub.RecordSource = strSQL

End Sub

But when I goto tick one of the boxes in the subform I get the error:



and then this error when I try to save the record or move out of that form etc.



Is it because the recordset has no records in it? It will only ever have a maximum of one records, but obviously if nothing has been set - then no record will exist (empty recordset).

Can anyone help?

Thanks

[Stewart Ross]

Hi. I suspect that your error is because you are trying to refer to the subform directly to set its recordsource property, but the subform is not independent, as it is attached to your mainform as a control. This changes how its recordsource property is accessed, as the subform is otherwise treated as a control. To access its recordsource you need to use the form property in addition to the subform's name.

There are several ways to refer to a subform on a main form. From code on the main form, the simplest is to use the me property and add the Form property on to the control name:

Me.frmLicencePermissions_sub.Form.RecordSource = strSQL

-Stewart

[matthardwick]

Hi. I suspect that your error is because you are trying to refer to the subform directly to set its recordsource property, but the subform is not independent, as it is attached to your mainform as a control. This changes how its recordsource property is accessed, as the subform is otherwise treated as a control. To access its recordsource you need to use the form property in addition to the subform's name.

There are several ways to refer to a subform on a main form. From code on the main form, the simplest is to use the me property and add the Form property on to the control name:

Me.frmLicencePermissions_sub.Form.RecordSource = strSQL

-Stewart

That didn't work, well it worked - but I still got the exact same error. :(

[Stewart Ross]

Sorry, Matt - I can see no other problem. I am assuming that your project references are set correctly.

Yours is a similar problem to that experienced by another poster using Access 2007 (which I guess you are), but I can't find any mention of this specific problem in the MS Knowledge base.

As a test, I would suggest using a generic main form/sub form combination and setting the subform's recordsource in code from the main form. If this fails too there is something generic wrong - a setup issue or somesuch. If it works, your original code is incorrect somehow.

-Stewart

ps - is there any chance that this is a Vista issue relating to the running of VBA code - I use XP myself so am not familiar with the environment. I'm referring to the account restrictions on Vista which ordinarily prevent software installations unless you lift those restrictions - I did see some reference on the MS knowledge base to circumstances where the VBA project library links were not updated as a result of the restrictions.

[missinglinq]

I don't think this has a bearing on your problem, but this kind of code always needs to be in the AfterUpdate event for the combobox rather than in the OnChange event. If the user goes to use the AutoExpand feature, by starting to type in a selection, the OnChange event fires when the first character is entered, before an item has actually been selected.

Linq ;0)>