Dlookup

[SDaxx]

I have tried several ways to make the dlookup work on a form I have in my database and it is not returning any results any help would be appreciated.

I have a table (tblWorkRequest)with the following fields ID (PK), WorkOrderNo, RequestDate, RequestedBy, Location, Description, AssignedTo, ScheduledCompletionDate, and ActualCompletionDate.

I have two forms (frmWorkRequest and frmAssignWorkRequest).

frmWorkRequest has the follow fields txtWorkOrderNo, txtRequestDate, txtRequestedBy, txtLocation and txtDescription.

frmAssignWorkRequest has the following fields cboWorkOrderNo, txtRequestDate, txtRequestedBy, txtLocation, txtDescription, txtAssignedTo, txtScheduledCompletionDate and txtActualCompletionDate.

I want a user to be able to choose from the cboWorkOrderNo list on the AssignWorkRequest form and that the follow fields would populate txtRequestDate, txtRequestedBy, txtLocation, txtDescription and then they would enter information into the other boxes and it would be added to that record in the WorkRequest table.

This is the code I have so far.

Private Sub cboWorkOrderNo_AfterUpdate()
txtRequestDate = DLookup("RequestDate", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
txtRequestedBY = DLookup("RequestedBy", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
txtLocation = DLookup("Location", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
txtDescriptionOfWork = DLookup("DescriptionOfWork", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
End Sub

Any help would be appreciated.

[mcupito]

Are you using Parent / Child forms? (Subforms)

Also, is WorkOrderNo unique for every row?

Personally, I would create a query and stick it in VBA on the AfterUpdate event of the Combobox or Button Click event to open up (or show) the other form. I would make that query the data source for the form you want the values to show up on, and tie in the correct information onto the form.
Then, use the fields you already have in conjunction with the fields you want the user to enter in and insert the necessary information.

You can use the combobox as a filter in the WHERE clause, like you do for you DLookUp().


Before all of that, though. Try running your DLookUps in a SELECT query to see what results you're returning.

[Rabbit]

First, your database in unnormalized, you are repeating a bunch of information that is unnecessary. You can read more about normalization in our article here: http://bytes.com/topic/access/insigh...ble-structures

Second, is the work order number a string field? If it is, you need to surround the value in quotes.

[NeoPa]

I suspect Rabbit's second point is the answer you need.

Nevertheless, the first point is more important and will help you more in the long run if you take it to heart.

PS. Remember to set Best Answer when you've checked out his suggestion.