Update database table with trigger not working?

[jan00se]

I have this form where user enters a time (i.e - 15:45) into a textbox which is stored to this field in the database. For data checking purposes we also have two fields, one to store the hour (15) and the other field to store minute (45). My problem is my code for some reason cannot update the hour and minute field.
This is my code. Basically users enters to tm_rand text box 15:45. Just for simplicity I am just trying to update hour field (h_rand) but even this is not working. I ran the query to test SQL code and it works so SQL code should be alright. The msgbox test123 does appear and no error msg.

Private Sub tm_rand_AfterUpdate()
Dim db As Database

    Dim var1 As String

    Dim h As Integer
    Dim m As Integer
    h = Left(tm_rand, 2)
    m = Mid(tm_rand, 4, 2)
    Set db = CurrentDb()
    var1 = "UPDATE Entry4 SET h_rand = " & h & " where [id] = '" & id & "'"
    DoCmd.SetWarnings False
    DoCmd.RunSQL var1
    MsgBox ("test123")

 

[dhookom]

I would reference controls on the form with "Me." for instance

 h = Left(Me.tm_rand, 2)

I would also not use string functions like Left and Mid with date/time fields. Consider using DatePart() to grab the hours and minutes.

What is the data type of Iid and where does it come from? Again if it is a bound control, consider prefixing with "Me." to avoid confusion.

Have you tried using a breakpoint so you can step through your code?


Duane
Hook'D on Access
MS Access MVP

 

[jan00se]

I just change my code a bit and still get same problem. ID is from a text field in the form. The data type of id is text and I did put quote around it in the SQl.

 Dim db As Database

    Dim var1 As String

    Dim h As Integer
    Dim m As Integer
      h = DatePart("h", Me.tm_rand) ***New 
    'm = Mid(tm_rand, 4, 2)
    Set db = CurrentDb()
    MsgBox (id)
    var1 = "UPDATE Entry4 SET h_rand = " & h & " where [id] = '" & Me.id & "'"  ** NEw
    DoCmd.SetWarnings False
    DoCmd.RunSQL var1
    MsgBox ("test123")
[\code]

 

[dhookom]

If this is a form bound to the table Entry4, then why not just update a control bound to h_rand?

What makes you think it's not working? If your form is bound to Entry4, the update won't appear unless the form is requeried or refreshed.

Duane
Hook'D on Access
MS Access MVP

 

[jan00se]

The problem is the update does not appear in the Entry4 table. I am not sure why I cannot update the information in the table. User enters time into tm_rand text field.

 

[MazeWorX]

Sorry to wade in but

DoCmd.SetWarnings False

you need to SetWarnings True when your done. also why not use Split

dim vTime as Variant

vTime = Split(Me.tm_rand, ":")

var1 = "UPDATE Entry4 SET h_rand = " & vTime(0) & " where [id] = '" & Me.id & "';"

vTime(1) would reference the minutes portion

And for testing purposes comment out SetWarnings False so you can see what the query is doing. I assume you update the minutes somewhere else? I would also assume there is more code?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[dhookom]

Thanks MazeWorX. Wade in anytime.

Duane
Hook'D on Access
MS Access MVP

 

[jan00se]

Right now I have just this update statement.

var1 = "UPDATE Entry4 SET h_rand = 13 where id = '001-012-004'"

I changed the set warning to true and after the msgbox asking if you want to update the record. I clicked yes and got this error.

MS Access can't update all records in the update query.
MS Office access didn't update .....1 record(s) due to lock violations....

 

[jan00se]

Btw the code just posted is the entire code.

 

[dhookom]

If you have the record open in your form you are probably locking it. You ignored my question regarding the current form's record source and why you need to run a query.

Duane
Hook'D on Access
MS Access MVP

 

[jan00se]

I am not running a query. I wanted to make sure the SQL works. I meant I tested the SQL through query and was able to update. The entire coding is VBA.

 

[jan00se]

what do you meant by locking it? How do I unlock it. When I close it I keep getting this message saying someone else changed the record do I want to save the change to a clipboard.

 

[dhookom]

jan00se,
Since the code is running in a form, can we assume it is a bound form?

What is the record source of the form? Is it Entry4 or include Entry4?

If your form is bound to Entry4 and the current record is determined by Me.ID then your query is attempting to update the record in your form. This would not make sense because you would be able to simply edit/update the field in the form rather than running a query.

At the very least, you would need to save the record in the form prior to updating the same record with a SQL statement.

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

Ahhh now we are getting some where. Sorry jan00se some time we need to prompt the op for all of the information to get to the bottom of the problem.

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[jan00se]

You are right I need to save the record first before updating the table. I did notice that before I clicked yes for the save new changes that the table did store current tm_rand. Then when I committed the new change the tm_rand goes back to the old time (15:45). Here is the code in case someone wants to use it. Thank you Duane and MazeWorX for your help.

Private Sub tm_rand_AfterUpdate()

RunCommand acCmdSaveRecord
Dim db As Database

Dim var1 As String

Dim h As Integer
Dim m As Integer
h = DatePart("h", Me.tm_rand)
m = DatePart("n", Me.tm_rand)
Set db = CurrentDb()
var1 = "UPDATE Entry4 SET h_rand = " & h & ", m_rand =" & m & " where [id] = '" & Me.id & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL var1
End Sub

 

[dhookom]

jan00se,
I asked you 3 times if your form was bound to the table you were attempting to update and you never answered. If you don't understand the question then you should ask for clarification rather than ignoring someone who is attempting to help you.

Duane
Hook'D on Access
MS Access MVP

 

[jan00se]

You never asked me directly if my form was bound. This : "If this is a form bound to the table Entry4, then why not just update a control bound to h_rand? " I was not aware this is a question asking me if my form was bound. I realized that you weren't aware my form was bound when only from March 14, 2012 11:24 post. I thought I was being clear there are three fields. One field where user enters the time and the other two fields which would automatically updated with the minute and hour. I assume that it was known these three fields are in the Entry4 table.

 

[dhookom]

I was attempting to simplify your code and avoid possible errors.
Since the form is bound to Entry4 and if tm_rand is a datetime field, all you need to do is:

Private Sub tm_rand_AfterUpdate()
    If Not IsNull(Me.tm_rand) Then
        Me.h_rand = DatePart("h",Me.tm_rand)
        Me.m_rand = DatePart("n",Me.tm_rand)
    End If
End Sub

Also, it is generally considered poor practice to store a value that can easily be calculated. I assume you understand your needs better than I do.

Duane
Hook'D on Access
MS Access MVP