Some challenging VBA coding for Forms

Hi guys!
I have 3 queries here, answer whatever u have time for
Say I have a form with the following fields

a. cbo Pipe class
b.cbo Component Type
c. txtComponent Id
c.cbo size
d.txt schedule
e.txt mesc

I have 20 pipe classes, each has 36 components. (all the classes hav similar structure n same components, only their corresponding numerical values differ)

1. If comp <> "BROB" And comp <> "RECB" And comp <> "REEB" And comp <> "TERB"….. Then
Me.cboSize2.Visible = False
End if

Can I write that statement in a better way? I want to know what’s the VB syntax for something like this

If comp IN(“BROB”,”RECB”…….) then
-------
End IF

2. I need to populate combo box Size with values from table class1_mescList if pipe class=1, But only those sizes for which component Id= txtComponent ID.

I have to use table class2_mescList if pipe class=2…. Etc, u get the picture!

How can I write a query for this? I don’t think trying to give specifications in Row source will be the best approach (as I said I have 30 classes!)

3. This is how I find value for txtSchedule

Private Sub txtSchedule1_GotFocus()
Dim class
class = cboPipeClass.Value
Dim size
size = cboSize1.Value
Dim schedule
Select Case class
Case "11440"
schedule = DLookup("Schedule", "11440_ScheduleList", "Size=" & [size])
Case "11441"
schedule = DLookup("Schedule", "11441_ScheduleList", "Size=" & [size])
End Select

txtSchedule.Value = schedule

End Sub

As u can see, the only difference between when pipe class=11440 and when its =11441 is that in the first case, we perform the DLookUp fn on 11440_ScheduleList table and in the second case on 11441_ScheduleList table.
Is there an option other that writing 30 cases?? can do something to say
(classNo)_ScheduleList in the DLookup function.
Its kinda important cause the we might add more classes later on, in which case whoever is maintaining the DB will have to come back to the code and add another case statement.

For the first part you can do the following:

if Instr(Comp, "BROBRECBREEBTERB") > 0 then
Me.cboSize2.visible = false
else
Me.cboSize2.visible = true
endif

where the string in the instr function would contain all the unique strings that you DO NOT want to have cbosize2 available for.

For the second part I think you're may be making it a bit more complicated than necessary. Is the structure (STRUCTURE not CONTENT) for your tables

Class1_mescList
Class2_mescList
...
Class30_mescList

the same? If they are you can have all of these tables combined into 1 table and just have a class identifier included then when you create your combo box you can base it on only the class that is relevant.

Part 3 would also follow this model where you should only have to pick the class along with your current information to be able to get what you want.

Take a close look at the structure of your class tables and schedule tables, if they are nearly identical or are identical in structure you are going to save yourself a lot of aggravation and time if you can combine them into one table and put in an additional identifier for the class.

Thanks rpeare!

hmm i tried using
If InStr(comp, "BROBRECBREEB") > 0 Then

instead of

If comp <> "BROB" And comp <> "RECB" And comp <> "REEB" then

but its not working! that peice of IF block is not doing anything after replacement.

For my 2nd and 3rd queries, you are right- the structures are exactly the same. what you suggested would definetly mak the coding part of it a lot easier. And it would solve the problem of adding additional classes later on.
The reason i chose to have seperate tables is that, each one allready has about 400 entries. I didn't want 400* 20 (the no of classes) =8000 entries in one table!
Wouldn't that make searching the table a lot slower? and better chance of it being corrupted by the user?

For part 1 you're going to have to post your code, I can't guess at what went wrong without seeing what you did. remember to encapsulate all of your code in [ code ] [ /code ] markers (take out the spaces within the square brackets to make it work correctly.

Secondly, having 20 separate tables for storing identical data is a lot more problematic than storing them on one table. One table regardless of how much it contains, is no less or no more susceptible to corruption than 20 tables. As far as making your database slower I seriously doubt you will even notice the difference in time because everything you're going to run is going to be based on a class and instead of looking up from an individual table you'll be looking up a chunk of records one time per query (or should be) so it shouldn't really make that much difference in human time. The only thing you have to be wary of and program for is that a user can't accidentally modify one class while working on another.

For my 2nd and 3rd queries, you are right- the structures are exactly the same. what you suggested would definetly mak the coding part of it a lot easier. And it would solve the problem of adding additional classes later on.
The reason i chose to have seperate tables is that, each one allready has about 400 entries. I didn't want 400* 20 (the no of classes) =8000 entries in one table!
Wouldn't that make searching the table a lot slower? and better chance of it being corrupted by the user?

hi rpeare,

oo i was making a silly mistake with that first problem.. its works now!

i took your advice and restructured my tables.. you guys were right, that does make life simpler!
thanks a lot!