Userform to enter entries and also search for and update entries in database

  • Hello I am trying to create a userform that is meant to both add data to a new row in my database. the code for this works. but i also want this userform to be able to search for data entered based on the reference number which is the first textbox on entry. so ive attempted for the form to search then repopulate my combo and text boxes then upon leaving the userform if anychanges have been made it updates it in the database


    VB:

  • Re: Userform to enter entries and also search for and update entries in database


    Quote from snb;720648


    I looked there and took bits, im using a different code now but my combobox named Combobody is being populate from the first column, i need it to be populated from another column when doing the search


  • Re: Userform to enter entries and also search for and update entries in database


    I've looked at that link and adapted the code but im getting run-time error 91: object variable with block variable not set. for the ".Autofilter" underneath the cmdfind control


  • Re: Userform to enter entries and also search for and update entries in database


    If you have used my code then my original example sets MyData in the Initialiize event. You have removed it.


    Code
    Private Sub UserForm_Initialize()
    'change sheet name and Range here
        Set Ws = Sheet1
        Set MyData = Ws.Range("a8").CurrentRegion   'database
  • Re: Userform to enter entries and also search for and update entries in database


    I have set the MyData in the event but now another error is coming up.


    Its a run-time error 424: object required.


  • Re: Userform to enter entries and also search for and update entries in database


    First line has

    Code
    Dim Ws As WorksheetDim MyData As Range

    which should be

    Code
    Dim Ws As Worksheet
    Dim MyData As Range


    I'm not trawling through the rest of it - there does not seem to be any indication where the error is.

  • Re: Userform to enter entries and also search for and update entries in database


    If there's an object missing chances are you have deleted or re-named a control.

  • Re: Userform to enter entries and also search for and update entries in database


    Thank you all for your input. I've managed to have it working, well mostly working. I can add and find entries but when I attempt to amend/edit an old entry it doesn't change anything.


    I am still using an adapted version of RoyUk's.


  • Re: Userform to enter entries and also search for and update entries in database


    No that checks if r is gtreater than zero, which it must be to find an entry. The actual line is

    Code
    If r <= 0 Then Exit Sub


    If it isn't then the code stops.


    You need to check all the variables have values and it would probably help if you attached the workbook.

  • Re: Userform to enter entries and also search for and update entries in database


    On further attempts at debugging ive found that my delete button doesnt work. Its coming up with an error in the

    Code
    Set c = Ws.Cells(1, r)

    in the Delete sub.


    its coming up with a run time error 1004: appplication-define or object defined error. But c has been declared.

  • Re: Userform to enter entries and also search for and update entries in database


    You use wS correctly to set it as the the data sheet, but at the end of the initialize event you the set it to be


    Code
    Set Ws  = Worksheets("MasterColour")


    The code is working correctly but you have pointed the amendments to go to the wrong sheet.


    If you want to use VBA you must understand what the examples are doing.

  • Re: Userform to enter entries and also search for and update entries in database


    I had set the ws to be that worksheet because thats where my combbox gets its values from. If i set the ws to "mastercolour" then set it to the correct sheet does that resolve it?
    I have attempted this code, there are no errors popping up but its not deleting the row it should be.


Participate now!

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