other method than Dlookup vba form (1 Viewer)

Andre Bruin · Jan 21, 2019

Hi,

on a form, I've 800 textfields which are filled with data via a dlookup in VBA.
Because, it's a little bit slow, I want to use another way to fill this fields with data from a table.

See below for the DLookup function what I have at the moment.

code:
Me(k1) = ELookup("[KLant]", "[Planning_tbl02]", "[Plek#]=" & p & " AND [datum]='" & Me(k4) & "'" & " AND [bezet_ochtend]='" & "bezet" & "'")

So it's a DLookup function with 3 criteria's

Please, can somebody help me?

thanks in advance,

rg,
André

Ranman256 · Jan 21, 2019

that is the wrong way to do it.
the form with 800 fields should be filled via query.

the form connects to the query,
the text box connects to the field.

dlookup is good for 1 or 2 boxes.

isladogs · Jan 21, 2019

Agree with Ranman

But do you actually mean 800 records?
The maximum number of controls allowed on a form is 754!

MajP · Jan 21, 2019

Are you using Allen Browne's extended dlookup (elookup)? Or is that a typo?
http://allenbrowne.com/ser-42.html

Andre Bruin · Jan 22, 2019

Hi,

I've tried already with a query, but that's very slow as well.
And now I'm using the Elookup from Allen Browne's already, but with the same result.

The form is a single form with 780 unbound textboxes and it is a sort of Calendar which is used for a planning.
At the moment it takes about 3,5 seconds to load the form.

rg,
André

isladogs · Jan 22, 2019

Not sure how you've managed by bypass the 754 controls limit. See Access specifications
However at some point soon your form will no longer be editable and possibly unopenable.

Whether you use a query or VBA, running lots of DLookup or ELookup will be very slow. Surprised it only takes 3.5 seconds. It sounds like you have an unnormalized table structure.

Suggest you revise the whole process. There are plenty of good calendar examples around both at this site and elsewhere e.g. Peter Hibbs has several at UA.

Andre Bruin · Jan 27, 2019

Hi,

thanks for your replies.

I want to change the code to SQL, but I struggle with the multiple conditions.
Can somebody make my Dlookup function into a SQL code?

Thanks in advance,

rg,
Andre

June7 · Jan 27, 2019

What do you mean by 'change the code to SQL code'? SQL is query. You said you already tried a query.

Also don't see how you can have 780 textboxes on form.

If you want to provide db for analysis, follow instructions at bottom of my post. Although appears there will be a language barrier issue, might be able to understand it well enough.

Should avoid using punctuation/special characters (underscore only exception) in naming convention.

other method than Dlookup vba form (1 Viewer)

Andre Bruin

New member

Ranman256

Well-known member

isladogs

MVP / VIP

MajP

You've got your good things, and you've got mine.

Andre Bruin

New member

isladogs

MVP / VIP

Andre Bruin

New member

June7

AWF VIP

Similar threads

Users who are viewing this thread