How to create and use an Access Form that runs off of a VBA created recordset, not an instantiated query!

Hi Guys,

Thanks for your responses,

I am using this code to fetch my data from a remote database:
****************************************************************************************
Public Function get_cfp_record(Match_Key As Long) As DAO.Recordset

    Dim myQuery     As DAO.QueryDef
    Dim myRecSet    As DAO.Recordset
   
    Set myQuery = CurrentDb.CreateQueryDef(get_random_num(Now()))
    myQuery.Connect = ODBC_CON
    myQuery.SQL = "EXECUTE PROCEDURE get_cfp_record(" & Match_Key & ");"
   
    Set myRecSet = CurrentDb.OpenRecordset("SELECT Expr1000 as Extract_Key, Expr1001 as Match_Key, Expr1002 as Match_Type, Expr1003 as Creation_Dtime, Expr1004 as Index_Number, Expr1005 as Index_Desc, Expr1006 as H1_Creation_Date, Expr1007 as H1_External_Id, Expr1008 as H1_Gi_Tmp_Key, Expr1009 as H1_Cky," & _
                             "Expr1010 as H1_Crk, Expr1011 as H1_Store_No, Expr1012 as H1_Title, Expr1013 as H1_Forenames, Expr1014 as H1_Surname, Expr1015 as H1_Dob, Expr1016 as H1_Dob_Str, Expr1017 as H1_Gender, Expr1018 as H1_Organisation, Expr1019 as H1_Thoroughfare," & _
                             "Expr1020 as H1_Dbldep_Locality, Expr1021 as H1_Dep_Locality, Expr1022 as H1_Town, Expr1023 as H1_Postcode, Expr1024 as H1_Post_Out, Expr1025 as H1_Post_In, Expr1026 as H1_Dps, Expr1027 as H1_Addrk, Expr1028 as H1_Home_Tel, Expr1029 as H1_Work_Tel," & _
                             "Expr1030 as H1_Mobl_Tel, Expr1031 as H1_Fax_Tel, Expr1032 as H1_Email, Expr1033 as H1_Rx_Category, Expr1034 as H1_Spare, Expr1035 as H1_Goneaway, Expr1036 as H1_Deceased, Expr1037 as H1_Sn_Rank, Expr1038 as H1_Cf_Cust, Expr1039 as H2_Creation_Date," & _
                             "Expr1040 as H2_External_Id, Expr1041 as H2_Gi_Tmp_Key, Expr1042 as H2_Cky, Expr1043 as H2_Crk, Expr1044 as H2_Store_No, Expr1045 as H2_Title, Expr1046 as H2_Forenames, Expr1047 as H2_Surname, Expr1048 as H2_Dob, Expr1049 as H2_Dob_Str," & _
                             "Expr1050 as H2_Gender, Expr1051 as H2_Organisation, Expr1052 as H2_Thoroughfare, Expr1053 as H2_Dbldep_Locality, Expr1054 as H2_Dep_Locality, Expr1055 as H2_Town, Expr1056 as H2_Postcode, Expr1057 as H2_Post_Out, Expr1058 as H2_Post_In, Expr1059 as H2_Dps," & _
                             "Expr1060 as H2_Addrk, Expr1061 as H2_Home_Tel, Expr1062 as H2_Work_Tel, Expr1063 as H2_Mobl_Tel, Expr1064 as H2_Fax_Tel, Expr1065 as H2_Email, Expr1066 as H2_Rx_Category, Expr1067 as H2_Spare, Expr1068 as H2_Goneaway, Expr1069 as H2_Deceased," & _
                             "Expr1070 as H2_Sn_Rank, Expr1071 as H2_Cf_Cust, Expr1072 as Title_Match, Expr1073 as Title_Score, Expr1074 as Gender_Match, Expr1075 as Gender_Score, Expr1076 as Fn_Match, Expr1077 as Sn_Match, Expr1078 as Name_Match, Expr1079 as Name_Score," & _
                             "Expr1080 as Dob_Match, Expr1081 as Dob_Score, Expr1082 as Dob_Fuzzy, Expr1083 as Dob_Year, Expr1084 as Dob_Y1, Expr1085 as Dob_Y2, Expr1086 as Dob_Y3, Expr1087 as Dob_Y4, Expr1088 as Dob_Month, Expr1089 as Dob_M1," & _
                             "Expr1090 as Dob_M2, Expr1091 as Dob_Day, Expr1092 as Dob_D1, Expr1093 as Dob_D2, Expr1094 as Pc_Match, Expr1095 as Addrk_Match, Expr1096 as Non_Paf_Elements, Expr1097 as Address_Match, Expr1098 as Address_Score, Expr1099 as Telephone_Match," & _
                             "Expr1100 as Telephone_Score, Expr1101 as Email_Match, Expr1102 as Email_Score, Expr1103 as Rx_Cat_Match, Expr1104 as Rx_Cat_Score, Expr1105 as Organ_Match, Expr1106 as Organ_Score, Expr1107 as Spare_Match, Expr1108 as Spare_Score, Expr1109 as Auto_Match," & _
                             "Expr1110 as Auto_Match_Desc, Expr1111 as User_Review, Expr1112 as User_Review_Desc, Expr1113 as Manual_Match, Expr1114 as Manual_Match_Dtime, Expr1115 as Processed_Flag, Expr1116 as Processed_Dtime, Expr1117 as Match_Score" & _
                             " FROM " & myQuery.Name & ";")
   
    If myRecSet.RecordCount <> 0 Then
        myRecSet.MoveLast
        myRecSet.MoveFirst
    End If
   
    Set get_cfp_record = myRecSet
   
    'DoCmd.DeleteObject acQuery, myQuery.Name

