LinkBack URL
About LinkBacks
Hi,
Is it possible to have a data entry form with 86 fields that can scroll through the records, add new ones, or edit existing records?
This has to do with the 'Invoice Data' sheet.
Thank you
Hi,
Is it possible to have a data entry form with 86 fields that can scroll through the records, add new ones, or edit existing records?
This has to do with the 'Invoice Data' sheet.
Thank you
Hi,
I'm not aware of any restriction on the number of fields in a date entry form or an Activex UserForm. That said though it would be a pretty 'busy' form and arguably a bit of a pain to navigate through and select the right boxes 100% of the time.
However I think you are going the wrong way with this database layout and making any subsequent analysis or summarisation much more difficult.
I suggest you change your Invoice Field layout to the following columns
Inv
Cust ID
Inv Date
Due Date
NCD
RFL
Code - values in this column would be the code reference number, i.e. 0, 1, 2, 3,....n etc.
HRS
QTY
UOM
The way I generally handle stuff like this is to create a date entry row above the database. New entries would be recorded here first. Then a simple macro would copy that invoice record an add it to the database, then clear the data entry row ready for the next entry. You could do the same thing with a 10 field data entry form but unless there's a particular reason to use a form then the KISS principle should apply here.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thank you Richard. I agree with the 4 fields repeating but how to store them? You said those would clear and make ready for new entries. I want to be able to pull up old records as needed.
I appreciate more information on how to achieve this strategy.
Hi,
Sorry, I missed out the Code Value field (i.e. stuff like LB102, LB116, ..etc) from the original layout I suggested. The original one I mentioned would have been better described as Code Type perhaps.
All the invoice rows would be stored much as you have now. The difference being that you would have many more rows and of course fewer columns.
A modular approach is needed here.
The first task is to populate the database, the second is to provide a find/edit facility.
I'm goint out for a whiel now and will revert later.
See if this might offer the start of a workable solution. See the Invoice Data (2) sheet.
I've just added the macro for adding a new invoice.
Editing existing records can come later if this is a way forward.
Something looked oddly familiar, similar to a post a few months back.
The initial OP seemed to get frustrated with lack of responses despite several efforts to assist.
I have attached a VBA approach that I had half completed before 'life' events intervened.
If the approach is of any interest I will expand thoughts on the project.
In application of this nature I always steer away from on sheet formula approach as in 99% of cases they become slow and unwieldy.
It became apparent that the initial OP had limited experience of Excel/VBA therefore the VBA is deliberately kept low grade.
Therefore I have steered clear of using arrays, collections, dictionaries, modules, etc.
Use 'CLEAR' button before entering new data. Date is entered just using numbers 22082019 produces 22/08/2019.
The invoice entry is four stage Header > Labor > Materials > Footer.
Both Labor & Materials are selected from multichoice listbox then flip/floped to quantity entry page.
torachan.
Thank you both for your valuable help. With Richard's workbook how can I add more than one service or material per Invoice? If this is possible I like this approach.
torachan I downloaded the workbook and started to edit some of the details for my needs. It is almost there. Your addition is confusing to me but I have not had much time to try it out though.
I have a question perhaps it is possible.
Can the 'Lookup Invoice' sheet serve as a New Invoice and a Lookup Previous Invoice?
Maybe code to clear, code to print and store, and code to lookup a previous invoice for printing or editing if needed?
Thank you very much
torachan I looked over your new workbook but it doesn't add customers and the invoice sheet is missing details for calculations.
Maybe this version doesn't have your latest updates?
The whole concept of a database is that you have a distinct record for each line item detail. If you have two items for the same invoice then you would add two rows.Originally Posted by Bart60
Obviously the incrementation of the invoice number would need to take that into account. I suggest either a pop up message box as the record is entered which asks if there is another item for this invoice and the invoice number is not changed if the answer is Yes, or perhaps an option button which is set to supress updating the invoice number.
Hello Richard,
I did some revisions of my workbook and notice in particular Invoice Data2. The area in yellow is for creating new data and to post the data to the database table.
I did not include the extra rows for A:F and was hoping for this blank area for the code buttons or notes since the sheet has the labor and materials for reference.
Can code be derived like you have done with your workbook?
Thank you very much
@Bart60.
Not sure where you are refering 'does not add customers'
It certainly does add to the 'Customer' database.
It does not add to the Invoice Template as of yet, and you last file demonstrates why.
An orderly procedure needs to be followed when developing an app., from 'n' years of experience I have learned to leave the writing of 'reports' & 'templates' until last.
Get your data storage and manipulation figured out first, there have been three changes to your invoice template already.
Your 'Invoice Data 2' page has already become 'jumbled', set your pseudo-database tables out on individual sheets for clarity and ease of referencing.
Even if you go with the 'on sheet' formula route follow Richards advice, your invoice transaction file needs to be 'one element per line'
The 'Invoice Data' sheet generated by my submission illustrates the minimal storage needed, there is no need for 'pretty' heavily formatted tables.
torachan.
@torachan.
I don't disagree with your vast knowledge on properly setting up the data storage. However I think have the descriptions of the codes at hand from my latest revision seems to be a benefit for the user. The Invoice report was there already so I will deal with that later. Baby steps for now I guess.
So with that in mind can there be code to populate the database table from the latest revision 5.4?
Or do you prefer your method? I am just after something logical that is also efficient.
Thank you
Try the attached.
As torachan has mentioned you should keep each line item as a separate record in the database. You should also avoid complicating things like having other stuff on the same sheet as the database. I've moved your lists to a new tab.
Tha Add buttin transfers the relevant entries from the input area to the database.
#14 reply is way off. Thanks for the langorous effort.
Last edited by Bart60; 08-26-2019 at 01:11 PM.
Please post your query about formula here. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Due to the treatment I received here forced me to derive my own solution.
Thanks anyway but I will think twice in the future about asking help on this forum.
I was wondering though is it normal practice to not believe the user rather a fellow moderator? Perhaps "I don't fit in".
Thanks for letting us know that you resolved the issue. I would have been happy to help.
If you wish to discuss anything further, my mailbox now has some space - sorry it was full before.
If you have a good look through the various threads here you will find hundreds of people getting masses of help that often goes above and beyond. You will be in the same position if you stay around. Tomorrow is another day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks