Simple Update SQL

[DADAZHU]

Hi,



I used the following SQL without success, I thought it should be easy. Can anyone help please?

strSQL = "UPDATE tblUnit SET Unit_Name = '" & strUName & "' WHERE CC = '" & strCC & "'"

Debug.print showed: UPDATE tblUnit SET Unit_Name = '' WHERE CC = '', both strCC and strUName have been checked before the SQL is executed.

I also tested strSQL = "UPDATE tblUnit SET Unit_Name = 'AAA' WHERE CC = 'A01'", and it worked.

Many thanks.

[pdebaets]

First, make sure you have "Option Explicit" at the top of your module, just below "Option Compare Database"

Compile your modules.

Next, put a

debug.print strUName & ", " & strcc

... just above where you set strSQL. post the results back here.

[DADAZHU]

Hi, Peter,

Thanks for your help. I actually have the following codes in an excel spreadsheet from which I read unit info and update the tblUnit table in Access if there are any differences. And I do have "Option Explicit" at the top of my module

I did what you suggested, the results are as following,

 
AAA,243
UPDATE tblUnit SET Unit_Name ='' WHERE CC =''

The routines are as below,

Private Sub 
btnUpdateUnitInfoInDB_Click()
 
Dim strSQL As String
Dim strCC As String, strUName As String
Dim i As Long
 
strSQL = "UPDATE tblUnit SET Unit_Name ='" & strUName & "' WHERE CC ='" & strCC & "'"
Call OpenCN
 
On Error GoTo ErrHander
For i = 2 To 116 
strUName = Me.Range("B" & i).Value
strCC = Me.Range("A" & i).Value
Debug.Print strUName & "," & strCC
 
cn.Execute strSQL
Debug.Print strSQL
 
Next i
Call CloseCN
 
Exit Sub
ErrHander:
MsgBox "Error!"
Call CloseCN
End Sub

[pdebaets]

You are setting the value of the variables AFTER you've created the SQL statement. Try this:

Private Sub
btnUpdateUnitInfoInDB_Click()
  
Dim strSQL As String
Dim strCC As String, strUName As String
Dim i As Long
  

Call OpenCN
  
On Error GoTo ErrHander
For i = 2 To 116 
strUName = Me.Range("B" & i).Value
strCC = Me.Range("A" & i).Value
Debug.Print strUName & "," & strCC
strSQL = "UPDATE tblUnit SET Unit_Name ='" & strUName & "' WHERE CC ='" & strCC & "'"
  
cn.Execute strSQL
Debug.Print strSQL
  
Next i
Call CloseCN
  
Exit Sub
ErrHander:
MsgBox "Error!"
Call CloseCN
End Sub

[DADAZHU]

You are a champ. Thanks Peter.