Can i condense this code ? or for clarity should it be different ?

[trevorC via AccessMonster.com]

Hi,
I'd like to be able to condense this to as few lines as possible. Can this be
done or am i doing things wrong to start with.

Me.Scan_input= UCase(InputBox(Chr(13) & Chr(13) & " Five entries are
required to complete a record", " Scan all 4 barcodes now,
in any order", , 4850, 450))

If Len(Me.Scan_input) = 0 Then exit sub

tr = DLookup("[Customer Name]", "[Customers]", "[Customer Name] = forms!
[serial number additions]![Scan_input]")

rt = DLookup("[Model]", "[Model Types]", "[Model] = forms![serial number
additions]![Scan_input]")

chk_1 = DLookup("[form number length]", "[reference check]", "[Check Number]
= forms![serial number additions]![build_check]")
chk_2 = DLookup("[form number Start]", "[reference check]", "[Check Number] =
forms![serial number additions]![build_check]")
chk_3 = DLookup("[JEA Serial number length]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_4 = DLookup("[JEA Serial number Start]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_5 = DLookup("[Part number length]", "[reference check]", "[Check Number]
= forms![serial number additions]![build_check]")
chk_6 = DLookup("[Part number Start]", "[reference check]", "[Check Number] =
forms![serial number additions]![build_check]")
chk_7 = DLookup("[LCD Serial number length]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_8 = DLookup("[LCD Serial number Start]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_9 = DLookup("[Touch Serial number length]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_10 = DLookup("[Touch Serial number Start]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")

If IsNull(chk_1) Then chk1 = False Else If Len(Me.Scan_input) = CLng(chk_1)
Then chk1 = True Else chk1 = False
If IsNull(chk_2) Then chk2 = False Else If Left(Me.Scan_input, Len(chk_2)) =
chk_2 Then chk2 = True Else chk2 = False
If IsNull(chk_3) Then chk3 = False Else If Len(Me.Scan_input) = CLng(chk_3)
Then chk3 = True Else chk3 = False
If IsNull(chk_4) Then chk4 = False Else If Left(Me.Scan_input, Len(chk_4)) =
chk_4 Then chk4 = True Else chk4 = False
If IsNull(chk_5) Then chk5 = False Else If Len(Me.Scan_input) = CLng(chk_5)
Then chk5 = True Else chk5 = False
If IsNull(chk_6) Then chk6 = False Else If Left(Me.Scan_input, Len(chk_6)) =
chk_6 Then chk6 = True Else chk6 = False
If IsNull(chk_7) Then chk7 = False Else If Len(Me.Scan_input) = CLng(chk_7)
Then chk7 = True Else chk7 = False
If IsNull(chk_8) Then chk8 = False Else If Left(Me.Scan_input, Len(chk_8)) =
chk_8 Then chk8 = True Else chk8 = False
If IsNull(chk_9) Then chk9 = False Else If Len(Me.Scan_input) = CLng(chk_9)
Then chk9 = True Else chk9 = False
If IsNull(chk_10) Then chk10 = False Else If Left(Me.Scan_input, Len(chk_10))
= chk_10 Then chk10 = True Else chk10 = False

If chk1 = True And chk2 = True _
Or chk3 = True And chk4 = True _
Or chk5 = True And chk6 = True _
Or chk7 = True And chk8 = True _
Or chk9 = True And chk10 = True Then

'***FORM***
If chk1 = True And chk2 = True Then
Me.Form_Number = Me.Scan_input
Me.Form_Number.Tag = "ok"
End If
code continues .....for other checks

 

[Albert D. Kallal]

Here is some tips:

First, dlookup is great for one value, but for "many" values from the same
record,
pull the whole record into what we call a reocrdset.

The code:

Dim rst As DAO.Recordset
Dim strSql As String

strSql = "select * from [reference check] " & _
"where [Check Number] = " & Me![build_check]
Set rst = CurrentDb.OpenRecordset(strSql)

--> you can test here if the record actually exits
if rst.RecordCount = 0 then
' code here for not found.... all values will be null
end if

The code that follows can now be written as:

chk1 = myNZ(rst![form number length])
chk2 = myNZL(rst![form number Start])
chk3 = myNZ(rst![JEA Serial number length])

Note in the above how we create a function called myNZ (for the len ones),
and
you could create one called myNZL for the ones with "left" in the code.

The function will look like:

Public Function myNZ(v As Variant) As Variant

myNZ = False

If IsNull(v) = False Then
If Len(Me.Scan_input) = CLng(v) Then
myNZ = True
End If
End If

End Function


So, the dlookup() can replaced with that recordset that pulls in the whole
reocrd, and thus no need for many dlooups().

And, the public function placed in the form need only be written once to be
used over and over.
Your code of:

If chk1 = True And chk2 = True _
Or chk3 = True And chk4 = True _
Or chk5 = True And chk6 = True _
Or chk7 = True And chk8 = True _
Or chk9 = True And chk10 = True Then

That code is ok. I suppose you could try:

If (chk1 and chk2) _
or (chk3 and chk4) _
or (chk5 and chk6) _

etc, but it not a whole lot shorter...

 

[BruceM via AccessMonster.com]

Did the code compile? Do you have Option Explicit below Option Compare
Database at the top of the code module? You have not declared the variables
rt and tr that I can see, but as I expect they would be declared as variants
to allow for Null that isn't necessarily a problem. However, forcing
variable declaration helps guard against typos that otherwise may be
interpreted as Nulls. Regarding compiling the code, there seem to be several
instances of If without End If. If so, compiling would have picked that up.

Looking at this from a bit of a different angle than Albert used, it is often
worthwhile to turn repeating code into a single variable:

Dim strCN as String
strCN = "[Check Number] = " & Forms![serial number additions]![build_check]")
DLookup("[Touch Serial number Start]", "[reference check]", strCN)

Also, if this code is on the form [serial number additions] you could just
have:
Me.[build_check]
instead of:
Forms![serial number additions]![build_check]

It looks as if you are testing whether a check box is null, which I think
could happen with a triple state check box, but I have never found a use for
those, and am not sure I understand them. I think it is if you want "no
preference" as one of the choices, but it is apt to be confusing to users.
However, I think you would need to use a Number field, not a Yes/No if you
want the possibility of a check box value being null.

You posted a number of statements like the following:
If IsNull(chk_1) Then chk1 = False Else If Len(Me.Scan_input) = CLng(chk_1)
Then chk1 = True Else chk1 = False

As mentioned, in a Yes/No field there is no possibility of Null, so there is
no need to test that. Since it seems you are not allowing the field to be
Null there is no reason for a triple state check box, so there is no reason
for the field being a number field, or if it is a number field you only want
to allow 0 or -1 as values. On another point, the value of a yes/no field
(chk_1) is either 0 or -1 (CLng seems to be redundant in any case). Length
of a field will not be a negative number, so if the length of Scan_input is 1
or greater that test will always fail, and if it is 0 the test will pass only
if chk_1 is False. It is difficult to understand what you are trying to do
with that code.

All of that said, please note Albert's comments about the use of a related
recordset, and his use of a function in place of repeating code. It seems
you are storing preferences in Yes/No fields, which is generally not an
efficient or sound design.

Hi,
I'd like to be able to condense this to as few lines as possible. Can this be
done or am i doing things wrong to start with.

Me.Scan_input= UCase(InputBox(Chr(13) & Chr(13) & " Five entries are
required to complete a record", " Scan all 4 barcodes now,
in any order", , 4850, 450))

If Len(Me.Scan_input) = 0 Then exit sub

tr = DLookup("[Customer Name]", "[Customers]", "[Customer Name] = forms!
[serial number additions]![Scan_input]")

rt = DLookup("[Model]", "[Model Types]", "[Model] = forms![serial number
additions]![Scan_input]")

chk_1 = DLookup("[form number length]", "[reference check]", "[Check Number]
= forms![serial number additions]![build_check]")
chk_2 = DLookup("[form number Start]", "[reference check]", "[Check Number] =
forms![serial number additions]![build_check]")
chk_3 = DLookup("[JEA Serial number length]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_4 = DLookup("[JEA Serial number Start]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_5 = DLookup("[Part number length]", "[reference check]", "[Check Number]
= forms![serial number additions]![build_check]")
chk_6 = DLookup("[Part number Start]", "[reference check]", "[Check Number] =
forms![serial number additions]![build_check]")
chk_7 = DLookup("[LCD Serial number length]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_8 = DLookup("[LCD Serial number Start]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_9 = DLookup("[Touch Serial number length]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")
chk_10 = DLookup("[Touch Serial number Start]", "[reference check]", "[Check
Number] = forms![serial number additions]![build_check]")

If IsNull(chk_1) Then chk1 = False Else If Len(Me.Scan_input) = CLng(chk_1)
Then chk1 = True Else chk1 = False
If IsNull(chk_2) Then chk2 = False Else If Left(Me.Scan_input, Len(chk_2)) =
chk_2 Then chk2 = True Else chk2 = False
If IsNull(chk_3) Then chk3 = False Else If Len(Me.Scan_input) = CLng(chk_3)
Then chk3 = True Else chk3 = False
If IsNull(chk_4) Then chk4 = False Else If Left(Me.Scan_input, Len(chk_4)) =
chk_4 Then chk4 = True Else chk4 = False
If IsNull(chk_5) Then chk5 = False Else If Len(Me.Scan_input) = CLng(chk_5)
Then chk5 = True Else chk5 = False
If IsNull(chk_6) Then chk6 = False Else If Left(Me.Scan_input, Len(chk_6)) =
chk_6 Then chk6 = True Else chk6 = False
If IsNull(chk_7) Then chk7 = False Else If Len(Me.Scan_input) = CLng(chk_7)
Then chk7 = True Else chk7 = False
If IsNull(chk_8) Then chk8 = False Else If Left(Me.Scan_input, Len(chk_8)) =
chk_8 Then chk8 = True Else chk8 = False
If IsNull(chk_9) Then chk9 = False Else If Len(Me.Scan_input) = CLng(chk_9)
Then chk9 = True Else chk9 = False
If IsNull(chk_10) Then chk10 = False Else If Left(Me.Scan_input, Len(chk_10))
= chk_10 Then chk10 = True Else chk10 = False

If chk1 = True And chk2 = True _
Or chk3 = True And chk4 = True _
Or chk5 = True And chk6 = True _
Or chk7 = True And chk8 = True _
Or chk9 = True And chk10 = True Then

'***FORM***
If chk1 = True And chk2 = True Then
Me.Form_Number = Me.Scan_input
Me.Form_Number.Tag = "ok"
End If
code continues .....for other checks

 

[trevorC via AccessMonster.com]

Thankyou both for your comments, they will be stored and referenced as
needed. the suggestions given are very good and will help me reduce the code
i have.