Richard said:
Hi Dirk
Thanks for replying.
When I use action query to add a new record,
I will get error messages if integer and text fields are null at the
control of the unbound form. But when I use the recordset to addnew.
It goes through even if the the contrls are null value.
What I did now was changing all the allow null string at the table
value to yes, and Nz the number controls to 0 if null.
I am trying to understand what is the use of the " allow null string"
property.
Thanks again
Richard
Here's the code:
Dim Cnn As ADODB.Connection
Dim strSQL As String
Set Cnn = CurrentProject.Connection
' Get enrolmentid
Me.txtEnrolmentID = Nz(DMax("enrolmentid", "enrolments"),
0) + 1
' Get updated InvoiceNo
Call GetInvoiceNo
'get entrystamp
Me.txtentrystamp = Now()
strSQL = "INSERT INTO Enrolments (EnrolmentId, [Employee Id],
CompanyId, " & _
"EnrolmentDate, WorkshopId, Workshopdate,
WorkshopDuration, " & _
"Fees, Facilitator, [PP/In-house], ConsoId, [Man-day]) "
& _ "VALUES (" & Me.txtEnrolmentID & ",""" &
Me.cmbEmployeeId & """, " & _
"" & Me.cmbCompany & ", " & "#" &
Format(Me.txtEnrolmentdate, "dd/mmm/yy") & "#" & ", " & _
"""" & Me.cmbWORKSHOPID & """, " & "#" &
Format(Me.txtWORKSHOPDATE, "dd/mmm/yy") & "#" & ", " & _
"" & Me.cmbWorkshopDuration & ", """ & Nz(Me.txtFEES, 0)
& """, " & _
"""" & Me.txtFACILITATOR & """, """ & Me.cmbPP_In_house &
""", " & _
"" & Nz(Me.cmbConso_code, 0) & ", """ & Me.txtMan_day &
""")"
Cnn.Execute (strSQL)
Cnn.Close
Set Cnn = Nothing
If I leave the controls empty in the form, I'll get an error message
Your code is building a SQL string in which the Null values from the
controls on the form are being forced to either "" (i.e., a zero-length
string) or 0, depending on whether the target field is text or numeric.
In other words, at no time is a Null value being specified for any of
these fields. I won't copy your statement, because it's too involved
and I don't know which fields are supposed to be Null and which aren't,
but consider this example:
Text box txtID has the numeric key value 123, to be inserted in
field ID of MyTable.
.
Text box txtMyText currently has a Null value, which I want
to insert in field MyText of table MyTable.
I try this code:
strSQL = _
"INSERT INTO MyTable (ID, MyText) VALUES (" & _
Me.txtID & ", " & _
"""" & Me.txtMyText & """" & _
")"
As a result of executing that statement, strSQL ends up with this
value:
INSERT INTO MyTable (ID, MyText) VALUES (123, "")
As you see, it is not the value Null that is being inserted in the field
MyText, but a zero-length string. A zero-length string is a completely
different thing from Null -- it is a known value, while Null is a
"non-value". And when you try to insert this zero-length string in the
text field, if the field doesn't allow zero-length strings, the insert
is rejected with an error.
If you want to insert Null in the MyText field when txtMyText is Null,
you could rewrite the above statement like this:
strSQL = _
"INSERT INTO MyTable (ID, MyText) VALUES (" & _
Me.txtID & ", " & _
IIf(IsNull(Me.txtMyText), _
"Null", _
"""" & Me.txtMyText & """") & _
")"
Now when the statement is executed with a Null in txtMyText, the strSQL
ends up with this value:
INSERT INTO MyTable (ID, MyText) VALUES (123, Null)
That SQL statement should then be accepted (provided, of course, that
MyText allows Nulls). If, on the other hand, txtMyText contained the
string value "Boojum", then the result of executing the assignment to
strSQL would be:
INSERT INTO MyTable (ID, MyText) VALUES (123, "Boojum")
Does that clarify what is going on and what to do about it? You would
want to do something similar with every control for which you want to
allow a Null value to be inserted in the table. That would apply to
your number fields as well, except that since there are no quotes
involved you can use the Nz function to do it; e.g.,
... & Nz(Me.txtFEES, "Null") & ...
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
