Can't clear recordsouce after unhide columns

[DeveloperSteve]

I have a user input form that sets the recordset for a datasheet form and opens it based on the user selections. If the user views and or sorts the columns and then closes the datasheet, the code works just fine. However, if the user hides / unhides columns (right click column, then hide or unhide) the application fails the next time the user tries to open the datasheet.

Run-time error '103': The identifier that starts with ... is too long. The maximum length is 128.

While the datasheet form is originally unbound, after the hide / unhide the Record Source property contains the record source from the previous search.

How can I clear the record source on the form close event and make it save that way, so it can be opened without an error the next time?

 

[vbaInet]

That's an interesting one and one that's hard to visualise. Can you upload a stripped down version of your db so we can follow your steps to reproduce the problem.

 

[DeveloperSteve]

I can do that. I believe I can reproduce the problem with a simplified database and stored procedure (the db is in SQL Server). The front-end is an adp.

While I'm working on an example, can you tell me if the following code should remove any record source from the form upon the close event?

Private Sub Form_Close(Cancel As Integer)
Me.RecordSource = ""
Me.Requery
DoCmd.Save
End Sub

Watching the value of Me.RecordSouce in the immediate window while executing the code shows me the record source being deleted before the save. However it seems to me that using the hide / unhide column feature while the form is open somehow causes the record source to be replaced after the close event! I then have no way to open the form without an error.

To avoid the problem, perhaps I should be opening a new instance of the form each time.


FWIW - here is the code that retrieves the recordset for the datasheet in the first place. The adp calls the stored procedure and passes in a parameter in xml format. The xml parameter is quite long, but works just fine. However if a column is hidden or unhidden, the forms recordsource property is saved containing the stored procedure name followed by the xml paramter. The next time the form is opened I get the 103 error.

Function Open_Datasheet_015(strWhere As String, strQueried As String)

Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim xmlParams, parameters As String
Set cmd = New ADODB.Command

'create property list in XML format for Where In statement for sproc
strWhere = strWhere & ","
xmlParams = Replace(strWhere, "',", "</value>")
xmlParams = "'<values>" & Replace(xmlParams, "'", "<value>") & "</values>'"
parameters = "'" & PublicFunctions.GetRegionForUser() & "', " & xmlParams

cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "CurrentSet015Datasheet" & parameters
cmd.CommandTimeout = 300
Set rs = cmd.Execute
'rs.Filter = "([PropNo] in (" & strWhere & ")) AND (Region LIKE '" & PublicFunctions.GetRegionForUser() & "')"

DoCmd.OpenForm "frm015Datasheet", acFormDS, , , acFormReadOnly, acWindowNormal, strQueried
Set Forms("frm015Datasheet").Recordset = rs
rs.Close
Set rs = Nothing
Set cmd = Nothing


End Function

 

[DeveloperSteve]

I ended up using the method of creating a new form instance to avoid the problem here.