Select Case or If Then Statement

[Mahoney]

Hello,

I dont know if i am correct, but I am writing a Select Case statement or and if then statement, but it does not seem to work if the source type is a table/query type, which is linked to a table? if so, is there a way the select case can work while keeping it in a table/query row source type, and not change it to value list and enter the choices manually

many thanks

 

[JANR]

Can you post your SELECT CASE statement and also where you have put it (what event).

A description of what its suppose to check for is also helpful

JR

PS don't double post it makes the solution harder to follow for others.

 

[Mahoney]

Thanks JR

here is the code;

Private Sub DispatchBy_AfterUpdate()
Select Case DispatchBy
Case "Email"
Me.AWB.Enabled = False
Me.TapeFormat.Enabled = False
Case "Sattelite"
Me.AWB.Enabled = False
Me.TapeFormat.Enabled = True
Case "FedEx", "Aramex", "DHL"
Me.AWB.Enabled = True
Me.TapeFormat.Enabled = True
End Select

DispatchBy is a combobox that gets the values from a table called Courier Companies and it is table/query type

I think the problem is the table/query row source type, i am assuming that it should be a field list, but there must be a way for it to be done

thanks again

 

[JANR]

I while ago somebody had the same problem and the solution was simple. He was refrencing the combobox "viewed" value instead of what was "stored" aka the bound column.

I can't see anything wrong with your select statement, but you should perhaps include a CASE ELSE, if this fires then debugging is much easier.

JR

 

[Mahoney]

Hello again,

I am not very sure how to change the value to the viewed instead of the stored.
However i tried it on another database, where the values are wriiten manually rather than getting these values from a related table and it worked perfectly.

Please help on how to make it work while the values are from a related table.

I am quite sure now that it has to do with the rowsource type, where it should be field list rather than table/query

many thanks

 

[JANR]

Could you post an example of you DB with the relevant tables and form?

To view what your combobox is storing and what your Select Case is evaluating, insert a Debug.Print in your Select statement. When you choose from the combobox the value is printed to the immediate window. After choosing press Ctrl+G to goto codewindow and see what is printed.

Private Sub DispatchBy_AfterUpdate()
    [COLOR=red]Debug.Print Me.DispatchBy
[/COLOR]    Select Case DispatchBy
    Case "Email"
        Me.AWB.Enabled = False
        Me.TapeFormat.Enabled = False
    Case "Sattelite"
        Me.AWB.Enabled = False
        Me.TapeFormat.Enabled = True
    Case "FedEx", "Aramex", "DHL"
        Me.AWB.Enabled = True
        Me.TapeFormat.Enabled = True
    [COLOR=red]Case Else
        MsgBox "Wrong value"
[/COLOR]End Select
End Sub

JR

 

[Mahoney]

Thanks for your reply JR

I dont know how to post a sample, and i dont want to hassle you with this too.

I added the debug.print, but nothing prints on the immediate window?

thanks again

 

[JANR]

This is wierd?? To start at the beginning, You have a combobox set to table/query. If you set the rowsource to a normal lookuptable it should read something like this:

SELECT ID, DispatchBy FROM tblDispatch;

Then you hide the recordID field by setting the first columnwidth to zero, and the boundcolumn to 1. This is how the normal setup looks like. When you select "email" you see the word email, but what you are testing in your Select Case is infact the recordID aka a number. This will of cause fail and case else should fire, that's why it's importante to include it in your statement to trapp errors.

But it's easy still to use your Select Case but switch it to evaluate the second column instead.

Private Sub DispatchBy_AfterUpdate()
     
    Select Case DispatchBy.Column(1)
    Case "Email"
        Me.AWB.Enabled = False
        Me.TapeFormat.Enabled = False
    Case "Sattelite"
        Me.AWB.Enabled = False
        Me.TapeFormat.Enabled = True
    Case "FedEx", "Aramex", "DHL"
        Me.AWB.Enabled = True
        Me.TapeFormat.Enabled = True
    
    [COLOR=red]Case Else
[/COLOR]        'MsgBox "Wrong Value"
    End Select
End Sub

You could of cause use a valuelist, but it depends on DispatchBy is bound to a table or not. If it's bound the you start drifting away form a normalized setup of your DB if you already have a table in your db why not use it.

One final note it's ok to HARDCODE values like you have if "nothing" will ever change in the future like a new carrier!! then you have to include it in your select case.

I'v tested this so I know its working if you will I can post it tomorrow in a samplebase.

JR

 

[gemma-the-husky]

thinking about this some more ...

msgbox("Testing: " & despatchby.column(1))
Case "Email"
Me.AWB.Enabled = False
Me.TapeFormat.Enabled = False
Case "Sattelite"
Me.AWB.Enabled = False
Me.TapeFormat.Enabled = True
Case "FedEx", "Aramex", "DHL" Me.AWB.Enabled = True
Me.TapeFormat.Enabled = True
End Select

the red items are very likely the issue

what JR is getting at, is "how do you sleect these values in the first place - a combo box?"

if so where does the combo box get its values from

often a combo box has 2 columns, the first column being a number, and the second a text, which is displayed in the box itself, but this is not always the case

we think you are testing the text, as you are using column(1) but it may be that column(1) is not correct - we cant be sure until you clarify where the text is coming from. Please note that if there is only one column, then you need to just use

mycombobox, or mycombobox.column(0), as the first coulmn is coliumn zero

-------
the other thing is, is how do you know it is not working - what should happen to the fields you are setting

also, try adding in the blue line, to see what value is realy being tested - or put a breakpoint in your code, and step through it - very useful!

 

[Mahoney]

Many thanks Jan & Gemma,

It worked perfectly, I only added the .column(1) after DispatchBy
But still trying to figure out the exact meaning of it

Many thanks again

all the best