What am i doing wrong here?

Sam Summers

Sam Summers

Registered User.
Local time
Today, 16:11
Joined
Sep 17, 2001
Messages
939
Hi,

Being self taught i do not fully understand all vba functions and procedures and which best to use yet.

I have this code:

Code: 
Private Sub Form_Current()

Me!Position1QrySubform.Form!Type.SetFocus
If Me!Position1QrySubform.Form!Type = "Foam" Then
Me.Btn1.BackColor = RGB(255, 228, 181)
Else
If Me!Position1QrySubform.Form!Type = "Water" Then
Me.Btn1.BackColor = RGB(255, 0, 0)
Else
If Me!Position1QrySubform.Form!Type = "Powder" Then
Me.Btn1.BackColor = RGB(30, 144, 255)
Else
If Me!Position1QrySubform.Form!Type = "CO2" Then
Me.Btn1.BackColor = RGB(0, 0, 0)
Else
If Me!Position1QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn1.BackColor = RGB(0, 201, 87)
Else
Me!Position2QrySubform.Form!Type.SetFocus
If Me!Position2QrySubform.Form!Type = "Foam" Then
Me.Btn2.BackColor = RGB(255, 228, 181)
Else
If Me!Position2QrySubform.Form!Type = "Water" Then
Me.Btn2.BackColor = RGB(255, 0, 0)
Else
If Me!Position2QrySubform.Form!Type = "Powder" Then
Me.Btn2.BackColor = RGB(30, 144, 255)
Else
If Me!Position2QrySubform.Form!Type = "CO2" Then
Me.Btn2.BackColor = RGB(0, 0, 0)
Else
If Me!Position2QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn2.BackColor = RGB(0, 201, 87)
Else
Me!Position3QrySubform.Form!Type.SetFocus
If Me!Position3QrySubform.Form!Type = "Foam" Then
Me.Btn3.BackColor = RGB(255, 228, 181)
Else
If Me!Position3QrySubform.Form!Type = "Water" Then
Me.Btn3.BackColor = RGB(255, 0, 0)
Else
If Me!Position3QrySubform.Form!Type = "Powder" Then
Me.Btn3.BackColor = RGB(30, 144, 255)
Else
If Me!Position3QrySubform.Form!Type = "CO2" Then
Me.Btn3.BackColor = RGB(0, 0, 0)
Else
If Me!Position3QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn3.BackColor = RGB(0, 201, 87)
Else
Me!Position4QrySubform.Form!Type.SetFocus
If Me!Position4QrySubform.Form!Type = "Foam" Then
Me.Btn4.BackColor = RGB(255, 228, 181)
Else
If Me!Position4QrySubform.Form!Type = "Water" Then
Me.Btn4.BackColor = RGB(255, 0, 0)
Else
If Me!Position4QrySubform.Form!Type = "Powder" Then
Me.Btn4.BackColor = RGB(30, 144, 255)
Else
If Me!Position4QrySubform.Form!Type = "CO2" Then
Me.Btn4.BackColor = RGB(0, 0, 0)
Else
If Me!Position4QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn4.BackColor = RGB(0, 201, 87)
Else
Me!Position5QrySubform.Form!Type.SetFocus
If Me!Position5QrySubform.Form!Type = "Foam" Then
Me.Btn5.BackColor = RGB(255, 228, 181)
Else
If Me!Position5QrySubform.Form!Type = "Water" Then
Me.Btn5.BackColor = RGB(255, 0, 0)
Else
If Me!Position5QrySubform.Form!Type = "Powder" Then
Me.Btn5.BackColor = RGB(30, 144, 255)
Else
If Me!Position5QrySubform.Form!Type = "CO2" Then
Me.Btn5.BackColor = RGB(0, 0, 0)
Else
If Me!Position5QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn5.BackColor = RGB(0, 201, 87)
Else
Me!Position6QrySubform.Form!Type.SetFocus
If Me!Position6QrySubform.Form!Type = "Foam" Then
Me.Btn6.BackColor = RGB(255, 228, 181)
Else
If Me!Position6QrySubform.Form!Type = "Water" Then
Me.Btn6.BackColor = RGB(255, 0, 0)
Else
If Me!Position6QrySubform.Form!Type = "Powder" Then
Me.Btn6.BackColor = RGB(30, 144, 255)
Else
If Me!Position6QrySubform.Form!Type = "CO2" Then
Me.Btn6.BackColor = RGB(0, 0, 0)
Else
If Me!Position6QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn6.BackColor = RGB(0, 201, 87)
Else
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

