multiple check for value-URGENT need

[igourine]

Hi,
i am still stacking in my DB, Please help me to get over this stage:banghead:. i have value in the form call (welder) & based on the other data i will enter in form i need VBA to check if this welder is certified to do the job or not.
i attached sample for ready referance & i hope can someone help me.
note: i create VBA but not working proparly
thanks in advance.

 

[PeterF]

Your check on nz(ctl.Column(15) , 0) wont work, ctl.Column(15) is a zero length string. A ZLS is not null but "".

You can rewrite your code like:

if "" & ctl.Column(15) = "" then ...

Check your table structure for ZLS. Or fix the ZLS with following code

Function RemoveZLSfromDB()
'Function to fix zero lenght string in DB all tabledefs ar changend an all fieldsvalues are set to null
Dim tblDefs As TableDefs
Dim tbl As TableDef
Dim fld As Field
Dim fldname As Field
Dim RS As DAO.Recordset
Dim rsfld As Field
Dim ChangeCounter As Integer
Dim prevChangeCounter As Integer
Dim FieldCounter As Integer

ChangeCounter = 0

    Set tblDefs = CurrentDb.TableDefs
    For Each tbl In tblDefs
        prevChangeCounter = ChangeCounter
        For Each fld In tbl.Fields
            If fld.AllowZeroLength Then
                fld.AllowZeroLength = False
                ChangeCounter = ChangeCounter + 1
            End If
        Next
        If ChangeCounter > prevChangeCounter Then
'            CurrentDb.TableDefs.Refresh tbl.CreateField
            Set RS = CurrentDb.OpenRecordset("select * from " & tbl.Name)
            If Not RS.EOF Then
                RS.MoveFirst
                While Not RS.EOF
                    For Each rsfld In RS.Fields
                        If rsfld = "" Then
                            RS.Edit
                            rsfld = Null
                            RS.Update
                            FieldCounter = FieldCounter + 1
                        End If
                    Next
                    RS.MoveNext
                Wend
            End If
            RS.Close
        End If
    Next
    MsgBox "Velden gewijzigd " & ChangeCounter & " en " & FieldCounter & " ZLS uit de tabellen verwijdert."

    Set RS = Nothing
End Function

Note: this will remove all ZLS in the DB. For new fields don't allow a ZLS (access default).

 

[igourine]

Hi PeterF,
thank you for your reply but still i did not understand your new code can you please try to use the sample i attached.
also i notice that (Me.WeldingProcess is not equal ctl.Column(6). it could be problem also 'cause value is not the same.

note: iam just biginner in VBA

 

[igourine]

Dear Peter,
any help please?

 

[Mihail]

Hi !
You can maximize the chances to an answer by posting a 2003 version of your DB.
I use Access 2007 but I'm not able to ope your DB.