Problem with Dlookup

[TSchrupp]

I'm a relative newbie to this, so any assistance will be appreciated.

I am attempting to write a VBA module to populate a combo box with a list of valid data based on input from a previous field.

My problem is that the module returns only the value for the first record in the lookup table, which not only is incorrect, but also is not a list.

The Event Procedure for this is as follows:


Private Sub Dept_ID_AfterUpdate()

On Error Resume Next
Dim CostCenter As String

' Evaluate filter before it's passed to DLookup function.
CostCenter = "Cost_Cntr = " & Forms![entry form]![datasum subform]![dept id]"

' Look up valid WO's for this department
Me!WO_number = DLookup("Ref_no", "project codes", CostCenter)

Exit_Dept_ID_AfterUpdate:
Exit Sub

Err_Dept_ID_AfterUpdate:
MsgBox "Error Number " & Err.Number & ":" & Err.Description
Resume Exit_Dept_ID_AfterUpdate


End Sub

 

[KyleS]

All the DLookup will do is populate the field. to fill the list for the user to select you need to use the .RowSource property.

So:

Private Sub Dept_ID_AfterUpdate()

On Error Resume Next
Dim strSQL As String

' Evaluate filter before it's passed to DLookup function.
strSQL = "SELECT * FROM [project codes] WHERE [project codes].[Cost_Cntr] = Forms![entry form]![datasum subform]![dept id]"

' Look up valid WO's for this department
Me!WO_number.RowSource = strSQL

Exit_Dept_ID_AfterUpdate:
Exit Sub

Err_Dept_ID_AfterUpdate:
MsgBox "Error Number " & Err.Number & ":" & Err.Description
Resume Exit_Dept_ID_AfterUpdate


End Sub Kyle

+ =
+ =

 

[scriverb]

The DLookup function is designed to return only one value. You are trying to use it as a query to return all records the fit the criteria. This type of function is left to a query or SQL statement.

Your ComboBox can have as its RowSource a query that references the Text Box: Forms![entry form]![datasum subform]![dept id]

Example, make a query with the following SQL code:
Select [project codes].[ref_no] FROM [project codes]
WHERE [project codes].[Cost_Cntr] = Forms![entry form]![datasum subform]![dept id]
ORDER by [project codes].[ref_no];

Change the RowSource of the ComboBox to the name of the above query. You may have to adjust some other properties of the ComboBox to match the single column in the query.

Then you code in the AfterUpdate event can be changed to something like this:

Private Sub Dept_ID_AfterUpdate()
On Error Resume Next
Dim CostCenter As String
CostCenter = "Cost_Cntr = " & Forms![entry form]![datasum subform]![dept id]"
If DLookup("Ref_no", "project codes", CostCenter) > 0 then
Me!WO_number.requery
else
msgbox "Code entered invalid. Re-enter data."
Me!WO_number.setfocus
end if

Exit_Dept_ID_AfterUpdate:
Exit Sub

Err_Dept_ID_AfterUpdate:
MsgBox "Error Number " & Err.Number & ":" & Err.Description
Resume Exit_Dept_ID_AfterUpdate
End Sub

This code accepts the entered WO_number then tests it for validity by doing a DLookup using it as the criteria to see if it finds any records for the code. If it does then it requeries the ComboBox which is using a query to populate itself. The query is looking back at the WO_number text box to populate the comboBox with all records meeting this criteria statement.

I have written all of this without testing so we may have to adjust some of it. Let me know.




Bob Scriver