VB Forum / Database Access / October 2004

Hi,

It could be in a case if some of the field names in your recordset have space or some special characters. For example if field name,returned fro the query is Last Name, then it would fail. What you need to do in this case is to wrap your field names into square brackets [Last Name]. You also might get this error in a case if some of the field names are reserved words, like Name. Remedy is the same - square brackets

Also do not forget to use Set instruction when you reference recordset to the connection (it should be Set global_data_env_main.ActiveConnection = cn). Otherwise ADO will open another connection and you could get multiple opened connections from the same application.

 Hi,

 I keep getting this error when I am trying to requery a disconnected recordset.  Here is my code, what I am basically doing is saving a new record and then trying to open the recordset, requery to get the saved (up to date) data and then disconnect the recordset.  It works on the global_data_env_add.Requery but not the global_data_env_main.Requery.  Any advice or help on this would be greatly appreciated.

 With cmd
             Set .ActiveConnection = cn
             .CommandType = adCmdStoredProc
             .CommandText = "proc_update_referral_info"
             .Parameters("@ri_id").Value = lbl_ri_id
             .Parameters("@ri_tenancy_status").Value = IIf(Nz(cmb_ri_tenancy_status) = "", Null, cmb_ri_tenancy_status)
             .Parameters("@ri_off_id").Value = IIf(Nz(cmb_off_full_name.BoundText) = "", Null, cmb_off_full_name.BoundText)
             .Parameters("@ri_sw_id").Value = IIf(Nz(cmb_sw_worker.BoundText) = "", Null, cmb_sw_worker.BoundText)
             .Parameters("@ri_eligibility").Value = IIf(Nz(cmb_ri_eligibility) = "", Null, cmb_ri_eligibility)
             .Parameters("@ri_rb_id").Value = IIf(Nz(cmb_ri_referred_by.BoundText) = "", Null, cmb_ri_referred_by.BoundText)
             .Parameters("@ri_referrer_name").Value = IIf(Nz(txt_ri_referrer_name) = "", Null, txt_ri_referrer_name)
             .Parameters("@ri_referred_date").Value = IIf(Nz(msk_ri_date_referred) = "__/__/____", Null, msk_ri_date_referred)
             .Parameters("@ri_rr_id").Value = IIf(Nz(cmb_ri_referred_reason.BoundText) = "", Null, cmb_ri_referred_reason.BoundText)
             .Parameters("@ri_closed").Value = chk_closed.Value
             .Parameters("@ri_rc_id").Value = IIf(Nz(cmb_ri_reason_closed.BoundText) = "", Null, cmb_ri_reason_closed.BoundText)
             .Parameters("@ri_closed_date").Value = IIf(Nz(msk_ri_closed_date) = "__/__/____", Null, msk_ri_closed_date)
             .Parameters("@ri_pending").Value = chk_ri_pending.Value
             .Parameters("@ri_notes").Value = IIf(Nz(txt_ri_notes) = "", Null, txt_ri_notes)
             .Execute
         End With
         cn.CommitTrans
         global_data_env_add.ActiveConnection = cn
         global_data_env_add.Requery
         Set global_data_env_add.ActiveConnection = Nothing
         global_data_env_main.ActiveConnection = cn
         global_data_env_main.Requery - Produces an error
         Set global_data_env_main.ActiveConnection = Nothing
         
         local_update

 thanks

 Damon