NULL in Access - Not working

04-14-2004, 12:51 PM
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.

04-14-2004, 12:55 PM
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?

04-14-2004, 01:00 PM
Also try

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

04-14-2004, 01:02 PM
I get 0 when I run that debug code.

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

04-14-2004, 01:08 PM
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?

04-14-2004, 01:09 PM
Can you please post more code?

04-14-2004, 01:16 PM
' 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
Case 1 'Previous Record
If .BOF Then .MoveFirst
Case 2 'Next Record
If .EOF Then .MoveLast
Case 3 'Last Record
End Select

End With

Exit Sub


MsgBox Err.Description

04-14-2004, 01:19 PM
>>"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.

04-14-2004, 01:23 PM
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.

04-14-2004, 02:12 PM
Unexpected error? Got my curiosity up, so I did a few quick searches: look at...;en-us;Q195638

04-14-2004, 03:23 PM
Ditch the data controls and use standard ADO connections and recordsets...they are much more flexible

04-15-2004, 06:15 AM
The code at (;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?

04-15-2004, 06:18 AM
Sure use a standard combobox. Just populate it at run-time via an ADO recordset and the .AddItem method.

04-15-2004, 03:12 PM
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.

04-16-2004, 02:04 AM
'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
End If

04-16-2004, 06:38 AM
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

04-16-2004, 08:56 AM
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:


Appreciate all your help and would greatly appreciate some more.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum