Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need better option than Dlookup 1

Status
Not open for further replies.
burgerman

burgerman

Technical User
Sep 21, 2002
31
0
0
US
Here’s my delima. I have a form that has 84 text boxes that are populated with information from one table. I have successfully pulled the data from the table using the dlookup function but it was very time consuming and it takes the form over 30 seconds to load . There has to be a better way to do this I’m just new at using VBA. Thanks

Heres the Code I used Mind you it is lengthy:
Dim sixamlab As Currency, sixamemp As Integer, sixamsales As Integer, sixamManHours As Integer
Dim sevenamlab As Currency, sevenamemp As Integer, sevenamsales As Integer, sevenamManHours As Integer
Dim eightamlab As Currency, eightamemp As Integer, eightamsales As Integer, eightamManHours As Integer
Dim nineamlab As Currency, nineamemp As Integer, nineamsales As Integer, nineamManHours As Integer
Dim tenamlab As Currency, tenamemp As Integer, tenamsales As Integer, tenamManHours As Integer
Dim elevenamlab As Currency, elevenamemp As Integer, elevenamsales As Integer, elevenamManHours As Integer
Dim twelvepmlab As Currency, twelvepmemp As Integer, twelvepmsales As Integer, twelvepmManHours As Integer
Dim onepmlab As Currency, onepmemp As Integer, onepmsales As Integer, onepmManHours As Integer
Dim twopmlab As Currency, twopmemp As Integer, twopmsales As Integer, twopmManHours As Integer
Dim threepmlab As Currency, threepmemp As Integer, threepmsales As Integer, threepmManHours As Integer
Dim fourpmlab As Currency, fourpmemp As Integer, fourpmsales As Integer, fourpmManHours As Integer
Dim fivepmlab As Currency, fivepmemp As Integer, fivepmsales As Integer, fivepmManHours As Integer
Dim sixpmlab As Currency, sixpmemp As Integer, sixpmsales As Integer, sixpmManHours As Integer
Dim sevenpmlab As Currency, sevenpmemp As Integer, sevenpmsales As Integer, sevenpmManHours As Integer
Dim eightpmlab As Currency, eightpmemp As Integer, eightpmsales As Integer, eightpmManHours As Integer
Dim ninepmlab As Currency, ninepmemp As Integer, ninepmsales As Integer, ninepmManHours As Integer
Dim tenpmlab As Currency, tenpmemp As Integer, tenpmsales As Integer, tenpmManHours As Integer
Dim elevenpmlab As Currency, elevenpmemp As Integer, elevenpmsales As Integer, elevenpmManHours As Integer
Dim twelveamlab As Currency, twelveamemp As Integer, twelveamsales As Integer, twelveamManHours As Integer
Dim oneamlab As Currency, oneamemp As Integer, oneamsales As Integer, oneamManHours As Integer
Dim twoamlab As Currency, twoamemp As Integer, twoamsales As Integer, twoamManHours As Integer


'6 Am
sixamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='06:00'")
sixamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='06:00'")
sixamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='06:00'")
sixamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='06:00'")
'sevenam
sevenamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='07:00'")
sevenamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='07:00'")
sevenamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='07:00'")
sevenamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='07:00'")
'eightam
eightamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='08:00'")
eightamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='08:00'")
eightamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='08:00'")
eightamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='08:00'")
'nineam
nineamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='09:00'")
nineamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='09:00'")
nineamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='09:00'")
nineamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='09:00'")
'tenam
tenamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='10:00'")
tenamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='10:00'")
tenamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='10:00'")
tenamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='10:00'")
'elevenam
elevenamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='11:00'")
elevenamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='11:00'")
elevenamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='11:00'")
elevenamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='11:00'")
'twelvepm
twelvepmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='12:00'")
twelvepmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='12:00'")
twelvepmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='12:00'")
twelvepmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='12:00'")
'onepm
onepmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='13:00'")
onepmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='13:00'")
onepmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='13:00'")
onepmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='13:00'")
'twopm
twopmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='14:00'")
twopmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='14:00'")
twopmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='14:00'")
twopmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='14:00'")

'threepm
threepmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='15:00'")
threepmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='15:00'")
threepmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='15:00'")
threepmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='15:00'")
'fourpm
fourpmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='16:00'")
fourpmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='16:00'")
fourpmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='16:00'")
fourpmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='16:00'")
'fivepm
fivepmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='17:00'")
fivepmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='17:00'")
fivepmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='17:00'")
fivepmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='17:00'")
'sixpm
sixpmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='18:00'")
sixpmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='18:00'")
sixpmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='18:00'")
sixpmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='18:00'")
'sevenpm
sevenpmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='19:00'")
sevenpmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='19:00'")
sevenpmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='19:00'")
sevenpmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='19:00'")

