Update query problem

[jlg5454]

Hello,

I have an form (which uses unbound fields) I use to Add, Update and edit records in a table. I got the Add and delete buttons to work, however I am having problems with the Edit/Save button. Example: there is a data field which may or may not be left blank. If you are editing another field other than the date field(Hire Date) and leave the data field blank, it does not update. If I do put a date in that field it works. Here is my code below:

Private Sub cmdSave_Click()

If IsNull(Me!CboFind) Then
MsgBox "Is this a new addition? If so, use the Add button below to save this record."
Me!txtSalesnum.SetFocus
CboFind.Requery
Exit Sub
End If


'**** Save Changes ****

Dim db As DAO.Database
Dim strSQL As String
Dim cmt As Variant
Dim dte As Variant
Dim Name As String
DoCmd.SetWarnings False
Set db = CurrentDb


If Len(txtSalesnum.Value) < 1 Then
MsgBox "Please Enter the Salesburgh No."
Exit Sub
End If

If Len(txtIDNum.Value) < 1 Then
MsgBox "Please Enter the Card No."
Exit Sub
End If

If Len(txtExpDte.Value) = "" Then
MsgBox "Please Enter the Expire Date"
Exit Sub
End If



If txtHireDte.Value = "" Then
dte = ""
Else
dte = txtHireDte.Value
End If

If txtComments.Value = "" Then
cmt = " "
Else
cmt = txtComments.Value
End If


strSQL = "UPDATE tblCard_ID "
strSQL = strSQL & "SET ID_Number= '" & Me!txtIDNum & "'"
'strSQL = strSQL & ", Date= #" & Me.txtDte & "#"
strSQL = strSQL & ", Salesburgh_Card_No= " & Me!txtSalesnum & ""
strSQL = strSQL & ", Employee= '" & Me!txtname & "'"
'strSQL = strSQL & ", Employee= '" & Me!txtname & "'"
strSQL = strSQL & ", Hire_Date= '" & Me!txtHireDte & "'"
strSQL = strSQL & ", Expire_Date= '" & Me!txtExpDte & "'"
strSQL = strSQL & ", Temp_Sevice= '" & Me!CboTemp & "'"
strSQL = strSQL & ", Comments= """ & Me!txtComments & """"
strSQL = strSQL & " WHERE Salesburgh_Card_No=" & [CboFind] & ""



Debug.Print strSQL
db.Execute strSQL
Me!CboFind.Requery
strSQL = db.RecordsAffected


MsgBox txtSalesnum.Value & " Card No. Has Been Edited"
setDefaults



Set db = Nothing
txtSalesnum.SetFocus

End Sub


I had to make the date fields strings or else the Update query kept giving an error message.

Thanks for any assistance

 

[mrdod]

Hello jlg545 how are you?

Just wondering why you have Employee twice?

strSQL = strSQL & ", Salesburgh_Card_No= " & Me!txtSalesnum & ""
strSQL = strSQL & ", Employee= '" & Me!txtname & "'"
'strSQL = strSQL & ", Employee= '" & Me!txtname & "'"
strSQL = strSQL & ", Hire_Date= '" & Me!txtHireDte & "'"

 

[AccessGuruCarl]

When using an update query, the query should only contain the values getting updated. If the field is blank then you need to set it to NULL, in order for the query to run. But this may also add the NULL string to your table field. You will to check.
Use conditional IIF statements to replace empty fields with NULL or Create additional update query's then based on the criteria, you run whichever update is needed.

Or you can try using pure SQL..
INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) VALUES (7196, 'Green', 'Salesman', 7782, SYSDATE, 2000, NULL, 10) WHERE empno = 7196

To add a new record, you just leave the Where clause off!

Hope this helps...

In the SQL, there is no comm.... So when the SQL runs it leaves the field blank.


AccessGuruCarl
Programmers helping programmers
you can't find a better site.