Timestamp using a button in VBA

[insomnai]

I have a form with unbound textboxes and listboxes etc, this form holds a subform based on a table (thanks to OBP for all his help so far...) now what I would like to do is select the chosen record in dataview in the subform, and clicking on a button on a main form, timestamp a defined field in table (which will update the subform table)

I'm sure this one will be simple, but I think I may be over complicating it with what I'm doing.

Any help is always greatfully received.

what i have tried so far is adding a new button 'btnCalled' on the 'frmNewBookings' and in it's 'onclick' event, i'm using:

!Called = Me.txtNow ('Called' is a field in the table 'tblNewBookings', 'txtNow' is a textbox with '=now()' written in it on the main form 'frmNewBookings')

Craig / Insomnai

[OBP]

Craig, what does the !Called refer to, is it part of a Recordset or is it a Field on your subform.
If it is the latter then the teminology is wrong.
Where is the Button on the Main Form or Subform.
They both change the syntax.

[insomnai]

Aaah, cheers OBP, the table where the timestamp information is headed for is the same table we spoke of in our last exercise. unbound information being sent to the recordset. !Called is a field name in the same table that got !Name, !chkWaitReturn etc.

The button doing the timestamp will add time info to the table at any given time when a record from the table is selected in a subform...

I just read that and it sounds like i've got a mental illness lol.

Craig

[OBP]

Craig, to use that you also have to open the Recordset to the Table, did you do that?

[insomnai]

OBP, yes I think I did that. This is the bit i've now got on my 'add booking' button that is on my main form:

Private Sub btnAddBooking_Click()
Dim rstable As Object
Set rstable = CurrentDb.OpenRecordset("tblNewBookings")
With rstable
.AddNew
!Name = Me.txtName
!Called = Now()
!From = Me.lstTrvFrom
!Type = Me.cboCustType
!To = Me.lstTrvTo
If Me.chkWaitReturn = -1 Then !WaitReturn = -1
!Driver = Me.lstDriversOnDuty
.Update
.Bookmark = .LastModified
End With
rstable.Close
Set rstable = Nothing
Me.subfrmNewBookings.Requery
Me.txtName = ""
Me.txtTrvFrom = ""
Me.TxtTrvTo = ""
Me.lstTrvFrom = ""
Me.lstTrvTo = ""
Me.chkWaitReturn = False
Me.lstDriversOnDuty = ""
Me.txtName.SetFocus
End Sub


Now, I've got the time of call logged using the Now() bit being added to the 'Called' field.. but what I want to do is selecting a record on the subform (which in turn is a dataview of the table that the above code adds to) and then using a second button lets say 'btnDispatched' add Now() to the 'Dispatched' field for that selected record...

I have used similar code to what you suggested but it adds the current time to the first record and keeps updating the first record regardless of which record is selected.

Private Sub btnDispatched_Click()
Dim rstable As Object
Set rstable = CurrentDb.OpenRecordset("tblNewBookings")
With rstable
-->.Edit<--
!Dispatched = Now()
.Update
-->.Bookmark = .LastModified<--
End With
rstable.Close
-->Set rstable = Nothing<--
Me.subfrmNewBookings.Requery
End Sub

I think where I have added the --> <-- is where it is wrong but I don't know how to put it right...

Thanks in advance!

Craig

[OBP]

Craig, yes it would only update the first record, to add records you use

.AddNew

not .Edit.

But if you want to put the data in to the same Record Number as the Form you have match records with
rs.Bookmark = me.bookmark

Of course if you are using an unbound Form with no record numbers you will have to "Find" the Record in the Table that you want to put the data in to.

[insomnai]

Aaah, so finding the record in VBA should be my next port of call.

Would the code I posted work once I 'find' the record? And what would be the best way to find a record in VBA?

You see, the thing I am confused with is the subform which displays the records that have been added from the unbound form/textboxes/listboxes are selectable in a subform. I want to edit a record which has been selected from that subform (datatable view) with the btnDispatched button that is on the main form.

Any ideas?

Regards

Craig

[OBP]

If the record that you want is the last record you can just rstable.movelast.
However if you want to find a specific record you can use the Key Field for that record and use it in an SQL Statement like this
Dim db As Database
Dim rs As Object
Dim SQL As String

SQL = "SELECT tblGroups.* " & _
"FROM tblGroups " & _
"WHERE GroupID = " & groupn
Set rs = CurrentDb.OpenRecordset(SQL)
If rs.RecordCount <> 0 Then rs.Delete
rs.Close
Set rs = Nothing

In this particular version I have used a Variable called groupn to look for, but you can use the ID number on your form if you know it. When you try and find that you might have to use the
"WHERE GroupID = '" & groupID & "' "
as the Syntax varies to what you use.