better display of results

[saranmc]

As a part of a database that i am developing i have a function that i developed in Access 2010 . on presenting it to my Superiors i was asked to enhance the presentation or Display. i am just hoping someone can Point me the right direction..

so basically i am inserting some values from one table to the other. but i first run Loops to determine which field names match and copy from the Import table only those fields which match for the target table. so far it works perfectly. no Problems. i am displaying the matching field names in a msg box. the code for this field Name comparision is as follows:

Private Sub Command50_Click()
Dim n As Long
Dim m As Long
Dim Ret_Type As Integer
Dim str As String
Dim stp As String
Dim mystr As String
Dim mysas As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("MLE_Table")
Set rs1 = CurrentDb.OpenRecordset("tbl_Import")


With rs
For n = 0 To .Fields.Count - 1
str = CurrentDb().TableDefs("MLE_Table").Fields(n).Name

    With rs1
    For m = 0 To .Fields.Count - 1
    stp = CurrentDb().TableDefs("tbl_Import").Field(m).Name

        If str = stp Then
             mystr = mystr & str & ", "
             fnd = True
             Exit For
        End If
        Next m
    If Not fnd Then mysas = mysas & str & vbCrLf
    fnd = False     
    End With
Next n
.Close

End With

Ret_Type = MsgBox("The Following Fields could not be found in your upload !!" & vbCrLf & mysas, vbOKOnly + vbExclamation, " MISSING DATA")

End Sub

now what my colleagues want is that this msg box is not sufficient.. they want a more detailed Display. maybe a form or a text file or something so that the user has a more clear Picture.

the Suggestion was to Show up all the fields of the target table and then Show the fields that matched as green or maybe a tick or checkmark.

i am sure this cannot be done in a msgbox. i know it sounds elegant and i am not sure it can be done. some colleagues say it can be.

can somebody Point me in the right direction or some Suggestion please. i am not experianced enough in Access, so this would be a learning experiance..

thanks in advance..

[mbizup]

You could set up a table to hold the names of the fields, with columns defined like this:

MLEColumnName -- Holds the names of the original table's fields
ImportColumnNam e -- Holds the names of the import table's column names

In your code, in the part of the IF-Then block where matching column names are found, record both column names in the table using an Insert Query. The SQL would look something like this:

strSQL = "INSERT INTO tblMatchFields (MLEColumnName, ImportColumnName) Values('" & str & "','" & stp & "')"
CurrentDB.Execute strSQL, dbfailOnError

Then extend your "If Not fnd" code to use a similar query to insert a record containing just the MLEColumnName:

strSQL = "INSERT INTO tblMatchFields (MLEColumnName) Values('" & str & "')"

That would give you a table containing a list of matching fields in both tables and also unmatched records where you have an MLEColumnName, but no ImportTableColu mnName. With a popup form bound to that table, you could then use conditional formatting to highlight 'unmatched' records where the ImportColumnNam e is null.

You would also have to include code to clear all records from this table each time an import is run...

[saranmc]

thats a lot of Information! thanks mbizup..

let me try it out. i will get back as soon as I have something!

[saranmc]

mbizup, i tried out your code.
before i explain further i made a small error in my post.

i wanted to show all fields of the IMPORT table and the fields that matched.

now as per your Explanation,
i set up a table named TEMP_Table, it has two columns.

1. MLEcolumnName
2. ImportcolumnNam e

Now ideally i would want all the fields of the Import table to Show up on the second column and the fields that matched or all fields of the MLE table on the 1st column. 1st column does not matter as i will Need to Highlight the matched fields later on.

so I incorporated your code and the 1st column works fine, it gives me the matched fields, but the 2nd one is not accurate.

the code is here..

With rs
For n = 0 To .Fields.Count - 1
str = CurrentDb().TableDefs("MLE_Table").Fields(n).Name
    With rs1
    For m = 0 To .Fields.Count - 1
    stp = CurrentDb().TableDefs("tbl_Import").Fields(m).Name
    
        If str = stp Then
             mystr = mystr & str & ", "
             fnd = True
             strSQL = "INSERT INTO TEMP_Table (MLEColumnName, ImportColumnName) Values('" & str & "','" & stp & "')"
            CurrentDb.Execute strSQL, dbFailOnError
             Exit For
        End If
        Next m
    If Not fnd Then mysas = mysas & str & vbCrLf
    fnd = False    
    samsql = "INSERT INTO TEMP_Table (ImportColumnName) Values('" & stp & "')"
    CurrentDb.Execute samsql, dbFailOnError
    End With
Next n
.Close
End With

now the only Thing i want is the 2nd column to Show me all the Import table fields. it Shows but not accurately, like one particular field keeps repeating. i think something to do with the FOR Loop.

do you see an error somewhere??

[mbizup]

Since you're no longer using the message box, you don't need the " mysas = mysas & str & vbCrLf", right?

