RECORDSET help....

[txgeekgirl]

RecordSets - You love them - you hate them... I need help with them.
I need this code to find the first record so I can fill a form. and then my EU can click EDIT - (unlock the form) and Update the record. I have code for all of that - it's the recordset giving me something to work with I do not have.

Sub TypeCB_AfterUpdate()
   Dim rst As Object
   Dim con As Object
   Dim mySql As String
   
   Set con = Application.CurrentProject.Connection
   mySql = "SELECT * FROM [Records_Disposition]" + _
            "WHERE [Type] = " & Me!TypeCB + ";"
   Set rst = CreateObject("ADODB.Recordset")
   rst.Open mySql, con, adOpenKeyset
   
   rst.FindFirst
   
   MsgBox mySql
    ' Close the recordset and the database.
    rs.Close
    
   
   MsgBox "one"
   
   If rst.NoMatch Then
    warning "No matching records", "TypeCB_AfterUpdate"
    Set rs = Nothing
    Set con = Nothing
   End If
   
End Sub

 

[pbaldy]

What's the problem/error? For starters, you won't end up with a space between the SQL string on the first line and the second. Also, I would use "&" to concatenate. Third, you're opening the recordset but not doing anything with it. I'd expect:

Me.TextboxName = rst!FieldName

 

[txgeekgirl]

Still getting Nada.

 Sub TypeCB_AfterUpdate()
   Dim rst As Object
   Dim con As Object
   Dim mySql As String
   
   Set con = Application.CurrentProject.Connection
   mySql = "SELECT * FROM [Records_Disposition]" + _
            " WHERE [Type] = " & Me!TypeCB & ";"
   Set rst = CreateObject("ADODB.Recordset")
   rst.Open mySql, con, adOpenKeyset
   
   MsgBox "one"
   
   rst.FindFirst
   Me.CaseNotxt = rst!ClientID
   
'Close the recordset and the database.
    rs.Close

P.S. JG is who we should all aspire to be - the alternative is what we have.

 

[DJkarl]

Is [Type] a text field? If so add some single quotes to your SQL.

" WHERE [Type] = '" & Me!TypeCB & "';"

 

[boblarson]

And this:

rst.FindFirst

is extraneous. It isn't doing anything.

 

[txgeekgirl]

DJKarl - That did it. Thanks -

Is there a way to have it load records into this form?

 

[txgeekgirl]

I actually wrote a query so they could find the specific record they needed to edit. The query runs but it doesn't do anything - not even fill a line. So I thought having it do a record search would be better.

SELECT Records_Disposition.Type, Records_Disposition.ClientID, Records_Disposition.Pages_NoSec, Records_Disposition.Section_1, Records_Disposition.Section_2, Records_Disposition.Section_3, Records_Disposition.Section_4, Records_Disposition.Section_5, Records_Disposition.Section_6, Records_Disposition.Section_7, Records_Disposition.Section_8, Records_Disposition.Section_9, Records_Disposition.Section_10, Records_Disposition.Section_11, Records_Disposition.Section_12, Records_Disposition.Section_13, Records_Disposition.Section_14, Records_Disposition.Section_15, Records_Disposition.Discharged, Records_Disposition.ScanDate, Records_Disposition.Labeled, Records_Disposition.Shred, Records_Disposition.Retrieved, Records_Disposition.AuthorizingStaff
FROM Records_Disposition
WHERE (((Records_Disposition.Type)=[forms]![Search_Records]![TypeCB]) AND ((Records_Disposition.ClientID)=[Forms]![Search_Records]![CaseNoTxt]));

 

[DJkarl]

Well if you are using an unbound form you can just write the statements like

Me!Textboxname = rst!fieldname

for each control and field.

Or if the control names are the same as the source data you could set the recordsource property of the form to your SQL statement, or set the recordsource property to the table and use filter criteria to show the record(s) in question.