locked
Coding Next Previous buttons RRS feed

  • Question

  • Hi...

     

    I am coding the Next and Previous buttons for which I have following code which seems not be working for me.

    I am learning VBA hence please forgive me if I have made a mistake in explaining.

    ****

    Private Sub cmdNext_Click()
    Dim CurRow As Range

    CurRow = 2


    'CurRow = Range("CurRow").Value

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value

     

    If CurRow = Range("A1048576").End(xlUp).Row Then GoTo LastRec

    CurRow = CurRow + 1
    Range("CurRow").Value = CurRow

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value


    Exit Sub
    LastRec:
    MsgBox "You're at the last record!"
    End Sub

     

     

     


    End Sub

    Private Sub cmdPrevious_Click()

    Dim CurRow As Range

    CurRow = 2
    'CurRow = Range("CurRow").Value
    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value

     

     

     

    If CurRow = 3 Then GoTo FirstRec

    CurRow = CurRow - 1
    Range("CurRow").Value = CurRow

     


    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value

    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

     

     


    Exit Sub
    FirstRec:
    MsgBox "You're at the first record!"

    End Sub

     

    ****

     

    Basically I want to reflect the entries on user form  by next and previous buttons kept on the form so that user can view their data entry on the user form.

     

    Sometimes I get the message Type missmatch as I am not sure how to define the CurRow function so that the above code works.

     

    Please Help. 


    Bunty

    Tuesday, December 11, 2012 3:49 PM

Answers

  • There should be only one End Sub above Private Sub cmdPrevious_Click().

    You should declare CurRow as a variable of type Long, not as a Range:

    Dim CurRow As Long


    Regards, Hans Vogelaar

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:36 PM
    Tuesday, December 11, 2012 3:56 PM
  • Try it this way -

    Dim CurRow As Long

    Private Sub cmdNext_Click()
    CurRow = Range("CurRow").Value

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value
     
    If CurRow = Cells(Rows.Count, "A").End(xlUp).Row Then GoTo LastRec
    CurRow = CurRow + 1
    Range("CurRow").Value = CurRow

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    Exit Sub
    LastRec:
    MsgBox "You're at the last record!"
    End Sub

    Private Sub cmdPrevious_Click()
    CurRow = Range("CurRow").Value

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value
     
    CurRow = CurRow - 1
    If CurRow = 3 Then GoTo FirstRec
    Range("CurRow").Value = CurRow

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    Exit Sub
    FirstRec:
    MsgBox "You're at the first record!"
    End Sub

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:36 PM
    Tuesday, December 11, 2012 4:16 PM
  • Select a blank cell on your sheet, and name it CurRow, out in a column beyond your datatable - say, cell K2. The code will store the currentrow there, but having it there should not affect your other code.

    Your user form appears to be named "frmDataentry" so in your VBA code change all instances of UserForm1 to frmDataentry


    Tuesday, December 11, 2012 5:11 PM
  • Enter a valid row number, e.g. 2, in the cell you named CurRow.

    Regards, Hans Vogelaar

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 5:34 PM
  • You should have some initialization routine to populate the objects on your form when the form is first loaded, and your "CurRow" cell should have some valid row number in it. Otherwise, you may blank out some data - my suggestions only had to do with how to do Next/Previous....

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 6:09 PM
  • You need to initialize your form to populate with data - other wise you will overwirte data with blanks:

    Private Sub UserForm_Initialize()

    CurRow = Range("CurRow").Value

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    End Sub

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 6:40 PM
  • I would add a boolean variable and use it to control writing data to the spreadsheet:

    Dim boolDataOK As Boolean

    Used like

    If boolDataOK Then

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value

    End If

    and then change your reset routine to set it to false (prevent writing the data to the sheet)

    Private Sub cmdReset_Click()

    'Existing code

    boolDataOK = False 

    End Sub

    And set it to true after these blocks

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    by simply adding

    boolDataOK = True

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 7:54 PM
  • This goes at the top of your module:

    Dim boolDataOK As Boolean

    And anywhere that you write data to your sheet, wrap those lines in the IF..End If

    If boolDataOK Then    'Add this

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    ....

    Range("I" & CurRow).Value = frmDataentry.txtCom.Value

    End If  'And add this


    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:34 PM
    Tuesday, December 11, 2012 8:25 PM

