Problem With Unbound Forms! (1 Viewer)

[geko]

Afternoon all,

I am trying to code an unbound form, complete with navigation buttons.

All seems fine, except for the code that checks to see if any of the fields have changed (similar to Dirty I guess).

For some reason it is saying that (depending on what record) the control value is different to that of the field value, when nothing has changed.

I have noticed that it seems to happen on fields where there is no entry, i.e the value is null, but I cannot seem to account for this.

Am I doing something wrong?
Is there some code that would make this task easier?

I seem to be bashing my head against a wall with this one.

The code for the function is as follows:

Public Function CheckChange(rstChkChg As DAO.Recordset) As Boolean

'==========
'
'  Purpose: Checks to see if the current record has changed,
'           usually called when navigating through records.
'
'==========

Dim iChk As Integer

    iChk = 0

    If iCnt = 0 Then
        CheckChange = False
    Else
        FindRecord rstChkChg
        
        If Me.txtSupplier <> rstChkChg.Fields("Supplier").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk + 1
        End If
        
        If Me.txtAddr1 <> rstChkChg.Fields("Addr1").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.txtAddr2 <> rstChkChg.Fields("Addr2").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.txtAddr3 <> rstChkChg.Fields("Addr3").Value Then
            iChk = iChk + 1
        Else
            iChk = iChk
        End If
        
        If Me.txtAddr4 <> rstChkChg.Fields("Addr4").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.cboTown <> rstChkChg.Fields("Town").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.cboCounty <> rstChkChg.Fields("County").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.txtPCode <> rstChkChg.Fields("PCode").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If

        If Me.txtTel <> rstChkChg.Fields("Tel").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.txtFax <> rstChkChg.Fields("Fax").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.txtEmail <> rstChkChg.Fields("Email").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If

        If Me.txtURL <> rstChkChg.Fields("URL").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.chkOld <> rstChkChg.Fields("Old").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
        
        If Me.txtNotes <> rstChkChg.Fields("Notes").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
    End If

    If iChk > 0 Then
        CheckChange = True
    Else
        CheckChange = False
    End If

End Function

If the kind ppl of the forum have any ideas/suggestions to help me out that would be most appreciated.

If you need more info then let me know.

Many thanks in advance,

G

Please note:

I cannot use Winzip (or other compression based software) here at work, so am unable to attatch a file for you as the smallest I can get the file is still too large to use as an attachment for the post.

Sorry for the lameness!

 

[Mile-O]

2 questions:

1. What version of Windows are you using?
2. Why are you using an unbound form for this?

 

[geko]

1. Win 98 (A2K) - Running off a Novell Network.
2. Because it will be getting more complicated than this, and if I can't get it working on a simple version what hope have I got when it gets more complicated.

Thanks for the quick reply.

 

[ecniv]

You are not accounting for nulls... This means your checks are um well pointless as it compares to null and either errors or doesn't compare properly.

Try putting this in a module (global)

public function nnz(byval varA as variant, byval varB as variant) as variant
    nnz=iif(isnull(varA),varB,varA)
end function

then in your checking code:

if nnz(txtField,"")<>nnz(rst("field"),"") then
'---- other code
end if

This is only one option, you could check whether the textbox is null or the recordset field is null in your main code check, but I think you'll find this is a little clearer?

Oh - if you are in Access - its the NZ function.
Unbound is the way to go (grin)


Vince

 

[Dugantrain]

Two things:
-First, I'm assuming that there is an error in your code:

If Me.txtSupplier <> rstChkChg.Fields("Supplier").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk + 1
        End If

The Else statement shouldn't be adding a 1 if the two values are equal. Second, you can't equate a Null to a Null. So if one of your fields is Null and the Source Field is Null then, for example:

Me.txtSupplier <> rstChkChg.Fields("Supplier").Value

Will validate because a Null will never be equal to a Null. Try recoding this to say something like:

If Not IsNull(Me.txtSupplier) And Not IsNull(rstChkChg.Fields("Supplier").Value) Then
     If Me.txtSupplier <> rstChkChg.Fields("Supplier").Value Then
            iChk = iChk + 1
        Else:
            iChk = iChk
        End If
End If

This code is untested.

 

[geko]

Checking the help file on Nz now.

Nice one cheers Vince.

Dugantrain:

The typo you pointed out, although a typo does not matter, as the error still occurs on the other fields. But thanks for pointing it out anyway.

I did try something like what you have suggested for the null values, but that didn't work either, so I'm gonna have a look at this Nz function Vince suggested.

Many thanks,

G

PS. will be sure to let you know how I get on

 

[Mile-O]

2. Because it will be getting more complicated than this, and if I can't get it working on a simple version what hope have I got when it gets more complicated.

That doesn't really answer my question. Why are you pursuing a method using an unbound form? The fact that it will get more complicated that 'this' doesn't really help. Especially since I don't know what 'this' is.

Have you designed a bad table structure and are trying to work around it?

One thing I would suggest when working with unbound forms (something I only do if using Visual Basic to access an Access backend) is to put your code into a Class module.

 

[geko]

No there is nothing wrong with my table structure, I just prefer the functionality that you can get from an unbound form, even if it does require more time to code.

Not only that, but it helps you cut down on the amount of mistakes the monkeys that will be using the system can make!

I have had too many weird things happen to systems I designed using bound forms, the only way to combat these that I can see is to use unbound forms.

Besides, most ppl seem to suggest using unbound forms, regardless.

