Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I want to loop some VBA code on Form_Load that will update one field in my Form based on 2 other fields. Currently it will only update the first record or when I click on a record.

Currently it looks like this:

Private Sub Form_Current()

    If Style = "W" And Size = "120" Then ContainerType = 9
    If Style = "W" And Size = "240" Then ContainerType = 2
    If Style = "W" And Size = "360" Then ContainerType = 34
    If Style = "R" And Size = "120" Then ContainerType = 37
    If Style = "R" And Size = "240" Then ContainerType = 5
    If Style = "R" And Size = "360" Then ContainerType = 12
    If Style = "Y" And Size = "120" Then ContainerType = 24
    If Style = "Y" And Size = "240" Then ContainerType = 4
    If Style = "Y" And Size = "360" Then ContainerType = 14
    If Style = ("2Y") And Size = "120" Then ContainerType = 9
    If Style = ("2Y") And Size = "240" Then ContainerType = 25
    If Style = ("2Y") And Size = "360" Then ContainerType = 28
    If Style = ("3Y") And Size = "120" Then ContainerType = 9
    If Style = ("3Y") And Size = "240" Then ContainerType = 51
    If Style = ("3Y") And Size = "360" Then ContainerType = 29

End Sub

Perhaps there is a better way to do this?

share|improve this question
I'm not seeing any pattern in your ContainerType values. Can you explain the mathematics behind this code? – Dai May 30 at 3:26

2 Answers

up vote 0 down vote

Certainly a "better way to do this" would be to create a table named something like [ContainerType_lookup]:

Style  Size  ContainerType
-----  ----  -------------
W      120               9
W      240               2
W      360              34
...

and then use either a SQL JOIN or the DLookup() function to find the appropriate ContainerType. That way...

  1. You don't necessarily have to modify your code if something changes (like a new ContainerType), and

  2. If you ever need to derive the ContainerType anywhere else in the application you don't have to replicate your VBA logic there, too.

share|improve this answer
up vote 0 down vote

In my opinion I think you can use Select Case to split it and make it less heavy to read. You also get some more organisation grouping them by Size or Style depending what you need.

But, i agree that using a table with or without a query will make your life easier here.

Select Case Size
    Case "120" 
        If Style = "W" Then ContainerType = 9
        If Style = "R" Then ContainerType = 37
        ...
    Case "240"
        If Style = "W" Then ContainerType = 2
        If Style = "R" Then ContainerType = 5
        ...
    Case "360"
        If Style = "W" Then ContainerType = 34
        If Style = "R" Then ContainerType = 12
        ...
    Case Else
        Exit Sub
End Select
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.