End Function

************************************************************************************


This code is called from here:

Private Sub record_list_Click()
    Set Form_Dedupe_Analysis.Form.Recordset = get_cfp_record(record_list.Value)
End Sub

'record_list' is a list box on the form, when I select a record from that list I want to display the details of it on the form, so the idea was to assign the recordset containing that record to the form's recordset. The underlying schema of the recordset will always be the same.

I have commented out the deletion of the query because that seems to cause more problems than it solves and I haven't added error code for the same reason. I will obviously add these once the core functionality is working.

Anybody have any ideas? I can post more information if required.

The reason for all the aliasing is that I'm calling a stored procedure from an Informix database which returns a single record. An unfortunate side effect of calling an Informix Stored Procedure is that you lose all the field names, so I have to manually rename the fields using the above sql. This works fine normally for DAO recordsets that I attach to a listbox.

What I want to do now is take a recordset (which I can attach to a listbox and view the data without problem) and assign it to a form's recordset, but when I do this the form seems to ignore the recordset that I try to assign to it using the statement:

Set Form_Dedupe_Analysis.Form.Recordset = get_cfp_record(record_list.Value)

If I pause the code after this line and run these commands in the debug window I get:

?Form_Dedupe_Analysis.Form.Recordset.recordcount
 1
?Form_Dedupe_Analysis.Form.Recordset.fields(1).name
Match_Key

Which shows the forms recordset is populated, but the fields on the form whose controlsource is match_key doesn't update, it just continues to show #Name?

What can I do to make this work??!?!?!?!?!?

If there is a solution to this is ADO then I would give that a try but I have no experience with ADO at all.

Another alternative would be to run an sql on the database but I would need to be able to run this Informix specific command:

"SET ISOLATION TO DIRTY READ;"

before running the sql so that even if the table is locked Access could still read it (At the moment this is handled by the stored procedure)

I've managed to solve this now by rebuilding the form from scratch, and it works fine.

I can create disconnected DAO recordsets by creating a querydef with no name, running the query and then create the recordset, which I can now pass back to my form and set it as the form's recordset.

As the query has no name I don't delete it as I believe Access will handle deleting it once all references to it have been removed.