Writing a function that controls the contents of a text box

[Aleashia]

Hi everyone,

Firstly excuse my awful vba knowledge and I am learning (slowly) but I am wanting to create a function ( I think a str).

I have created a form which displays species of plants (the database is for a gardening centre). I have created a separate textbox called 'advice'. Now I am wanting to create a function called stradvice which will give advice about a few plant species:

For plants with a lifespan of one year the advice would be 'beware of night frost' and for those plants with a lifespan of two years 'water regularly'.

I am unsure how to proceed, I am thinking a string of IIF statements would suffice?!? Any help would be appreciated- thankyou.

 

[shadow9449]

Hi everyone,

Firstly excuse my awful vba knowledge and I am learning (slowly) but I am wanting to create a function ( I think a str).

I have created a form which displays species of plants (the database is for a gardening centre). I have created a separate textbox called 'advice'. Now I am wanting to create a function called stradvice which will give advice about a few plant species:

For plants with a lifespan of one year the advice would be 'beware of night frost' and for those plants with a lifespan of two years 'water regularly'.

I am unsure how to proceed, I am thinking a string of IIF statements would suffice?!? Any help would be appreciated- thankyou.

Do you know how to use the Select Case statement?

SHADOW

 

[Aleashia]

Yes I do, although I will probably mess up the syntax somewhere along the way

 

[Summerwind]

Excuse me for butting in but I don't think that you do need a Select Case statement.

I would be inclined to create a new table for your advice strings consisting of a key field and the advice field. Add a new field to your plants table which links to the new table. Now your testbox contents will reflect the advice for that plant and you don't need to hard code every advice string in a select clause.

 

[Aleashia]

Thanks, I must however do it all in VBA since it is part of a course I am doing. I am self studying this since the university I am attending this year teaches most of their classes in a language foreign to my own (sucks I know). So I don't have many places to turn for help!

I am actually struggling a bit because I have never done VBA before and haven't the time to thoroughly learn it :s

 

[Summerwind]

Do you have to create tables in code or are you given them for your assignment?

How many advice strings do you need?

You can create tables in code and add columns to existing ones using ADOX (Well, I can) but it sounds like this is outside of the requirements of your course.

 

[Aleashia]

I have created a database with tables, all related etc (which was required) and created a few forms.

I am using the form 'Plants' and I have created a new textbox on this form with the label 'advice'. I am supposed to write in the class module of this form a function that will give advice about a few species of plants (not all).

I know I will have to use IIF statements and I think the control source for the textbox will be: =stradvice(species).

But my lack of vba knowledge isn't helping=/ Hope this explains it more thoroughly

 

[Bat17]

your control box is OK try this function to go with it. 'Select Case' is easier to maintain than If statements.

Function stradvice(strSpecies) As String
If IsNull(strSpecies) Then
    stradvice = ""
    Exit Function
End If
Select Case strSpecies
    Case "Cactus"
      stradvice = "water Sparingly"
    Case "Poisen Ivy"
        stradvice = "Dont Touch"
    Case "Roses"
        stradvice = "spay in spring"
     Case Else ' none of the above
        stradvice = ""
End Select
End Function

If you were doing this for real though it should be done in table for speed and easy maintenance though.

HTH

Peter

 

[Aleashia]

your control box is OK try this function to go with it. 'Select Case' is easier to maintain than If statements.

Function stradvice(strSpecies) As String
If IsNull(strSpecies) Then
    stradvice = ""
    Exit Function
End If
Select Case strSpecies
    Case "Cactus"
      stradvice = "water Sparingly"
    Case "Poisen Ivy"
        stradvice = "Dont Touch"
    Case "Roses"
        stradvice = "spay in spring"
     Case Else ' none of the above
        stradvice = ""
End Select
End Function

If you were doing this for real though it should be done in table for speed and easy maintenance though.

HTH

Peter

Thankyou! it works perfectly! Yeah I could of done this in a table quite easily, but alas I have to use VBA and thankyou so much for taking the time to help me out =)

 

[Bat17]

Glad to help