Ubound Access Form Update problems

[jlg5454]

Hi, I have an Unbound Update/Edit form that Does not allow me to update with null values. I only have two required fields. Any suggestions appreciated.

Code:

Private Sub cmdSave_Click()



'**** Save Changes ****

Dim db As DAO.Database
Dim strSQL As String
Dim dte As Variant
Dim Name As String

Set db = CurrentDb


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



strSQL = "UPDATE InboundID_Cards "
strSQL = strSQL & "SET CardName= '" & Me!txtcardname & "'"
strSQL = strSQL & ", [Card No#]= " & Me!txtIDNum & ""
strSQL = strSQL & ", Expiration= #" & Me!txtExpDte & "#"
strSQL = strSQL & ", Distributed= #" & Me!txtDistributedDte & "#"
strSQL = strSQL & ", Returned= #" & Me!txtReturnedDte & "#"
strSQL = strSQL & ", Consultant= '" & Me!txtConsult & "'"
strSQL = strSQL & ", Team= '" & Me!txtTeam & "'"
strSQL = strSQL & ", SSNUM= '" & Me!txtSSNUM & "'"
strSQL = strSQL & ", Supervisor= '" & Me!txtSupervisor & "'"
strSQL = strSQL & ", Floor= " & Me!txtFloor & ""
strSQL = strSQL & ", FloorAccess= '" & Me!txtFloorAccess & "'"
strSQL = strSQL & " WHERE CardName= '" & [CboFind] & "'"



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


MsgBox "Card Name" & Me!txtcardname & " Has Been Edited"
setDefaults



Set db = Nothing
Me!txtcardname.SetFocus

End Sub

 

[Remou]

Do you wish to edit the table to allow Zero Length Strings and null values, or do you wish to edit the SQL to eliminate null vales and ZLS's?

 

[jlg5454]

I would like the Edit Form to also allow null values to any field where no value may exist.

Thanks,

Joe

 

[Remou]

A form is not fussy about data, unless you have asked it to be. The above code does not show any code that would complain about nulls except the SQL, as far as I can see, hence my question.

 

[jlg5454]

Your right, I mis understood. The code stops and breaks at the db.Execute strSQL which runs the SQL string.

 

[Remou]

So, are you able to fix this problem now?

 

[jlg5454]

No, It will not allow me to complete an update with for example of leaving a date field null.

 

[Remou]

Can you edit the table? That is probably the best option.

 

[jlg5454]

I fixed it. Thanks for trying.

A guy I worked with hinted me towards this by assigning a varible to the two date fields to allow them to accept nulls.

If IsNull(txtDistributedDte.Value) Then
dte = "Distributed = null"
Else
dte = "Distributed = #" & txtDistributedDte & "#"
End If

If IsNull(txtReturnedDte.Value) Then
dte2 = "Returned = null "
Else
dte2 = "Returned = #" & txtReturnedDte & "# "
End If