Test For Valid Record

gray

Registered User.
Local time
Today, 14:22
Joined
Mar 19, 2007
Messages
578
Hi

Access2002/2007 .mdb
WinXpPro SP3

I know this is a popular question because I've read quite a lot on it but each time I think I've cracked it yet another failure comes along.

I run the following tests in the Current Event of a Main Form (in this case after a filter has been applied) to make sure there is a valid record in both main and subforms......Forms are DAO based.
Code:
With mySubform                
           If Not Me.Form.Recordset Is Nothing Then
                    If Not Me.Form.Recordset.EOF Then
                        If Me.Form.Recordset.RecordCount > 0 Then
                            If Not IsNull(Me.Form.Recordset![ID]) Then
                        
                                If Not .Form.Recordset Is Nothing Then
                                    If Not .Form.Recordset.EOF Then
                                        If .Form.Recordset.RecordCount > 0 Then
                                            If Not IsNull(.Form.Recordset![ID]) Then
                        
                                                If Me.Form.Recordset![ID] <> .Form.Recordset![ID] Then

The above now fails at the last line with "Object Invalid or No Longer Set"... short of testing the outside air temperature, last year's total rainfall on the Spanish Plain and if there is an 'R' in the month what else do I need to make this reliable please??

Thanks
 
Not sure what you are trying to do. But there is too much code.

If Not IsNull(Me.Form.Recordset![ID]) Then

If there is an ID then the Recordset is not nothing, this is more than zero records and if it is the only record it is both at BOF and EOF.

Please explain in plain english what you need to gain by this code.
 
Hi -thanks for the reply OZ!

Yes, there is a fair bit of code for what must be a very common situation. This has been a pain in my side since I discovered Access!

There are one or two factors to consider.

1. The RS's are attached to the Forms/Subforms at Form_Open using aseparate workspaces. This is so they can be wrapped in SQL Transactions later.
2. Because of the above, Master/Child links can't be used so the subforms are 're-opened' by the main form current event.. a WHERE statement in the subform recordsource 'filters' for the appropriate new main form ID
3. Genuine 'filters' (of the right_click variiety) can be added to main or subforms. Obviously these can return zero records.
4. As might be expected, there are many references made by the main and subforms to one another.

So... particularly during testing, I have had situations where there may not be an RS at all attached to a form or it may have zero records or the value of the field I want to test might be NULL (... I know there's a whole debate on NULLs :-) ...) .. or the form.recordsource object or the ME! object is not present... or a subform has been unloaded because there are zero records. Sometimes these situations might be legitimate...(e.g. during the handling of filters).... sometimes they are not.

I'm trying find a legitimate test to make sure a reference to a value on another form is 'legal' before I make it.... so.... to make sure the target form is loaded, it has a current RS, there are more then zero records in its RS and to see if a field is NULL before attempting to use it blows something up.

I've seen some really convoluted ideas for this... including one that tests the type of variable returned from a check of the recordset to see if it's numeric.... sadly even that failed. Another way was to pull the SQL from the form being inspected, run it in a standalone RS, check its result before proceeding...

I know some of you clever gents out there have a much easier way than anything I've tried? :-)

Thanks
 
Last edited:
1. The RS's are attached to the Forms/Subforms at Form_Open using aseparate workspaces. This is so they can be wrapped in SQL Transactions later.
Is this plain English. :confused: :banghead:
 
Hi

Sorry OZ... sometimes such concepts are not easily explained using the written word...
1. The RS's are attached to the Forms/Subforms at Form_Open using aseparate workspaces. This is so they can be wrapped in SQL Transactions later.

Let me expand a little on this.

As I understand it, opening a form with an SQL statement in the form's recordsource property will fire up that form using a default DAO recordset and run with it in the default Access workspace. If you were to use SQL Transactions (beginTrans/CommitTrans/RollbackTrans) with a form of this nature, ALL wrires in that default workspace would be affected. This is a bad thing.

To make it a better thing, the trick is to build a new workspace (per form in my case) create a new recordset(s) in the new workspace(s) and then bind those to the form. Something along these lines:-
Code:
Public Sub Form_Open(Cancel As Integer)
    Dim SQLLine As String
    Dim Extg_DbName As String
    
    SQLLine = "SELECT * FROM Orders"
               
    Extg_DbName = CurrentDb.Properties(0).Value
    Set Me.Parent.Frm_WrkDAO = CreateWorkspace("Cstmrs_Dtls_Frm_Workspace", "admin", "", dbUseJet)
    Workspaces.Append Me.Parent.Frm_WrkDAO
    Set Me.Parent.Frm_DbsDAO = Me.Parent.Frm_WrkDAO.OpenDatabase(Extg_DbName)
 
   If Not Me.Parent.Form.Recordset Is Nothing Then
        If Me.Parent.Form.Recordset.RecordCount > 0 Then
            If Not IsNull(Me.Parent.Form.Recordset![ID]) Then
                SQLLine = "SELECT * FROM Orders WHERE [Customer ID] = " & Me.Parent.Form.Recordset![ID]
            End If
        End If
    End If
   
    Set Frm_RstDAO = Me.Parent.Frm_DbsDAO.OpenRecordset(SQLLine, dbOpenDynaset)
    Set Me.Recordset = Frm_RstDAO
End Sub

Unfortunately, this method precludes use of Master/Child links to control subform "filtering". In order to achieve the same functionality, the main-form current event re-opens its dependents with each change of main record (literally...mySubform.form.form_open(0)... ) with a new recordsource as in the above (see 2nd ref to SQLLINE).

This is where the chaos begins. Sometimes there will be records in those dependents, sometimes not. As we know, Access will unload a subform that has no records... as a result, many/all VB module references to it fail. Equally, if there are no records in the main form (e.g new Database), references to it in subforms' modules will fail. Further, certain objects such as the me![xxx] syntax will dissolve into thin air.

The lengthy IF statement (in my original post) has grown up as I have met new failure cases..

It seems to me, that if Access is going to unload a subform there should be a reasonable way to test for that without having to cycle thru' the whole of the forms collection and all the controls on those forms)? One might imagine a simple test like:-

IF mySubform,Recordset.RecordCount > 0 then
'carry on

would be sufficient... not so... unless one gets into trapping error codes.... another bad thing :-)

