CODEWITHSUNDEEP
ms-accesssql-update
psolomon
psolomon

Reputation: 173

Intermittent problem with update query in MS Access

I have an UPDATE query that is intermittently behaving in an unexpected way. The context is that I have a table for storing phone numbers alongside a user_id (user_id is foreign key from main Users table). The phone column is of type Number. I am updating the phone number through a button on the main form, and the event handler is pasted below.

Private Sub button_add_phone_Click()

Dim phone_query As String
Dim phone_number As String
Dim user_query As String
Dim user_id As Integer
Dim user_rs As DAO.recordset
Dim curr_db As DAO.Database

Set curr_db = CurrentDb()

If Not IsNull(Form!hidden_user_id) Then
    user_id = Form!hidden_user_id
Else
    MsgBox "Select a user to proceed."
    Exit Sub
End If

phone_number = Form!Phone

user_query = "select * from AVIT_Phone where user_id = " & user_id
Set user_rs = curr_db.OpenRecordset(user_query, dbOpenSnapshot, dbSeeChanges)

If user_rs.EOF Then
    phone_query = "insert into AVIT_Phone (user_id, phone) values (" & user_id & ", '" & phone_number & "')"
Else
    phone_query = "update AVIT_Phone set phone = " & phone_number & " where user_id = " & user_id
End If

curr_db.execute phone_query, dbSQLPassThrough

MsgBox "Phone number updated for user " & Form!text_name

curr_db.Close

Set user_rs = Nothing
Set curr_db = Nothing

End Sub

The intermittent problem that I am encountering is that occasionally, the update query is setting the phone to NULL rather than the value from the textbox (Form!phone).

I thought initially that there is some formatting error or type conversion error, but the intermittency of it suggests that there is something else happening.

Please let me know if you see anything in the code which would cause this behavior.

Upvotes: 0

Views: 95

Answers (1)

psolomon
psolomon

Reputation: 173

In this case, phone numbers were entered as 10-digit values which were causing an over-flow in the phone number column which was of data type Number. Field Size of the column was Long Integer, which has range of values -2,147,483,648 to +2,147,483,647.

Solution is to store phone number as a text field rather than numeric field, as suggested by June7.

Upvotes: 1

Related Questions