access help: order entry

[homer15]

hey everyone,

i got bored and started trying to build a billing system using the order entry database in access. i've tried tweaking it a little bit to work more with what i have in mind. here are the problems i hit:

i tried to make a quoting system. i figured this would essentially be the same as the invoicing and order system, just with a new set of tables and forms. i replicated them, but it's not working the way i thought it would. actually, it's not working at all.

i tried to make some kind of envelop printing feature, but that all my attempts have failed. it looks correct to me in the design view, but it won't work correctly when it's running. i think the one included in the database is pretty blank.

also, on the customer order form, i put a combo box in to make it easier to select customers, but it won't let me add customers.

any help is appreciated. you can get the files <a href="http://royaltrucking.com/homer15.htm" target="_blank">here</a>. there is a rar and a zip format.

[doctorgonzo]

Okay, I downloaded your database, and I have a few questions.

On the envelope report, where is the data coming from? The record source property is blank, and it doesn't look like there is any code in the report to load data. Do you open it from another form?

As for the combo box, you can use the "Not in list" event to handle when you enter a new customer ID that isn't already in the list. One way of making it work would be to open a form allowing you to enter the new customer information to the customer table when you enter a customer ID that isn't in the list, then adding that customer ID back to the combo box to allow it to be used.

[homer15]

yea, that was my latest attempt at the envelope, and stopped before i even started... the return address informatoin is coming from the my company info table. the customer information would be listed in the recipient area... initially, i had a print envelope form that would allow you to select a customer to print from. that also used a combo box. i just looked over what i posted and i see it's not in the latest version.

as for the customer information form, the form you're looking at is where the new customer information is entered, as well as other customers are selected. i guess i could setup a new form with just the customer code box... how would i go about doing that?

[doctorgonzo]

Here's what I did. I changed the combo box some; now it only contains the customer code. If you select an item in the list, it looks up the customer ID from the customer code and goes to that record. However, if you just type in a new customer code, it will go to a new record with the customer code you typed in copied to that field (though you can't see it). After you enter the new customer information, the new customer code is added to the combo box.

Unfortunately, even compressed it is too big to attach here, so how should I get it to you?

[homer15]

yhpm

[homer15]

thanks gonz, that works great. i especially appreciate the descriptions you added to the code... does anyone else think they could help me with the other 2 problems? any help is very much appreciated. thanks guys.

[homer15]

anyone?

[doctorgonzo]

What are the other two problems exactly? The envelope and what?

[homer15]

the quoting system. i have a very, very basic knowledge of access, but i can get by with vb coding. i don't really know how to link up the quoting system, though. what i want is to build a quote for a customer, and print it out. it would work exactly like the order/invoice system. and then maybe some way to transfer a quote over to an order. that's not as important, though. thanks, doctorgonzo.

[doctorgonzo]

Could you walk me through what you want the quotes forms to allow you to do?

[homer15]

ok, what i was wanting to do is be able to track quotes given out. the quote form would get all of it's products from the product table, and customer information from the customer table. the entire layout of the quote form is the same as the orders form. once i locate the customer i want, i would like to be able to input all of the products for the quote. tax and shipping then i would like to preview the quote (via the preview quote button). the quote report will need to be printable, just like the invoice report on the ordering system. also, if it were possible to transfer a quote for a customer to an order for a customer, that'd be terrific.

here's a breakdown of each form:

the quotes by customer contains customer information and quote information (in the form of the quotes by customer subform). along the bottom there are buttons to enter orders and preview quotes

the quotes form contains a quote ID as well as a link to the quotes detail subform. also, there is space for shipping information and tax.

thanks for your help

[doctorgonzo]

Okay, after reading that and looking over the forms, it all makes sense now.

My suggestion would be this: instead of duplicating everything for both orders and quotes, store generic order information in one table. Include in that table a Yes/No field that indicates whether the order is a real order or just a quote. When you want to change a quote into an order, you just change that field instead of copying information from one table to another. You can display either quotes or orders depending on whether you select records with that field set to True or False. That way, you can reuse your forms and your code instead of duplicating it.

As for the envelopes, is there a place that you want to print it from, to get the customer's name and address?

[homer15]

thanks gonz, i'll play around with it for a while.

as for the envelopes, i reckon they could print from the orders by customer form istelf, with another button at the bottom.

[doctorgonzo]

The reason that nothing works in the version I have is because the record source for the report isn't set to anything. If the record source is set to "My Company Information" and the appropriate fields are used, it works fine.

[homer15]

yea, i've figured that much out, but i can't figure out how to make it print a specific customers envelope.

[doctorgonzo]

You could have the report, when it opens, set its record set to a SQL statement like this:

"SELECT [My Company Information].CompanyName, [My Company Information].Address, [My Company Information].City, [My Company Information].StateOrProvince, [My Company Information].PostalCode, [My Company Information].Country, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName
FROM [My Company Information], Customers
WHERE (((Customers.CustomerID)=2) AND (([My Company Information].SetupID)=1));"

I've omitted some customer information, but you get the idea. All you would need to do is change the CustomerID and SetupID values to match the customer and setup values you want to print.

[homer15]

interesting... i'll play around with that too...
i'm going to mess around with the quoting and order system to see if i can make it do what i want. i'm not sure, but i think i'd want them to be seperate. i'll see what i can make happen. thanks for your help, man.