Yes class modules, agreed, but these are something I am still learning and am not yet confident enough to put into a system.

 

[geko]

Fantasatic, Nz works a treat!

That's why I like all this IT stuff, always learning, can never know everything.

Thanks again.

G

 

[Mile-O]

Not only that, but it helps you cut down on the amount of mistakes the monkeys that will be using the system can make!

I find that irrelevant.

I have had too many weird things happen to systems I designed using bound forms.

Such as?

Besides, most ppl seem to suggest using unbound forms, regardless.

Any incrimnation names?

Yes class modules, agreed, but these are something I am still learning and am not yet confident enough to put into a system.

They aren't that much different.

I tried to do an example but I've run out of time - the attachment shows how to load records into a form using a class module. I intended to add a Save and Add function to it too but it shouldn't be that hard to work out.

 

[Rich]

Besides, most ppl seem to suggest using unbound forms, regardless.

Where?

 

[Pat Hartman]

Besides, most ppl seem to suggest using unbound forms, regardless.

- certainly not in this forum.

Bound forms/reports are the things that make Access great! Without them, you are better off using VB. If you have had trouble with bound forms in the past it is because you don't understand them and are not using the events properly.

It is more productive to learn your tool than to work around it.

 

[geko]

Well let's just put the differneced in opinions of bound vs. unbound forms to the fact that they are just differences in opinions... I'm sure there are threads all over the forum that discuss this issue.

Anyway, thanks to those that offered advice and suggestions.

I have since modified the code as follows:

Public Function CheckChange(rstChkChg As DAO.Recordset) As Boolean

'==========
'
'  Purpose: Checks to see if the current record has changed,
'           usually called when navigating through records.
'           Only works where controls that will have table data in them,
'           are the ones that have tag entries, and no other control has a
'           tag entry.
'
'==========

Dim iChk As Integer

    iChk = 0

    If iCnt = 0 Then
        CheckChange = False
    Else:
        FindRecord rstChkChg
        
        For Each ctrl In Me.Controls
        'ctrl is defined elsewhere as: Dim ctrl as Control
            If ctrl.Tag <> "" Then
            
                strFld = ctrl.Tag
                'strFld is defined elsewhere as: Dim strFld as string
            
                If Nz(ctrl, "") <> Nz(rstChkChg.Fields(strFld), "") Then
                    iChk = iChk + 1
                Else:
                    iChk = iChk
                End If
            End If
        Next
    End If

    If iChk > 0 Then
        CheckChange = True
    Else
        CheckChange = False
    End If

End Function

Many thanks again.

G

 

[Pat Hartman]

Well let's just put the differneced in opinions of bound vs. unbound forms to the fact that they are just differences in opinions

- I'm going to have to disagree with that. It is simply hubris to believe that writing potentially hundreds of lines of custom code for each unbound form is somehow better than (or even equivalent to) relying on the well known and tested, bound form solution. There is enough code to be written and tested to handle validation and formatting. There is simply no justifiable reason to duplicate the "hidden" code that Access uses to populate a bound form, navigate a recordset, and control when records should be saved. In fact, I would consider it fiscally irresponsible if you worked for me. I would not want my programmers wasting company time and money writing and testing unnecessary code.

 

[john471]

Pat,

One main reason I use unbound forms by preference, is to inhibit users accidentally moving to a different record (and subsequently editing the wrong one) by using the page up/down keys, or scroll-point type mouse functionality.

Are you able to advise on how to circumvent such a disaster without resorting to unbound forms ?

(Using AC97 / WIN2K & WINNT)

Cheers

John.

 

[Mile-O]

Set the form's Cycle property to Current Record.

And,m to deal with the mouse scrll: click here

 

[WayneRyan]

John,

I'm not responding for Pat, but I also feel very strongly against unbound
forms.

This will get rid of the Page/Down & Page/Up keys.

Private Sub Form_Load()
  Me.KeyPreview = True
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  Select Case KeyCode
     Case 33, 34
       KeyCode = 0
  End Select

End Sub

This thread here covers both topics:

http://www.access-programmers.co.uk/forums/showthread.php?t=2096&highlight=disable+mouse

I would say that it is far less effort in the long run to disable the Access
"features" that you don't want, than to recreate all of the data management
functions that it provides so well.

hth,
Wayne

 

[john471]

Thanks Wayne.

I had previously searched on the mouse-wheel issue, but only found the modified DLL solution, and if our company IT police caught me doing that on dozens of our PCs, I'd be, well... let's just say it wouldn't be pretty for me.

Many of my forms in my current project are to display data only, so I use bound forms for those anyway, with all controls locked, but with an "edit" command button that brings up a seperate, unbound, edit form that allows qualified users to edit (only a small subset of the main form's fields) data when desired.

It took some doing, but I got it working, and it meets my needs for now... but for my next project ......

Thanks again.

 

[Mile-O]

Many of my forms in my current project are to display data only, so I use bound forms for those anyway, with all controls locked, but with an "edit" command button that brings up a seperate, unbound, edit form that allows qualified users to edit (only a small subset of the main form's fields) data when desired.

Why not use the edit button to detect if the user is qualified and then loop through each control and enable it for editing rather than waste space with a second form?

 

[john471]

Why not use the edit button to detect if the user is qualified and then loop through each control and enable it for editing rather than waste space with a second form?

Not a bad idea, but that would not have solved my mouse-wheel issue, which I had no other solution to at the time. Now that I have done it that way, I shall leave it thus, for this project.