Access closes when activating vba code

[Beejer]

I have a weird problem that I'm trying to solve but can't seem to find
a solution at this time. My Access database closes completely when the
"Else" of the VBA code is applicable.

I'm running Access XP Pro (2002) french on WinXP
Form: frm_commande (data coming from Qry_Commande)
Subform: sfrm_commande_detail

The following VBA code is running on Form_Current event of the subform.
All the fields used in the code are in the footer of the subform and I
created a two dummy text fields to help get the data. These data are
not saved in any tables, they are just used to calculate information.

Dummy 1 - Date_Facture which equals the Date_facturation field of the
form (I tried using the field of the form directly but I get the same
result).

Dummy 2 - TauxTPS which is used to get the dynamic rate that will be
used to calculate the proper tax amount according to the date.

Me.Refresh
If (IsNull(Me.Date_Facture)) Or Me.Date_Facture = " " Then
Exit Sub
Else
Me.Refresh
If Me.Date_Facture < Me.Date_juillet Then
Me.TauxTPS.ControlSource = "=0.07"
Else
Me.TauxTPS.ControlSource = "=0.06"
End If
End If

As soon as I have data in the Date_Facture field which is a core data
to establish the rate, the Access database closes completely... no
error message nothing. Tried to compact and repair the DB... still no
success. Anybody has an idea???

[Allen Browne]

Let's do some basic repair:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

With that done, let's try some specifics on this routine:

1. Remove the lines:
Me.Refresh
The record canot be dirty in this event.

2. Leave TAUTPS unbound (nothing in its ControlSource.)
Set is Format property to:
General Number
so Access knows it is a numeric value.

3. Try this in the Current event procedure of the form
Private Sub Form_Current()
Dim dblTausTPS
If Me.Date_facturation < Me.Date_juillet Then
Me.TauxTPS = 0.07
ElseIf Not IsNull(Me.Date_facturation) Then
Me.TauxTPS = 0.06
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Beejer" <bj********@yahoo.ca> wrote in message
news:11**********************@d56g2000cwd.googlegr oups.com...

I have a weird problem that I'm trying to solve but can't seem to find
a solution at this time. My Access database closes completely when the
"Else" of the VBA code is applicable.

I'm running Access XP Pro (2002) french on WinXP
Form: frm_commande (data coming from Qry_Commande)
Subform: sfrm_commande_detail

The following VBA code is running on Form_Current event of the subform.
All the fields used in the code are in the footer of the subform and I
created a two dummy text fields to help get the data. These data are
not saved in any tables, they are just used to calculate information.

Dummy 1 - Date_Facture which equals the Date_facturation field of the
form (I tried using the field of the form directly but I get the same
result).

Dummy 2 - TauxTPS which is used to get the dynamic rate that will be
used to calculate the proper tax amount according to the date.

Me.Refresh
If (IsNull(Me.Date_Facture)) Or Me.Date_Facture = " " Then
Exit Sub
Else
Me.Refresh
If Me.Date_Facture < Me.Date_juillet Then
Me.TauxTPS.ControlSource = "=0.07"
Else
Me.TauxTPS.ControlSource = "=0.06"
End If
End If

As soon as I have data in the Date_Facture field which is a core data
to establish the rate, the Access database closes completely... no
error message nothing. Tried to compact and repair the DB... still no
success. Anybody has an idea???

[Beejer]

Allen, your my savior!!!

It is working like a charm. Thank you so much.

Bruno

Allen Browne a écrit :

Let's do some basic repair:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

With that done, let's try some specifics on this routine:

1. Remove the lines:
Me.Refresh
The record canot be dirty in this event.

2. Leave TAUTPS unbound (nothing in its ControlSource.)
Set is Format property to:
General Number
so Access knows it is a numeric value.

3. Try this in the Current event procedure of the form
Private Sub Form_Current()
Dim dblTausTPS
If Me.Date_facturation < Me.Date_juillet Then
Me.TauxTPS = 0.07
ElseIf Not IsNull(Me.Date_facturation) Then
Me.TauxTPS = 0.06
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Beejer" <bj********@yahoo.cawrote in message
news:11**********************@d56g2000cwd.googlegr oups.com...

I have a weird problem that I'm trying to solve but can't seem to find
a solution at this time. My Access database closes completely when the
"Else" of the VBA code is applicable.

I'm running Access XP Pro (2002) french on WinXP
Form: frm_commande (data coming from Qry_Commande)
Subform: sfrm_commande_detail

The following VBA code is running on Form_Current event of the subform.
All the fields used in the code are in the footer of the subform and I
created a two dummy text fields to help get the data. These data are
not saved in any tables, they are just used to calculate information.

Dummy 1 - Date_Facture which equals the Date_facturation field of the
form (I tried using the field of the form directly but I get the same
result).

Dummy 2 - TauxTPS which is used to get the dynamic rate that will be
used to calculate the proper tax amount according to the date.

Me.Refresh
If (IsNull(Me.Date_Facture)) Or Me.Date_Facture = " " Then
Exit Sub
Else
Me.Refresh
If Me.Date_Facture < Me.Date_juillet Then
Me.TauxTPS.ControlSource = "=0.07"
Else
Me.TauxTPS.ControlSource = "=0.06"
End If
End If

As soon as I have data in the Date_Facture field which is a core data
to establish the rate, the Access database closes completely... no
error message nothing. Tried to compact and repair the DB... still no
success. Anybody has an idea???