DLookup

[goraz]

Hi
I want to display a message box in a form, when a reader have more than 3 books requested. I have this code:

Private Sub Id_leitor_AfterUpdate()
If Id_reader = DLookup("Id_reader", "Query1", "Countregistry = 3 ") Then
MsgBox (" Error!!! ")

In Query1 I have Id_reader; Countregistry (not returned books).
Well the problem :banghead: is that only the first Id_reader in Query1 (with 3 Countregistry) will be shown the message box, the second Id_reader whith 3 Countregistry does not show the message. How can I get this right?

[DarkSprout]

Here's a function that uses the same syntax as DLookup, but returns an array


'// #Function#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~
'// Procedure : DLookupArray | Function
'// Author : =DarkSprout= [July08]
'// Purpose : Returns an Array of the requested field 'strFieldName'
'// Note : Not a replacement for VBA.Dlookup
'// although 'DLookupArray( ... )(0)' would work the same
'// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~
Public Function DLookupArray(strFieldName As String, strTableName As String, Optional strWhere As String) As Variant()
Dim rs As DAO.Recordset
Dim Return_Array() As Variant
Dim sql As String
Dim iLoop As Long
On Error GoTo ERR_HANDLE
sql = "Select [" & strFieldName & "] From [" & strTableName & "]" & IIf(strWhere = "", "", " WHERE " & strWhere)
Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)
With rs
If .RecordCount > 0 Then
ReDim Return_Array(.RecordCount)
For iLoop = 0 To .RecordCount - 1
Return_Array(iLoop) = .Fields(strFieldName)
.MoveNext
Next iLoop
End If
End With
'// return;
DLookupArray = Return_Array
EXIT_PROC:
On Error Resume Next
Erase Return_Array
rs.Close
Set rs = Nothing
On Error GoTo 0
Exit Function
ERR_HANDLE:
MsgBox Error$ & vbCrLf & vbCrLf & "with DLookupArray()", 4144, "Recordset Error"
Resume EXIT_PROC
End Function


Hope this solves your problem
=DarkSprout=

[CreganTur]

Welcome to the forum- it's always good to see new members!

DLookup only returns the first record that matches the criteria, that's why it's not working for you. If you want to get all that matches, then you need to create a recordset, which is what Darksprout's post shows you how to do.

[goraz]

Hi
Many thanks to you both, for the replys :bow: . But since I?m really a noobie to VBA :bug: , I don?t know where to put the "Function" that Darksprout's posted, and how to repalce the fields by my owns :motz2: ...
:banghead:

[CreganTur]

C&P Sprout's function into a new code module- click on the Module object on the Database window and then select New.

Now go to your Form. I'm guessing you have a button to kick off this code. Open the code for your button's OnClick event. Somewhere within that even put the code:
DLookupArray "FieldName", "TableName", "WHERECondition"
replace FieldName with the name of the field(s) you want returned (seperate multiples by commas, use an asterisk (*) to pull all fields), replace TableName with the name of the table you want to query, and WHERECondition with the actual value you want to lookup- from your example in post one it would be: Countregistry = 3

Underneath that code put this(this is an example to show how to get the records out of the recordset- they will be written to the Immediate Window):
Dim i As Integer
For i = LBound to UBound
Debug.Print DLookupArray (i)
Next

[DarkSprout]

In Your Form:

Private Sub Id_leitor_AfterUpdate()
Dim aTestArray() As String 'create string array
Dim i As Integer

'// This will return an Array contaning the fields meeting the criteria
aTestArray() = DLookupArray("Id_reader", "Query1", "Countregistry = 3")

'// Output Data (test)
For i = LBound(aTestArray) To UBound(aTestArray)
MsgBox aTestArray(i), 4144, "Item#" & i
Next

End Sub
This should do the job

[goraz]

Hi
Many thanks to you all. Now I have a MsgBox "Compile error: Sub or Function not defined"

[DarkSprout]

OK, to debug:

Private Sub Id_leitor_AfterUpdate()
Dim aTestArray() As String 'create string array
Dim i As Integer

10 On Error GoTo ERR_HANDLE

'// This will return an Array contaning the fields meeting the criteria
20 aTestArray() = DLookupArray("Id_reader", "Query1", "Countregistry = 3")

'// Output Data (test)
30 For i = LBound(aTestArray) To UBound(aTestArray)
40 MsgBox aTestArray(i), 4144, "Item#" & i
50 Next

EXIT_PROC:
60 On Error GoTo 0
70 Exit Sub

ERR_HANDLE:
80 MsgBox Error$ & "with Id_leitor_AfterUpdate()" & vbCrLf & vbCrLf _
& "In Line " & VBA.Erl, 4144, "Error#" & Err
90 Resume EXIT_PROC
End Sub

I've added an error handler and line numbers - please note the VBA.Erl in line 80 - this returns the line that broke

((( URL:http://www.mztools.com/v3/mztools3.aspx | very handy VBA tool, includes a button to add/remove line numbers to code )))

Please ensure that the DLookupArray() function is in a Module

Now activate your form Event - you should now be able to locate the error
((( Please Note: my code is only an example of your _AfterUpdate() Event )))

[Imdabaum]

I'm not exactly sure what I'm doing wrong here, but I'm running into this issue. I have a textbox with a control source as a DLookup. The recordset contains IDs from different tables, and I want the customer name to display in a textbox related to the customer id. Easy enough I thought, but this is what I have/get

'Attempt 1
textbox.ControlSource = DLookUp("CustomerName","TBL_CUSTOMERS","CustomerNumber = '" & [Me]![CustID] & "'")

textbox = #Name? when CustID is null, I thought it was easy enough to handle, using Nz and setting the textbox to an empty string if there was no Customer id

'Attempt 2
textbox.controlSource = Nz(DLookUp("CustomerName","TBL_CUSTOMERS","CustomerNumber = '" _
& [Me]![CustID] & "'"),"")
'I thought this should have given me the customer name or an empty string.

At this point I have tried, =IIF(IsNull(CustID)....) and =IIF(Not IsNull(CustID)... And still the textbox always turns up as #Name?

I tried to evaluate the code in vba and even when CustID is null, it still attempts to evaluate the DLookup instead of just giving me the appropriate value.

I realize that I can set the value of this textbox on Current event, or through VBA using simple If statement, but is there a solution where I can just set the controlsource in design to evaluate it?

[DarkSprout]

.ControlSource is used in Table/Query binding, this would throw errors

Use:=

[textbox] = DLookUp("CustomerName ....
'// Or
Me.textbox = DLookUp("CustomerName ....


This would assign the return value to the TextBox.