Form Recordset in DataSheet View

%3CLINGO-SUB%20id%3D%22lingo-sub-1126227%22%20slang%3D%22en-US%22%3EForm%20Recordset%20in%20DataSheet%20View%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1126227%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20perplexing...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20ADO%20recordsets%20to%20bind%20my%20forms%20to%20an%20SQL%20backend%2C%20and%20this%20is%20all%20going%20very%20well.%26nbsp%3B%20I%20am%20sending%20commands%20up%20to%20SQL%20to%20run%20SP%20and%20getting%20records%20back%20all%20good.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20now%20wanting%20to%20load%20a%20view%20and%20thought%20gee%20why%20cant%20I%20use%20the%20same%20command%20tools%20to%20load%20the%20view...then%20thought%20hold%20on%20do%20I%20even%20need%20to%3F%26nbsp%3B%20Surely%20if%20I%20simply%20send%20the%20SELECT%20statement%20via%20command%20it%20will%20work...and%20it%20does...well%20kind%20of.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I%20have%20is%20a%20table%20of%20jobs.%26nbsp%3B%20The%20jobs%20are%20nominally%20numbered%20101%2C102%2C%20103%20and%20so%20on.%26nbsp%3B%20There%20is%20some%20other%20details%20as%20well%20but%20they%20are%20not%20relevant%20to%20the%20question%20or%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFrom%20Code%20I%20am%20performing%20the%20following%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ECall%20cssFindExistingRecord(%22dbo.ActiveTemp%22%2C%20Me.Form)%0A%0A'Which%20calls%20too%0APublic%20Sub%20cssFindExistingRecord(strTable%20As%20String%2C%20frm%20As%20Form)%0A'Using%20only%20the%20passed%20in%20table%0A'set%20the%20recrodset%20for%20the%20form%0A'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''%20%20%20%20%20%20%20%20%20%20%20%20%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Call%20cssLoad(%22SELECT%20*%20FROM%20%22%20%26amp%3B%20strTable%2C%20frm)%20%20%20'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''%0AEnd%20Sub%0A'Which%20calls%20too%0ADim%20com%20As%20New%20ADODB.Command%0A%20%20%20%20With%20com%20%20%20%0A%0A%20%20%20%20Set%20.ActiveConnection%20%3D%20gcn%0A%20%20%20%20.CommandType%20%3D%20adCmdText%0A%20%20%20%20.CommandText%20%3D%20strCommand%20'Runs%20sp%20with%20parameter%0A%20%20%20%20%0A%20%20%20%20Set%20frm.Recordset%20%3D%20.Execute%20%20%20%0A%20%20%20%20End%20With%0A%20%20%20%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EEffectively%20the%20code%20is%20working%20as%20I%20am%20returning%20a%20record.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20that%20is%20the%20problem.%26nbsp%3B%20I%20am%20returning%20one%20record.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Datasheet%20view%2C%20as%20I%20want%20to%20display%20this%20I%20am%20simply%20returning%20the%20102%20record%2019%20times%2C%20rather%20than%20102%2C%20103%20etc%20to%20121.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20see%20why%20this%20is%3F%26nbsp%3B%20I%20have%20looked%20online%20and%20can%20see%20examples%20on%20calling%20SP%2C%20but%20not%20using%20this%20method%20to%20simply%20return%20all%20the%20records.%26nbsp%3B%20The%20weird%20part%20to%20my%20mind%20is%20this%20method%20works%20to%20display%20records%20in%20subforms%20returned%20from%20SP.%26nbsp%3B%20So%20why%20not%20in%20this%20format%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1126227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3E2016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAccess%20Web%20Database%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Occasional Contributor

This is perplexing...

 

I am using ADO recordsets to bind my forms to an SQL backend, and this is all going very well.  I am sending commands up to SQL to run SP and getting records back all good.  

 

I am now wanting to load a view and thought gee why cant I use the same command tools to load the view...then thought hold on do I even need to?  Surely if I simply send the SELECT statement via command it will work...and it does...well kind of.

 

What I have is a table of jobs.  The jobs are nominally numbered 101,102, 103 and so on.  There is some other details as well but they are not relevant to the question or issue.

 

From Code I am performing the following:

 

Call cssFindExistingRecord("dbo.ActiveTemp", Me.Form)

'Which calls too
Public Sub cssFindExistingRecord(strTable As String, frm As Form)
'Using only the passed in table
'set the recrodset for the form
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''            
                Call cssLoad("SELECT * FROM " & strTable, frm)   '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
'Which calls too
Dim com As New ADODB.Command
    With com   

    Set .ActiveConnection = gcn
    .CommandType = adCmdText
    .CommandText = strCommand 'Runs sp with parameter
    
    Set frm.Recordset = .Execute   
    End With
    

 

Effectively the code is working as I am returning a record.

 

However that is the problem.  I am returning one record.

 

In Datasheet view, as I want to display this I am simply returning the 102 record 19 times, rather than 102, 103 etc to 121.

 

I don't see why this is?  I have looked online and can see examples on calling SP, but not using this method to simply return all the records.  The weird part to my mind is this method works to display records in datasheet views in subforms returned from SP.  So why not in this format?

0 Replies