Access 2007 beta and VBA

[Guest]

I'm having trouble with coding in VBA in Access 2007. Code that works in
Access 2003 doesn't work in 2007. It's simple code to check that there are
enough characters in a field, yet Access totally ignores it.

Help

Thanks in advance

 

[Douglas J. Steele]

What's the code?

 

[Guest]

Works fine in Access 2003, nada 2007 beta

Private Sub Form_Load()

Dim intSeller As Integer

intSeller = Nz(Me.OptionValue.Value, 0)

Select Case intSeller 'Dispalys house status
Case 0 'Determined by numeric values
txtShowStatus = "Unselected"
Exit Sub 'determined by the option group
Case 1
txtShowStatus = "Potential"
Case 2
txtShowStatus = "Active"
Case 3
txtShowStatus = "Seen"
Case 4
txtShowStatus = "Sold"
End Select
End Sub

 

[Guest]

Different code same problem

What's the code?

 

[Wayne Morgan]

What error do you get? Does it highlight a line of code as generating the
error?

Doing this in the Load event of the form would mean that the value in the
option group is stored in the table. It also indicates that both the option
group and the textbox are on the same form. You then assign the text value
to the textbox txtShowStatus. Is this textbox also bound to the table? If
so, why? You have the information already stored by storing the option group
value. Instead of using this code, try making the textbox a calculated
control instead.

Example for the control source for txtShowStatus:
=Choose(Nz([OptionValue],0) + 1, "Unselected", "Potential", "Active",
"Seen", "Sold")

As far as the error goes, I suspect it is a timing issue with the form just
loading. The value should be available in the form's load event, but I'm
guessing it isn't. Also, if you move to another record, doing this in code
will require this code to be in the form's Current event also so that the
text will change to match the value in the next record. If you have the code
in the Current event, that event also fires when the form loads the first
record as the form loads, making the code in the Load event unnecessary.
Making the textbox a calculated control will make the code to fill in the
textbox in both events unnecessary.

 

[Guest]

I think you might misunderstand, the code runs fine in Access 2003, your
desciption is accurate, however, in Access 2007 beta, the code, any code,
doesn't run at all - even if I toggle a breakpoint in the form load procedure
it doesn't break to debug (which it would and does in 2003). I want to use
this version and it sees other people are, but, if I can't use VBA I'll have
to rollback to "03

What error do you get? Does it highlight a line of code as generating the
error?

Doing this in the Load event of the form would mean that the value in the
option group is stored in the table. It also indicates that both the option
group and the textbox are on the same form. You then assign the text value
to the textbox txtShowStatus. Is this textbox also bound to the table? If
so, why? You have the information already stored by storing the option group
value. Instead of using this code, try making the textbox a calculated
control instead.

Example for the control source for txtShowStatus:
=Choose(Nz([OptionValue],0) + 1, "Unselected", "Potential", "Active",
"Seen", "Sold")

As far as the error goes, I suspect it is a timing issue with the form just
loading. The value should be available in the form's load event, but I'm
guessing it isn't. Also, if you move to another record, doing this in code
will require this code to be in the form's Current event also so that the
text will change to match the value in the next record. If you have the code
in the Current event, that event also fires when the form loads the first
record as the form loads, making the code in the Load event unnecessary.
Making the textbox a calculated control will make the code to fill in the
textbox in both events unnecessary.

--
Wayne Morgan
MS Access MVP

Works fine in Access 2003, nada 2007 beta

Private Sub Form_Load()

Dim intSeller As Integer

intSeller = Nz(Me.OptionValue.Value, 0)

Select Case intSeller 'Dispalys house status
Case 0 'Determined by numeric values
txtShowStatus = "Unselected"
Exit Sub 'determined by the option group
Case 1
txtShowStatus = "Potential"
Case 2
txtShowStatus = "Active"
Case 3
txtShowStatus = "Seen"
Case 4
txtShowStatus = "Sold"
End Select
End Sub

 

[Guest]

PS: no error message

