Is there a better way to code this?

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

Hi is there a better way of doing this I would have to add about 30 more
Me.Combo131 = "" to complete this task? I have tried using the wildcard
Me.Combo131 = "AC(*)" in various ways but none of them work.

If Me.Combo131 = "AC10" Or Me.Combo131 = "AC20" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "BA10" Or Me.Combo131 = "BA30" Or Me.Combo131 = "CD10" Or
Me.Combo131 = "CD20" Or Me.Combo131 = "CD30" Or Me.Combo131 = "CD40" Or
Me.Combo131 = "CD50" Or Me.Combo131 = "CD60" Or Me.Combo131 = "CD70" Or
Me.Combo131 = "CD71" Or Me.Combo131 = "DD40" Or Me.Combo131 = "DD60" Or
Me.Combo131 = "DD80" Or Me.Combo131 = "DR10" Or Me.Combo131 = "DR20" Or
Me.Combo131 = "DR30" Or Me.Combo131 = "DR40" Or Me.Combo131 = "DR50" Or
Me.Combo131 = "DR60" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Then
msgbox "YOU MUST REQUEST A COPY"
End If


Thanks Bob
 
I would use a table with a single field (duplicates not allowed).

Then open a recordset Where the [FieldName] = Me.Combo131

(BTW, you should take the time to rename the controls. What is Combo131? It
is easier to understand if you had renamed it (for example) cboProjCode.

Or you could use DLookUp().
Or, on Allen Browne's site, ELookUp() => (http://allenbrowne.com/ser-42.html)

HTH
 
I have tried using the wildcard Me.Combo131 = "AC(*)" in various ways but
none of them work.
Click to expand...

Try:

If Me.Combo131 Like "AC*" Then
msgbox "YOU MUST REQUEST A COPY"
End If
 
Steve using a table with the following seems to work fine

If (Not IsNull(DLookup("[DrivingOffenceCodes]", "tblConvictionCodes",
"[ConvictionCode]='" & Me.DrivingOffenceCodes & "'"))) Then
msgbox "YOU MUST REQUEST A COPY"
Cancel = True
End If

I have rename the Combo131 to cboDrivingOffenceCodes as you say it makes it
easier to understand.

Thanks Bob
Steve Sanford said:
I would use a table with a single field (duplicates not allowed).

Then open a recordset Where the [FieldName] = Me.Combo131

(BTW, you should take the time to rename the controls. What is Combo131?
It
is easier to understand if you had renamed it (for example) cboProjCode.

Or you could use DLookUp().
Or, on Allen Browne's site, ELookUp() =>
(http://allenbrowne.com/ser-42.html)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


bob said:
Hi is there a better way of doing this I would have to add about 30 more
Me.Combo131 = "" to complete this task? I have tried using the wildcard
Me.Combo131 = "AC(*)" in various ways but none of them work.

If Me.Combo131 = "AC10" Or Me.Combo131 = "AC20" Or Me.Combo131 = "AC30"
Or
Me.Combo131 = "BA10" Or Me.Combo131 = "BA30" Or Me.Combo131 = "CD10" Or
Me.Combo131 = "CD20" Or Me.Combo131 = "CD30" Or Me.Combo131 = "CD40" Or
Me.Combo131 = "CD50" Or Me.Combo131 = "CD60" Or Me.Combo131 = "CD70" Or
Me.Combo131 = "CD71" Or Me.Combo131 = "DD40" Or Me.Combo131 = "DD60" Or
Me.Combo131 = "DD80" Or Me.Combo131 = "DR10" Or Me.Combo131 = "DR20" Or
Me.Combo131 = "DR30" Or Me.Combo131 = "DR40" Or Me.Combo131 = "DR50" Or
Me.Combo131 = "DR60" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Then
msgbox "YOU MUST REQUEST A COPY"
End If


Thanks Bob
Click to expand...
Click to expand...
 
Stuart that works fine and is better than using a table as there is less
data to enter and if any new codes come along I will not have to up date the
table each time.

Thanks Bob
 
Excellent.

Keep in mind the ELookup(). It is faster and does more than the standard
DLookup().

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


bob said:
Steve using a table with the following seems to work fine

If (Not IsNull(DLookup("[DrivingOffenceCodes]", "tblConvictionCodes",
"[ConvictionCode]='" & Me.DrivingOffenceCodes & "'"))) Then
msgbox "YOU MUST REQUEST A COPY"
Cancel = True
End If

I have rename the Combo131 to cboDrivingOffenceCodes as you say it makes it
easier to understand.

Thanks Bob
Steve Sanford said:
I would use a table with a single field (duplicates not allowed).

Then open a recordset Where the [FieldName] = Me.Combo131

(BTW, you should take the time to rename the controls. What is Combo131?
It
is easier to understand if you had renamed it (for example) cboProjCode.

Or you could use DLookUp().
Or, on Allen Browne's site, ELookUp() =>
(http://allenbrowne.com/ser-42.html)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


bob said:
Hi is there a better way of doing this I would have to add about 30 more
Me.Combo131 = "" to complete this task? I have tried using the wildcard
Me.Combo131 = "AC(*)" in various ways but none of them work.

If Me.Combo131 = "AC10" Or Me.Combo131 = "AC20" Or Me.Combo131 = "AC30"
Or
Me.Combo131 = "BA10" Or Me.Combo131 = "BA30" Or Me.Combo131 = "CD10" Or
Me.Combo131 = "CD20" Or Me.Combo131 = "CD30" Or Me.Combo131 = "CD40" Or
Me.Combo131 = "CD50" Or Me.Combo131 = "CD60" Or Me.Combo131 = "CD70" Or
Me.Combo131 = "CD71" Or Me.Combo131 = "DD40" Or Me.Combo131 = "DD60" Or
Me.Combo131 = "DD80" Or Me.Combo131 = "DR10" Or Me.Combo131 = "DR20" Or
Me.Combo131 = "DR30" Or Me.Combo131 = "DR40" Or Me.Combo131 = "DR50" Or
Me.Combo131 = "DR60" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or Me.Combo131 = "AC30" Or
Me.Combo131 = "AC30" Then
msgbox "YOU MUST REQUEST A COPY"
End If


Thanks Bob
Click to expand...
Click to expand...
Click to expand...
 
I would still use a table, but change the data to:

AC
BA
CD
DD
DR


And the code to:

If (Not IsNull(DLookup("[DrivingOffenceCodes]", "tblConvictionCodes",
"[ConvictionCode]='" & Left(Me.DrivingOffenceCodes,2) & "'"))) Then
msgbox "YOU MUST REQUEST A COPY"
Cancel = True
End If


If you have new Conviction Codes that have different first two letters, all
you have to do is add them to the table; the code would stay the same.

If you hard codt the first two letters, each time there is a new prefix, you
(someone) will have to edit the code.

Just my two cents....
 
I agree. Data belongs in tables. Always avoid hardcoding values.
Sooner or later, the rules will changes and values will have to be added,
deleted, or changed. It is much easier to change data in a table than it is
to recode the applicaton and redistribute it.
--
Dave Hargis, Microsoft Access MVP


Steve Sanford said:
I would still use a table, but change the data to:

AC
BA
CD
DD
DR


And the code to:

If (Not IsNull(DLookup("[DrivingOffenceCodes]", "tblConvictionCodes",
"[ConvictionCode]='" & Left(Me.DrivingOffenceCodes,2) & "'"))) Then
msgbox "YOU MUST REQUEST A COPY"
Cancel = True
End If


If you have new Conviction Codes that have different first two letters, all
you have to do is add them to the table; the code would stay the same.

If you hard codt the first two letters, each time there is a new prefix, you
(someone) will have to edit the code.

Just my two cents....

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


bob said:
Stuart that works fine and is better than using a table as there is less
data to enter and if any new codes come along I will not have to up date the
table each time.

Thanks Bob
Click to expand...
Click to expand...
 
Back
Top