'eightpm
eightpmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='20:00'")
eightpmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='20:00'")
eightpmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='20:00'")
eightpmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='20:00'")
'ninepm
ninepmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='21:00'")
ninepmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='21:00'")
ninepmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='21:00'")
ninepmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='21:00'")
'tenpm
tenpmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='22:00'")
tenpmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='22:00'")
tenpmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='22:00'")
tenpmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='22:00'")
'elevenpm
elevenpmlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='23:00'")
elevenpmemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='23:00'")
elevenpmsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='23:00'")
elevenpmManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='23:00'")
'twelveam
twelveamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='00:00'")
twelveamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='00:00'")
twelveamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='00:00'")
twelveamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='00:00'")
'oneam
oneamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='01:00'")
oneamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='01:00'")
oneamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='01:00'")
oneamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='01:00'")
'twoam
twoamlab = DLookup("[Tot_Wages]", "Sorted Hourly Sales", "[Hour]='02:00'")
twoamemp = DLookup("[Tot_Num_Employees]", "Sorted Hourly Sales", "[Hour]='02:00'")
twoamsales = DLookup("[Tot_Net_Sales]", "Sorted Hourly Sales", "[Hour]='02:00'")
twoamManHours = DLookup("[ManHours]", "Sorted Hourly Sales", "[Hour]='02:00'")

[Text1] = sixamlab
[Text2] = sixamemp
[Text3] = sixamsales
[Text4] = sixamManHours
[Text5] = sevenamlab
[Text6] = sevenamemp
[Text7] = sevenamsales
[Text8] = sevenamManHours
[Text9] = eightamlab
[Text10] = eightamemp
[Text11] = eightamsales
[Text12] = eightamManHours
[Text13] = nineamlab
[Text14] = nineamemp
[Text15] = nineamsales
[Text16] = nineamManHours
[Text17] = tenamlab
[Text18] = tenamemp
[Text19] = tenamsales
[Text20] = tenamManHours
[Text21] = elevenamlab
[Text22] = elevenamemp
[Text23] = elevenamsales
[Text24] = elevenamManHours
[Text25] = twelvepmlab
[Text26] = twelvepmemp
[Text27] = twelvepmsales
[Text28] = twelvepmManHours
[Text29] = onepmlab
[Text30] = onepmemp
[Text31] = onepmsales
[Text32] = onepmManHours
[Text33] = twopmlab
[Text34] = twopmemp
[Text35] = twopmsales
[Text36] = twopmManHours
[Text37] = threepmlab
[Text38] = threepmemp
[Text39] = threepmsales
[Text40] = threepmManHours
[Text41] = fourpmlab
[Text42] = fourpmemp
[Text43] = fourpmsales
[Text44] = fourpmManHours
[Text45] = fivepmlab
[Text46] = fivepmemp
[Text47] = fivepmsales
[Text48] = fivepmManHours
[Text49] = sixpmlab
[Text50] = sixpmemp
[Text51] = sixpmsales
[Text52] = sixpmManHours
[Text53] = sevenpmlab
[Text54] = sevenpmemp
[Text55] = sevenpmsales
[Text56] = sevenpmManHours
[Text57] = eightpmlab
[Text58] = eightpmemp
[Text59] = eightpmsales
[Text60] = eightpmManHours
[Text61] = ninepmlab
[Text62] = ninepmemp
[Text63] = ninepmsales
[Text64] = ninepmManHours
[Text65] = tenpmlab
[Text66] = tenpmemp
[Text67] = tenpmsales
[Text68] = tenpmManHours
[Text69] = elevenpmlab
[Text70] = elevenpmemp
[Text71] = elevenpmsales
[Text72] = elevenpmManHours

[Text73] = twelveamlab
[Text74] = twelveamemp
[Text75] = twelveamsales
[Text76] = twelveamManHours
[Text77] = oneamlab
[Text78] = oneamemp
[Text79] = oneamsales
[Text80] = oneamManHours
[Text81] = twoamlab
[Text82] = twoamemp
[Text83] = twoamsales
[Text84] = twoamManHours
 
Sort by date Sort by votes
Try something like:

Code: 
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='06:00'")
If Not rst.EOF
    [Text1] = rst![Tot_Wages]
    [Text2] = rst![Tot_Num_Employees]
    [Text3] = rst![Tot_Net_Sales]
    [Text4] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='07:00'")
If Not rst.EOF
    [Text5] = rst![Tot_Wages]
    [Text6] = rst![Tot_Num_Employees]
    [Text7] = rst![Tot_Net_Sales]
    [Text8] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='08:00'")
If Not rst.EOF
    [Text9] = rst![Tot_Wages]
    [Text10] = rst![Tot_Num_Employees]
    [Text11] = rst![Tot_Net_Sales]
    [Text12] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='09:00'")
