Solved Using Add Existing Fields to Create Combo Boxes

[CammoLammo]

Hi,
I'm new to Access and I'm trying to make an entry form for the orders in my database. I have been using the Add Existing Fields option to add the different attributes to the form and for the Payment Type a combo box is created where I can only choose from the given payment types, when I do the same thing for the Customer First Name a combo box is not created. Whenever I add the CustID to the form the ID is also duplicated and a new field is created called CustID_Customer(1,2,3,etc). Attached is a picture of the Add Existing Fields section I'm referring to. The relationships between Customer and Order are identical to the relationships between PaymentType and Order (many Customer/Payment Type to one Order) so I'm not sure why they are behaving differently. There is also an image below showing the PaymentType combo box and the lack of a combo box under CustFirstName. Does anybody know how I can change the CustFirstName to a combo box that only contains Customers in my Customer table and that displays the related Customer for each entry and also how I can stop the CustID primary key from being duplicated.

Thanks for any help!

 

[bastanu]

I suspect you have the PaymentType set up as a lookup filed in the PaymentType table and that is the reason why it is created as a combo box by default. While many use the lookup feature most advanced programmers avoid it as it can create issues that are hard to identify. See http://access.mvps.org/access/lookupfields.htm for more info.
To convert the default text boxes to combo boxes simply right-click the text box in design view and select Change/Combo box. Now go to the Row Source Type and select table/query and in the Row Source click the builder (...) on the right and build your SQL statement, should be something like SELECT DISTINCT CustFirstName FROM Customer Order By CustFirstName;".

Cheers,
Vlad

 

[CammoLammo]

I suspect you have the PaymentType set up as a lookup filed in the PaymentType table and that is the reason why it is created as a combo box by default. While many use the lookup feature most advanced programmers avoid it as it can create issues that are hard to identify. See http://access.mvps.org/access/lookupfields.htm for more info.
To convert the default text boxes to combo boxes simply right-click the text box in design view and select Change/Combo box. Now go to the Row Source Type and select table/query and in the Row Source click the builder (...) on the right and build your SQL statement, should be something like SELECT DISTINCT CustFirstName FROM Customer Order By CustFirstName;".

Cheers,
Vlad

Hey Vlad,
First off thanks for the reply! My PaymentTypeID is a AutoNumber field and my PaymentType is a Short Text data type so I don't think it is the wrong data type, unless you are referring to some other kind of setting. When I right click on the Customer First Name field in my form and change it to a Combo Box and change the SQL it will show all the Customer First Name options but it will not let me select any, Windows makes an error "BING BING" noise. I can not enter anything into these boxes when they are still normal text boxes and not combo boxes, all they seem to do is show the customer for existing records.

 

[June7]

What is RecordSource for form - is it a query?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[CammoLammo]

I attached my database, the RecordSource is a query that Access automatically made, I created the form by selecting my Orders table and clicking Form under the create tab. After looking at the query I have realised that it is making new fields and adding it to the query, these are the duplicate IDs I was talking about in the original post.

 

[June7]

Users really do not need to see these primary and foreign key fields.

Customer combobox is bound to wrong field. Should be CustID field from Order table. Set fields from Customer table as Locked Yes and TabStop No but the combobox should not be locked. Also need to fix combobx ColumnCount and ColumnWidths properties.

I removed the duplicate fields from query and fixed combobox ControlSource. Don't really know why query was doing this.

Could avoid this issue by not including Customer table in query. Just bind form to Order table. Customer info can be displayed in textboxes with expression referencing combobox columns: =CustFirstName.Column(2)

Similarly for PaymentType and GameTitle comboboxes

 

[CammoLammo]

Thanks so much for the help, you are a lifesaver. Now my stuff all works nicely!