AUTOMATIC ENTRY

[danaleblanc]

Have a huge Order once a month for a
buisness and has to fax the order to them. How could I do this where I could
type in the product number and the description would come up automatic?

 

[jwindon]

Is the description of the product stored in the same table as the product ID? Or another?

 

[danaleblanc]

Yes, in the same table.

 

[Rich]

Restrict the data by setting the criteria in the underlying query the report is based on.

 

[danaleblanc]

Rich,
Sorry I am not understanding what you are saying.

 

[jwindon]

What do you want to do once the product description is found?

Is this just for display purposes? Or, are you planning on running a report after that?


If the product # AND the product description are in the same table, and you are wanting basically to "find" the description do this.

Base your form off of the table. Put both the Product# and Product descriptions on the form.

Add a combo box at the top of your form, choosing Option 3 "Find records matching my selection". Using the table of the product numbers and descriptions.

If this is NOT what you want to do, please post again.

 

[danaleblanc]

I am sorry if I am not making myself clear. Basicaly what I want to do is on a form enter the product # and the description appear automaticaly and then print a report based on both the product # and the description for that month. The other fields I am using in the form are a date field, a product #, product description and a quanity field. Thank you for all your help.

 

[jwindon]

Your posted question has changed.

What type of information is entered on your form? Is this form for entering product information, or is this form for Ordering?

What does the date field mean?

You are looking at a need to run a query that will use the criteria of the combo box I mentioned in my previous post AND on a date field.

To accomplish what you want to do, first create the combo box on a form with the fields you have indicated (it is my hope that this form is based off of some kind of Orders Table, but I fear not as you have stated that the product description is in the same table).

I am thinking that your structure needs to be reviewed before moving on.

If this is confusing to you, post your table structure and list the forms that are used to manipulate their data. That will give a clearer picture of what relations are present in your database so that I / others can better assist you.

 

[danaleblanc]

I didn't think this would be so complicated, but I guess I was wrong. I have tables - ORDER table is link to form to input orders (a date field, a product # field, a product description field and a quanity field). In other table (PRODUCT #/DESCRIPTION) I have a product # and a product description field. This table is used to enter product # and description ahead of time. In my form (which is link to my ORDER table)have all these fields, but a combo box for the product # listing all the product # inputed in PRODUCT #/DESCRIPTION. On report (which is link to a query, so user can enter a date parameter) list orders based on dates. All this works properly, I just want the Product desciption to appear automaticly when I choose its Product # on the form. Apparently, I don't explain myself well, thanks for all your trouble.

 

[jwindon]

So it is my understanding that (as I asked previously) your objective for this post is to learn how to DISPLAY that decription.

OK.

Check the datasource on the combo that lists your Product Numbers. Click on the Build button next to the row source.

You probably have at least the product# in this grid.

Say that the description is in the second column. This is column(1) . They are numbered beginning with zero.

Put an unbound control on your form.

On the AfterUpdate event of your combo, put the code:

Me.UnboundControlName = Me.ComboBoxName.Column(1)

That will do it.

 

[danaleblanc]

Understand code, did what you said, but receive error message saying "Microsoft Access can't find the macro 'Me'."

 

[jwindon]

The code needs to go on the event, but you need to click the build button next to the property setting AfterUpdate and choose code builder. Then put the code between the sub and end sub.

 

[danaleblanc]

Thank you it worked. Thanks for all your trouble.

 

[danaleblanc]

Was given a code to display the product description when the product # was selected (worked great). Did not notice till I printed the report (linked to the table with data from form) that it was just inputing the Produt # in the table not the product description. I don't explain myself well so I probably didn't make that point clear.
Thanks again for your time.

 

[jwindon]

You need to base your report off of a query that joins the table that has the product ID and description......WITH.....the table that contains the transactions (those entered on your form).

Then you may have ALL that information on your report.

Please post your table names and form names and control sources if you don't understand.

 

[danaleblanc]

I thought I understood, however when I print report it list ALL the product descriptions with the one selected on the form highlighted (I have altleast 100 product desctiption). I have the query group, so when I run the query it looks right just not on the report. My tables are: ORDER table is link to form to input orders (a date field, a product # field, a product description field and a quanity field). In other table (PRODUCT #/DESCRIPTION) I have a product # and a product description field. This table is used to enter product # and description ahead of time. In my form (which is link to my ORDER table)have all these fields, but a combo box for the product # listing all the product # inputed in PRODUCT #/DESCRIPTION. Thanks again for all your trouble.

 

[jwindon]

First off you should only be storing product description once.

tblProducts
ProductID (PK)
ProductDescription
CostPerUnit

tblOrders
OrderID
ProductID (FK) (use datatype lookup)
CustomerID (FK) (use datatype lookup)
Qnty
OrderDate

tblCustomers
CustomerID (PK)
CustomerName
CustomerPhone
etc.

Your form should be based off of tblOrders. The combo on that form can be based off of the tblOrders as well.

The report to print all the order out for one customer will be based off a query.

The tables to include in the query are tblProducts, tblCustomers and tblOrders.

Join the CustomerID in the query grid. Also, join the ProductID from tblOrders and tblProducts so that you can include the product description in the query grid. The result of the query will be ALL the orders for that particular customer. You can set the criteria for the customer a form as so... Forms!FormName![ControlName]

Base the report off that query. Note, if you use a form for criteria in the query, the form must be open for the query and the resulting report to run without asking for a value.

HTH

[This message has been edited by jwindon (edited 11-24-2001).]

 

[danaleblanc]

Again thought I understood what you wanted me to do, but I am still doing something wrong. Don't have a customer table is that what I am doing wrong. Only list product description in table once. But have a empty field (product description) in ORDER table that is link to form so it can input product description (to ORDER table) from selection in combo box. I feel like I am just making it worse trying to explain? Can I just send you this database?

 

[jwindon]

Ok. That is interesting. Inputting a product description thru the Order's form. If you want to send the db to me....zip it up and send to MyAvonGirl@aol.com.

I am currently on line as of your last post.

I should add that you should enter the Product Description in the tblProducts. That would be the norm. Perhaps an option ....if there is not description for the product selected in the combo, then open the form to enter product descriptions. The way you are doing it will store duplicate descriptions OR overwrite current descriptions in the tblProducts because you are setting the value of ProductDescription twice...once ahead of time....and again if you were to bind the ProductDescription on this form. Moreover...HOW does the productID get related back to the tblProducts? If you open that product table, are the descriptions you entered orphaned? I know that currently, it is NOT bound. That is what I wanted you to do.

My advice is to change this approach. Have your form based, not off a query, but the tblOrders itself. I will help you add methods to add product descriptions if they are not available.


[This message has been edited by jwindon (edited 11-24-2001).]