RecordSet, Find, Easy and Simple??? Help :)

[Randix]

Well, I was trying to keep this easy, but since I only know 49.3% of what I'm doing...

Say for example I have a form called "test" and it contains a field called "test_field" from a table called "test". Then say I have another table called "staff_relations" which contains several fields, one specifically called "process_owner" and the other called "director". the two tables are not related.

What I was trying to do, in a very simple way, was after "test_field" was updated with whatever, to put in code using recordset, to open up the "staff_relations" table, search for specific text in the "process_owner" field, and when it finds it (assume there is only 1 occurrence of it), to replace the value of "test_field" with the value of "director" in that same record in the "staff_relations" table. So I did:

Private Sub test_field_AfterUpdate()
Dim rst As Recordset
Dim db As Database
Set rst = db.OpenRecordset("SELECT * FROM staff_relations WHERE [Process_Owner] = Baird, Sean")
If rst.RecordCount > 0 Then
Let Form!test.test_field = rst.Fields(5).Value
End If
rst.Close
End Sub

It doesn't work...what am I missing?

 

[chrismcbride]

Randix
You are not all that far off, I think. Although I am not sure about some of the logic of the process. But in order to get the below procedure to do what you want, you should/could change the following...

1. I usually use a string variable to hold whatever SQL that I want to pass to OpenRecordset. So add Dim strSql as String to you declarations.

2. Add --- strSql = "SELECT * FROM staff_relations WHERE [Process_Owner] = 'Baird, Sean'" --- before the Set db = line.

Note that in order to put a literal string value into a sql string, you must enclose it in single quotes. This is true for variables holding string values as well, however in the case of a variable you must break the string, insert the variable and start the string again. All this must be done while still enclosing the single quotes that designate the variable as a string within the double quotes the form the body of the SQL string. Sooooooo, the sql with a variable would look like...

strSql = "SELECT * FROM staff_relations WHERE [Process_Owner] = '" & me.FeildName & "';"

3. I always put the if rst.recordcount line in before doing anything with the recordset as this prevents lots of potential errors.

4. I never use the Let keyword in the case that I am just inserting values. I do not think it is required and have only used it with property settings in VB. You may refer to the contents of the recordset by the field name of the table that the recordset is derived from. Sooooo, you're next line could be...

me.test_field = rst!FieldFivesName

5. rst.close is inadequate to reclaim the memory that was allocated to the recordset object variable (also the db object vaiable). You must explicitly set each = nothing. Failing to do this will use up system resources, lead to hangs when you try to close the database and contribute greatly to database bloating. The best way to do this is to set up a exit lable that will always be called.

So right under Private Sub...
goes
On Error GoTo EH

Then after all the code in your sub, but before the end sub, goes a lable. The code will fall thru the lable into whatever code is next and execute. After that code goes the error handler label EH. In EH you do whatever you need to do to correct or notifiy the user and then use the resume keyword to move back up to the exit lable. Soooo, the last bit looks like this

End If

SeeYa: --- colon means line lable

Set rst = Nothing
Set db = Nothing
Exit Sub

EH:
msgbox err.number & " - " & err.description

Resume SeeYa


Give it a try and email if you have trouble.
Chris

 

[Abby N]

Looks like you get two replies for the price of one, as Chris hadn't posted when I started writing this.

Hi Randix. You're very close. I only see 2 minor errors with your code. First is the WHERE statement. The criteria needs to be dynamic and enclosed in apostrophes. This code should do it:

Set rst = db.OpenRecordset("SELECT * FROM staff_relations WHERE [Process_Owner] = '" & Me.test_field & "';")

Second, when setting test_field to the value of Director you don't need the 'Let' method. Try this:

Me.test_field = rst.Fields(5).Value

One more thing, while the SQL statement above should work, you can improve performance by having the SELECT statement only return the director field, like this:

Set rst = db.OpenRecordset("SELECT director FROM staff_relations WHERE [Process_Owner] = '" & Me.test_field & "';")

Of course, if you do that you'll also need to change the code that updates the test_field to:

Me.test_field = rst.Fields(1).Value

I hope this helps.

~Abby

[This message has been edited by Abby N (edited 04-05-2001).]

 

[Randix]

Your suggestions were GREAT. Thank you very much!