By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Dlookup

P: 9
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cboWorkOrderNo_AfterUpdate()
  2. txtRequestDate = DLookup("RequestDate", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
  3. txtRequestedBY = DLookup("RequestedBy", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
  4. txtLocation = DLookup("Location", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
  5. txtDescriptionOfWork = DLookup("DescriptionOfWork", "WorkRequest", "WorkOrderNo = " & Me.cboWorkOrderNo)
  6. End Sub
Any help would be appreciated.
Feb 28 '14 #1
Share this Question
Share on Google+
3 Replies


100+
P: 294
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.
Feb 28 '14 #2

Rabbit
Expert Mod 10K+
P: 12,342
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.
Feb 28 '14 #3

NeoPa
Expert Mod 15k+
P: 31,308
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.
Mar 1 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.