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

Using a recordset instead of DLookup

P: 62
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. Dim MyDatabase As DAO.Database
  3. Dim rstPeople As DAO.Recordset
  4.  
  5.     Set MyDatabase = CurrentDb
  6.     Set rstPeople = MyDatabase.OpenRecordset("SELECT q_People.Department, q_People.Office FROM q_People WHERE People_ID = " & [People_ID])
  7.         Me.Department = rstPeople!Department
  8.         Me.Office = rstPeople!Office
  9.     rstPeople.Close
  10.     Set rstPeople = Nothing
  11. End Sub
  12.  
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
Aug 31 '12 #1

✓ answered by 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.

Share this Question
Share on Google+
10 Replies


Rabbit
Expert Mod 10K+
P: 12,366
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.
Aug 31 '12 #2

P: 62
@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
Aug 31 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
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.
Aug 31 '12 #4

P: 62
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...

Expand|Select|Wrap|Line Numbers
  1.  
  2. With rstPeople
  3.         Me.Department = rstPeople!Department
  4.         Me.Office = rstPeople!Office
  5.     End With
Thanks so much for your help! Sorry for being such a nube.
Aug 31 '12 #5

P: 62
@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!
Aug 31 '12 #6

Rabbit
Expert Mod 10K+
P: 12,366
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.
Aug 31 '12 #7

P: 62
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. Dim MyDatabase As DAO.Database
  4. Dim rstPeople As DAO.Recordset
  5.  
  6. Set MyDatabase= CurrentDb
  7. Set rstPeople = MyDatabase.OpenRecordset("SELECT q_People.Person_ID, q_People.Department, q_People.office FROM q_People")
  8. 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:

Expand|Select|Wrap|Line Numbers
  1. With rstPeople
  2.       Debug.Print "Dynamic-type recordset: " & .Name
  3.  
  4.    '  Enumerate records
  5.       Do While Not .EOF
  6.          Debug.Print !Person_ID
  7.          Debug.Print !Department
  8.          Debug.Print !Office
  9.          .MoveNext
  10.       Loop
  11.  
  12.       .Close
  13. End With

The OnCurrent event of the form is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3. With rstPeople
  4.          Me.Department = rstPeople!Department
  5.          Me.Office = rstPeople!Office
  6.      End With
  7.  
  8. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.  
  3. Set rstPeople = Nothing
  4. rstPeople .Close
  5.  
  6. End Sub
Please let me know if you need more information.


Thanks again for all your help :-)
Aug 31 '12 #8

Rabbit
Expert Mod 10K+
P: 12,366
I was right, you didn't declare your variables globally.
Expand|Select|Wrap|Line Numbers
  1. Dim rstPeople As DAO.Recordset 
  2.  
  3. Private Sub Form_Open(Cancel As Integer) 
  4. Dim MyDatabase As DAO.Database   
  5. Set MyDatabase= CurrentDb 
  6. Set rstPeople = MyDatabase.OpenRecordset("SELECT q_People.Person_ID, q_People.Department, q_People.office FROM q_People") 
  7. 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.
Aug 31 '12 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Aug 31 '12 #10

P: 62
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT q_People.Person_ID, q_People.Display_NM, q_People.Office, q_People.Department 
  2. FROM q_People 
  3. ORDER BY q_People.Display_NM; 
I then set the Control Source of the unbound Office and Department fields respectively as follows:

Expand|Select|Wrap|Line Numbers
  1. =[PersonName_DD].[column](2)
  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
Sep 4 '12 #11

Post your reply

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