Updating records via VBA in bound continuous form 1

[DrDan1]

Hi. I'm using Access 2010 and I'm stuck! I'm essentially putting together a system for booking jobs. I have a form for booking them (done and dusted) and a form for confirming a job as complete. when updating the job as completed, it needs to update TWO tables, including the jobs table.

Each job record has a marker for complete or not, and the form is bound to the jobs table with the criteria to show only ones that aren't complete. I have a [Complete] button next to each record that updates both tables and then refreshes the form. The record should be removed after the refresh as the complete marker is set to Yes.

When this happens I get a Write Conflict error. I know why it's doing this but I don't know how to get around it. It's a combination of using a bound form and code to update the tables. I'd like to NOT use a bound form, but I need to display the details for job before the complete button.

Another reason I'd like to not use a bound form is because some of the details, such as the person doing the job, should be editable, such as the time taken to do it, or the person doing it. I only want these details to be edited when using the button.

Can anyone offer any advice?

Thanks,
Dan


----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--Bruce Lee

 

[DrDan1]

FYI: The code I have to do the updates to the 2 tables is as follows:

Private Sub Command31_Click()
    Dim sqlQuery As String
    Dim sqlQuery2 As String
    Dim SiteID As Integer
    Dim UpgradeId As Integer
    Dim EngineerID As Integer
    Dim EnvironmentID As Integer
    Dim DoneTime
    
    SiteID = Me.Recordset.SiteID
    UpgradeId = Me.Recordset.UpgradeId
    EngineerID = Me.Recordset.Engineer
    DoneTime = Me.Recordset.ActualTime
    EnvironmentID = Me.Recordset.Environment
    Stream = Me.Recordset.Stream
    Patch = Me.Recordset.Patch
  
    DoCmd.SetWarnings False
    
    'UPDATE UPGRADES TABLE
    sqlQuery = "UPDATE Upgrades SET Engineer=" & EngineerID & ", ActualTime=" & DoneTime & _
        ", UpgradeComplete='Y' WHERE UpgradeID=" & UpgradeId
    DoCmd.RunSQL (sqlQuery)
    'MsgBox sqlQuery
    
    'UPDATE SITE VERSION
    sqlQuery2 = "UPDATE SiteVersion SET Stream=" & Stream & " WHERE Environment=" & EnvironmentID & _
        " AND SiteID=" & SiteID
    DoCmd.RunSQL (sqlQuery2)
    'MsgBox sqlQuery
    
    DoCmd.SetWarnings True
    Me.Refresh
  
End Sub

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--Bruce Lee

 

[TheAceMan1]

How are ya DrDan1 . . .

[blue]The record should be removed after the refresh as the complete marker is set to Yes.[/blue]

Your actually hiding the record with your marker ... so [purple]you need to show the new recordset[/purple] with a requery ([blue]Me.Requery[/blue]) instead of a refresh ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DrDan1]

Thanks Aceman. I tried that already but got the same alert. I'm pretty new to Access but I was wondering if it would be possible to show the results in an unbound form. That way I can ensure that updating an individual record will only be done by the code.

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--Bruce Lee

 

[TheAceMan1]

[blue] ... wondering if it would be possible to show the results in an unbound form.[/blue]

Since the results are [blue]a single record disappearing amoung many[/blue], I don't quite see the point since, a bound form would be more revealing.

Also ... is the record in question edited before the button is hit? ... It matters if the record is in [blue]edit mode[/blue] when you run your code.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DrDan1]

I currently have the form properties set to allow edits, although I want it set to no. But if I set it to no then I can't alter any of the combo or text boxes. As I said, I'm an access newbie, so maybe I'm going around this in completely the wrong way.

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--Bruce Lee

 

[TheAceMan1]

DrDan1 . . .

In one instance you want to update textboxes, in another you want to update only thru code, yet again you want to allow the user to enter their name and a duration of time. My point in mentioning this and edit mode has to do with the write conflict error mentioned. A bound record goes into [blue]edit mode[/blue] as soon as you start typing in an editable control (the record pointer changes to thee pencil icon). The record is now open for writing and you get the conflict when you attempt to write/update thru code at the same time.

The solution in this case is to save the record before you write thru code:

[blue]   [purple][b]If Me.Dirty Then Me.Dirty=False[/b][/purple] [green]'save record if in edit mode[/green]
   DoCmd.SetWarnings False
      [green]'
      'rest of code
      '[/green][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DrDan1]

You sir, are a star! Thank you. That did the trick wonderfully.

----------------------------------------
Knowing is not enough, we must apply. Willing is not enough, we must do.
--Bruce Lee