Loading database to userform from list box

[research15]

I would be grateful for any help with the following problem. I have built a userform to help with inputting data for a research project due to the number of variables involved and to allow for comparison of data at the time of input. By following guides I have built a form that works well and exports to the database using IF, iRow and visible commands to make things work smoothly with a listbox showing key columns from the database.

The final part that would make data input much easier is the ability to go back and modify records.

This is the code for writing to the `database` sheet:

Sub Submit()


Dim sh As Worksheet
Dim iRow As Long


Set sh = ThisWorkbook.Sheets("Database")


iRow = [Counta(Database!A:A)] + 1


With sh


.Cells(iRow, 1) = iRow - 1
.Cells(iRow, 2) = frmFORM.txtTrialID.Value
.Cells(iRow, 3) = IIf(frmFORM.chkcomplete.Value = True, "Complete", "Incomplete")
.Cells(iRow, 4) = IIf(frmFORM.chkhistcomp.Value = True, "Complete", "Incomplete")


.Cells(iRow, 5) = IIf(frmFORM.chkCFsurgcomp.Value = True, "Complete", "Incomplete")
.Cells(iRow, 6) = IIf(frmFORM.chkCFSurg2Comp.Value = True, "Complete", "Incomplete")
.Cells(iRow, 7) = IIf(frmFORM.chkCFPathComp.Value = True, "Complete", "Incomplete")
.Cells(iRow, 8) = IIf(frmFORM.chkXRComp.Value = True, "Complete", "Incomplete")




.Cells(iRow, 9) = IIf(frmFORM.chkcomplete.Value = True And frmFORM.chkhistcomp.Value = True And frmFORM.chkCFsurgcomp.Value = True And frmFORM.chkCFSurg2Comp.Value = True And frmFORM.chkCFPathComp.Value = True And frmFORM.chkXRComp.Value = True, "Complete", "Incomplete")

End With
End Sub

This is my attempt at coding for recalling the data for modification:

Sub Modify()


Dim sh As Worksheet
Dim iRow As Long


Set sh = ThisWorkbook.Sheets("Database")


iRow = lstDatabase.ActiveCell


frmFORM.txtDateSurg.Value = Sheets("Database").Cells(iRow, 10).Value
frmFORM.txtAge.Value = Sheets("Database").Cells(iRow, 11).Value






End Sub

This was trying to use the selected row within my listbox to set the row to be recalled. I am only trying with a section of database first before I expand to all the data.

From my understanding if I can get the data to populate my userform, I could then modify and this would overwrite the database upon clicking save keeping the same row number.

Any help would be greatly appreciated. I have found several videos and guides to how to recall information and the complexity seems to vary wildly and I've tried with very limited success.

Many thanks

 

[dmt32]

Hi,
welcome to forum

As you appear to be load your listbox using RowSource property you can get the correct record row using Listindex

Untested but try these codes in your userform

Dim sh          As Worksheet
 
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim ctrl        As Variant
    Dim c           As Long, iRow As Long
  
   
    iRow = Me.ListBox1.ListIndex + 2
   
   
    c = 2
    For Each ctrl In Array(Me.txtTrialID, Me.chkcomplete, Me.chkhistcomp, _
        Me.chkCFsurgcomp, Me.chkCFSurg2Comp, Me.chkCFPathComp, Me.chkXRComp)
        With sh.Cells(iRow, c)
            ctrl.Value = IIf(c = 2, .Value, CBool(.Value = "Complete"))
        End With
        c = c + 1
    Next
End Sub


Private Sub UserForm_Initialize()
     Set sh = ThisWorkbook.Sheets("Database")
End Sub

Change the name of ListBox1 as required.

If you Double Click your listbox, hopefully, the correct record will populate your controls with correct values

Note: your object variable sh should be placed at the TOP of the forms code page OUTSIDE any procedure. The variable is initialized when form is first shown.
This approach makes it available to all procedures in your form & saves repeating the code.

- All other instances of the line: Set sh = ThisWorkbook.Sheets("Database") and the variable sh can be deleted.

Hope helpful

Dave

 

[research15]

Many thanks for your help.

