By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 816 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

binding a form to a recordset returns an empty recordset

P: 5
Hello,

I'm a bit of a newbie when it comes to Access (and DB's in general).

I've got a form that allows the assembles a SQL string (that I've tested interactively, and proven that it returns the correct records), then passes it to another form in OpenArgs using the DoCmd.OpenForm.

I've verified that the newly opened form receives the SQL string, and re-tested the SQL statement to verify that that's all good. However, the recordset I'm generating contains zero records, instead of the correct few hundred that are returned if I manually plug the string into a query.

I'm mystified as to what it is I'm missing / doing wrong. Any help would be greatly appreciated.

Here's the code from the newly opened form...

******

Private Sub Form_Open(Cancel As Integer)

Dim cnnIO As New ADODB.Connection
Dim rstIO As New ADODB.Recordset

On Error GoTo CatchErrors

' connect to the database
Set cnnIO = CurrentProject.Connection

' open the recordset based on that assembled SQL string passed in the OpenArgs property
Set rstIO = New Recordset


With rstIO
Set .ActiveConnection = cnnIO
Debug.Print Me.OpenArgs
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open Me.OpenArgs
End With

'bind the controls to the open recordset
Set Me.Recordset = rstIO
Debug.Print rstIO.RecordCount

Me.txtIO_ID.ControlSource = "IOidsIO_ID"
'only one control bound for testing purposes

rstIO.Close
cnnIO.Close

Set rstIO = Nothing
Set cnnIO = Nothing

cmdOK_Click_exit:

Exit Sub

CatchErrors:

MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume cmdOK_Click_exit

End Sub
Oct 27 '06 #1
Share this Question
Share on Google+
5 Replies


100+
P: 143
Post the SQL. Although you have tested it, there may be something
Oct 27 '06 #2

NeoPa
Expert Mod 15k+
P: 31,342
' connect to the database
Set cnnIO = CurrentProject.Connection

' open the recordset based on that assembled SQL string passed in the OpenArgs property
Set rstIO = New Recordset


With rstIO
Set .ActiveConnection = cnnIO
Debug.Print Me.OpenArgs
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseServer
.Open Me.OpenArgs
End With
Maybe I'm missing something here, but there doesn't appear to be any code which assigns the SQL string to the recordset object.

There is a comment about it, but no code as far as I can see.
Oct 27 '06 #3

P: 5
Here's the SQL string..

SELECT [tblIO].[IOidsIO_ID], [tblIO].[IOtxtSource_LRU], SourceATA.LRUtxtATA, [tblIO].[IOtxtSink_LRU], qryLRUCopy.LRUtxtATA, [tblIO].[IOtxtSignalMeaning]
FROM (tblIO INNER JOIN tblLRU AS SourceATA
ON [tblIO].[IOtxtSource_LRU]=SourceATA.LRUtxtName) INNER JOIN qryLRUCopy
ON [tblIO].[IOtxtSink_LRU]=qryLRUCopy.LRUtxtName
WHERE SourceATA.LRUtxtATA Like '25*' And qryLRUCopy.LRUtxtATA Like '25*' Or SourceATA.LRUtxtATA Like '25*' And qryLRUCopy.LRUtxtATA Not Like '25*' Or SourceATA.LRUtxtATA Not Like '25*' And qryLRUCopy.LRUtxtATA Like '25*'
ORDER BY [tblIO].[IOtxtSource_LRU], [tblIO].[IOtxtSink_LRU];

As I said, I've verfied that it works when manually pasted into a query..

I'm under the impression that the
rst.Open Me.OpenArgs
line assigns that SQL string to the recordset (as the SQL string is passed into the form using OpenArgs - I've verified that the OpenArgs parameter of the newly opened form correctly contains the string.
Oct 30 '06 #4

Andrew Thackray
P: 76
The simplest method is simply to set the forms recordsource property to the SQL string on loading the form

Expand|Select|Wrap|Line Numbers
  1.  
  2.   me.Recordsource = SQLstring
  3.  
  4.  
This automatically causes the forms requery event which will load the forms recordset with the desired records.

If the form is already loaded and you want to change its binding on the fly from another form use

Expand|Select|Wrap|Line Numbers
  1.  
  2.  Forms("formname").recordsource = SQLstring
  3.  
  4.  
Oct 30 '06 #5

P: 5
The simplest method is simply to set the forms recordsource property to the SQL string on loading the form

Expand|Select|Wrap|Line Numbers
  1.  
  2.   me.Recordsource = SQLstring
  3.  
  4.  
This automatically causes the forms requery event which will load the forms recordset with the desired records.

If the form is already loaded and you want to change its binding on the fly from another form use

Expand|Select|Wrap|Line Numbers
  1.  
  2.  Forms("formname").recordsource = SQLstring
  3.  
  4.  
That's worked like a charm - thank you very much!

I'm still curious as to why it wasn't working before, but I can now get on - cheers!
Oct 30 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.