All replies

  • There should be only one End Sub above Private Sub cmdPrevious_Click().

    You should declare CurRow as a variable of type Long, not as a Range:

    Dim CurRow As Long


    Regards, Hans Vogelaar

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:36 PM
    Tuesday, December 11, 2012 3:56 PM
  • Try it this way -

    Dim CurRow As Long

    Private Sub cmdNext_Click()
    CurRow = Range("CurRow").Value

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value
     
    If CurRow = Cells(Rows.Count, "A").End(xlUp).Row Then GoTo LastRec
    CurRow = CurRow + 1
    Range("CurRow").Value = CurRow

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    Exit Sub
    LastRec:
    MsgBox "You're at the last record!"
    End Sub

    Private Sub cmdPrevious_Click()
    CurRow = Range("CurRow").Value

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value
     
    CurRow = CurRow - 1
    If CurRow = 3 Then GoTo FirstRec
    Range("CurRow").Value = CurRow

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    Exit Sub
    FirstRec:
    MsgBox "You're at the first record!"
    End Sub

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:36 PM
    Tuesday, December 11, 2012 4:16 PM
  • Thanks for your prompt reply Bernie & Hans,

    I have changed the code and I am getting Run Time Error '424'. It says "Object Required' at the "UserForm1.Show".

    Also I have not defined any range as "CurRow" in my database. I am not sure how CurRow = Range("CurRow").Value would work.

    Please Advice,

    Thanks,


    Bunty

    Tuesday, December 11, 2012 5:06 PM
  • Select a blank cell on your sheet, and name it CurRow, out in a column beyond your datatable - say, cell K2. The code will store the currentrow there, but having it there should not affect your other code.

    Your user form appears to be named "frmDataentry" so in your VBA code change all instances of UserForm1 to frmDataentry


    Tuesday, December 11, 2012 5:11 PM
  • Yes, it was to rename the form name. I did it.

    Now Run Time Error 1004 : Method 'Range' of object'_Global' failed.

    When I click on "Debug" it takes me to the this line:

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value

    Thanks,


    Bunty

    Tuesday, December 11, 2012 5:20 PM
  • Enter a valid row number, e.g. 2, in the cell you named CurRow.

    Regards, Hans Vogelaar

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 5:34 PM
  • You should have some initialization routine to populate the objects on your form when the form is first loaded, and your "CurRow" cell should have some valid row number in it. Otherwise, you may blank out some data - my suggestions only had to do with how to do Next/Previous....

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 6:09 PM
  • Thanks Hans,

    Seems to be working, The data reflects on the form by clicking next and previous, however it deletes existing data in the database and the value of K2(CurRow) keeps changing I think due to this.

    In Next:

    If CurRow = Cells(Rows.Count, "A").End(xlUp).Row Then GoTo LastRec
    CurRow = CurRow + 1
    Range("CurRow").Value = CurRow

    In Prev.

    CurRow = CurRow - 1
    If CurRow = 3 Then GoTo FirstRec
    Range("CurRow").Value = CurRow

    When I click next previous. Right now it's 4. It is also removing/exchangin some data in the data base.

    I really appreciate you're looking in to this.


    Bunty

    Tuesday, December 11, 2012 6:09 PM
  • Hi Hans,

    For your reference I have posted my recent file at skydrive at below location for better understanding

    http://sdrv.ms/VAQaPm

    You will need to change the path : "C:\Database\Database.accdb" as per your system


    Bunty

    Tuesday, December 11, 2012 6:32 PM
  • You need to initialize your form to populate with data - other wise you will overwirte data with blanks:

    Private Sub UserForm_Initialize()

    CurRow = Range("CurRow").Value

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    End Sub

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 6:40 PM
  • Thanks Bernie and Hans,

    It worked great. Thanks :)

    One last help. Everything works perfect when I click next pre. but when I click "Reset" and then I click next pre. some data messed up. For example if the date picker has date 1/27/2012 now I clicked reset and then when I press next, the entire row's data  from column B diappears. The reset code is:

    ************

    Private Sub cmdReset_Click()


    For Each ctl In Me.Controls


    If TypeName(ctl) = "TextBox" Then
    ctl.Value = ""

    End If

    If TypeName(ctl) = "ComboBox" Then
    ctl.Value = ""


    End If
    Next ctl

     

    End Sub

    **************

    Thanks,


    Bunty

    Tuesday, December 11, 2012 7:03 PM
  • I would add a boolean variable and use it to control writing data to the spreadsheet:

    Dim boolDataOK As Boolean

    Used like

    If boolDataOK Then

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value

    End If

    and then change your reset routine to set it to false (prevent writing the data to the sheet)

    Private Sub cmdReset_Click()

    'Existing code

    boolDataOK = False 

    End Sub

    And set it to true after these blocks

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("B" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("C" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("D" & CurRow).Value
    frmDataentry.cboWork.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("F" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("G" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("H" & CurRow).Value
    frmDataentry.txtCom.Value = Range("I" & CurRow).Value

    by simply adding

    boolDataOK = True

    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:35 PM
    Tuesday, December 11, 2012 7:54 PM
  • Thanks for your reply Bernie,

    Where I have to paste the following code?

    **********

    Dim boolDataOK As Boolean

    Used like

    If boolDataOK Then

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("C" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("D" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("E" & CurRow).Value = frmDataentry.cboWork.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("G" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("H" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("I" & CurRow).Value = frmDataentry.txtCom.Value

    End If


    Bunty

    Tuesday, December 11, 2012 8:12 PM
  • This goes at the top of your module:

    Dim boolDataOK As Boolean

    And anywhere that you write data to your sheet, wrap those lines in the IF..End If

    If boolDataOK Then    'Add this

    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    ....

    Range("I" & CurRow).Value = frmDataentry.txtCom.Value

    End If  'And add this


    • Marked as answer by bunty_1244 Wednesday, December 19, 2012 11:34 PM
    Tuesday, December 11, 2012 8:25 PM
  • This seems quite confusing as it is not working for me. Let me be more specific. Request you to download a zipped folder kept at skydrive at: http://sdrv.ms/ZbNvCx

    You will just need to change the database path from which the combo box filter populates it’s lists.

    Once you download my zipped folder, when you follow this steps you will get to know what is happening wrong.

    Step 1: Once the database path is set as per your system and once you open the file click on Reset.

    Step 2: Now click Previous and Next

    Step 3: You will now see that some lines of data disappeared.

    Step 4: Select today’s date in date picker and click done. One record would be appended, now click on next or previous.

    Step 5: You will see that today’s date are copied somewhere above in the database.

    I am also trying to do following tasks.

    1. If we choose any date in date picker then rest fields should be filled with very first entry of that date, so that from that point user can do next and previous.
    2. Update: Upon clicking, the updated entries would be updated on the database at the same place.
    3. Delete: Upon clicking, the entire row would be deleted.

    I hope this will help you to better understand the problem.

    I really appreciate your help towards this,

    Thanks


    Bunty

    Tuesday, December 11, 2012 9:04 PM
  • What you are looking for is not just advice but for somebody to do your work. There are all sorts of Excel/DB consultants who are willing and able to take on your project - it should be a simple task to find and hire one.

    Bernie

    Tuesday, December 11, 2012 11:18 PM
  • Hi Bernie,

    I understand your concern. I posted my application to sky drive so that you can better understand the problem, and hence I have written instructions step by step so that you can view the problem.

    However you are also right from your side, I will try my level best to figure it out, and will let you know.

    Thank you soo much for your help.

    Best Regards,


    Bunty

    Wednesday, December 12, 2012 11:36 PM
  • Hi Bernie,

    I am now able to restict entries written to sheet, it's great , thanks :)

    But somehow after some time, I am not able to initialize the form, I am getting the error 35787 - " Can't set value to NULL when check box property = FALSE" I understand that this is to do with the datepicker, however I am not able to figure it out. Just an FYI : there are data present in the worksheet where user form writes the data...

    For your reference my entire code is:

    *****


    Private Sub cboTeam_Change()

    If cboTeam.MatchFound = False Then
      Me.lblTeam.Visible = True
      Else
      Me.lblTeam.Visible = False
      End If
     
     
    End Sub

    Private Sub cboUsAnalyst_Change()
    If cboUsAnalyst.MatchFound = False Then
      Me.lblUsAnalyst.Visible = True
      Else
      Me.lblUsAnalyst.Visible = False
      End If
     
    End Sub

    Private Sub cboWork_Change()
        Dim db As DAO.Database
        Dim rs As DAO.Recordset

     
        Me.txtReportName.Enabled = True
        Me.txtReportName.Value = ""
        Set db = OpenDatabase("C:\Database\Database.accdb")
        Set rs = db.OpenRecordset("SELECT * FROM tblNB", dbOpenDynaset)

     
        If Me.cboWork.Value <> "" Then
            rs.FindFirst "Field1=" & Chr(34) & Me.cboWork & Chr(34)

     
            If Not rs.NoMatch Then
           
                Me.txtReportName.Value = Me.cboWork.Value
                Me.cboReportType = "NA"
                Me.cboUsAnalyst = "NA"
                Me.txtReportNumber = "NA"
                Me.txtReportName.Enabled = False
                Me.cboUsAnalyst.Enabled = False
                Me.cboReportType.Enabled = False
                Me.txtReportNumber.Enabled = False
              
            ' MsgBox "Please add comments", vbExclamation, "*** MANDATORY ***"
            
            
     
            
               
            ElseIf Me.txtReportName.Value <> Me.cboWork.Value Then
           
                Me.txtReportName.Enabled = True
                'Me.txtReportName = ""
                Me.cboUsAnalyst.Enabled = True
                 Me.cboUsAnalyst = ""
                Me.cboReportType.Enabled = True
                 'Me.cboReportType = ""
                Me.txtReportNumber.Enabled = True
                'Me.txtReportNumber = ""
                Me.txtReportNumber.Enabled = True
                'Me.txtReportNumber = ""
               
     
               
               
               
            End If
        End If
        rs.Close
        db.Close
       
     
       
    End Sub

    Private Sub cboReportType_Change()

    If cboReportType.MatchFound = False Then
      Me.lblReportType.Visible = True
      Else
      Me.lblReportType.Visible = False
      End If

    End Sub

     

    Private Sub cmdClose_Click()
    Unload Me

    End Sub

    Private Sub cmdNext_Click()
    Dim CurRow As Long

    CurRow = Range("CurRow").Value

    If boolDataOK Then
    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.txtEMP.Value
    Range("C" & CurRow).Value = frmDataentry.txtName.Value
    Range("D" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("E" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("G" & CurRow).Value = frmDataentry.cboWork.Value
    Range("H" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("I" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("J" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("K" & CurRow).Value = frmDataentry.txtCom.Value
    Range("P" & CurRow).Value = frmDataentry.txtTotal.Value
    End If

    If CurRow = Cells(Rows.Count, "A").End(xlUp).Row Then GoTo LastRec
    CurRow = CurRow + 1
    Range("CurRow").Value = CurRow

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.txtEMP.Value = Range("B" & CurRow).Value
    frmDataentry.txtName.Value = Range("C" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("D" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("F" & CurRow).Value
    frmDataentry.cboWork.Value = Range("G" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("H" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("I" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("J" & CurRow).Value
    frmDataentry.txtCom.Value = Range("K" & CurRow).Value
    frmDataentry.txtTotal.Value = Range("P" & CurRow).Value
    boolDataOK = True
    Exit Sub
    LastRec:
    MsgBox "You're at the last record!"

     

     

     


    End Sub
     
    Private Sub cmdPrevious_Click()

    CurRow = Range("CurRow").Value

    If boolDataOK Then


    Range("A" & CurRow).Value = frmDataentry.DTPicker1.Value
    Range("B" & CurRow).Value = frmDataentry.txtEMP.Value
    Range("C" & CurRow).Value = frmDataentry.txtName.Value


    Range("D" & CurRow).Value = frmDataentry.cboTeam.Value
    Range("E" & CurRow).Value = frmDataentry.cboReportType.Value
    Range("F" & CurRow).Value = frmDataentry.txtReportNumber.Value
    Range("G" & CurRow).Value = frmDataentry.cboWork.Value
    Range("H" & CurRow).Value = frmDataentry.txtReportName.Value
    Range("I" & CurRow).Value = frmDataentry.cboUsAnalyst.Value
    Range("J" & CurRow).Value = frmDataentry.txtTimeSpent.Value
    Range("K" & CurRow).Value = frmDataentry.txtCom.Value
    Range("P" & CurRow).Value = frmDataentry.txtTotal.Value
     
     End If
     
     
    CurRow = CurRow - 1
    If CurRow = 1 Then GoTo FirstRec
    Range("CurRow").Value = CurRow

    'frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.txtEMP.Value = Range("B" & CurRow).Value
    frmDataentry.txtName.Value = Range("C" & CurRow).Value


    frmDataentry.cboTeam.Value = Range("D" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("F" & CurRow).Value
    frmDataentry.cboWork.Value = Range("G" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("H" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("I" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("J" & CurRow).Value
    frmDataentry.txtCom.Value = Range("K" & CurRow).Value
    frmDataentry.txtTotal.Value = Range("P" & CurRow).Value
    boolDataOK = True


    Exit Sub
    FirstRec:
    MsgBox "You're at the first record!"

    End Sub

    Private Sub cmdReset_Click()


    For Each ctl In Me.Controls


    If TypeName(ctl) = "TextBox" Then
    ctl.Value = ""

    End If

    If TypeName(ctl) = "ComboBox" Then
    ctl.Value = ""

    End If
    Next ctl

     

    boolDataOK = False

     

     


    End Sub


    Private Sub cmdDone_Click()

    Dim RowCount As Long
    Dim ctl As Control

    If Me.cboTeam.Value = "" Then
    MsgBox "Cannot left blank", vbExclamation, "Team"
    Me.cboTeam.SetFocus


     
       
    Exit Sub
    End If

    If Me.cboReportType.Value = "" Then
    MsgBox "Can not left blank", vbExclamation, "Report Type"
    Me.cboReportType.SetFocus
    Exit Sub
    End If

     

    If Me.txtReportNumber.Value = "" Then
    MsgBox "Can not left blank", vbExclamation, "Report Number"
    Me.txtReportNumber.SetFocus
    Exit Sub
    End If

    If Me.cboWork.Value = "" Then
    MsgBox "Can not be left blank", vbExclamation, "Work Done"
    Me.cboWork.SetFocus
    Exit Sub
    End If


    If cboWork.MatchFound = False Then
    MsgBox "Please select from the list", vbExclamation, "Workdone"

    Exit Sub
    End If


    If Me.txtReportName.Value = "" Then
    MsgBox "Can not left blank", vbExclamation, "Report Name"
    Me.txtReportName.SetFocus
    Exit Sub
    End If

    If Me.cboUsAnalyst.Value = "" Then
    MsgBox "Can not left blank", vbExclamation, "US Analyst"
    Me.cboUsAnalyst.SetFocus
    Exit Sub
    End If

    If Not IsNumeric(Me.txtTimeSpent.Value) Then
    MsgBox "Only numeric entry is allowed", vbExclamation, "Time Spent"
    Me.txtTimeSpent.SetFocus
    Exit Sub
    End If

     

     


    RowCount = Worksheets("Timesheet").Range("A1").CurrentRegion.Rows.Count
    With Worksheets("Timesheet").Range("A1")

    .Offset(RowCount, 0).Value = Me.DTPicker1.Value
    .Offset(RowCount, 1).Value = Me.txtEMP.Value
    .Offset(RowCount, 2).Value = Me.txtName.Value


    .Offset(RowCount, 3).Value = Me.cboTeam.Value
    .Offset(RowCount, 4).Value = Me.cboReportType.Value
    .Offset(RowCount, 5).Value = Me.txtReportNumber.Value

    .Offset(RowCount, 6).Value = Me.cboWork.Value

    .Offset(RowCount, 7).Value = Me.txtReportName.Value
    .Offset(RowCount, 8).Value = Me.cboUsAnalyst.Value
    .Offset(RowCount, 9).Value = Me.txtTimeSpent.Value
    .Offset(RowCount, 10).Value = Me.txtCom.Value
    .Offset(RowCount, 11).Value = Me.txtCom.Value
    '.Offset(RowCount, 16).Value = Me.txtTotal.Value


    End With
    ' Clear the form

    'For Each ctl In Me.Controls


    'If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
    'ctl.Value = ""


    'End If

     

    'Next ctl
    Me.txtReportNumber.Enabled = True
    Me.cboUsAnalyst.Enabled = True
    Me.cboReportType.Enabled = True

    MsgBox ("1 Record Appended to TimeSheet"), vbExclamation, "Thank You!"

      Me.lblTeam.Visible = False
      Me.lblReportType.Visible = False
      Me.lblWorkdone.Visible = False
      Me.lblUsAnalyst.Visible = False

     


    End Sub
    Private Sub cmdUpdate_Click()

    End Sub

     

    Private Sub CommandButton1_Click()
    Unload Me

     

    End Sub

    Private Sub DTPicker1_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
    DTPicker1.Value = Now()
    Range("A1").Value = DTPicker1.Value


    End Sub

    Private Sub lblTeam_Click()

    End Sub

    Private Sub lblWorkdone_Click()

    End Sub

    Private Sub txtCom_Change()


    If Me.cboWork.Value = "Report Improvement" Or Me.cboWork.Value = "Internal" Or Me.cboWork.Value = "Team Meeting" Or Me.cboWork.Value = "Training" Then
    MsgBox "Comments are mandatory: Do not write NA", vbExclamation, "Comments"
    Exit Sub
    End If


    'ElseIf Me.txtCom.Value <> "" Then
    'MsgBox "Can not left blank", vbExclamation, "Comments"
    Me.txtCom.SetFocus
    'Exit Sub
    'End If

     

    End Sub

     


    Private Sub Userform_Initialize()


    'You need remove the reference to the '"Microsoft DAO 3.51 (or 3.6) Object Library." _
      and add a reference to the Microsoft Office 14 (or 12) Access database engine Object Library."
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim NoOfRecords As Long
    Dim boolDataOK As Boolean

    CurRow = Range("CurRow").Value

    frmDataentry.DTPicker1.Value = Range("A" & CurRow).Value
    frmDataentry.txtEMP.Value = Range("B" & CurRow).Value
    frmDataentry.txtName.Value = Range("C" & CurRow).Value
    frmDataentry.cboTeam.Value = Range("D" & CurRow).Value
    frmDataentry.cboReportType.Value = Range("E" & CurRow).Value
    frmDataentry.txtReportNumber.Value = Range("F" & CurRow).Value
    frmDataentry.cboWork.Value = Range("G" & CurRow).Value
    frmDataentry.txtReportName.Value = Range("H" & CurRow).Value
    frmDataentry.cboUsAnalyst.Value = Range("I" & CurRow).Value
    frmDataentry.txtTimeSpent.Value = Range("J" & CurRow).Value
    frmDataentry.txtCom.Value = Range("K" & CurRow).Value
    frmDataentry.txtCom.Value = Range("P" & CurRow).Value

     

      'Open the .accdb form database to retrieve data
      Set db = OpenDatabase("C:\Database\Database.accdb")
      'Define the first recordset
      Set rs = db.OpenRecordset("SELECT * FROM tblTeam")
     
      'Determine the number of records in the recordset
      With rs
        .MoveLast
        NoOfRecords = .RecordCount
        .MoveFirst
      End With
      'Set the number of ListBox columns = number of fields in the recordset
      'ListBoxTeam.ColumnCount = rs.Fields.Count
      'Load the listbox with the retrieved records
      cboTeam.Column = rs.GetRows(NoOfRecords)
     
      'ListBoxReportType.Column = rs.GetRows(NoOfRecords)
      'ListBoxUsAnalyst.Column = rs.GetRows(NoOfRecords)
      'ListBoxWorkdone.Column = rs.GetRows(NoOfRecords)
      'Cleanup
      rs.Close
     
      'FOR ANOTHER LIST BOX REPORT TYPE
           
      'Define the first recordset
      Set rs = db.OpenRecordset("SELECT * FROM tblReportType")
     
      'Determine the number of records in the recordset
      With rs
        .MoveLast
        NoOfRecords = .RecordCount
        .MoveFirst
      End With
     
      cboReportType.Column = rs.GetRows(NoOfRecords)
      rs.Close
     
    'FOR ANOTHER LIST BOX WORKDONE

      Set rs = db.OpenRecordset("SELECT * FROM tblWorkdone")
     
      'Determine the number of records in the recordset
      With rs
        .MoveLast
        NoOfRecords = .RecordCount
        .MoveFirst
      End With
       cboWork.Column = rs.GetRows(NoOfRecords)

      rs.Close
     
    'FOR ANOTHER LIST BOX US Analsyst

      Set rs = db.OpenRecordset("SELECT * FROM tblUsAnalyst")
     
       With rs
        .MoveLast
        NoOfRecords = .RecordCount
        .MoveFirst
      End With
     
      cboUsAnalyst.Column = rs.GetRows(NoOfRecords)

      rs.Close

    lbl_Exit:

     

      Me.lblTeam.Visible = False
      Me.lblReportType.Visible = False
      Me.lblWorkdone.Visible = False
      Me.lblUsAnalyst.Visible = False
       
    Me.DTPicker1.Value = Date

     

      Exit Sub

       

    End Sub

     **********

    Please Advice.


    Bunty

    Friday, December 14, 2012 8:33 PM
  • Hi,

    Good News: The problem was the range "CurRow" cell Q2 which change when you click next and previous, I have recorded a macro and put that code when the form initialize, it basically set back q2's value to 2 so now I am not getting this error when the form is opened(initialized), however when I reached to the last cell where my data ends it the "CurRow" value is 30 it again showing the same error.("35787-Can't set value to NULL when check box property = FALSE") which I am trying to resolve.

    Other thing is that, before I was able to append values by clicking the "Done" button but now when I click done it does not append values to the sheet, may be it is happening due to "boolDataOK = False" which I need in my code in order to restrict any unusual entries written to the sheet. I hope once you review entire code, you would get to know what I am trying to say.

    My updated file is kept at: http://sdrv.ms/Ve9WkD

    Please Advice.


    Bunty

    Thursday, January 10, 2013 1:01 AM