What error do you get? Does it highlight a line of code as generating the
error?

Doing this in the Load event of the form would mean that the value in the
option group is stored in the table. It also indicates that both the option
group and the textbox are on the same form. You then assign the text value
to the textbox txtShowStatus. Is this textbox also bound to the table? If
so, why? You have the information already stored by storing the option group
value. Instead of using this code, try making the textbox a calculated
control instead.

Example for the control source for txtShowStatus:
=Choose(Nz([OptionValue],0) + 1, "Unselected", "Potential", "Active",
"Seen", "Sold")

As far as the error goes, I suspect it is a timing issue with the form just
loading. The value should be available in the form's load event, but I'm
guessing it isn't. Also, if you move to another record, doing this in code
will require this code to be in the form's Current event also so that the
text will change to match the value in the next record. If you have the code
in the Current event, that event also fires when the form loads the first
record as the form loads, making the code in the Load event unnecessary.
Making the textbox a calculated control will make the code to fill in the
textbox in both events unnecessary.

--
Wayne Morgan
MS Access MVP

Works fine in Access 2003, nada 2007 beta

Private Sub Form_Load()

Dim intSeller As Integer

intSeller = Nz(Me.OptionValue.Value, 0)

Select Case intSeller 'Dispalys house status
Case 0 'Determined by numeric values
txtShowStatus = "Unselected"
Exit Sub 'determined by the option group
Case 1
txtShowStatus = "Potential"
Case 2
txtShowStatus = "Active"
Case 3
txtShowStatus = "Seen"
Case 4
txtShowStatus = "Sold"
End Select
End Sub

 

[Guest]

As an aside to this thread ... It looks like I'll have to "rebuild" all the
command buttons in Access 2007! They all produce a compile error in VB in
the error handling. These forms command buttons have been working fine since
Access 2000 and some since 97. All the code was produced by the Access
Wizard.

 

[Wayne Morgan]

Verify that the needed References are set and that none of them show as
Missing. Also, check the security level to see if you're preventing code
from running. If those aren't it, then I would suspect a corrupted install
of Office 2007.

--
Wayne Morgan
MS Access MVP

PS: no error message

What error do you get? Does it highlight a line of code as generating the
error?

Doing this in the Load event of the form would mean that the value in the
option group is stored in the table. It also indicates that both the
option
group and the textbox are on the same form. You then assign the text
value
to the textbox txtShowStatus. Is this textbox also bound to the table? If
so, why? You have the information already stored by storing the option
group
value. Instead of using this code, try making the textbox a calculated
control instead.

Example for the control source for txtShowStatus:
=Choose(Nz([OptionValue],0) + 1, "Unselected", "Potential", "Active",
"Seen", "Sold")

As far as the error goes, I suspect it is a timing issue with the form
just
loading. The value should be available in the form's load event, but I'm
guessing it isn't. Also, if you move to another record, doing this in
code
will require this code to be in the form's Current event also so that the
text will change to match the value in the next record. If you have the
code
in the Current event, that event also fires when the form loads the first
record as the form loads, making the code in the Load event unnecessary.
Making the textbox a calculated control will make the code to fill in the
textbox in both events unnecessary.

--
Wayne Morgan
MS Access MVP

Works fine in Access 2003, nada 2007 beta

Private Sub Form_Load()

Dim intSeller As Integer

intSeller = Nz(Me.OptionValue.Value, 0)

Select Case intSeller 'Dispalys house status
Case 0 'Determined by numeric values
txtShowStatus = "Unselected"
Exit Sub 'determined by the option group
Case 1
txtShowStatus = "Potential"
Case 2
txtShowStatus = "Active"
Case 3
txtShowStatus = "Seen"
Case 4
txtShowStatus = "Sold"
End Select
End Sub

:

What's the code?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I'm having trouble with coding in VBA in Access 2007. Code that
works
in
Access 2003 doesn't work in 2007. It's simple code to check that
there
are
enough characters in a field, yet Access totally ignores it.

Help

Thanks in advance