Double Click List to Open Form

0
votes

I'm really just beginning creating forms in Access 2007, and I'm trying to get a form to open with customer information if I click on the Customer's name in a list box.

Here is what the lookup query is:

SELECT msm_customers_extended.id AS ID, UCase([filed_name]) AS [Customer Name], UCase([address1]) & " " & UCase([address2]) AS Address
FROM msm_customers_extended
ORDER BY UCase([filed_name]);

So I have the bound column set to "1", and the Control Source set to "Customer ID". I have a macro under the double click event set to:

Action: OpenForm Arguments:

  • Form Name: Customer Detail
  • View: Form
  • Filter Name: (Blank)
  • Where Condition: [ID] = [Customer ID]
  • Data Mode: (Blank)
  • Window Mode: Dialog

When I test it out, I get an input box saying "Customer ID", so I'm assuming I'm not grabbing the ID from my list box. How do I get the ID from the list to link to my 2nd form that I'm trying to open?

2
ms-accessms-access-2007
I think you really should change your sort from ORDER BY UCase([filed_name]) to ORDER BY [filed_name], since case doesn't matter. - David-W-Fenton

2 Answers

1
votes

As bluefeet stated, you should just change your condition. Since you are using macros, not VBA, try changing the Where condition to: 

[ID] = Forms!CustomerListFormName!ListBoxName

Altough macros are an excellent way to discover the properties and events of Access, I encourage you to switch to VBA as soon as you start to master the properties and events. Variables, error handling, readability, VBA will give you much more satisfaction. And don't forget that you can automatically translate your existing macros to VBA to get you started quickly.

0
votes

Change you WHERE Condition from this: 

Where Condition: [ID] = [Customer ID]

To this:

"[ID]=" & Me![Customer ID]

You have to tell it where to pull the Customer ID from. By using, the Me you are letting it know it is from that form.