End Sub

Now it is working but only to a point.
So the first form (Position1) is Blank and this works.

And Position2 is "Foam" and this code works and shows the correct colour but everything (code) after this is not working so i am doing something wrong?

Any pointers would be great.
Many thanks in advance
 
Sam Summers said:
Any pointers would be great.
Click to expand...
Code: 
Private Sub Form_Current()

Me!Position1QrySubform.Form!Type.SetFocus
If Me!Position1QrySubform.Form!Type = "Foam" Then
Me.Btn1.BackColor = RGB(255, 228, 181)
ElseIf Me!Position1QrySubform.Form!Type = "Water" Then
Me.Btn1.BackColor = RGB(255, 0, 0)
ElseIf Me!Position1QrySubform.Form!Type = "Powder" Then
Me.Btn1.BackColor = RGB(30, 144, 255)
ElseIf Me!Position1QrySubform.Form!Type = "CO2" Then
Me.Btn1.BackColor = RGB(0, 0, 0)
ElseIf Me!Position1QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn1.BackColor = RGB(0, 201, 87)
end if

Me!Position2QrySubform.Form!Type.SetFocus
If Me!Position2QrySubform.Form!Type = "Foam" Then
Me.Btn2.BackColor = RGB(255, 228, 181)
ElseIf Me!Position2QrySubform.Form!Type = "Water" Then
Me.Btn2.BackColor = RGB(255, 0, 0)
ElseIf Me!Position2QrySubform.Form!Type = "Powder" Then
Me.Btn2.BackColor = RGB(30, 144, 255)
ElseIf Me!Position2QrySubform.Form!Type = "CO2" Then
Me.Btn2.BackColor = RGB(0, 0, 0)
ElseIf Me!Position2QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn2.BackColor = RGB(0, 201, 87)
endif

Me!Position3QrySubform.Form!Type.SetFocus
If Me!Position3QrySubform.Form!Type = "Foam" Then
Me.Btn3.BackColor = RGB(255, 228, 181)
ElseIf Me!Position3QrySubform.Form!Type = "Water" Then
Me.Btn3.BackColor = RGB(255, 0, 0)
ElseIf Me!Position3QrySubform.Form!Type = "Powder" Then
Me.Btn3.BackColor = RGB(30, 144, 255)
ElseIf Me!Position3QrySubform.Form!Type = "CO2" Then
Me.Btn3.BackColor = RGB(0, 0, 0)
ElseIf Me!Position3QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn3.BackColor = RGB(0, 201, 87)
endif

Me!Position4QrySubform.Form!Type.SetFocus
If Me!Position4QrySubform.Form!Type = "Foam" Then
Me.Btn4.BackColor = RGB(255, 228, 181)
ElseIf Me!Position4QrySubform.Form!Type = "Water" Then
Me.Btn4.BackColor = RGB(255, 0, 0)
ElseIf Me!Position4QrySubform.Form!Type = "Powder" Then
Me.Btn4.BackColor = RGB(30, 144, 255)
ElseIf Me!Position4QrySubform.Form!Type = "CO2" Then
Me.Btn4.BackColor = RGB(0, 0, 0)
ElseIf Me!Position4QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn4.BackColor = RGB(0, 201, 87)
endif

Me!Position5QrySubform.Form!Type.SetFocus
If Me!Position5QrySubform.Form!Type = "Foam" Then
Me.Btn5.BackColor = RGB(255, 228, 181)
ElseIf Me!Position5QrySubform.Form!Type = "Water" Then
Me.Btn5.BackColor = RGB(255, 0, 0)
ElseIf Me!Position5QrySubform.Form!Type = "Powder" Then
Me.Btn5.BackColor = RGB(30, 144, 255)
ElseIf Me!Position5QrySubform.Form!Type = "CO2" Then
Me.Btn5.BackColor = RGB(0, 0, 0)
ElseIf Me!Position5QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn5.BackColor = RGB(0, 201, 87)
endif

