• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

check list of values

hello,

i have a list of part values

1000098S32100081000
1000135S31603081000
0500058S31603790001
0500058S31603790003
0500058S31603790006
0500058S31603700020
0500058S31603709920
0500058S31603718920
0500058S31603710820
0500058S31603723220

and to these values there are two codes that apply: 7488897 and 7849999

what i want to do is to check if the
code entered is 7488897 or 7849999 and the part is contained on the list, then the values can be saved.

i do not know how to do this check. any help with the code, would be great.

thank you very much.
0
metropia
Asked:
metropia
  • 2
3 Solutions
 
metropiaAuthor Commented:
i am putting together this solution:
Dim steamless(40)
steamless(0) = "1000098S32100081000"
steamless(1) = "1000135S31603081000"
steamless(2) = "0500058S31603790001"
steamless(3) = "0500058S31603790003"
steamless(4) = "0500058S31603790006"
steamless(5) = "0500058S31603700020"
steamless(6) = "0500058S31603709920"
steamless(7) = "0500058S31603718920"
steamless(8) = "0500058S31603710820"
steamless(9) = "0500058S31603723220"

If Instr(Join(steamless, ID) > 0 And (string7 <> "7304410000" Or string7 <> "7507120000") Then
    MsgBox("Please verify that the part and code values are correct before saving") 
    MACRO_SUCCESS = FALSE
    MACRO_MESSAGE = "Part was not saved"
End If

Open in new window

Does it look correct?
0
 
aikimarkCommented:
Your If statement isn't syntactically correct.  Your Join() function needs two parameters and you have only supplied one.
Corrected:
If InStr(Join(steamless, ""), ID) > 0 And (string7 <> "7304410000" Or string7 <> "7507120000") Then

Open in new window


Unless these are the only combinations, this should really be a database type solution.

The fact that you have
steamless(40)

Open in new window

as the size of your array, leads me to believe that your validation problem is actually much larger than you've indicated.

The best validation is the one where the user can not supply bad data.  In your case, I would expect to see two controls that supply the two codes and another control that contains the valid part values.  The validation code only needs to check whether the two controls have selected (non null or non empty) values.
Here's the way I would have written your code if I weren't using a database.
Dim steamless As String
steamless = "1000098S32100081000^1000135S31603081000^0500058S31603790001^0500058S31603790003^0500058S31603790006^0500058S31603700020^0500058S31603709920^0500058S31603718920^0500058S31603710820^0500058S31603723220^"

If InStr(steamless, ID & "^") > 0 And InStr("7304410000^7507120000^", string7 & "^") Then
Else
    MsgBox ("Please verify that the part and code values are correct before saving")
    MACRO_SUCCESS = False
    MACRO_MESSAGE = "Part was not saved"
End If

Open in new window

0
 
aikimarkCommented:
Using dictionary objects, this solution would look like this:
Dim dicSteamless As Object
Dim dicParts As Object
Set dicSteamless = CreateObject("scripting.dictionary")
Set dicParts = CreateObject("scripting.dictionary")

dicSteamless.Add "1000098S32100081000", 1
dicSteamless.Add "1000135S31603081000", 1
dicSteamless.Add "0500058S31603790001", 1
dicSteamless.Add "0500058S31603790003", 1
dicSteamless.Add "0500058S31603790006", 1
dicSteamless.Add "0500058S31603700020", 1
dicSteamless.Add "0500058S31603709920", 1
dicSteamless.Add "0500058S31603718920", 1
dicSteamless.Add "0500058S31603710820", 1
dicSteamless.Add "0500058S31603723220", 1
dicParts.Add "7304410000", 1
dicParts.Add "7507120000", 1

If dicSteamless.Exists(ID) And dicParts.Exists(string7) Then
Else
    MsgBox ("Please verify that the part and code values are correct before saving")
    MACRO_SUCCESS = False
    MACRO_MESSAGE = "Part was not saved"
End If

Open in new window

0
 
ArkCommented:
   Dim colSteamless As New Collection
   Dim colParts As New Collection
   colSteamless.Add 1, "1000098S32100081000"
   colSteamless.Add 1, "1000135S31603081000"
   colSteamless.Add 1, "0500058S31603790001"
   colSteamless.Add 1, "0500058S31603790003"
   colSteamless.Add 1, "0500058S31603790006"
   colSteamless.Add 1, "0500058S31603700020"
   colSteamless.Add 1, "0500058S31603709920"
   colSteamless.Add 1, "0500058S31603718920"
   colSteamless.Add 1, "0500058S31603710820"
   colSteamless.Add 1, "0500058S31603723220"
   colParts.Add 1, "7304410000"
   colParts.Add 1, "7507120000"

   Dim stm As Integer, prt As Integer
On Error GoTo Fail
   stm = colSteamless(id)
   prt = colParts(string7)
   MsgBox "Exists!"
   On Error GoTo 0
   'DoSave
   'or fnValidate=True
   Exit Sub 'or Exit Function
: Fail
   MsgBox "Missing!"

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now