Using a recordset instead of DLookup

[Sandra Walsh]

Hello -

Summary:
-I have an ODBC Connection to a People table. This table is in our CRM system and the connection is read-only.

-I have a set of queries against this People table ending in a final UNION query called q_People that pulls the Persons_ID, Name, Department and Office.

-My local table contains the Person_ID field

-I can't include the UNION query in my form's Record Source as this makes the record not updateable.

-I want to display the persons Department and Office on my form, based on the Person_ID

I have 2 Unbound fields on my form named Department and Office

I have the following Form_Current event:

Private Sub Form_Current()
Dim MyDatabase As DAO.Database
Dim rstPeople As DAO.Recordset
 
    Set MyDatabase = CurrentDb
    Set rstPeople = MyDatabase.OpenRecordset("SELECT q_People.Department, q_People.Office FROM q_People WHERE People_ID = " & [People_ID])
        Me.Department = rstPeople!Department
        Me.Office = rstPeople!Office
    rstPeople.Close
    Set rstPeople = Nothing
End Sub
 

This populates the fields correctly on my form but the form takes several seconds to calculate each time I move fron one record to another.

QUESTION:

Would the data on the form update faster if I generate the record set just once when the form is opened? I would then use similar code as above to refer to an existing recordset and avoid having to generate the recordset each time the user views a new record.

I have tried to do this a couple of ways but am not able to get it to work.

Many thanks,
Sandra

[Rabbit]

This looks more like an Access VBA question than a VB 4/5/6 question.

You mention DLookUp in the title but you didn't say anything about it in the post.

Yes, you can generate the recordset once with all the people and then just search the recordset when you need to.

[Sandra Walsh]

@Rabbit
Thank you for your reply.

My apologies - I did not realize I was in the wrong section.

I will repost under Access VBA and clarify the title.

Sandra

[Rabbit]

There's no need to recreate it, I can move the thread for you. My responses to your inquiry still stands though. Because I was answering off the assumption that it was in Access.

[Sandra Walsh]

Thank you Rabbit.

Next Question:

I have set up the recordset when the form is opened. I am now trying to figure out the syntax for the onCurrent event and am getting an Object Requried error...

 
With rstPeople
        Me.Department = rstPeople!Department
        Me.Office = rstPeople!Office
    End With

Thanks so much for your help! Sorry for being such a nube.

[Sandra Walsh]

@Rabbit
Thanks for moving the thread.

How do I edit the title? If you can do it, it should read something like ...

"How to reference fields in an existing RecordSet"

Thanks again!

[Rabbit]

I believe this all falls under the original question so I will the the title as is. I still don't know why you just don't use DLookup.

We would need to see all the code related to the recordset to diagnose the problem. I suspect that you may have set up the recordset in the form's open or load event, but you failed to declare the variables globally. But that is only a guess and I don't know why you're getting the error unless I can see all the code related to it.

[Sandra Walsh]

The reason I am trying to get away from using DLookup is that is slows the form down considerably as I have to use it for two fields. I am hoping the RecordSet method will speed things up.

Here is the code for the forms OnOpen event:

Private Sub Form_Open(Cancel As Integer)
 
Dim MyDatabase As DAO.Database
Dim rstPeople As DAO.Recordset
 
Set MyDatabase= CurrentDb
Set rstPeople = MyDatabase.OpenRecordset("SELECT q_People.Person_ID, q_People.Department, q_People.office FROM q_People")
End Sub

There fields referenced in this code are all on the main form. I have tested the code and my debug code enumerates the records correctly:

With rstPeople
      Debug.Print "Dynamic-type recordset: " & .Name
 
   '  Enumerate records
      Do While Not .EOF
         Debug.Print !Person_ID
         Debug.Print !Department
         Debug.Print !Office
         .MoveNext
      Loop
 
      .Close
End With

The OnCurrent event of the form is:

Private Sub Form_Current()
 
With rstPeople
         Me.Department = rstPeople!Department
         Me.Office = rstPeople!Office
     End With
 
End Sub

This worked correclty when I generated the recordset as part of the OnCurrent event. Maybe I need to refer to the "pre-generated" recordset differently?

I also have the following OnClose event:

Private Sub Form_Close()
 
Set rstPeople = Nothing
rstPeople .Close
 
End Sub

Please let me know if you need more information.


Thanks again for all your help :-)

[Rabbit]

I was right, you didn't declare your variables globally.

Dim rstPeople As DAO.Recordset 
 
Private Sub Form_Open(Cancel As Integer) 
Dim MyDatabase As DAO.Database   
Set MyDatabase= CurrentDb 
Set rstPeople = MyDatabase.OpenRecordset("SELECT q_People.Person_ID, q_People.Department, q_People.office FROM q_People") 
End Sub

Notice the slight, but key difference, the variable is declared outside the sub. This makes it so the variable can be accessed from other subs and functions on the form.

[TheSmileyCoder]

I think you are trying to solve the wrong problem here, or at least there seems to me, to be a much simpler approach.

Make a combobox cmb_DisplayDepartment, bind it to your local ID_Person, and make its rowsource q_People. Assuming you select the columns in the order listed in your first post (Persons_ID, Name, Department and Office) then set the combobox properties like so:
Column Count: 3
Column Width: 0;0;3cm (or inches)

Similarly for cmb_DisplayOffice:
column count: 4
Column Width: 0;0;0;3cm

Remember to disable the combobox as what it is actually selecting is the person, but it LOOKS like its used to select a department/office.

[Sandra Walsh]

Thanks for the clue - I totally forgot about this simple solution.

Since the values for the Office and Department field are read-only from the existing dataset, I just needed to display those values on the form. I can use a query that includes these fields for reporting purposes since reports do not require an updateable dataset.

I set the row source of the PersonName_DD combo as follows:

SELECT q_People.Person_ID, q_People.Display_NM, q_People.Office, q_People.Department 
FROM q_People 
ORDER BY q_People.Display_NM; 

I then set the Control Source of the unbound Office and Department fields respectively as follows:

=[PersonName_DD].[column](2)
=[PersonName_DD].[column](3)

It takes a couple of seconds the first time you open a record, but then it is instant.

Thanks so much for all your help!

Sandra