Good evening to all.
Im doing my first data entry form using visual basic forms, its going pretty well and for the most part its working as i need to.
But a critical issue I can't figure out is if its possible to edit previous entries. I'll explain:
I work on a library and I need to keep a record of which books are given to which people, to consult in the library reading room, so i have a few combo boxes for all the users types and all, then a simple text box for the book id number. Then i need to add two more field, one is the time stamp of the moment i provide the book to the user, and the other is the time stamp of the moment the user returns the book.
I can easily add the first one using simply .Value = Format(Now, "hh:mm:ss"), but thats where the easy parts end.
I figure i need to add an ID column to identify the entries, then somehow call that id number to retrieve all the fields data and only change the fHoraOut field and save again (thats what i used to do when I made some forms using php and mysql)... i hope im making some sense here... anyway here is my code so far:
Private Sub fGuardar_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("DatosCampus")
'find first empty row in database
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
With ws
.Cells(lRow, 1).Value = Me.fRegID.Value
.Cells(lRow, 2).Value = Me.tFecha.Value
.Cells(lRow, 3).Value = Me.fNombre.Value
.Cells(lRow, 4).Value = Me.cDestinatario.Value
.Cells(lRow, 5).Value = Me.cTipoDocumento.Value
.Cells(lRow, 6).Value = Me.fNumero.Value
.Cells(lRow, 7).Value = Me.cTipoConsulta.Value
.Cells(lRow, 8).Value = Me.fInventario.Value
.Cells(lRow, 9).Value = Me.fHoraIn.Value
.Cells(lRow, 10).Value = Me.fHoraOut.Value
End With
'clear the data
Me.fRegID.Value = ""
Me.tFecha.Value = Format(Date, "yyyy/mm/dd")
Me.fNombre.Value = ""
Me.cDestinatario.Value = ""
Me.cTipoDocumento.Value = ""
Me.fNumero.Value = ""
Me.cTipoConsulta.Value = ""
Me.fInventario.Value = ""
Me.fHoraIn.Value = Format(Now, "hh:mm:ss")
Me.fHoraOut.Value = ""
Me.fNombre.SetFocus
End Sub
Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("datoslistas")
For Each cDoc In ws.Range("Tipo_Documento")
With Me.cTipoDocumento
.AddItem cDoc.Value
End With
Next cDoc
For Each cCon In ws.Range("Tipo_de_Consulta")
With Me.cTipoConsulta
.AddItem cCon.Value
End With
Next cCon
For Each cDes In ws.Range("destinatario")
With Me.cDestinatario
.AddItem cDes.Value
End With
Next cDes
Me.tFecha.Value = Format(Date, "yyyy/mm/dd")
Me.fRegID.Value = ""
Me.fHoraIn.Value = Format(Now, "hh:mm:ss")
Me.fHoraOut.Value = ""
Me.fNombre.SetFocus
End Sub
and the columns i have are:
ID
Fecha
Nombre
Destinatario
Tipo de Documento
Numero
Tipo de Consulta
Numero de Inventario
Hora de retiro
Hora de devolucion
Observaciones
the "Observaciones" one is to be filled by me in case i need to manually to the worksheet.
Any help would be greatly appreciated.
Bookmarks