CODEWITHSUNDEEP
ms-accessvbaexcel
PrestonDocks
PrestonDocks

Reputation: 5418

After Adding records to Recordset new record not available in the sub procedure

I have a sub procedure that adds a new record to the recordset.

Dim RS As Recordset
Set RS = getRSObject("Tickets")
RS.AddNew
RS.Fields("subject").Value = Me.txtSubject.Text
RS.Fields("detail").Value = Me.txtDetail.Text
RS.Fields("dateopen").Value = Format(Now, "mm/dd/yy hh:nn")
RS.Fields("status").Value = "Open"
RS.Fields("user").Value = Me.ComboUser.Text
RS.Fields("assignedto").Value = Me.comboAssignedTo.Text
RS.Fields("priority").Value = Me.ComboPriority.Text
RS.Fields("category").Value = Me.comboCategory.Text
RS.Fields("unplanned").Value = Me.cbUnplannedWork.Value
RS.Save

RS.Close
doEvents

My problem is that a few lines further down in the same module, I update the list of tickets by querying the same access table. But this new record is not in this recordset. It is almost as though the records are not being written to access until the sub procedure finishes.

I refresh my list of tickets manually after the above procedure has completed I see the record that I inserted.

If I run the insert as a full sql statement as in "insert into tickets (......" this works fine and I see the record in the list, but it prone it issues, if users put characters such as hyphen's in text fields.

Can anyone give me a command that will force the data to be written to the Access database and made available before the sub finishes.

Upvotes: 0

Views: 237

Answers (1)

danielpiestrak
danielpiestrak

Reputation: 5439

Try using RS.Update instead of RS.Save

Dim RS As Recordset
Set RS = getRSObject("Tickets")
RS.AddNew
RS.Fields("subject").Value = Me.txtSubject.Text
RS.Fields("detail").Value = Me.txtDetail.Text
RS.Fields("dateopen").Value = Format(Now, "mm/dd/yy hh:nn")
RS.Fields("status").Value = "Open"
RS.Fields("user").Value = Me.ComboUser.Text
RS.Fields("assignedto").Value = Me.comboAssignedTo.Text
RS.Fields("priority").Value = Me.ComboPriority.Text
RS.Fields("category").Value = Me.comboCategory.Text
RS.Fields("unplanned").Value = Me.cbUnplannedWork.Value

RS.Update


RS.Close
doEvents

Upvotes: 1

Related Questions