Using MS Access/Combo does not work in form property Data Entry = Yes
QUESTION: Sorry to bother again. This was my question: I have a problem with the form. The record source of the form is a query with one table (with values CustomerID, Customer Name, CustomerAccount). I need the form to have there 3 fields in such a way that CustomerID should work as a dropdown and two other fields are populated automatically based on the customer selection (later I will add a subform). However, when I create a form based on this query it does not give me a drop down option for CustomerID - just a text box. When I delete it and add it manually as combo box I can see all CustomerIDs but selection does not work. What can be wrong?
This was your answer:. What it sounds like you want is a SEARCH combo to find and retrieve the Customer record. You can crea6te this search combo using the combobox wizard and selecting the third option.
Unfortunately, my form has to have property Data Entry = Yes. It is only data entry form so every time the user opens the form it has to be blank and ready for new record and the user should only see the current record.
Well..., as soon as I set the form property to Data Entry = Yes the combobox stops working.
ANSWER: OK, I'm a bit confused here. You should have a table of customers. You should NOT always be entering a new customer. It sounds to me like your main form should be an Orders table with CustomerID a foreign key.
You do not want to repeat all three customer fields in this table.
Can you refresh my memory as to what you are trying to do with this form when its finished?
---------- FOLLOW-UP ----------
QUESTION: I clarify. I do not have Table Orders as I do not track them at all. All I worry about is transactions (what product and how much of it is purchased for each customer at a given day). So the main form has 4 fields CustomerID (a dropdown), CustomerName (filled out based on CustomerID selection), CustomerAccount (same as with CustomerName) and Date. A subform will have transactions (ProductID, Qties, etc.).
Every time user opens the form it should be blank. User should not even see previous transactions. First, User selects the CustomerID from a combobox (dropdown), the rest customer data is filled out automatically. Then the user goes to the subform.
Everything works fine, thanks to your advices, the only thing is that when CustomerID is selected CustomerName and CustomerAccount are not filled out automatically. It happens as soon as I make the form property Data Entry = Yes. But I need it according to the requirements.
ANSWER: Do you have a Customer's table? Assuming that you do I would do this differently. I would enter data directly into the Transactions table. The only thing you need is a CustomerID foreign key in the Transactions table.
Users should NOT be entering Customer info every time. You should be searching for a customer. Not entering the data again.
---------- FOLLOW-UP ----------
QUESTION: Sorry, this is not a solution. You suggest to select customer right in the transaction table. User enters hundreds of items (products) for each customer on a given day. So they would have to select the same customer over and over - hundred times. Please do not ask for the business logic. I am very very simplifying my case. CustomerID must be on the main form and transactions in the subform. Actually, my DB is not about customers or transactions at all. It is more complicated but the principle of how it works is the same.
OK, So you use a similar solution to the one I gave you for the date. The main point here is that you are creating redundant data but adding the same records for a customer all the time. This is poor design. What you should be doing is RETRIEVING the customer record using a Search combo. Then linking the transaction subform on CustomerID.
Then, as you add a new Transaction, it will automatically populate the CustomerID in the Transaction. The user then doesn't have to keep entering information that repeats.
So the main form should NOT have Data Entry set to yes. If should, however, have the controls holding the customer data locked so users can't change the data. they should only be able to select a customer or add a new one.