Question regarding DLookup Text Boxes

[Exxili]

Hi There everyone!

I have some Combo Boxes on a form

1. Search by CustomerName
2. Search by PhoneNo.
3. Search by PostCode.

below these I have a number of text boxes related to all kinds of info such as CustomerName, Address1,2,3,4 - PostCode, Email, Phone, etc.

These Text Boxes are populated by a After Update refresh caused by the combo search boxes above.

That refresh triggers the D-Lookup Below for each text box (each text box displays different info, below is just one of them)

=DLOOKUP("ADDRESS_1","[CustomerTable]","ACCOUNT_REF=" & "comboSearchbyName")

Can I have an OR Statement in this Dlookup. So if a user chooses to SearchbyPostcode instead in the second Combo Search Box, it will trigger the same DLookup?

Or is there another solution I am missing?

Thanks for any help!

 

[Minty]

Have a look here for a much more powerful search facility, and a well documented example database.
http://allenbrowne.com/ser-62.html

 

[plog]

Can I have an OR Statement in this Dlookup. So if a user chooses to SearchbyPostcode instead in the second Combo Search Box, it will trigger the same DLookup?

Very confusing what you hope to achieve. First let me point out some big issues with the entire thing:

1. What about duplicates? DLookup returns 1 result. If multiple records share zip codes and you search by zip code, there's no telling which result will be returned. Is that fine? I wouldn't think so.

2. You're DLookup is incorrect. Your criteria argument isn't using user-supplied data. It is literally using the text "comboSearchbyName". I seriusly doubt you have an ACCOUNT_REF value that is literally "comboSearchbyName".

3. Where do you hope to put this "OR". You certainly can put it in the criteria argument (the 3rd one). But I fear you are hoping to put it in the return field argument (1st one). A Dlookup can use multiple criteria seperated by OR, it cannot return multiple fields if you seperate them by OR.

Again, though I think the real issue with this whole thing is #1--what about dupes?

 

[Ranman256]

youd need code to determine what got filled then submit the where clause.

sub btnFind_click()
dim sWhere as string

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

      'remove 1st And
 sWhere= mid(sWhere,4)

txtBox =DLOOKUP("ADDRESS_1","[CustomerTable]",sWhere)
end sub

 

[Exxili]

Hey Minty

Thank you for that, I perhaps should have explained what this is being used for,

The search combo boxes are being used to select a specific customer and then populate a seperate table (called tasklist) with the CustomerReference.

Its more of a Find a customer and then assign them to this Tasklist Table. Would that search facility be able to do that?

 

[Minty]

Yes you can use the search results recordset in a variety of ways.
If you look at the way the results are displayed you can refer to any of the selected records various fields.
Personally I would probably have an event based on double clicking the customer name, to add the current detail to your tasklist.

 

[Exxili]

Hi again PLog!

1. What about duplicates? DLookup returns 1 result. If multiple records share zip codes and you search by zip code, there's no telling which result will be returned. Is that fine? I wouldn't think so.

PostCode is a unique result for each customer, so it wont have multiple records., Also the combo box is using a query to get customer name from the CustomerTable, and is then using an Append to append the CustomerReference to the Task List table. Is this wrong?

I do also wish to put the OR statement in the criteria of the DLookup. E.g IF a user uses ComboSearchBox1, the textboxes will display all customer details about the customer chosen in searchbox1. If however a user chooses to search by ComboSearchBox2, then the textboxes should still also display the customer details related to the chosen customer

does that make sense?

Yes you can use the search results recordset in a variety of ways.
If you look at the way the results are displayed you can refer to any of the selected records various fields.
Personally I would probably have an event based on double clicking the customer name, to add the current detail to your tasklist.

Oh Wow, I did not even know you could do that. How would you add an event to a search based table?

 

[plog]

I don't think your initial attempt is the correct way to do this. A Dlookup returns 1 value from 1 field. Sounds like you want multiple fields and its possible multiple records returned.

I don't understand how APPEND fits into this, but for searching, Minty's solution is the best method.

 

[Minty]

Two things based on your reply - If your postcode genuinely is unique you don't need an OR for any other criteria - by definition if your search combo box for postcodes only lists your customers postcodes you are bound to get a hit.

Secondly
The search results table / listbox in the example has a double click event associated with it - you can do anything you want from there (short of it giving you the lotto numbers for next week)

 

[Exxili]

I don't think your initial attempt is the correct way to do this. A Dlookup returns 1 value from 1 field. Sounds like you want multiple fields and its possible multiple records returned.

I don't understand how APPEND fits into this, but for searching, Minty's solution is the best method.

ok this may be my fault, I will explain hopefully a bit better.

I have 3 Combo Search boxes on TaskList form.
1. SearchbyCustomerName
2. SearchbyCustomerPostCode
3. SearchByCustomerPhoneNumber

Below this I have a number of text boxes that have the Dlookup as described above.

Examples:

If a customer selects "McDonalds" as customer Name in Combo Box 1 above. The multiple text boxes below the search combo boxes on the form will show the name, address, postcode, phoneno, and email address related to that CustomerName

