Replacing Dlookups for editing and speed?

[g4tv4life]

Hey everyone, I have a system for managing radio testing in my area. When a facility is selected from a combo box this code runs

Private Sub Combo10_AfterUpdate()
 Me.Text33.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text32] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text34.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text46] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text35.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text47] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text36.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text48] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text37.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text49] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text38.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text50] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text39.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text51] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text40.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text52] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text41.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text53] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text42.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text54] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text110.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text55] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
 Me.Text109.Value = ELookup("Response", "[Radio Testing]", "Tested = " & [Forms]![Radio Switchboard]![Text56] & " And Facility = " & [Forms]![Radio Switchboard]!Combo10.Column(1))
Me.[Radio Info].Form.Requery
Me.[Radio Info].Form.Refresh
Call ChartMake
End Sub

This code places a yes/no/out of service value into each textbox field then creates a chart using excel to display the results (held in a different procedure called ChartMake)
There are 12 text boxes for each month of the year, 1 test per month so its relatively easy to sort them.

I swapped over to Elookup to try and increase the speed (a function created by Alan Brown which is faster than Dlookup). It helped slightly but do to the fact the database uses
a linked backend over a virtual drive (over the internet x.x) its horrendously slow (around 10 seconds to load the page). Most of that time is in this code from above.

What I want to do is come up with a faster alternative such as a query to insert the values. I would also like to display each value as a combo box and bind them so that they can be
edited if needed. I have the query all set up which brings back the values as I need them.I just need to take each row of the query, and connect it to each combo box.



Any ideas or other methods?

[ranman256]

Use a query. You set the query to whatever values are on the form.
The query will instantly retrieve them all.

[June7]

What do you mean by 'take each row of the query, and connect to each combo box'?

Are you using an unbound form and this code is populating controls on the form?

What is structure of the source table?

10 seconds doesn't seem so bad. I have some complicated reports that take 40 seconds to render with data on local server.

[g4tv4life]

The source table is structured as this:
ID______ Tested ___________ Facility _______________ Response
access id___Date____________ID from another table ______Yes/No/Out of Service

I use a form that loops through and adds each one in as it is above. The Elookup runs when a combo box selects the facility and it grabs result for each month(I also have old data so I have another combo box that changes the date range).

I just want to change it so instead of using Elookup to get each months response, a query takes both of those values to get the range i'm looking for. I just need a way get the query results to the form in a editable way (I also use the elookup values to create charts by exporting each textbox to a cells in excel which

[June7]

There is no looping structure in the posted code, just sequential lines.

The form with 12 controls (1 for each month) is de-normalizing the data structure and that will not allow for direct edits of the source table.

Do you want to accomplish the graphing within Access? I really don't understand how loading this form pertains to graphing the data. The data entry should be unrelated to the graphing. I analyze graphing issues best when I can work with data. If you want to provide db, follow instructions at bottom of my post.