Code about a check box to change value in tables crash my access

[Grooz13]

I have a form i created to work like a calendar and I have listbox so i can drag and drop jobs on the day i want the job to be done and it works well and it changes the date in my database... however I would like my client to be sent a new fax telling him we changed the date of the job... I added a checkbox on my Form and if the checkbox is checked it will change the value fax (a yes/no field) to no so my secretary will resend a fax to everyone with changed date at the end of t he day...



however... the code is crashing my access every single time... if I don't go step by step in the code... it simply close... no message nothing... If I don't go step by step it's simply crash access, no answer from access at all ... he is my code

 
Dim DateF As Date
Dim LeSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
DateF = DateDebut + ind2 - 1
Set db = CurrentDb
LeSQL = "Select Date_Shipping, Fax from Tbl_Production where No_Job_Auto=" & NC
Set rst = db.OpenRecordset(LeSQL, dbOpenDynaset)
 If Not rst.EOF Then
    rst.Edit
    rst!Date_Shipping_Debut = DateF
   If Btn_Fax= True Then
   rst!Fax = -1
   End If
    rst.Update
 End If
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

what's in bold is my new code... if i remove it, it works flawlessly anyone have an idea ? (p.s sorry for my really bad english it's not my first language)

[RuralGuy]

Try removing the test and just setting the field value as a test:

'-- If Btn_Fax= True Then
   rst!Fax = -1
'-- End If

[Grooz13]

what do you mean 6

[RuralGuy]

See if just trying to change the field will kill Access.

[Grooz13]

no it don't crashed it

[RuralGuy]

So it looks like this line:

If Btn_Fax= True Then

...is the line that crashes the system. Try typing it over again and leave the old line commented out. Does it still crash?

[Grooz13]

yeah it still crash :S

[RuralGuy]

Try:

If Me.Btn_Fax = True Then

[Grooz13]

it still crash again... i'm so hopeless

[RuralGuy]

Is your system up to date with patches? Both Win7 and Access 2007? Have you done a Compact and Repair?

[Grooz13]

yeah system is up to date and I did compact and repair, it's in a fonction... does it has anything to do with this ?

[RuralGuy]

Sometimes just a section of code can be corrupt, especially if you edit the code while the debugger is running. Maybe try importing your system into a fresh db.

[Grooz13]

It's really weird.. this if crash my access wherever I put him... I tried changing my control to put a radio button but it crashed again... what is wrong with me

[RuralGuy]

Like I said, IMPORT your db into a new empty db. I suspect your db is corrupt and importing into a new db might cure it.

[Rawb]

Is the function an Event in your Form? Or is it in a Module?

If it's in a Module, you need to either reference the Check Box using the full path to the Form or pass it to the Function as a variable:

Dim DateF As Date
Dim LeSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
DateF = DateDebut + ind2 - 1
Set db = CurrentDb
LeSQL = "Select Date_Shipping, Fax from Tbl_Production where No_Job_Auto=" & NC
Set rst = db.OpenRecordset(LeSQL, dbOpenDynaset)
 If Not rst.EOF Then
    rst.Edit
    rst!Date_Shipping_Debut = DateF
   If [Forms]![MyForm]![Btn_Fax]= True Then
   rst!Fax = -1
   End If
    rst.Update
 End If
rst.Close
Set rst = Nothing
db.Close
Set db = Nothing

Be sure to change MyForm to the Form's name.

Also, if Tbl_Production.Fax is a Yes/No Field, you should set it to False instead of -1. Setting a non-number Field to a number can sometimes cause strange things to happen.