customer drop down list

[jduke44]

Hey guys, I have a question that I am hoping you can point me in the right direction on. Before I ask it, I am new to access. I have been looking at templates and reading and things on how to build a database. I have my tables setup, relationships I think are right and my form created. One of the things I am having trouble with is creating a drop down list to be able to pick a customer name for. I setup the combo box and have a customerID as the control source in the properties. I have the Row Source Type as table/query and the Row Source query setup so it takes the info from the customer table.

query:

field: customer.* name
table: customer customer

I think I am doing this the same way the templates have the properties but for some reason it won't allow me to pick from the list. It keeps saying "control can't be edited. It's bound to unknown field 'CustomerID'".

I attached my database. I hope it is clear enough that you might see what I have done wrong. Thanks in advance.

 

[b_bill]

Hi,
I'm not much farther along than you in my database studies, but it seems to me that queries are not for relating input forms to; they are mostly for gathering information. The customerID in your form is based on a query and not on the original table. As far as I know, your input forms must put information directly to the table, either as a main form entry, or from a subform - both of which must directly connect to a table.
Good luck!

 

[ansentry]

Have a look at your db now.

May I suggest that you change the way in which you name your tables, queries etc.

The way I do it is (other may do it differently)

Table: tblCustomer
Query: qryCustomer
Form: frmCustomer
SubForm: sfrmOrderDetails

Also do not use the word Name as a field name, use CusName or ClientName. Name along with others are "Reserved Words" and should not be used to name fields.

If you have to have gaps in your form, query etc names then please use _ to join them e.g. frm_Customer.

I hope that the changes I made to your db help you with your project.

 

[jduke44]

ansentry, you are awesome! I am pretty sure that is what I needed to do. You even solved my subtotal and totals problem which would have been my next question.

I guess the next question would be is what did you do to get al that to work. Like I said, I tried to follow the templates that MS gives but no avail. I noticed you changed the column count but that was about it (as far as the customerID). Also, what did you do differently on the subtotals?

I will take your suggestion on the names for tables, queries and such. I noticed that is how alot of them do it. I wasn't aware I using the reserved word for name. I will change that. That's the problem with using a template from MS. They use gaps in their names quite frequently. I noticed in reading other posts that causes problems with programming, so I will go back and change all that. Thanks for the heads up.

BTW, pretty impressive you were able to follow my logic with things. I was almost sure I would have to explain what i was trying to accomplish.

P.S. Bill, thanks for trying to help out.

 

[ansentry]

ansentry, you are awesome!

Who am I to disagree! (Joke)

what did you do differently on the subtotals?

1. I did the "ExtPrice" calculation in the query that is the source for the subform.
2. I then included that control in the subform.
3. Your "OrderDetailsTotal" in the subform and your "SubTotal" on the main form then worked. I changed the name of the sub total to txtSubTotal.
4. Once you have the total from the subform on the main form the other calculation just refer to the controls on the main form. Have a look at the source for Sales Tax and Order Total.

you changed the column count but that was about it (as far as the customerID)

The major problem was that you did not have CustomerID (Ordertbl) in the query that was the source for the form. When you selected a customer in the combo box access has not where to store the id number in the ordertbl.

Hope this explains it.

 

[jduke44]

John, this sorta explains it. The sorta is only bc of my my lack of knowledge with access. The reason I had customer.* in the first column in the query was bc I was following what the template was doing. I did change it that before but it didn't seem to matter. Anyways, I do have another issue. When I add a record in the customertbl table then I go to my form, it adds that record to the end as if I added another order. I hope I am clear on this. How did you add a customer? Was it in the table itself?

Do you have any ideas? Thanks again.

 

[ansentry]

I see what you mean, will have a look, fix it and send it back to you.

It will be after the weekend.

 

[ansentry]

Fixed,

I think it may have been me, the relationship between customer and orders in the query that is the source for the Order form was wrong.

In the Join properties selection 2 had been selected instead of 1.


Sorry about that.

You will also notice that I have shifted your Add Customer macro from the detail section of the form to the double click event of the cboCustomerLookUp. I just found it too hard to work on the form with the macro running when you clicked the form anywhere in the detail section. Feel free to move it back.

Good Luck with you project.

 

[jduke44]

John, I know you never can hear this enough, but you are awesome! I believe this is exactly what I wanted. I will look at how you set that up so that I can learn how to do it on my own.

Just so you have an idea of why I am doing this, it's actually for my wife. She started to sell Mary Kay cosmetic products and I thought it would be neat to set up a database for her to keep track of it since I want to learn Access anyways. I can use software or their templates but I am a glutton for punishment to want to do it myself (with a little help from my firends of course ).

Oh yeah, I love the fact you changed the add to where you did bc I could not figure out why it would come up if I clicked anywhere on the form. I thought that was one of the problems I was having. I really appreciate you doing that so quickly.

Joe.