VBA Userform - Editing Controls (Similar to Dataform) (Excel 97)

[Phendrena]

Hi there,

Would anyone please be able to point me to a guide that advises how I can
add editing controls to a userform?

I have setup a data entry userform.
I would like to add the ability to search for records and then edit and
update the records. I have the search working - it nicely populates a
seperate listbox.

What I haven't worked out is :
How to get the data back from the data worksheet and into the fields on the
userform & how to update the data and not add a new record.

Can anyone help me please?

 

[royUK]

There's a database form example here

'VBA Project* Protection' (http://www.excel-it.com/vba_examples.htm)


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site
' (http://www.excel-it.com)

 

[Phendrena]

Hi,

Thanks for the reply.
I've had a look and downloaded DataBaseForm.xls (I can't seem to get teh
actual form to work on Excel 97). Anyway, looking through the code i've come
to the following :

Private Sub cmbFind_Click()
Dim strFind, FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Set rSearch = Sheet1.Range("a6", Range("a65536").End(xlUp))
strFind = Me.TextBox1.Value 'what to look for
Dim f As Integer
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
With Me 'load entry to form
.TextBox2.Value = c.Offset(0, 1).Value
.TextBox3.Value = c.Offset(0, 2).Value
.TextBox4.Value = c.Offset(0, 3).Value
.cmbAmend.Enabled = True 'allow amendment or
.cmbDelete.Enabled = True 'allow record deletion
.cmbAdd.Enabled = False 'don't want to duplicate record
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1 'count number of matching records
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
MsgBox "There are " & f & " instances of " & strFind
Me.Height = frmMax
End If
Else: MsgBox strFind & " not listed" 'search failed
End If
End With
End Sub

I'm trying to adapt the code to work with my spreadsheet :-

Private Sub cmdSearch_Click() ' ///// Search
Routine ///// '
Dim strFind, FirstAddress As String 'what to find
Dim rSearch As Range 'range to search
Set rSearch = YBS.Range("B2", Range("B2:B5000"))
strFind = Me.txtActionedSearch.Value 'what to look for
Dim f As Integer
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
With Me 'load entry to form
.cboActioned.Value = c.Offset(0, 1).Value
.txtCanxDate.Value = c.Offset(0, 2).Value
.txtPHName.Value = c.Offset(0, 3).Value
'.cmbAmend.Enabled = True 'allow amendment or
'.cmbDelete.Enabled = True 'allow record deletion
'.cmbAdd.Enabled = False 'don't want to duplicate record
f = 0
End With
FirstAddress = c.Address
Do
f = f + 1 'count number of matching records
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
If f > 1 Then
MsgBox "There are " & f & " instances of " & strFind
'Me.Height = frmMax
End If
Else: MsgBox strFind & " not listed" 'search failed
End If
End With
End Sub

When i click the update command button excel errors with rune-time error
'424' object required.

Can you advise on how to correct the error please?

Thanks,