Use data from Pop Up Menu on data entry form

[exbaitman]

I have a basic form using for simple data entry. Call this "Orders." I started with an "auto-form" based on the Orders table. One of the fields is combo box pulling info from another table, call this "Customers". I have created a pop up when the user presses a button that shows the data sheet view of Customers table which assists the user in choosing the correct customer for the order.

What I would like, is for when they find the correct customer, they can just double click on that customer and it populates that customer in the field.



Is this possible without writing code? I can write simple formulas but nothing complicated.

If not, can I accomplish this by having the combo-box show multiple columns???

Any other ideas?

[ItsMe]

Some websites have a window open that displays a calendar for the user to select a date. Is this close to what you are looking for? The user clicks a button, a form pops up, they select a customer, the customer info shows up on the original form, and the pop up window closes.

I have done similar stuff. I would probably opt for a combo on the main form. It may be less distracting for the user. Using a pop up form will require a small amount of code, at least. You may need a similar amount of code for the combo but may not need any code at all. The wizard should be able to guide you through most of it and then it would be a matter of fine tuning the Combo's Properties.

[ssanfu]

How about using a main form/sub form?
Main form would be the customers, sub-form would be "Orders" form.
Customers would be in the main form details section - the orders sub-form would be in the form footer.

If you set up the links correctly, you find the customer in the main form and enter the orders in the sub form - no coding necessary. No popups required.

[keviny04]

I have a basic form using for simple data entry. Call this "Orders." I started with an "auto-form" based on the Orders table. One of the fields is combo box pulling info from another table, call this "Customers". I have created a pop up when the user presses a button that shows the data sheet view of Customers table which assists the user in choosing the correct customer for the order.
accomplish this by having the combo-box show multiple columns???

If you only need to assist the user in choosing the customer, then a multi-column combo box should suffice. Just set the column count, column widths, list width, and a row source with the needed fields. If you have a lot of customers, make sure you set a high row count (the "List Rows" property) for the drop down as well so the user can see more rows at a time.

Using a separate customer form is a bigger fuss, so I would only do it if it also served bigger purposes, such as allowing entries of new customers if the user needs to.

How about using a main form/sub form?
Main form would be the customers, sub-form would be "Orders" form.
Customers would be in the main form details section - the orders sub-form would be in the form footer.

If you set up the links correctly, you find the customer in the main form and enter the orders in the sub form - no coding necessary. No popups required.

No real-world order entry forms I've seen used that kind of layout. That's because when the user enters an order, he or she usually needs to look up not just the customer, but a whole lot of other things such as terms, ship-via, division, salesperson, etc. And your layout would not work. The OP's design is actually the more common real-world design, using combo boxes for lookups of other tables during order entry.

[exbaitman]

keviny04,

I got the multi-column combobox set up and it seems that will suffice. As a secondary option I have a button that opens a Customers subform in a separate tab. The downside is once you find the customer you have to go back to the original tab and type in the customer, rather than just double clicking on the correct customer.

The reason a subform iss desirable is because we use our City and State and Street Address columns to filter data, and we use the subform's search box to search for things. We have 792 customers, and its not always clear who is who (I know it sounds weird, but it is the case), so searching by city can help narrow it down, and if the street address is identical then we know we have to correct customer.

For now I appreciate the help getting the combobox setup. If anyone else thinks of an easy way to make my pop-up magic happen let me know.

[ItsMe]

...we use our City and State and Street Address columns to filter data...

Many of my customer Combos display the Name, City, and State. This usually suffices. If necessary, you can filter Combobox's Recordsets via another combo, etc.

[keviny04]

keviny04,

I got the multi-column combobox set up and it seems that will suffice. As a secondary option I have a button that opens a Customers subform in a separate tab. The downside is once you find the customer you have to go back to the original tab and type in the customer, rather than just double clicking on the correct customer.

You may add a "Select Customer" button above the customer subform. Clicking it takes the user back to the order entry form with the selected customer entered in the combo box. Here is a screenshot of what it may look like. You need VBA code for the button's OnClick event. It can be something like:

Private Sub SelectCustomerButton_Click()
     Dim cust_no As Variant
    
     ' Get selected customer from subform
     cust_no = Me![Customer subform].Form!cust_no
    
     ' Put selected customer in combo box
     Me!customer_combobox = cust_no
    
     ' Set focus on combo box
     Me!customer_combobox.SetFocus
End Sub

Many of my customer Combos display the Name, City, and State. This usually suffices. If necessary, you can filter Combobox's Recordsets via another combo, etc.

The OP said he needed filtering in multiple fields such as city, street address, etc., and this would be better done with a subform (e.g. using datasheet's built-in filtering ability for all columns). Using only combo boxes, you would need multiple combo boxes to accomplish that, which would clutter up the form.