We help IT Professionals succeed at work.

Access Record querying

CaptainGiblets
CaptainGiblets used Ask the Experts™
on
Using the below code (unfinished) i am hoping to query every record in a table and find out what columns are empty and then report to the user what columns are empty. So hopefully after clicking the button that this code belongs to the user will receive a message box that will say something along the lines of

ID is missing the current fields - Name, Contact, Address1
ID is missing the current fields - Address2
ID is missing the current fields - Tel

Where ID is the autonumber relating to that row in my backend sql 2008 database

Dim fields As Variant
Dim ssql As String
Dim test As String
Dim i As Integer
Dim report As String

report = "The current agreements are missing details" & vbNewLine

ssql = "select [ID], [Name], [Contact], [Address1], [Address2], [Address3], [Address4], [Postcode], [Tel], [MID] from v_missingfields"
Dim rs As New ADODB.Recordset
rs.Open ssql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Do While Not rs.EOF
    i = 1
    fields = Array("[id]", "[Name]", "[Contact]", "[Address1]", "[Address2]", "[Address3]", "[Address4]", "[Postcode]", "[Tel]", "[MID]")
    test = UBound(fields)


    Do While Not i = (test + 1)
    i = i + 1
    If IsNull(rs.fields(i)) Then
    Reports = Reports & fields(1) & "is missing the following information" & fields(i) & vbNewLine
End If
    
    Loop

rs.MoveNext

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
And the question is?

Jim.

Author

Commented:
sorry, basically i want to know what is missing from the code to get it to work as desired.
You have loop before movenext. You are always in same record
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
1. This:

    fields = Array("[id]", "[Name]", "[Contact]", "[Address1]", "[Address2]", "[Address3]", "[Address4]", "[Postcode]", "[Tel]", "[MID]")

should be:
    fields = Array("id", "Name", "Contact", "Address1", "Address2", "Address3", "Address4", "Postcode", "Tel", "MID")

2. Your missing a loop statement.  You have two DO's and only one loop statement.

3. On this check:

If IsNull(rs.fields(i)) Then

 I would do:

  Is Nz(rs.Fields(i),"")="" then

Jim.