If a customer selects "BT32 XXX" as the customer Postcode in Combo Box 2 above. The multiple text boxes below the search combo boxes on the form will show the name, address postcode phoneno and email address related to that PostCode

Regardless of which combo box that is chosen to perform the customer search, a Value of Account_REF on the TaskList Table will be appended with the chosen customer of the ComboSearchBoxes on the AfterUpdate event of each combo box, after a customer OR Postcode has been chosen. An append query runs to copy the chosen Customers (ACCOUNT_REF) from CustomerTable to TaskListTable

I am positive there is better ways to do this and Minty I believe you have now shown me one option that I will now very much look into so i thank you! but that is how it will be done currently.

 

[Minty]

I wouldn't automatically append a record just based on the after update of the combo box. If the user selects the wrong thing you've added the wrong record.
Add a control / command button / a.n.other action to confirm the selections. (Or use the double click action as suggested)

Good luck with the rest of your project.

 

[Exxili]

Hey Minty

I have replicated the search example you linked and actually rewrote it to suit my need however the doesn't seem to be a double click example anywhere in it. Would you have any other examples of these related to my issue above?

 

[Minty]

In the detail section of the example if you select the ClientID and open the properties sheet, you will see a list of available events, one of which is On Dbl Click.

To make it more obvious I make the ID text blue with an underline which makes it look like it's a hyperlink. It reminds the end user that there is an event that can be triggered by clicking it. You could obviously do this on any field - maybe the company name would be more relevant to your situation.

 

[Exxili]

Found it, there does not appear to be any Example Events though.

I attempted to link it to my update Query to update records in Tasklist table however whenever a user double clicks, the query asks to update 9 records instead of just the one Task they are trying to create.

Is there a way to do this with a Macro/Query where when you Double click a Customer in the Search Sub form. It will only update the specific TaskID your are editing with the Customer Reference?

My VBA Knowledge is limited, I am currently learning about VBA and it has helped me to use the search example you linked, however I cannot think how I would do this with VBA

 

[Minty]

There are a number of ways of dong this. You can alter your query to include the customer ID from the form as a criteria.
In simplest terms your would add Forms!YourFormName!TheNameOfTheControl into the criteria of your query.

If you open the query with the form open you can use the builder to get the syntax correct. That way your query would be linked to the forms current record.

 

[Exxili]

There are a number of ways of dong this. You can alter your query to include the customer ID from the form as a criteria.
In simplest terms your would add Forms!YourFormName!TheNameOfTheControl into the criteria of your query.

If you open the query with the form open you can use the builder to get the syntax correct. That way your query would be linked to the forms current record.

Hmm Almost on the right path, I have added the correct control (txtTaskID) however when the query is triggered (double clicking on a customer record in subform) it states it is about to update 0 rows now.

Haha :banghead: I have went from updating all rows to none something must be wrong with how I have done this query I believe. (see Attached Picture)

ACCOUNT_REF is the Customer Reference
Uploaded Data is the Customer Details Table
TaskList is the table behind the form OfficeTaskAssignment

 

[Minty]

Is txtTaskID really your account REf ?

 

[Exxili]

Is txtTaskID really your account REf ?

Oh Sorry I thought I had to Link the query to the specific ID(Primary Key) of the Task. I should link it to the 3rd Column in TaskList Table? Account_Ref (this value is the Primary Key of my customer table, but is in the TaskList Table as a foreign key i believe)

When I do this, It runs but it does not update the Account_REF value? Gives no Error Either.

Interestingly when I change it to an append Query instead of an Update Query, It gives me the attached error

 

[Minty]

I'm a bit lost at your purpose here - earlier you stated that you wanted to add a record, which would be an append, but your query initially was an update. It's got to be one or the other.

Perhaps you could post up a picture of your tables and any relationships you have set up. It may be your data layout is making you do things in an "unwieldy" fashion?

 

[Exxili]

I'm a bit lost at your purpose here - earlier you stated that you wanted to add a record, which would be an append, but your query initially was an update. It's got to be one or the other.

Perhaps you could post up a picture of your tables and any relationships you have set up. It may be your data layout is making you do things in an "unwieldy" fashion?

Yeah I am getting a little confused myself, Of Course I feel like that would be best to see where I might be going wrong here.

Okay so first of all (Attached - OfficeAssignmentForm)

This form is what the user will be using to create a Task. It shows the Task ID, Allows the user to assign a Engineer to the task (Engineer 2 in the screenshot) And shows the Sage Reference (ACCOUNT_REF in TaskList Table)

On the top right it allows the user to change the Task Status (Linked from TaskStatus Table) from Open - In Progress or Closed.

Next is the Sub form below (taskSrch) which is basically the sample you linked me. In this subform the user will double click to bind this TaskID to the customer they have selected.

The rest below that is just details about the task.

Please find all tables related to the above from including relationships.

Uploaded Data is the table that holds the customer information that the TaskSrch subform uses.

TaskListTable is the source for OfficeTaskAssignment Form