Me!Position6QrySubform.Form!Type.SetFocus
If Me!Position6QrySubform.Form!Type = "Foam" Then
Me.Btn6.BackColor = RGB(255, 228, 181)
ElseIf Me!Position6QrySubform.Form!Type = "Water" Then
Me.Btn6.BackColor = RGB(255, 0, 0)
ElseIf Me!Position6QrySubform.Form!Type = "Powder" Then
Me.Btn6.BackColor = RGB(30, 144, 255)
ElseIf Me!Position6QrySubform.Form!Type = "CO2" Then
Me.Btn6.BackColor = RGB(0, 0, 0)
ElseIf Me!Position6QrySubform.Form!Type = "Wet Chemical" Then
Me.Btn6.BackColor = RGB(0, 201, 87)
End If

End Sub
 
Can you explain in simple language what you are trying to achieve, because I just can't discern it.

No coding jargon, just simple english. Tell me what you want to happen, where you want it to happen and under what circumstances.
 
You should also indent your code, so you can easily follow/understand your nesting levels.
 
Sam Summers said:
Being self taught i do not fully understand all vba functions and procedures and which best to use yet.
Click to expand...
Code: 
Private Sub Form_Current()
'''''''''''
On Error Resume Next
Dim name_subform, name_button
Dim nomer_RGB As Long
Dim nomer_subform As Long
Dim NAME_TYPE As String
'''''''''''
For nomer_subform = 1 To 6
    name_subform = Replace("Position%1QrySubform", "%1", nomer_subform)
    name_button = Replace("Btn%1", "%1", nomer_subform)
    Debug.Print name_subform, name_button
    Me.Controls(name_subform).Form!Type.SetFocus
    NAME_TYPE = Me.Controls(name_subform).Form!Type
    If NAME_TYPE = "Foam" Then
        nomer_RGB = RGB(255, 228, 181)
    ElseIf NAME_TYPE = "Water" Then
        nomer_RGB = RGB(255, 0, 0)
    ElseIf NAME_TYPE = "Powder" Then
        nomer_RGB = RGB(30, 144, 255)
    ElseIf NAME_TYPE = "CO2" Then
        nomer_RGB = RGB(0, 0, 0)
    ElseIf NAME_TYPE = "Wet Chemical" Then
        nomer_RGB = RGB(0, 201, 87)
    End If
    If nomer_RGB > 0 Then
        Me.Controls(name_button).BackColor = nomer_RGB
    End If
Next
End Sub
 
Sam Summers said:
Any pointers would be great.
Click to expand...
The code based on https://www.access-programmers.co.uk/forums/threads/what-am-i-doing-wrong-here.322453/#post-1818137
Try:
Code: 
Private Sub Form_Current()
    FormCurrentSetColor Me!Position1QrySubform.Form!Type, Me.Btn1
    FormCurrentSetColor Me!Position2QrySubform.Form!Type, Me.Btn2
    FormCurrentSetColor Me!Position3QrySubform.Form!Type, Me.Btn3
    FormCurrentSetColor Me!Position4QrySubform.Form!Type, Me.Btn4
    FormCurrentSetColor Me!Position5QrySubform.Form!Type, Me.Btn5
    FormCurrentSetColor Me!Position6QrySubform.Form!Type, Me.Btn6
End Sub
Private Sub FormCurrentSetColor(ctrl As Control, btn As Control)
    ctrl.SetFocus
    Select Case ctrl.Value
        Case "Foam":          btn.BackColor = RGB(255, 228, 181)
        Case "Water":         btn.BackColor = RGB(255, 0, 0)
        Case "Powder":        btn.BackColor = RGB(30, 144, 255)
        Case "CO2":           btn.BackColor = RGB(0, 0, 0)
        Case "Wet Chemical":  btn.BackColor = RGB(0, 201, 87)
    End Select
End Sub
 
