VBA userform troubles... adding to database no problem....updating modifying info = :(

A

andrew.gomez

only started to learn / use VBA code.

working on a userform for a data entry project. the data from the
userform gets updated onto a database contained within the same
worksheet (though hidden).

here is my code:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for Triage Time In
If Trim(Me.TextBox21.Value) = "" Then
Me.TextBox21.SetFocus
MsgBox "Please Enter Information into Triage Time In"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox21.Value
ws.Cells(iRow, 2).Value = Me.ComboBox15.Value
ws.Cells(iRow, 5).Value = Me.ComboBox12.Value
ws.Cells(iRow, 6).Value = Me.TextBox36.Value
ws.Cells(iRow, 7).Value = Me.TextBox35.Value
ws.Cells(iRow, 8).Value = Me.TextBox34.Value
ws.Cells(iRow, 9).Value = Me.TextBox33.Value
ws.Cells(iRow, 10).Value = Me.ComboBox13.Value
ws.Cells(iRow, 11).Value = Me.ComboBox11.Value
ws.Cells(iRow, 12).Value = Me.ComboBox23.Value
ws.Cells(iRow, 13).Value = Me.ComboBox21.Value
ws.Cells(iRow, 14).Value = Me.ComboBox14.Value
ws.Cells(iRow, 15).Value = Me.TextBox20.Value
ws.Cells(iRow, 16).Value = Me.ComboBox19.Value
ws.Cells(iRow, 17).Value = Me.ComboBox22.Value
ws.Cells(iRow, 18).Value = Me.ComboBox16.Value
ws.Cells(iRow, 19).Value = Me.TextBox18.Value
ws.Cells(iRow, 20).Value = Me.ComboBox17.Value
ws.Cells(iRow, 21).Value = Me.ComboBox24.Value
ws.Cells(iRow, 22).Value = Me.ComboBox25.Value
ws.Cells(iRow, 23).Value = Me.TextBox17.Value
ws.Cells(iRow, 24).Value = Me.TextBox39.Value
ws.Cells(iRow, 25).Value = Me.TextBox19.Value
ws.Cells(iRow, 26).Value = Me.ComboBox18.Value
ws.Cells(iRow, 3).Value = Me.ComboBox20.Value
ws.Cells(iRow, 27).Value = Me.TextBox15.Value
ws.Cells(iRow, 28).Value = Me.ComboBox10.Value
ws.Cells(iRow, 29).Value = Me.TextBox26.Value
ws.Cells(iRow, 30).Value = Me.TextBox28.Value
ws.Cells(iRow, 49).Value = Me.TextBox40.Value

'clear the data

Me.ComboBox14.Value = ""
Me.TextBox21.Value = ""
Me.ComboBox15.Value = ""
Me.ComboBox12.Value = ""
Me.TextBox36.Value = ""
Me.TextBox35.Value = ""
Me.TextBox34.Value = ""
Me.TextBox33.Value = ""
Me.ComboBox13.Value = ""
Me.ComboBox11.Value = ""
Me.ComboBox23.Value = ""
Me.ComboBox21.Value = ""
Me.TextBox20.Value = ""
Me.ComboBox19.Value = ""
Me.ComboBox22.Value = ""
Me.ComboBox16.Value = ""
Me.TextBox18.Value = ""
Me.ComboBox17.Value = ""
Me.ComboBox24.Value = ""
Me.ComboBox25.Value = ""
Me.TextBox17.Value = ""
Me.TextBox39.Value = ""
Me.TextBox19.Value = ""
Me.ComboBox18.Value = ""
Me.ComboBox20.Value = ""
Me.TextBox15.Value = ""
Me.ComboBox10.Value = ""
Me.TextBox26.Value = ""
Me.TextBox28.Value = ""
Me.TextBox40.Value = ""
Me.ComboBox14.SetFocus

End Sub

Private Sub cmdClear_Click_Click()

Me.ComboBox14.Value = Null
Me.TextBox21.Value = Null
Me.ComboBox15.Value = Null
Me.ComboBox12.Value = Null
Me.TextBox36.Value = Null
Me.TextBox35.Value = Null
Me.TextBox34.Value = Null
Me.TextBox33.Value = Null
Me.ComboBox13.Value = Null
Me.ComboBox11.Value = Null
Me.ComboBox23.Value = Null
Me.ComboBox21.Value = Null
Me.TextBox20.Value = Null
Me.ComboBox19.Value = Null
Me.ComboBox22.Value = Null
Me.ComboBox16.Value = Null
Me.TextBox18.Value = Null
Me.ComboBox17.Value = Null
Me.ComboBox24.Value = Null
Me.ComboBox25.Value = Null
Me.TextBox17.Value = Null
Me.TextBox39.Value = Null
Me.TextBox19.Value = Null
Me.ComboBox18.Value = Null
Me.ComboBox20.Value = Null
Me.TextBox15.Value = Null
Me.ComboBox10.Value = Null
Me.TextBox26.Value = Null
Me.TextBox28.Value = Null
Me.TextBox40.Value = Null

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "To ensure you want to terminate this Program Please use
CLOSE tab located below"
End If
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub
Code:
--------------------------

very messing but does what i need it to at the moment.

i have 3 command tabs (update / close / clear all)

i want to add another command tab to search the (hidden) database then
populate the textbox's / combobox's if possible.

then the user can back track through what has already been enter then
make changes as and where needed.

can anyone point me in the right direction or save me from myself :(

any help will be greatly appreciated.

thanks

Andrew
 
V

Vasant Nanavati

Maybe I'm not understanding the problem, but why not just use the reverse
procedure while showing the UserForm?

Me.TextBox21.Value = ws.Cells(iRow, 1).Value

Etc.
______________________________________________________________________
 
A

andrew.gomez

i really dont know how to, to refer to the worksheet, cell range, then
to populate each field as required. and then to update the database
without duplications.

everything else i have managed to work out, but this seems alot
tricker than expected.

i can see what your saying, but im not sure i understand in the long
run. i would need to search from say... Textbox35. being the only
unique field i have (9 digit number).... then on the database there
will be roughly 100 entries per week. say 400 per month.

i need the find command to search through these entries. then to
repopulate the userform (with the missing information still blanked)
then the user can add the information as and where required then
update this info to complete the database.

again sorry for not making myself clear.

any help will be appreciated.

Andrew


---------------------------------------------------------------------
 
Top