User Form adding, searching, editing and deleting data in a different book

    Hello,


    I have a User Form that I got from www.onlinepclearning.com (great tutorials!) I am using it to search, add, edit and delete customers. The code is written to save the customers in the same book where the User Form is in. However, this book is on our company network and multiple people are using it. Only the first user to open it is able to save changes so everything the rest of the users do is lost. All the sheets in the workbook are template so only a copy of the filled out forms is being saved, leaving the template in it's original form. I don't want anyone to save any changes to the templates so saving changes to the customer database is not happening.


    I am trying to change the code so the customer database is stored in a stand alone workbook saved in the same place as the template workbook. When adding, searching, editing or deleting customers the User Form uses the "Customer Database" workbook. The code is sending the customer information to multiple templates in the workbook so it's pretty long.


    I have been struggling with this a over a month hoping I would be able to make the changes to the code myself but have had no luck. Everyone is getting pretty frustrated that they can't save new customers.


    I would be extremely grateful form some help on this on.


    Re: User Form adding, searching, editing and deleting data in a different book


    Lots of code, big ask...


    Upload a copy of the template, it'd be easier to look at that than scroll through a lot of code with nothing to relate it to.

    Re: User Form adding, searching, editing and deleting data in a different book


    Can I email it to you? There is a very large customer database in the template workbook.

    Re: User Form adding, searching, editing and deleting data in a different book


    Only a representative sample of the data is needed - not more than 10 or so rows. If there's more than 1 'data' sheet then include whatever. You can compress the file to make it smaller.

    Re: User Form adding, searching, editing and deleting data in a different book


    Big file, but having a look and only concentrating on customers as. there's simply too much other stuff. It'll take a little time, though. Luckily is the weekend :)

    Re: User Form adding, searching, editing and deleting data in a different book


    While it would be easier to use Excel to load/read this external workboook what Pike says is very true, a database would have been the preferred way to do this to start with. You could use Excel but opening/reading the file would be relatively slow and still subject to the Excel single user limitation.


    A 'database' is a nebulous concept these days. Rather than just being a special type of file it can be nearly any organised data no matter how it is stored. It might seem a little strange but an Excel file can also be considered a database, you just need the correct drivers to access the data.


    That is what I have done with the attached. The customer list has been moved to a new XLSX file with a tab called 'Customers'. Your Customer Search, Add & Edit functions have been edited to read/update the external data. These changes have been done in isolation with no investigation into possible effects on other parts of the app; it's too big for me to verify everything else still works and it would take a day or 2 to just to get to understand all the various dependencies - that's a 'Professional Services Invoice' kind of time (J/K :))


    As far as setup goes, this has been written as simply as possible and could be improved. The 'database' can be accessed by multiple users for reading but there is a small chance 2 users will try to update a record at the same time - I have not coded for that either, it is just an example after all) . This version also expects the source data file in the same directory as the workbook so the easiest way to test is copy the 2 files in the ZIP into a temp directory and then copy the application workbook a couple of times. Each user testing then opens one of the copies of the app. All of these copies will then access the shared customer data. If you go ahead wih this then the common data can be on a network drive but each user has a copy of the application workbook in a local directory.


    There's too much to give an overview here but would be happy to answer specific questions.


    One caveat - writing a multi-user app is something that should be designed in from the very beginning; adding multi-user capabilities afterwards is not the ideal so don't underestimate how long it could take to get this working as there is potentially a lot of work involved. It's possible you need just the customer details as external data and that could be done in a couple of days - perhaps.


    Also this has not been tested with multiple users - there is only one of me (and am too lazy to do the multiple copies thing - I could but it wouldn't be representative of anytihng), but it should work. One other thing unmentioned is your version of Excel. I have done rudimentary testing with Excel 2010, 2013 & 2016 but I wouldn't be surprised if you hit issues when first running this - I will do as much as I can to at least get it running for you.

    Re: User Form adding, searching, editing and deleting data in a different book


    Thank you so much!!! You are my hero!! I will give it a test run on the network with multiple users and see how it holds up.


    When I first started with this, the idea was to have the work orders automated the best I could. But as time and progress went on the bosses kept asking to add this n that and this is where I am at now. After a couple years of searching for the "Perfect" software to run the company they just gave up. I have been learning everything as I go and as I'm learning more I realize there could be better ways to do this. But at this point I have spent a crazy amount of hours on this the bosses would not be impressed if I started all over.


    I really appreciate all the help I have gotten in this forum, without that help I would not have been able to accomplish this. Thanks to all those that have helped me I got a raise!!!

    Re: User Form adding, searching, editing and deleting data in a different book


    Hello Grimes0332,


    I was wondering if you could help me with changing the address to 2 lines. The way the invoice prints the address is in the right place for window envelopes but we need the address in 2 lines.


    I would really appreciate your help once again.


    Thank you!

    Re: User Form adding, searching, editing and deleting data in a different book


    Do you mean you want to store a 2nd address line or just 'wrap' the existing address when printing?


    You need to explain a little clearer, there seems to be 55 dozen print routines

    Re: User Form adding, searching, editing and deleting data in a different book


    I want to store the address in 2 lines.


    Now they are stored as:

    B - C - D

    First & Last Name - Box 1415 Morden MB R6M 1B3 - 204-822-8745



    This is how I need them to be stored:


    B - C - D - E

    First & Last Name - Box 1415 - Morden MB R6M 1B3 - 204-822-8745



    I need to add another text box in the userform to separate the address as shown above.


    Hope this makes sense :)

    Re: User Form adding, searching, editing and deleting data in a different book


    The changes described below for you to have a go... try it on a copy of the 2 files.:




    • Inserted Column headed 'Address 2', Col D on Customers sheet in SourceData.xlsx
    • Inserted Column headed 'Address 2', Col Q on Customers sheet in SuperiorForms
    • Named Range 'outdata' extended by 1 column (To W, I think, but 1 column nonetheless)
    • New Textbox named Emp3A on customer userform, Edit Customer panel. Placed directly under 'Emp3' Other controls moved slightly to place it.
    • New Textbox named Address2 on customer userform, New Customer panel. Placed directly under 'Address'


      • The placement of those controls is entirely down to you.




    • lstCustomers_Click event modified to load the new field. Added the emp3a line




    Code
    ' Previous as was