If Not rst.EOF
    [Text13] = rst![Tot_Wages]
    [Text14] = rst![Tot_Num_Employees]
    [Text15] = rst![Tot_Net_Sales]
    [Text16] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='10:00'")
If Not rst.EOF
    [Text17] = rst![Tot_Wages]
    [Text18] = rst![Tot_Num_Employees]
    [Text19] = rst![Tot_Net_Sales]
    [Text20] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='11:00'")
If Not rst.EOF
    [Text21] = rst![Tot_Wages]
    [Text22] = rst![Tot_Num_Employees]
    [Text23] = rst![Tot_Net_Sales]
    [Text24] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='12:00'")
If Not rst.EOF
    [Text25] = rst![Tot_Wages]
    [Text26] = rst![Tot_Num_Employees]
    [Text27] = rst![Tot_Net_Sales]
    [Text28] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='13:00'")
If Not rst.EOF
    [Text29] = rst![Tot_Wages]
    [Text30] = rst![Tot_Num_Employees]
    [Text31] = rst![Tot_Net_Sales]
    [Text32] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='14:00'")
If Not rst.EOF
    [Text33] = rst![Tot_Wages]
    [Text34] = rst![Tot_Num_Employees]
    [Text35] = rst![Tot_Net_Sales]
    [Text36] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='15:00'")
If Not rst.EOF
    [Text37] = rst![Tot_Wages]
    [Text38] = rst![Tot_Num_Employees]
    [Text39] = rst![Tot_Net_Sales]
    [Text40] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='16:00'")
If Not rst.EOF
    [Text41] = rst![Tot_Wages]
    [Text42] = rst![Tot_Num_Employees]
    [Text43] = rst![Tot_Net_Sales]
    [Text44] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='17:00'")
If Not rst.EOF
    [Text45] = rst![Tot_Wages]
    [Text46] = rst![Tot_Num_Employees]
    [Text47] = rst![Tot_Net_Sales]
    [Text48] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='18:00'")
If Not rst.EOF
    [Text49] = rst![Tot_Wages]
    [Text50] = rst![Tot_Num_Employees]
    [Text51] = rst![Tot_Net_Sales]
    [Text52] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='19:00'")
If Not rst.EOF
    [Text53] = rst![Tot_Wages]
    [Text54] = rst![Tot_Num_Employees]
    [Text55] = rst![Tot_Net_Sales]
    [Text56] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='20:00'")
If Not rst.EOF
    [Text57] = rst![Tot_Wages]
    [Text58] = rst![Tot_Num_Employees]
    [Text59] = rst![Tot_Net_Sales]
    [Text60] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='21:00'")
If Not rst.EOF
    [Text61] = rst![Tot_Wages]
    [Text62] = rst![Tot_Num_Employees]
    [Text63] = rst![Tot_Net_Sales]
    [Text64] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='22:00'")
If Not rst.EOF
    [Text65] = rst![Tot_Wages]
    [Text66] = rst![Tot_Num_Employees]
    [Text67] = rst![Tot_Net_Sales]
    [Text68] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='23:00'")
If Not rst.EOF
    [Text69] = rst![Tot_Wages]
    [Text70] = rst![Tot_Num_Employees]
    [Text71] = rst![Tot_Net_Sales]
    [Text72] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='00:00'")
If Not rst.EOF
    [Text73] = rst![Tot_Wages]
    [Text74] = rst![Tot_Num_Employees]
    [Text75] = rst![Tot_Net_Sales]
    [Text76] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='01:00'")
If Not rst.EOF
    [Text77] = rst![Tot_Wages]
    [Text78] = rst![Tot_Num_Employees]
    [Text79] = rst![Tot_Net_Sales]
    [Text80] = rst![ManHours]
End If
Set rst = dbs.OpenRecordset("SELECT * FROM [Sorted Hourly Sales] WHERE [Hour]='02:00'")
If Not rst.EOF
    [Text81] = rst![Tot_Wages]
    [Text82] = rst![Tot_Num_Employees]
    [Text83] = rst![Tot_Net_Sales]
    [Text84] = rst![ManHours]
End If
rst.Close
dbs.Close

This should fly in comparison to your numerous DLookups.

HTH. [pc2]
 
Upvote 0 Downvote
That did it....
much better Thank you very much
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Upgrading from Access 2013 - whats the best option
Replies
2
Views
143
Moss100
Moss100
atadbitslow
  • Locked
  • Question
need help with syntax for a date
Replies
3
Views
71
dhookom
dhookom
meagain
  • Locked
  • Question
Automatic Option Setup
Replies
2
Views
51
strongm
strongm
marnb
  • Locked
  • Question
I need outlook to send out an email
Replies
2
Views
81
dhookom
dhookom
qqp
  • Locked
  • Question
Need my msgbox that warns user of missing criteria to stop the rest of the code from running
Replies
2
Views
68
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top