Ultimately,
so.... to make sure the target form is loaded, it has a current RS, there are more then zero records in its RS and to see if a field in it is NULL before attempting to use it

Hope this makes a little more sense.... Thanks
 
A little better.

Where are the tables?

Its back to sleep for me. Catch your reply in the morning.
 
Yes, I guess you must be 10 hrs or so ahead of us? It's over the pub for me though! ... untypically... the weather here is fabbo!

At the moment it's an .mdb which is NOT split into FrontEnd/BackEnd.. but I will split it sooner or later.. so local tables.

Thanks for tryng to help...
 
what does running this in the current event achieve?
what can it achieve?

a current event merely displays the record currently pointed to by a recordset cursor. in a 1-to-many relationship (form-subform) then the subform may or may not show any records - but any testing at this point is surely superfluous.

the time to test records is before any edits are saved - which for simplicity is in the forms before update event
 
Dave

Thanks for jumping in.

I could not for the life of me understand what the objective was.

It appears that no code is required, but simply a record source.

I feel better now.
 
Ah interesting.... I may have been working under a misapprehension... not so surprising given I'm completely self-taught... less a desktop Db system more a PHd :)

So, since I can't use Master/Child links, as I scroll thru' my main records, how do you recommend modifying the subforms so that they only display records pertinent to the current main-form record please (this is how I arrived at the Current Event)?

Thanks
 
Create a Main Form with a Record Source. A query will do.

Do similar for a Sub Form with its Record Source.

In Design attach the Sub Form to the Main Form.

Click on the Main Form first to give it focus. Then Clik on the Sub Form. (We are still in design view)

Look in the properties box for Master and Child links. Link the Primary Key of the Main to the Foreign Key of the Sub.

Save Close and reopen.

That should be it.
 
Ah... sadly I can't use master/child links... The method of opening the form and subforms (as per Msoft's own instructions) precludes them.... hence my use of
Code:
Call mySubform.Form.Form_Open(0)
in the main-form Current Event.

I think what happens when Master/Child links are used is that the subform is 'invisibly' re-loaded by Access when main-form pointer moves... but it does not fire the subform's Form_Open event. Because that is the case, the subform reverts to the default currentproject workspace... it then no longer resides in the form-specific workspace created when the form was first opened ... this in turn means taht the subform no longer participates in the Begin/Commit/Rollback transactions used by the main-form.

I think as a general comment though... checking the load/unloaded status of a form, the presence of recordsets, running a count of those recordsets and checking the existence of objects which point at them might be used in many situations other than mine.

My original IF statement attempts to do that. Frustratingly, I have checked if an object exists with "Is Nothing"... it has passed the test and then failed when I tried to use it and Access has told me it no longer exists....

cheers
 
I have no idea what you are doing and why.

Sorry but the method I explained is standard practice.
 
Ok .. no probs... thanks for trying anyway.... :)
 
By way of example here is a problem I've just had. In the main-form I delete a record (this is within an SQL transaction)... the Current Event of the mainform fires. In that event I call the following.....

Code:
    If Not Me.Form.Recordset Is Nothing Then
        If Not Me.Form.Recordset.EOF Then
            If Me.Form.Recordset.RecordCount > 0 Then
                If Not IsNull(Me.Form.Recordset![ID]) Then
                     If Not IsEmpty(Me.Form.Recordset![ID]) Then
                        If Not Me.Form.Recordset![ID] Is Nothing Then
                            MsgBox Me.Form.Recordset![ID]
All my tests are passed.... but the msgbox fails saying the record has been deleted.... Ignoring the rights and wrongs of opening and/or updating subforms for a moment... why does it pass all my tests and then fail plse? The whole point of the tests is to ignore the msgbox (in this case) if there is not a 'valid' record?

thnx
 

Users who are viewing this thread

Back
Top Bottom