Need Advice

[Starnheavn]

Ok, so I am building a database for our RFQs (requests for quotations). In this database Ive created table which includes RFQ#, date quote received, date quoted, Customer name, customer company, customer phone and fax and email, qty, description price, and total. From this, I want to create the actual quote form (to send to the customer) and have a page with our terms and conditions. And if the customer takes our quote I will want that information placed on to another form which will be a Commercial Invoice (basically the same info that the quote form has the only thing changing is the title saying Commercial Quote and of course I will give it an invoice number) I will also include a table for Vendors which will have Numbers assigned for them and the vendor information for our internal records. In addition, once the quote and/or commercia invoice are done, I want to have a control button that will send the forms via email. I need advice as to the order of doing these things. I am not at all an advanced user and this will be my first database that includes all this together. Ive created database with tables and forms but not to this multitude.

 

[AccessMSSQL]

You can definitely accomplish this. First I would make sure you set up a separate Customer table to store your customer name, address, contact information in. The RFQ table should not store the customer information. You should have a One to Many relationship set up from your Customer table to your RFQ table. Start there.

As far as your RFQ Form to send to your customer, you should set this up as a report. You can add your terms and conditions to the report footer as a subReport. What you can do is create a new table to store your terms and conditions. Make the field a memo field and you can type as much text as you want there. Create a report from this table and add it as a subreport to your main report. This way if your terms and conditions change you can just change it in the table.

You can set the invoice up as a new report, or just use the RFQ Quote report with some expressions in a query. if it will be the same as the RFQ except just with the title change and invoice number you can either create a new report for the invoice OR, based on a flag in your RFQ table you can modify the title and put an invoice number on the same report just using a query. For instance in a query you could put this expression: IIF(AcceptFlag = true,"Commercial Invoice", "Commercial Quote") as Title.

For automatic emailing - you will need a VBA routine to first print the report to a PDF or other format that can be emailed. You will also need to reference the Outlook Object Library in order to automate Outlook to attach and send the emails (given that you are Microsoft Outlook as your email client).

Hope this gets you going in the right direction.

AccessMSSQL.com

 

[Starnheavn]

Thank you so very much. It does really help. I was definately going about it the wrong way. I did place customer info in same table. But now will extract it and save to a different table. Now I have never done a Flag before so your experiece with this to walk me through we be appreciated so much. So just so I am sure I understand, I need to create a table just with Customer info, and the other table will have RFQ info in it such as RFQ Number, Qty, Description, etc? I appreciate your help so much. Thanks. ;-)

 

[Starnheavn]

Ok, a question....In setting up my Terms and Conditions: I want to be able to format this to make it look nice. If I type it in a table can I format it to have paragraphs bolded Text, etc.? I need it to be on a 2nd page not a footer as there is one page full of information for this title.

 

[AccessMSSQL]

Yes you can do this by setting your text box property called "Text Format" = Rich text. This is found in the Data tab. Then you can use html tags to add formatting within your textbox. Here is a link that might help:

http://office.microsoft.com/en-us/access-help/insert-or-add-a-rich-text-field-HA010014097.aspx

As far as setting a flag goes, this is just a Yes/No field or whatever field type you want to use in your RFQ table. It will just store a value in the RFQ record that will tell the report if it's a Quote or an Invoice. You can control the expression in the query that will be bound to the invoice / quote report. For example you could have a date field that represents the date customer accepted the quote. In your query expression you would have IIF(dateCustomerAccepted <> "","Invoice", "Quote") as Title.