How to create a customer product profile/template based on past purchases? Sales Processing...

[Kaloyanides]

In sales order processing database, I'm trying to have all of the products each customer purchased in the past show up in subform or dropdown with last price.

Main Form - frmSalesOrders - tblSalesInvoiceHeaders
Sub Form - ItemsPanel - qrySalesInvoiceItems (tblCompanies joined with tblSalesInvoiceItems by CompanyNum)

I can't think of a way to make this happen.

Easy thing? If not, anyone aware of a free template that I could download?



I need to obviously be able to record the sale and have all other items available (not just the items they've bought in the past).

Any advice would be greatly appreciated!

Have a great day!

Jason

[Gina Maylone]

Is your subform (ItemsPanel) continuous? It should be and then sort it by datesold, desc.

HTH.

[Homegrownandy]

If you store the information you can show it. So firstly, is that stored?

When a transaction happens what information is then saved about the transaction?

so lets assume the information you need is stored... you need a way to pick only the information you want to see. How do you want to do that? you could have a big list of all transactions with some filters. you could have a list of companies that you select then open a new form with just their info. however you want to show it or I should say however you want to define the query criteria.

[Kaloyanides]

First you lookup the company, then you click the sales button that opens an order entry form. The main order entry form has the customer info, delivery address, ref#, etc. There is then a subform for the order entry part. It is a continuous form. So there is the companies table, invoiceheader table and invoiceitems table. I capture the company number in the invoiceitems table.

My problem is, if I create a query on the subform to filter and show only the items the customer has ordered in the past, it's then not going to let me add new items. Right?

Say we have 1,000 items and the customer has only ordered 10 in the past. I'd like those 10 to show with 0 QTY but also be able to add new items.

Do I have to manage a separate table for the templates? Then, do a double-click to add a new item going forward?

Thanks!

[Gina Maylone]

First you lookup the company, then you click the sales button that opens an order entry form. The main order entry form has the customer info, delivery address, ref#, etc. There is then a subform for the order entry part. It is a continuous form. So there is the companies table, invoiceheader table and invoiceitems table. I capture the company number in the invoiceitems table.

My problem is, if I create a query on the subform to filter and show only the items the customer has ordered in the past, it's then not going to let me add new items. Right?

Say we have 1,000 items and the customer has only ordered 10 in the past. I'd like those 10 to show with 0 QTY but also be able to add new items.

Do I have to manage a separate table for the templates? Then, do a double-click to add a new item going forward?

Thanks!

What I would do is have 2 subforms. One for new order entry, and one to review previous orders. I'm not sure why you want to see previous orders with 0 QTY.?

HTH.

[Kaloyanides]

That's not a bad idea...

I was just thinking it would be cool to have the order entry screen auto populate with all of the items they've purchased in the past but with zero qty's so the order taker could just change the zero to whatever qty the customer wants...

I do like the idea of a separate form showing the previously purchased items. I think that would be the easiest to accomplish.

Thanks!

[Gina Maylone]

That's not a bad idea...

I was just thinking it would be cool to have the order entry screen auto populate with all of the items they've purchased in the past but with zero qty's so the order taker could just change the zero to whatever qty the customer wants...

I do like the idea of a separate form showing the previously purchased items. I think that would be the easiest to accomplish.

Thanks!

You're welcome! What you could do is have a check box or button on the previous sales form that would run a query to add that item to a new order.

[Kaloyanides]

How would I do that? Is it complicated? I did a little searching but can't find code for one subform to another. If you have an example you could share, great, if not, I'll keep searching. Appreciate your help!

[Gina Maylone]

How would I do that? Is it complicated? I did a little searching but can't find code for one subform to another. If you have an example you could share, great, if not, I'll keep searching. Appreciate your help!

Nah, not complicated, create an append query, using the query you use to pull a customer's previous sales and append to your new order table, the item # and customer # and whatever else you need, the price, etc. Set the criteria in the append query to the customer # and item # selected on the form.

I'm afraid I'm not being very clear hear. I'll upload something for you to look at shortly.