How do I update a cell with Visual Basic Code in Access 2007

Question

So my project is to calculate a list of deadlines for various items based off a conference start date. I have been trying to figure out how to use Access 2007 (employer software) to generate the due dates based off a change in the conference start date.

My failures include: Write Conflict Message <-- trying to get rid of this Not recognizing the variable Tried a Query, but couldn't get the query to show in the Form

ConferenceStartDate is the column containing the user input on the Form, and the variable I want to base all other dates on. I have the Form use the "Before Update" subroutine to make the change.

Private Sub ConferenceStartDate_BeforeUpdate(Cancel As Integer)

  ' Concept is to enter Volume and Conference Date Start/End and have it calculate the rest of the dates
  ' Then compare to current date and create a report on over due, next due, etc
  ' Then create emails based on templates for next data

  Dim rstNameList As DAO.Recordset
  Set rstNameList = Application.CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
  Dim startDate As Date
  Dim endDate As Date
  Dim recordNumber As Integer
  Dim stringRecordNumber As String
  Dim stringSQL As String

  ' *** Second Attempt
  ' Gives Write Conflict message.  Weird, if you say Update, moving to the next record does not update, but saying No Update actually updates the record.
  recordNumber = [ID]
  stringRecordNumber = "ID=" & CStr(recordNumber)

  ' Gets the start date of the conference
  startDate = [ConferenceStartDate]

 ' Add seven "d"ays tp startDate
 endDate = DateAdd("d", 7, startDate)


 ' rstNameList.FindFirst stringRecordNumber
 ' rstNameList.Edit
    'rstNameList!VolumeName = "MC-130"
 '     rstNameList!ConferenceStartDate = startDate
 '     rstNameList!ConferenceEndDate = endDate
 ' rstNameList.Update
 ' rstNameList.Close

 ' Gets rid of the Write Conflict error message
 ' Command doesnt work
 ' If Forms("Table1").Dirty Then Forms("Table1").Dirty = False

 ' *** First Attempt
 'Works to add
 'Set db = CurrentDb
 'Set rs = db.OpenRecordset("Table1")
 'rs.AddNew
  'rs("ID") = 5
  'rs("VolumeName") = "KC-130"
  'rs("ConferenceStartDate") = "1/1/1111"
  'rs("ConferenceEndDate") = "1/2/1212"
 'rs.Update
 'rs.Close

 ' *** Third Attempt
 ' Doesn't actually update
 ' stringSQL = "UPDATE Table1 SET [ConferenceEndDate] = #" & CStr(endDate) & "# WHERE " & stringRecordNumber

 ' DoCmd.RunSQL stringSQL

 End Sub

Any one have any idea how to not have a Write Conflict Message and the dates update based of input from a Form of a single date?

Thanx!

score 0 · Accepted Answer · answered Jan 27 '14 at 11:26

0

If your form is bound to a table then you shouldn't be trying to use SQL statements to update the current record in the form. As you have found, that can lead to write conflicts because two "processes" are trying to update the same record at the same time.

Instead, you should create bound controls for the fields you want to update (making them hidden, if necessary) and then update the values of those controls.

answered Jan 27 '14 at 11:26

Gord Thompson

116,920
32
215
418

For anyone looking for explanation on [bound controls](http://stackoverflow.com/questions/13009890/bound-and-unbound-controls-in-ms-access). I found what I needed from this using adding the following code in the BeforeUpdate of the ConferenceStartDate text block: `me.ConferenceEndDate.Value = DateAdd ("d", 7, [ConferenceStartDate])` – Scuttle Jan 28 '14 at 03:13

How do I update a cell with Visual Basic Code in Access 2007

1 Answers1