Aug 28 2019 10:03 AM
Aug 28 2019 10:03 AM
Hi, I am new to Access and I am looking to use it more in our flooring business to coordinate invoicing. I have noticed that most instructional materials show a traditional business that has a table of products with their product information (description, cost, etc). Is there a way to create a form or invoice where we can manually enter in each order, rather than choosing from a list of available products?
Thanks for your help!
Sep 04 2019 08:32 AM
Please be sure to backup your database before making any changes.
You need to change the Limit to List Data Property of the Combo Box to allow edits to the field not in the list. Open the Form in Design View and use the right mouse button to select the Combo Box you would like to edit, then select Properties at the bottom of the pop-up menu -- or select Property Sheet in the Tools Section of the Design Tab under Form Design Tools.
Under Property Sheet select the Data tab and set the Limit To List property to No. This will allow edits to the record that are not in the list.
You need to be careful with this option however, because most Combo Boxes insert Key fields into a record which links it to a record in another table, usually with many fields. For example, a Part No. field might link to a Parts table that has many Parts records, each with many fields, like Price, Vendor, Supplier, etc. Allowing edits in a Combo Box to links like this will more than likely not work properly.
Another, perhaps better option, is to create a separate form to edit the list while the form is being filled out. This option will allow the list to be updated an used by others, rather than reentered each time a new invoice is created.
Again, be sure to backup your database before making any changes.
First create a new form from the table used by the Combo Box; use text fields to edit each field in the list, or in some cases additional Combo Boxes will be used. Then, go back to the Combo Box and open it's Property Sheet. Select the Data tab and set the Allow Value List Edits to Yes, then add the new form to the List Items Edit Form property. After making this change an Edit List Items button will be added to the Combo Box and a new record can be added to the list using the new form as the main form is being filled in. This option will also work if your using a Row Source lists rather than a Control Source table and/or query.
Hope this helps.
Sep 05 2019 01:58 AM - edited Sep 05 2019 02:12 AM
@DarylinJ this is no issue...there are plenty ways to do it...but keep in mind that the "manual" way is always the best recipe for errors...
Lets take an example :
Assume you are Amazon - alike company and you are selling books
And a customer (A) comes and asks a copy of Book named "Programming in Access by Someone"...you have it in your stock ..so you enter "Programming in Access by Someone" ...and you sell it.
Then a 2nd customer comes (B) and ask for the same book "Programming in Access by Someone"...mistakenly you type "Advanced Programming in Access by Someone"...probably it would have in the cover somewhere the word "Advanced"...no big deal..you have a 2nd copy in your stock and you sell...
But sometime later you need to have a stocktaking...and you cross reference with your invoices...the book is called "Programming in Access by Someone" but while you should had 2 in your stock ...you only see 1...oops no there is an issue....where did the other copy went.....if you are small business and you scroll all your invoices you should probably pick it up by "visual examination"....but computers are dumb (that's why AI is the big fuss in the market right now) ...so whatever you do that relies on giving that exact parameter(queries/reports/forms) ...it would only show this 1 entry...the other is pretty much gone.....
Instead if...you had assigned some particular code to this book e.g. 1234 ...in every case some customer would come and ask to buy this particular book it will point to this specific code (1234) that in turn points to the exact book....no mistakes...what do you think ?