NULL in Access - Not working

BradLee
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.

LaVolpe
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?

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

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

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

LaVolpe
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?

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

BradLee
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
.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

LaVolpe
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.

BradLee
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.

LaVolpe
04-14-2004, 02:12 PM
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

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

BradLee
04-15-2004, 06:15 AM
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?

MKoslof
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.

BradLee
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.

zy_abc
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
Nz(adodcCR.Recordset("START_DATE"),Null)
End If

MKoslof
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

BradLee
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:

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

Appreciate all your help and would greatly appreciate some more.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum