A single Dlookup to check for a series of values...?

[BadSikander]

Hello.

I'm not very good with lookups, but from a button click I need to check what record ID's are found in a query, and then carry out different actions for each one that is present. The query contains minimum zero records and maximum forty records at all times.

I am doing this currently using dlookups like this:

If IsNull(DLookup("[CC SID]", "[CC Centres]", "[CC SID] = 1")) Then
Do this
Else
Do that
End If

If IsNull(DLookup("[CC SID]", "[CC Centres]", "[CC SID] = 2")) Then
Do this
Else
Do that
End If

If IsNull(DLookup("[CC SID]", "[CC Centres]", "[CC SID] = 3")) Then
Do this
Else
Do that
End If

... and so on, forty times.

I'm guessing this isn't a very efficient way of doing it, and it certainly runs very slowly, presumably because the query is being re-run for each dlookup.

Is there any way to alter this so the query is only looked at once, perhaps using a case statement? i.e. have a single dlookup check which of a series of potential record ID's are present, then still have a different action carried out for each?

Alternatively, if I was to bind the relevant query to a background form, could the dlookups be made to refer to that "already open" version of the query rather than running it again from scratch each time?

Or use a For loop of some kind instead?

 

[GinaWhipp]

Hmmm, perhaps a Select Case statement...

Select Case [CC SID]
     Case 1
      'Do something
     Case 2
      'Do something
etc...
     Case Else
      'Do something if none of the above applies
End Select

Of course, I'm not sure this work as you say this query isn't bound to the form. Is the field [CC SID] part of the query/RecordSource? And why these particular ID's? Are you searching for a certain piece of information?

 

[plog]

What exactly does the 'Do This' and 'Do That' look like? I'm guessing there's a lot of repetition among cases that could probably be made more efficient--possibly rolled up into either a loop within VBA or an update query.

 

[BadSikander]

Hello.

Thanks for the replies.

I am trying to artificially populate a map with markers representing my data.

On my main form I have an image of a map. Placed over the map at the correct geographical locations are forty invisible, unbound text controls, each representing one of the forty organisations listed in the query in question.

The user has already run that query to reach this point, filtering the names by certain parameters, so the data set ends up being between 0 & 40 names.

Now, if the filtered query holds IDs for organisations 1, 3, 5 & 15, then I want corresponding text controls on the map to become visible, acting as location "flags".

The only way I have since found to make it work at a sensible speed is to have a subform open based on the query. The On Current event of the subform then cycles through each record using MoveNext, looking at the current ID and making the corresponding control on the main form visible. It just about works, but seems a bit inefficient to have to have an extra subform opening rather than just examining the dataset somehow in code.

With a case query, I couldn't see how to make it run the dlookup only once and still have it return multiple values:

Select Case [dlookup relevant query]
Case 1
[if dataset includes ID = 1 then make Text Control 1 visible]
Case 2
[if dataset includes ID = 2 then make Text Control 2 visible]
etc...

With this structure, doesn't the case statement stop as soon as it finds a first match? I need it to react to each match in the list.

Alternatively, if I put the dlookup in each "line" of the case statement (sorry, don't know correct terminology), surely it then runs the query on each occasion and the whole process slows down again.

Select Case
Case 1
[dlookup relevant query, then if dataset includes ID = 1 then make Text Control 1 visible]
Case 2
[dlookup relevant query, if dataset includes ID = 2 then make Text Control 2 visible]
etc...

I was thinking about some sort of For Each loop that cycles through the recordset if that's possible, but not sure how I would construct it.

 

[GinaWhipp]

Hmmm, I did not realize you wanted to *loop*, in which case a loop statement would be the better choice. Have a look at...
http://techsupt.winbatch.com/ts/T000001033F34.html

 

[BadSikander]

Hmmm, I did not realize you wanted to *loop*, in which case a loop statement would be the better choice. Have a look at...
http://techsupt.winbatch.com/ts/T000001033F34.html

That's exactly what I needed. Many thanks for your help.

 

[GinaWhipp]

Glad to help!