DLookup

[LittleMan22]

Hi,

Here's some code that I have written right now:

Private Sub Form_Load()
Dim VarP As Variant
Dim VarQ As Variant
Dim VarR As Variant
VarP = DLookup("Carrier", "CurrentClientApplicationDataPolicies&quot
VarQ = DLookup("PolicyNumber", "CurrentClientApplicationDataPolicies&quot
VarR = DLookup("Amount", "CurrentClientApplicationDataPolicies&quot

Me.Remarks1 = VarP & ": #" & VarQ & " $" & VarR & "; "


The problem that I'm having though is that an individual can have more than 1 policy (it's a continous field) ... so how can I get DLookup to cycle through the values n + 1, n + 2, etc...

Thanks in advance,
Ryan.

 

[BoxHead]

Ryan,

I don't think DLookup is going to get you there. From MS Help: " if you don't supply a value for criteria, the DLookup function returns a random value in the domain. "

In the code you've provided, I don't see anything that actually relates the "Carrier", "PolicyNumber", or "Amount" to any specific individual.

I assume that there is a field on the form for the individual's name or unique IDNum.

You could try a listbox with the RowSource along the lines of:

SELECT DISTINCT 
CurrentClientApplicationDataPolicies.IDNum, CurrentClientApplicationDataPolicies.Carrier, CurrentClientApplicationDataPolicies.PolicyNumber, CurrentClientApplicationDataPolicies.Amount
FROM CurrentClientApplicationDataPolicies
WHERE (((CurrentClientApplicationDataPolicies.IDNum) Like [Forms]![Form1]![IDNum]));

You can set the backcolor of the listbox to be the same as the form detail and set the borders to transparent to avoid the listbox look if you want.

The easiest way would be to use the list box wizard and then edit the query to add your criteria you could easily add a fifth column to concatenate the values the way you'd like and then set that column as the only visible column.



HTH


John

Use what you have,
Learn what you can,
Create what you need.

 

[LittleMan22]

Jon,

The query is written to only hold the current client's policies so the displayed policies are automatically related to the specific individual in question.

Ryan.

 

[LittleMan22]

Some more info:

What the query "CurrentClientApplicationDataPolicies" holds is the current policies for the client (that is the client whose record is currently displayed). It's designed to only have 3 visible fields (policynumber, amount, carrier). And the average client only has about 2 current policies....

So what I want DLookup to do is start at n = 0 and go through these fields and call the values for n=0, n=1, n=2 (if applicable). I know that it's possible. I have similar code written in a module that cycles through this same query but for a different purpose:

Set rstPolicy = CurrentDb.OpenRecordset("CurrentClientApplicationDataPolicies&quot
n = 0
While Not rstPolicy.EOF
n = n + 1
Fdf_Output.FDFSetValue "Carrier" & n, rstPolicy("Carrier&quot, False
Fdf_Output.FDFSetValue "PolicyDate" & n, rstPolicy("PolicyDate&quot, False
Fdf_Output.FDFSetValue "Amount" & n, rstPolicy("Amount&quot, False
rstPolicy.MoveNext
Wend
rstPolicy.Close

So I want to use the basic principal that I used here, but use it with DLookup. How can I do this?

Ryan.

 

[BoxHead]

Not a clue.

DLookup is an aggregate function that only returns one value.

There may be some way to twist it around to get it do something it's not designed to do, but why? When and if you get it done, it will still be a concatenated listbox.


John

Use what you have,
Learn what you can,
Create what you need.

 

[LittleMan22]

Let me read up on a listbox...I don't even know what it is/does???

It might serve our purposes as long as it is able to compact the various fields into 1 field.

Ryan.