Me.Emp3.Value = Me.lstCustomers.Column(2, i)
Me.emp3a.Value = Me.lstCustomers.Column(3, i)
' Remainder as was



    All other location references (2, 3, 4, 5...) below inc by 1




    • SQL string in cmdEdit_Click (customers form) edited:




    Code
    ' Previous as was.
"[Address]=" & Chr(34) & Emp3.Text & Chr(34) & ", " & _
"[Address2]=" & Chr(34) & Emp3a.Text & Chr(34) & ", " & _
"[Phone Number]=" & Chr(34) & Emp4.Text & Chr(34) & ", " & _
' Remainder as was




    • SQL in cmdAdd_Click procedure changed to save data from the new textbox.




    Code
    sSQL = "Insert INTO [Customers$] ([ID],[Name],[Address],[Address2],[Phone Number],[Cell Number],[Email Address],[PST],[PST Number],[Copies]) Values (" & _
lngNextID & ",'" & _
Me.txtName.Text & "','" & _
Me.txtAddress.Text & "','" & _
Me.txtAddress2.Text & "'.'" & _ 
(remainder as was)





    It might also be an idea to take the chance to update the Tab order so the users can use the Tab keys to move from control to control in order. This is easy to do, you just hold down the Ctrl key and then click on each control in the order you want to access them. Include Labels in that as well. For example, You click the 'Invoice/Quote type' label followed by the Dropdown below it; then the 'Sales Staff' label followed by the drop down below that and so on. I'd skip the 'Close' button and leave that until last. You won't be able to Ctrl-Click on controls in a frame (so include the holding frame in the selection to start with). Once you got them all, go to the TabIndex Property in the Properties Box and type a 0 - The TabIndex for all the controls you've selected will be set sequentially from the number enterd in the order you selected them. Go back and do the same for the controls in any Frame you couldn't select earlier and finally set the TabIndex of the close button to 999 so it will be acccessed last.

    Re: User Form adding, searching, editing and deleting data in a different book


    Thank you for your help.


    I have followed all your instructions and parts seem to work. I am able to search for customers! :)


    If I add a new customer it does not show up in the SourceData. :(


    I can not edit customers either.


    Is there anything else that might need to be changed?

    Re: User Form adding, searching, editing and deleting data in a different book


    Maybe i didn't include all of the changed in that last, but I can add, exit and view the 2nd address line.


    Nearly midnight though, so I'll check in the morning and upload a copy of the workbook

    Re: User Form adding, searching, editing and deleting data in a different book


    Attached seems to work (But I don't know the app enough to check fully) - Can search, view, add, edit with the 2nd address.


    This only is for the Customers screen - there is just too much other stuff where other sheets are updated (send information to invoice/quote/receipt...)


    The appearance has changed slightly, I was just playing a little while picking up on other issues. For example, the Phone & Cell fields are numeric. if any non-numberic text is added to those text boxes (606-5655, for example) the field will simply not update - it needs a little more work.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login