better display of results

[saranmc]

yes yes i understand.. i executed it completely new with only the requred dimensions....

[saranmc]

i meant your #13 post.. i did not understand where exactly i Need to place the nested Loop to all the MLE table fields

[mbizup]

Ah okay...

That would be pretty much the same code with the loops reversed, executed AFTER the first block...

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

for each fld in rs.fields   ' Loop through MLE table fields
      for each fld1 in rs1.fields  ' Loop through Import fields
          if fld.name = fld1.name then
                 fnd = true ' We have a match! Exit, and continue to next IMPORT field
                 varFld = fld1.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..
        ' ... only record unmatched this time
        if isNull(VarFld) = true then
              strSQL = "INSERT INTO TEMP_Table (MLEColumnName, ImportColumnName) Values('" & fld.Name & "','')"
              CurrentDb.Execute strSQL, dbFailOnError
         end if
 
 Next

Unchecked of course but something like that should give you a listing of Matched fields, unmatched MLE fields and unmatched Import fields.

[mbizup]

I might have gone cross-eyed switching the MLE/Import loops around in that second block, so you may need to fix it up... Just a tip - when naming recordsets, especially when your code involves multiple recordsets, using names relevant to the actual data (eg: rsMLE and rsImport as opposed to rs and rs1) makes your code easier to follow.

[saranmc]

i understand your concept in #18.
the logic what you are doing makes sense.

but it does not seem to be working.
the 2nd column of Import table fields appears perfectly but the 1st column is completely messed up.. again some fields repeat and there are no blank spaces anywhere...

MLE	Import

pnr	pnr
pnr	keyword
pnr	Prio
pnr	description
pnr	combined tech / EFF
risk	risk
Reason	reason
Justification	justification
Justification	number MPD task already performed
CSN	CSN
CSN	MSN 3
CSN	MSN 5
CSN	MSN 6
CSN	MSN 8
CSN	MSN 10
CSN	MSN 11
CSN	MSN 12
CSN	MSN 14
CSN	MSN 13
CSN	MSN 16
CSN	Total
FAM	FAM
FAM	AP1 Leader
FAM	mutilple_in Fam
FAM	INC PN available
FAM	sales all
FAM	Sales SIA
FAM	purchase Price
FAM	total stock 13/11/2015
FAM	C-check hit
FAM	% consum
FAM	% sales
FAM	average QTY consum
FAM	average QTY sales
FAM	% consum1
FAM	% sales1
FAM	average QTY consum1
FAM	average QTY sales1
FAM	% consum2
FAM	% sales2
FAM	average QTY consum2
FAM	average QTY sales2
FAM	Trend _Consumption
FAM	Trend_ Sales

so you see some of the fields are repeating....
the pattern is that the 1st column strikes a match and then it repeats that matched field name uptil it matches a new field....

pnr...risk....r eason.....

These are the matched fields not all the field names as we want it!

[saranmc]

my code in #11 yields this result:

MLE    Import	         matchedcolumns
pnr		               MATCHED
	 pnr	
	 keyword	
	 Prio	
	 description	
	 tech / EFF	
risk		               MATCHED
	 risk	
Reason		               MATCHED
	 reason	
Justification		       MATCHED
	 justification	
	 number MPD task already performed	
CSN		               MATCHED
	 CSN	
	 MSN 3	
	 MSN 5	
	 MSN 6	
	 MSN 8	
	 MSN 10	
	 MSN 11	
	 MSN 12	
	 MSN 14	
	 MSN 13	
	 MSN 16	
	 Total	
FAM		                MATCHED
	 FAM	
	 AP1 Leader	
	 mutilple_in Fam	
	 INC PN available	
	 sales all	
	 Sales SIA	
	 purchase Price	
	 total stock 13/11/2015	
	 C-check hit	
	 % consum	
	 % sales	
	 average QTY consum	
	 average QTY sales	
	 % consum1	
	 % sales1	
	 average QTY consum1	
	 average QTY sales1	
	 % consum2	
	 % sales2	
	 average QTY consum2	
	 average QTY sales2	
	 Trend _Consumption	
	 Trend_ Sales

this Looks quite good.. only if I had all the MLE field names instead of the matches would have been great.!

[saranmc]

great!! I think I just hit Gold...
I slightly modified your #18 answer and now i think it Looks great..

I added an ELSE condition to your code.

For Each fld1 In rsIMP.Fields   
For Each fld In rsMLE.Fields  
        If fld1.Name = fld.Name Then
                  fnd = True 
                  varFld = fld.Name 
                  match = "MATCHED"
                 Exit For
        Else: varFld = " "
                match = " UNMATCHED IMPORT FIELDS"
           End If
        Next
strSQL = "INSERT INTO TEMP_Table (MLEColumnName, ImportColumnName, matchedcolumns) Values('" & varFld & "','" & fld1.Name & "', '" & match & "')"
               CurrentDb.Execute strSQL, dbFailOnError

  Next

For Each fld In rsMLE.Fields   
For Each fld1 In rsIMP.Fields 
           If fld.Name = fld1.Name Then
                  fnd = True
                  varFld = fld1.Name 
                 Exit For
                 Else: varFld = " UNMATCHED TARGET FIELDS "
           End If
        Next
   
         If varFld = " UNMATCHED TARGET FIELDS " Then
               strSQL = "INSERT INTO TEMP_Table (MLEColumnName, matchedcolumns) Values('" & fld.Name & "','" & varFld & "')"
               CurrentDb.Execute strSQL, dbFailOnError
          End If

  Next

this gives a good result similar to the one in #21, without blank spaces in Import field Name lists and at the end it lists all the MLE field names in the 1st column that have not matched.

I would have liked to have them simulatneously side by side so that the user does not have to scroll down but i guess this is also ok..

is it possible to Change the FONT Color of the cell in an IF Statement??
like IF the cell has this value Change Color to green..
never done it before.

[saranmc]

ok.. most Problems solved.. last step....

now i have a subform in a form which is a popup form.
this subform has the table connected to it.

i did conditional formatting to the subform..
but this formatting dissapears when i open the popup form..

i guess i have to activate or something??

[mbizup]

<<< is it possible to Change the FONT Color of the cell in an IF Statement??
like IF the cell has this value Change Color to green..>>>

Did you work out how to use "Expression IS" formatting in the conditional formatting popup.

<<
i did conditional formatting to the subform..
but this formatting dissapears when i open the popup form..
>>

Have you resolved this? Conditional formatting *should* work fine in a popup form. If this is still an issue, post another question specifically describing the Conditional Formatting issue -- with details including the Conditional Formatting rules you are using, and situations where it works or fails.

[mbizup]

By the way nice job working out the code... feels good, doesn't it?

[saranmc]

its so annoying that after getting the code to do all what i want but finally this last step stops me... i do the formatting using conditional formatting and it works fine on a subform, i just dont know where it dissapears to when i open the parent form. anyways as you said i will ask it as a seperate question...

thanks a lot mbizup.. the code i had orginally was a Long one which took me so much effort.. you surprised me with a short and compact code.. thanks a lot!!!!
as you said, feels really great!!!