phendrena
12-22-2008, 02:54 AM
Hi there,
I'm currently moving from Excel to Access.
I've recreated my data entry form in Access what I now need to recreate are my macros.
Now i'd imagine that VBA doesn't change much between applications, but my code doesn't want to work when i move out of a field and gives the following error:
"The expression On Key Press you entered as the event property setting produced the following error : User-defined type not defined"
I believe all my names are correct.
I've attached a copy of the file.
'---------------------------------------'
'-- Saved Box Change Routine --'
'---------------------------------------'
Private Sub cboSaved_Change()
Call sDispSaved
End Sub
Private Sub cboSaved_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDispSaved
End Sub
Private Sub sDispSaved()
' Saved
If cboSaved.Value = "Yes" Then
cboEligible.Value = "Yes"
cboReason.Visible = False
lblReason.Visible = False
txtOPrem.Visible = True
lblOPrem.Visible = True
txtIPrem.Visible = True
lblIPrem.Visible = True
txtDiscount.Visible = True
lblDiscount.Visible = True
cboEmpower.Visible = True
lblEmpower.Visible = True
Else
If cboSaved.Value = "No" Then
cboReason.Visible = True
lblReason.Visible = True
cboReason.BackColor = RGB(255, 255, 150)
txtOPrem.Value = ""
txtOPrem.Visible = False
lblOPrem.Visible = False
txtIPrem.Value = ""
txtIPrem.Visible = False
lblIPrem.Visible = False
txtDiscount.Value = ""
txtDiscount.Visible = False
lblDiscount.Visible = False
cboEmpower.Value = ""
cboEmpower.Visible = False
lblEmpower.Visible = False
Else
If cboSaved.Value = "" Then
cboReason.Visible = True
lblReason.Visible = True
txtOPrem.Visible = True
lblOPrem.Visible = True
txtIPrem.Visible = True
lblIPrem.Visible = True
txtDiscount.Visible = True
lblDiscount.Visible = True
cboEmpower.Visible = True
lblEmpower.Visible = True
End If
End If
End If
End Sub
'---------------------------------------'
'-- Scheme Box Change Routine --'
'---------------------------------------'
Private Sub cboScheme_Change()
Call sDispMigration
End Sub
Private Sub cboScheme_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDispMigration
End Sub
Private Sub sDispMigration()
' Migration
If cboScheme.Value = "Ford" Then
cboMigration.Visible = True
lblMigration.Visible = True
Else
If cboScheme.Value = "Jaguar" Then
cboMigration.Visible = True
lblMigration.Visible = True
Else
If cboScheme.Value = "Mazda" Then
cboMigration.Visible = True
lblMigration.Visible = True
' Hide Field
Else
If cboScheme.Value = "Clydesdale Bank" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Evans Halshaw" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Kia" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Land Rover" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Lexus" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "National Trust" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Other RSA" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Stratstone" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Toyota" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Volvo" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "YBS" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Yorkshire Bank" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
'---------------------------------------'
'-- Migration Box Change Routine --'
'---------------------------------------'
Private Sub cboMigration_Change()
Call sDispMigOptions
End Sub
Private Sub cboMigration_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDispMigOptions
End Sub
Private Sub sDispMigOptions()
If cboMigration.Value = "Yes" Then
Me.Width = "560"
Else
If cboMigration.Value = "No" Then
Me.Width = "375"
Else
If cboMigration.Value = "" Then
Me.Width = "375"
End If
End If
End If
End Sub
I'm currently moving from Excel to Access.
I've recreated my data entry form in Access what I now need to recreate are my macros.
Now i'd imagine that VBA doesn't change much between applications, but my code doesn't want to work when i move out of a field and gives the following error:
"The expression On Key Press you entered as the event property setting produced the following error : User-defined type not defined"
I believe all my names are correct.
I've attached a copy of the file.
'---------------------------------------'
'-- Saved Box Change Routine --'
'---------------------------------------'
Private Sub cboSaved_Change()
Call sDispSaved
End Sub
Private Sub cboSaved_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDispSaved
End Sub
Private Sub sDispSaved()
' Saved
If cboSaved.Value = "Yes" Then
cboEligible.Value = "Yes"
cboReason.Visible = False
lblReason.Visible = False
txtOPrem.Visible = True
lblOPrem.Visible = True
txtIPrem.Visible = True
lblIPrem.Visible = True
txtDiscount.Visible = True
lblDiscount.Visible = True
cboEmpower.Visible = True
lblEmpower.Visible = True
Else
If cboSaved.Value = "No" Then
cboReason.Visible = True
lblReason.Visible = True
cboReason.BackColor = RGB(255, 255, 150)
txtOPrem.Value = ""
txtOPrem.Visible = False
lblOPrem.Visible = False
txtIPrem.Value = ""
txtIPrem.Visible = False
lblIPrem.Visible = False
txtDiscount.Value = ""
txtDiscount.Visible = False
lblDiscount.Visible = False
cboEmpower.Value = ""
cboEmpower.Visible = False
lblEmpower.Visible = False
Else
If cboSaved.Value = "" Then
cboReason.Visible = True
lblReason.Visible = True
txtOPrem.Visible = True
lblOPrem.Visible = True
txtIPrem.Visible = True
lblIPrem.Visible = True
txtDiscount.Visible = True
lblDiscount.Visible = True
cboEmpower.Visible = True
lblEmpower.Visible = True
End If
End If
End If
End Sub
'---------------------------------------'
'-- Scheme Box Change Routine --'
'---------------------------------------'
Private Sub cboScheme_Change()
Call sDispMigration
End Sub
Private Sub cboScheme_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDispMigration
End Sub
Private Sub sDispMigration()
' Migration
If cboScheme.Value = "Ford" Then
cboMigration.Visible = True
lblMigration.Visible = True
Else
If cboScheme.Value = "Jaguar" Then
cboMigration.Visible = True
lblMigration.Visible = True
Else
If cboScheme.Value = "Mazda" Then
cboMigration.Visible = True
lblMigration.Visible = True
' Hide Field
Else
If cboScheme.Value = "Clydesdale Bank" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Evans Halshaw" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Kia" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Land Rover" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Lexus" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "National Trust" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Other RSA" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Stratstone" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Toyota" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Volvo" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "YBS" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "Yorkshire Bank" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
Else
If cboScheme.Value = "" Then
cboMigration.Visible = False
lblMigration.Visible = False
cboMigration.Value = ""
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub
'---------------------------------------'
'-- Migration Box Change Routine --'
'---------------------------------------'
Private Sub cboMigration_Change()
Call sDispMigOptions
End Sub
Private Sub cboMigration_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Call sDispMigOptions
End Sub
Private Sub sDispMigOptions()
If cboMigration.Value = "Yes" Then
Me.Width = "560"
Else
If cboMigration.Value = "No" Then
Me.Width = "375"
Else
If cboMigration.Value = "" Then
Me.Width = "375"
End If
End If
End If
End Sub