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_d etail

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_facturatio n 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.Cont rolSource = "=0.07"
Else
Me.TauxTPS.Cont rolSource = "=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]

Re: Access closes when activating vba code

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\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.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_factura tion < 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" <bjoannette@yah oo.ca> wrote in message
news:1151605333 .988927.297400@ d56g2000cwd.goo glegroups.com.. .[color=blue]
>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_d etail
>
> 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_facturatio n 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.Cont rolSource = "=0.07"
> Else
> Me.TauxTPS.Cont rolSource = "=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???[/color]

[Beejer]

Re: Access closes when activating vba code

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\m saccess.exe" /decompile
"c:\MyPath\MyDa tabase.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_factura tion < 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" <bjoannette@yah oo.cawrote in message
news:1151605333 .988927.297400@ d56g2000cwd.goo glegroups.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_d etail

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_facturatio n 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.Cont rolSource = "=0.07"
Else
Me.TauxTPS.Cont rolSource = "=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???