I cant get the code to run using the double click, so I tried linking it to a command button - which did get the code to run, but is flagging an error on the line `With sh.Cells(iRow, c)`

 Private Sub CommandButton1_Click()

    Dim ctrl        As Variant
    Dim c           As Long, iRow As Long
 
  
    iRow = Me.lstDatabase.ListIndex + 2
  
  
    c = 2
    For Each ctrl In Array(Me.txtTrialID, Me.chkcomplete, Me.chkhistcomp, _
        Me.chkCFsurgcomp, Me.chkCFSurg2Comp, Me.chkCFPathComp, Me.chkXRComp)
        With sh.Cells(iRow, c)
            ctrl.Value = IIf(c = 2, .Value, CBool(.Value = "Complete"))
        End With
        c = c + 1
    Next

End Sub

For the Set sh - I have the below in the form aswell if that changes anything.

Private Sub UserForm_Initialize()
Set sh = ThisWorkbook.Sheets("Database")
Call Reset

End Sub

Any ideas why it is giving an error code at that line?

Many thanks again for your help.

 

[dmt32]

I cant get the code to run using the double click, so I tried linking it to a command button - which did get the code to run, but is flagging an error on the line `With sh.Cells(iRow, c)`

Private Sub UserForm_Initialize()
Set sh = ThisWorkbook.Sheets("Database")
Call Reset

End Sub[/CODE]

Any ideas why it is giving an error code at that line?

Many thanks again for your help.

You don't say what the error is but will hazard a guess at "object required"? If so, maybe something in your Reset code that is clearing the object variable sh.
suggest delete the code call from the UserForm_Initialize event & see if resolves.

Dave

 

[research15]

Sorry, the error is 424 - `object required`

Ive deleted the userform_initialize event and the problem remains.

My Call Reset - triggers all the code for loading the options for CMB boxes..etc and populating the listbox. The code for reset is below (I have removed the variable option coding and reseting to save space).

Sub Reset()

Dim iRow As Long
iRow = [Counta(Database!A:A)]   ' Identifying the last row


With frmFORM

.chkCFsurgcomp.Value = False
.chkCFSurg2Comp.Value = False
.chkCFPathComp.Value = False
.chkXRComp.Value = False


.txtTrialID.Value = ""
.txtAge.Value = ""
.txtImagesize.Value = ""
.txtDateSurg.Value = ""

This is the end of the code to populate the listbox

.lstDatabase.ColumnCount = 9
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "40,40,70,70,70,70,70,70"

If iRow > 1 Then
    .lstDatabase.RowSource = "Database!A2:I" & iRow
    Else
    .lstDatabase.RowSource = "Database!A2:I2"
    End If
    
    

End With


End Sub

The code for submitting has the only sh definitions now and is as follows:

Sub Submit()

Dim sh As Worksheet
Dim iRow As Long

Set sh = ThisWorkbook.Sheets("Database")

iRow = [Counta(Database!A:A)] + 1

With sh

.Cells(iRow, 1) = iRow - 1
.Cells(iRow, 2) = frmFORM.txtTrialID.Value
.Cells(iRow, 3) = IIf(frmFORM.chkcomplete.Value = True, "Complete", "Incomplete")
.Cells(iRow, 4) = IIf(frmFORM.chkhistcomp.Value = True, "Complete", "Incomplete")

(Again reduce the number of rows to save space and the format is the same)

Do I need to code how to interpret the output from Complete/Incomplete back to a True/false value?

Many thanks

 

[dmt32]

Sorry, the error is 424 - `object required`

Ive deleted the userform_initialize event and the problem remains.

I said delete the Reset code call from the UserForm_Initialize event NOT the event itself

You need to re-instate it as I published and also follow the notes I provide

Note: your object variable sh should be placed at the TOP of the forms code page OUTSIDE any procedure. The variable is initialized when form is first shown.
This approach makes it available to all procedures in your form & saves repeating the code.

- All other instances of the line: Set sh = ThisWorkbook.Sheets("Database") and the variable sh can be deleted.

Dave

 

[research15]

Sorry for the delay. Self-inflicted stupidity causing problems. I was using fields outside of my listbox for my testing and obviously could not get the correct data fields to make it work.

The solution is correct and works.

 

[dmt32]

Sorry for the delay. Self-inflicted stupidity causing problems. I was using fields outside of my listbox for my testing and obviously could not get the correct data fields to make it work.

The solution is correct and works.

No worries glad you managed to resolve - appreciate feedback

Dave