Also, so that the insert query with just the ImportColumnNam e only gets run in the 'no match' condition, it needs to be enclosed in a BLOCK If statement...

If Not fnd Then    
     samsql = "INSERT INTO TEMP_Table (ImportColumnName) Values('" & stp & "')"
     CurrentDb.Execute samsql, dbFailOnError
End If

Finally, I might be misunderstandin g this - but from your description I think that your loop structure is backwards -- and that rs1 should be the outer loop and rs should be the inner loop. I think that would resolve the issue with the field name being 'stuck' on a single field.

[mbizup]

If you switched the order of the loops, you'd also have to adjust the str and stp definitions accordingly...

[saranmc]

ok, i enclosed it in an IF block and removed the unwanted Statement.

now what happens is that both columns have the same results that is the fields that matched.!

column2 does not Show Import table fields.

you think the Loop is backwards?? because the final aim was to insert the matched fields into the MLE table (which is done in a seperate function).
so the concept was to compare each Import table field Name with each MLE field Name. MLE table is the target table and hence the aim was to fill as many fields as possible in the MLE table.

ok I will try with the Loop the other way and come back..

[saranmc]

i switched the Loops and REMOVED the IF block you told me to put.
now it Displays!!

the INSERT Statement has to be outside the IF Statement as we want all the field names to Display.
the field names that are matched are being displayed on the 1st column.

but actually i think you are right, because this method has one Problem. when the fields are matched they are displayed in column2 and also the second time all fields are displayed. so the matched fields are doubled in the 2nd column!!

i tried removing them from the first INSERT. but in that Situation there is a blank cell in the second column when there is a match. that Looks wierd.

[mbizup]

Just verifying again - the IMPORT table is the one with extra (unmatched) fields, correct?

[saranmc]

Yes thats right... but the user would like to know the unmatched fields. maybe to think why they have not matched...

[saranmc]

now, i added one extra field "matchedcolumns " to Display MATCHED when a match is found.

after some modifications I am finally down to this code:

With rs1
For n = 0 To .Fields.Count - 1
str = CurrentDb().TableDefs("tbl_Import").Fields(n).Name
    With rs
    For m = 0 To .Fields.Count - 1
    stp = CurrentDb().TableDefs("MLE_Table").Fields(m).Name
            If str = stp Then
             fnd = True
             strSQL = "INSERT INTO TEMP_Table (MLEcolumnName, matchedcolumns) Values('" & stp & "', 'MATCHED')"
             CurrentDb.Execute strSQL, dbFailOnError
             Exit For
        End If
        Next m
        If Not fnd Then
        fnd = False     ' reset for next field
        samsql = "INSERT INTO TEMP_Table (ImportColumnName) Values('" & str & "')"
        CurrentDb.Execute samsql, dbFailOnError
    End With
Next n
.Close

End With

this Looks good but as i said earliers there is a blank in the importcolumnnam es whenever there is a match.

also having all the MLE table field names would be better rather than only the matcheed ones.
i tried to put an INSERT Statement but as it is inside the inner FOR Loop it runs everytime for one field of the outer Loop..
so i have so many times the entire field Name list repeating!!

[mbizup]

Perhaps something like this is more what you're trying to do?

' add these declarations
dim fld as dao.field  
dim fld1 as dao.field
dim varFld as Variant

varFld = Null

Set rs =  (your MLE recordset)
Set rs1 = (Your Import Table recordset)

for each fld1 in rs1.fields   ' Loop through import table fields
     for each fld in rs.fields  ' Loop through MLE fields
         if fld1.name = fld.name then
                fnd = true ' We have a match! Exit, and continue to next IMPORT field
                varFld = fld.Name  ' Save the matching field name
                exit for
         end if
      next
      ' varFld is inserted into the MLEColumnName field.  If there was no match, it will appear blank..
             strSQL = "INSERT INTO TEMP_Table (MLEColumnName, ImportColumnName) Values('" & VarFld  & "','" & fld1.Name & "')"
             CurrentDb.Execute strSQL, dbFailOnError

Next

The output should look something like this:

MLEField       ImportField
    a             a
    b             b
    c             c
                  d             <-- Unmatched
                  e             <-- Unmatched

[mbizup]

>>> also having all the MLE table field names would be better rather than only the matcheed ones.

You could add a second nested loop with the MLE recordset on the OUTSIDE, and an insert query to record ONLY unmatched MLE fields (ie: no records added in this second loop when matches are found).

[saranmc]

i tried your code mbizup.. it runs but the values are messed up..

i cant find the matched fields and also the order is strange.. it starts from the middle of the recordset..

you told me to put the Loop outside the inner FOR Loop but inside the outer FOR Loop.. right??

but i cant put it outside the inner FOR Loop as stp would have no value as it takes values from the Inner FOR Loop..

[mbizup]

What I posted was intended as a completely new/independent procedure...