SHANEMAC51 said:
Private Sub Form_Current()
Click to expand...
And there are my "two cents" :)
Code: 
Private Sub Form_Current()
Dim iVal%, objCtrl As Control, objBtn As Control
    For iVal = 1 To 6
        Set objCtrl = Me.Controls("Position" & iVal & "QrySubform").Form!Type
        Set objBtn = Me.Controls("Btn" & iVal)
      
        objCtrl.SetFocus
        Select Case objCtrl.Value
            Case "Foam":          objBtn.BackColor = RGB(255, 228, 181)
            Case "Water":         objBtn.BackColor = RGB(255, 0, 0)
            Case "Powder":        objBtn.BackColor = RGB(30, 144, 255)
            Case "CO2":           objBtn.BackColor = RGB(0, 0, 0)
            Case "Wet Chemical":  objBtn.BackColor = RGB(0, 201, 87)
        End Select
    Next iVal
    Set objCtrl = Nothing
    Set objBtn = Nothing
End Sub
 
Last edited:
I vote for Eugine's first offering. It is code that a novice can understand and apply in future situations. The second version is "slicker" but not more efficient. With only 6 options, repeating the call code 6 times isn't terrible. If there were many more buttons, I would go with the second option even though it is more difficult to understand, There is a point where you would want to switch from repetitive code to the loop.

PS: Sam, most of us are self taught as far as VBA goes. Some of us programmed in a different environment prior to Access and may have had professional training there but there really are not a lot of actual colleges teaching VBA these days even in their evening classes. It is just not fashionable.

Bookmark this link:
docs.microsoft.com

Functions (category list)

Office VBA reference topic
docs.microsoft.com
Spend some time familiarizing yourself with the internal VBA functions so something may ring a bell when you need to do something and you might remember a function that will do the job.

If you like books, the most helpful book I found in the early days is the Access Cookbook. Published by O'Reilly and written by Ken Getz, Paul Litwin & Andy Baron. I think version 2 is the most recent version. Don't worry that it is almost 20 years old. VBA hasn't changed in all that time so the code is still valid. The pictures will look a little odd but still be recognizable. Some of the "recipes" are no longer needed because MS built the functionality into Access but if you can learn by example, this book is the best choice out there.
 
Last edited:
Myself, I would have made a function in a general module:


Code: 
Public Function ExtingColor( stEType As String) As Long
Dim lClr As Long
    Select Case stEType
        Case "Foam"
            lClr = RGB(255,228,181)
        Case "Water"
            lClr = RGB(255,0,0)
        Case "Powder"
            lClr = RGB(30,144,255)
        Case "CO2"
            lClr = RGB(0,0,0)
        Case "Wet Chemical"
            lClr = RGB(0,201,87)
        Case Else
            lClr = RGB(128,128,128)        'this is mid-grey as an erroneous type
            MsgBox "Invalid Extinguisher Type <" & stEType & ">", vbOKOnly, "Invalid ETYPE"
    End Select
    ExtingColor = IClr
End Function

Then use this with:

Code: 
...
Me.Btn4.BackColor = ExtingColor( Me!Position4QrySubform.Form!Type )
Me.Btn5.BackColor = ExtingColor( Me!Position5QrySubform.Form!Type )
Me.Btn6.BackColor = ExtingColor( Me!Position6QrySubform.Form!Type )
...

I'll add the following comment: Using a control name of "Type" on that subform is not a good choice because "Type" is one of those reserved words that can confuse Access terribly. I used EType in my sample code because I know that those are the five major types of fire extinguisher contents. (My dad was a fire fighter and had to study that stuff, and I used to read some of it.)

The reason your code offering in post #1 of this thread didn't work was that your IF ladder took in ALL SIX positions as part of a single IF, when in fact each separate position needed its own separate (but shorter) IF ladder.
 
Wow everybody! And thank you SO much for all your advice and replies.

I am taking it all on board.

It is now working after using the case statement.

I did also look at using a function.

I just need to refine it now and look at all your options but the main thing is that it is working for this stage which is a great breakthrough but without your help i would not have managed it and i hate giving up.
 
You must log in or register to reply here.

Similar threads

L
Forecolor on or off
Replies
7
Views
779
tecrms
T
E
how to highlight and set color and font for selected ListBox item
Replies
2
Views
1,749
MajP
MajP
E
Search Button Not Finding Record and populating the text boxes on form.
Replies
13
Views
1,036
Pat Hartman
P
Sam Summers
Modal form not closing after code has run
Replies
8
Views
959
gemma-the-husky
gemma-the-husky
Sam Summers
Solved Lost!!!
Replies
1
Views
512
Sam Summers
Sam Summers

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom