How to auto populate a form

[e51lrrp]

I have a POS database and would like to enhance my frmNewCustomer. I have created a cboFind and want to be able to click on the information in the list of customers and have Access populate the form.

I have attached my database to this message in hopes that someone will be able to help me accomplish this.

I appreciate any and all help. Sort of don't know how to get started.
Thanks
S

[June7]

Here's one way to do what you want http://www.datapigtechnologies.com/f...tomfilter.html

Another is to construct filter criteria string and set the form Filter property.

Me.Filter = "CustomerID=" & Me.cboFind
Me.FilterOn = True

[e51lrrp]

THANK YOU SO MUCH for you prompt response. I long have you been at the ACCESS thing? I am trying to teach myself with books and tutorials and it seems like there is sooooooo much to learn. Thanks again for your assistance.
S

[June7]

Only about 6 years. Still learning.

[e51lrrp]

June7:
Using your suggestion where would I put the expression you gave me? In the property "on Click" . Would the filter criteria go in the query? That doesn't seem right. Not sure how to apply what you suggested. can you be more specific? S

[June7]

Which option do you want to use? The parameterized query code is explained in the tutorial.

For the second approach, use combobox AfterUpdate event or button Click event. The code could be macro or VBA procedure. The filter criteria would not be in the query.

[e51lrrp]

A BIG THANK YOU.....You really don't know how much I appreciate all your help. I even tried to hire a tutor locally but know one answered the ad....no one that new more than I do....again. Thank you so much. S

[e51lrrp]

It worked but when I get to the detail form it tells me I have a join problem. I have checked my relationships and don't see a problem Can you take a look at that aspect and give me any other clues...S

[June7]

All of the joins in the relationships builder are INNER.

What is the RecordSource of the detail form? If it is a query joining tables with INNER join, that might be issue.

What is the exact error message?

Post code or latest version of db.

[e51lrrp]

Yes it is a query and the sql statement does indicate all INNER Joins. Why is this a problem. I have attached the database. Also can you help me understand why when I process the populated customer order and then move to the order form it goes to 1 of 1. Then when I move to frmApparelOrderDetail that is when I get the join error. Also won't let me save as it doesn't recognize the first name (which is a required field). Thanks again for your patience. S

[June7]

If you want frmOrders opened associated with customer on frmNewCustomer, users should not be able to select customer in the combobox. Why is there a combobox allowing user to change the Organization? This should be a locked textbox only for display of the organization. This form opens to 1 of 1 because it is opening in Add record mode. Existing records are not in the form's recordset. The INNER join should not be a problem for this form.

I opened the frmApparelOrderDetail without error.

When opened from frmOrders, frmPlaquesEngravingDetails generates an error when attempting to enter data. I changed the RecordSource to just tblEngravingTrophiesDetails and the error doesn't trigger. The expression in Filter property doesn't make sense. For one thing, variable parameters should not be within quote marks. Remove it.

Should not allow detail forms to be opened until the Order info is provided and record is committed to table.

[e51lrrp]

June7...In my son's business the Organization is the important information NOT the customer. He deals with schools and team and furnishes uniforms. I thought having the organization in a drop down box would help speed up operations when the customer form is used....instead of searching for it. Then we would go from the customer to the order form which would at time be new. What would you suggest I do....most of his business is repeat in fact 80% of his business is repeat. Any suggestions that would improve my process will be greatly appreciated. When are you located? Are you available for hire offline?
Thanks Again for your assistance. S

[June7]

Who is the customer - who is buying product and paying the invoice? Is it the individual making the call or is it the organization they represent? Do you have mixed business from individuals and organizations? I have never designed a retail application but I am guessing that will complicate the data relationships.

No, I don't hire out. I am not a professional developer.

[e51lrrp]

June7....yes he does have a mixed customer base. He has individuals who want shirts/sweatshirts made but mostly it is team sports where the person represents the organization. What I would like to have happen is to start with the frmNewCustomer and have it populated (if it is a repeat customer) then proceed to the frmOrder for a new Order. Then to detail where the product would be identified. One problem that I have and have NO idea how to solve is for a customer to order all of the product types. People come in and order trophies, apparel and plaques. I added a mixed type but don't know how to encompass multiple detail forms. If I started with ApparelDetail and used the trophydetail as a subform would Access consider it one order and would the item numbers be sequential?

You mentioned not allowing detail forms to be opened until the Order info is provided....I thought that is what I was doing when I went from frmCustomer then click on my Order button then filled out that form and then click on the detail form. That is what I don't understand WHY it isn't writing to the order table.

Maybe I have to give up on the population of the customer form and make them search for the old customer and proceed from there. My next hurdle, beside the mixed order, is to put the customer order detail as a subform in the Customer form so that old orders can be looked brought up and looked at.


I appreciate your helping me think through these issues. I was just asking about tutoring off as I know you must be busy helping lots of people on this site. Thanjs again for listening....S

[June7]

The best I can suggest is to have AccountID for all customers regardless if individual or organization. If customer is an individual then maybe construct a 'company name' from their lastname firstname. Have other fields or a related table for 'organization contact' info. The individual customer will be their own 'contact'. People name parts would normally be in separate fields.

Problem with products is there aren't any. There is no list of products by item/stock number, like: 3953675 Trophy - bowling. There is a bunch of tables with descriptors of components and features, like tblCupStyle (10", 12", 8") and tblColor. Products are custom designed, nothing is standardized? This seems more like a manufacturing database, not an easy one to build. It has been topic of numerous threads. Search forum.

Mixed order does complicate. Subforms/subreports could be the best approach. Item numbers will not be sequential across subforms but don't think that is important.