NULL in Access - Not working

Does anyone know why this code would work

adodcCR.Recordset("START_DATE") = Null

But this code will not

If Len(Trim(txtStartDate.Text)) = 0 Then
adodcCR.Recordset("START_DATE") = Null
End If

Any help would be greatly appreciated.

Try debug.print Len(Trim(txtStartDate.Text)) before the If statement. What is the result after it's run? Are you getting an error of any sort?

Also try

If (txtStartDate.Text = "") Then
adodcCR.Recordset("START_DATE") = Null
End If

I get 0 when I run that debug code.

? adodccr.Recordset!start_date
Null
Right after I execute the If Statement, after that when I go to .MoveNext is when I get an Error (Operation was cancelled.)

After updating your record, are you using an .Update statement to save the change? Could you include the next couple of lines after what you've shown already?

Can you please post more code?

' code that is not working
If (txtStartDate.Text = "") Then
adodcCR.Recordset("START_DATE") = Null
End If

' debug.print adodcCR.Recordset("START_DATE") = Null at this point but if I look in
'the Access table the value is still there.
With adodcCR.Recordset

' where error is generated
Select Case Index
Case 0 'First Record
.MoveFirst
Case 1 'Previous Record
.MovePrevious
If .BOF Then .MoveFirst
Case 2 'Next Record
.MoveNext
If .EOF Then .MoveLast
Case 3 'Last Record
.MoveLast
End Select

End With

Exit Sub

goNavigateErr:

MsgBox Err.Description

>>"at this point but if I look in the Access table the value is still there."
Ok, when you change the record value, you need to commit the change. Add an .Update command after the change.

I'm getting the following error when I add the adodcCR.Recordset.Update

Consumer's event handler called a non-reentrant method in the provider.

Unexpected error? Got my curiosity up, so I did a few quick searches: look at...
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q195638

Ditch the data controls and use standard ADO connections and recordsets...they are much more flexible

The code at (http://support.microsoft.com/defaul...b;en-us;Q195638) doesn't seem to be working. Who knows?

I'm using data controls because I have to display so many dropdowns on the users form that are from linked tables. I've been using the datacombo dropdown which requires the use of an ado data control. Do you have any other recs on what I can do / use?

Sure use a standard combobox. Just populate it at run-time via an ADO recordset and the .AddItem method.

How would I go about doing that (using a standard combobox)? I have a lot of ID's that correspond with values I want the user to see. For instance record 150 could have the status_id of 1 which corresoonds with 'Active' which I display in the datacombo. Any help would be appreciated since now I am getting this error...

The Microsoft Jet Engine Database stopped the process because you and another user are attempting to change the same data at the same time.

Not sure what that means. Any help you could offer would be greatly appreciated. I haven't programmed in VB for a while... I'm just trying to get my skills back. Thanks.

*************************************
'code that is not working
If (txtStartDate.Text = "") Then
adodcCR.Recordset("START_DATE") = Null
End If
**************************************

Why not try the below code & see whether it works?
If Nz(txtStartDate.Text)="" Then
Nz(adodcCR.Recordset("START_DATE"),Null)
End If

No, ditch the ADODC controls..they are nothing but trouble. Use a standard ADO connection object and recordset. Open your recordset. Then loop the recordset until EOF, adding the field you want to the combo box via the .addItem method. I suggest you start by reading the ADO For Beginners Tutorial in the knowledge base

If I had a database as pictured in the rtf below - how could I deal with the fact (in ADO) that my form needs to display:

ChangeRequest.ChangeID
ChangeRequest.Phase
Phase.StartDate
Phase.CompletedDate
Type.Desc

Appreciate all your help and would greatly appreciate some more.