Rob,
Thanks for the explanation of Recordsets, I am begining to understand them.
Once I have the code that will extract the relevant recordsets, how in my case would I show that in a form?
As I understand that each textbox would have to be unbound, how would you assign the correct textbox to the relevant field from the recordset?
From the way I have been taught I would build the query on the recordsource on the form and then assign to each textbox.
Jez

Jez,

There are two ways in which you can implement a solution to your problem:
• Produce a Query "Shell" that maps EXACTLY to your recordset (they each have the same field names etc.)
  and then set the Forms Recordset property to your newly-created Recordset - All the controls will be
  bound to the names specified in the query, and since the names are the same, it will source the data.
  I'm not too sure how this will affect updating the data (I never use this method).
• The other method, which I use is to set the controls values once the Recordset has been opened.
  I will normally have a private method similar to this:
And then whenever the data is updated, call SyncControlsToRS, and all your control's value will be updated.
I then do the inverse to save the data.

I hope this helps,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Rob, I'm getting there slowly...

So would this then fit in (in the case from the RSDemo from earlier) after..
Do While Not rs.EOF
   Debug.Print rs.fields("Test")
Loop
Call SyncControlsToRS
Set cnn = nothing
set rs = nothing
End Sub

I am not clear on how you would send it back to the data though

Jez

Jez,

I would strongly suggest writing any code from scratch that you want to query.
I say this mainly because it will not only reinforce your learning (its all to
easy to forget what "was done earlier") why not come up with the code to produce
a Recordset for your form, and then ask if you get stuck anywhere along the way.
This way you get some of your work done, and learn what you need to know in the
process =)

With regards to this, you simply open up a Recordset, but make sure you select
the appropriate record in the SQL. e.g. "SELECT * FROM ATable WHERE ID=4" you
then simply replace the fields values with the controls values.
e.g. rs.Fields("AValue") = Nz(ControlWithValue.Value, NullValue).

Note:Note the use of the Nz() Function. This should always be used when
referencing controls on a Form, or an "Invalid Use of Null" error will be thrown.
Nz() will return either the controls value, or a value of your choosing if the
control is Null.

I hope this gives you some food for thought, as with most learning experiences
its normally best to just "have a go" - unless of course you work with explosives,
be careful there... =)

Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Rob,
Back on the trail of Recordsets. I am still a little unsure of them.
This code below is for a form which is to used as a Search box. I am connecting to the BackEndDB to retrieve data from tblCSATAddress to connect with the list box on the form. But I am still confused as to how the recordset thing applies.

This code here works as a SQL query and pulls 313 records which correct, but apply it with the rest of the code and I cant get it to pull any records. No error messages, just a blank form that pops up.

How can I be sure that I have connected to the DB?

Private Sub Form_Load()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sQRY As String
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;User Id=Admin; " & _
        "Data Source=" & cTables
    sQRY = _
        "SELECT " & vbCrLf & _
        "tblCSATAddress.CSATNumber, tblCSATAddress.JobNumber AS [Job Number], " & vbCrLf & _
        "tblCSATAddress.Contract, tblCSATAddress.Address, " & vbCrLf & _
        "tblCSATBusinessType.Description AS [Business Unit], tblCSATAddress.Engineer " & vbCrLf & _
        "FROM tblCSATAddress INNER JOIN tblCSATBusinessType ON tblCSATAddress.BusinessType = tblCSATBusinessType.BusinessType " & vbCrLf & _
        "WHERE tblCSATAddress.InputFlag= 0 AND tblCSATAddress.CSATNumber <> 1"
    rs.CursorLocation = adUseClient
    rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
    With Me
            lstSearch.RowSource = "tblCSATAddress IN IN '" & cTables & "' "
            If Me.lstSearch.ListCount <> 0 And Me.lstSearch.ListCount < 20 Then
                Me.lblNumRecs.Caption = "Number Of Address " + CStr((Me.lstSearch.ListCount) - 1)
            Else
                Me.lblNumRecs.Caption = "Number Of Address " + CStr((Me.lstSearch.ListCount))
            End If
           .lstSearch.SetFocus
        End With
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    Exit Sub
End Sub

Regards,
Jez

Hi Jez,

So, just so I am clear, the Recordset is being produced and the Search results are being populated in the list correctly, but you are unsure how to send the information from the search form to your main form??

Or have I misunderstood?

Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Afternoon Rob,
I assume that the recordset is being produced, as when I run the code there are no error messages. It doesnt seem to link it to the list box on the form though, and also doesnt count the number of records.
But I built a query in the BackEnd DB same as the SQL in the code and there are 313 records so I know it not that there are zero records to query.

I thought I would try and put in a MsgBox after the lines

rs.CursorLocation = adUseClient
rs.Open sQRY, cnn, adOpenKeyset, adLockReadOnly
MsgBox rs.RecordCount

When running the form again it popped up with 313 in the MsgBox, so I know that its connecting to the BackEndDB now, its just how I assign the code to the list box I am struggling with, How is this possible?

Jez

Jez,

Great, the "hard" work is done, you have your data.
Now simply put: Easy =)
You will need to alter the properties of the Listbox to display the data though, such as using Column Headers and such if required.

Regards,
Rob

<center>"Nothing can stop the man with the right mental attitude from achieving his goal;
nothing on earth can help the man with the wrong mental attitude".
Thomas Jefferson</center>

Rob,

Excellent, glad am getting there, its totally different from what I've done in the past.

Makes it a good start to the week when it works :-)

No doubt I will be back :-)
Regards,
Jez