What am i doing wrong here? (1 Viewer)

Sam Summers

Sam Summers

Registered User.
Local time
Today, 01:02
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
 
SHANEMAC51

SHANEMAC51

Active member
Local time
Today, 03:02
Joined
Jan 28, 2022
Messages
310
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
 
P

plog

Banishment Pending
Local time
Yesterday, 19:02
Joined
May 11, 2011
Messages
11,653
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.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:02
Joined
Oct 29, 2018
Messages
21,491
You should also indent your code, so you can easily follow/understand your nesting levels.
 
SHANEMAC51

SHANEMAC51

Active member
Local time
Today, 03:02
Joined
Jan 28, 2022
Messages
310
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
 
Eugene-LS

Eugene-LS

Registered User.
Local time
Today, 03:02
Joined
Dec 7, 2018
Messages
481
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
 
Eugene-LS

Eugene-LS

Registered User.
Local time
Today, 03:02
Joined
Dec 7, 2018
Messages
481
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:
P

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Feb 19, 2002
Messages
43,346
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:
The_Doc_Man

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:02
Joined
Feb 28, 2001
Messages
27,218
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.
 
Sam Summers

Sam Summers

Registered User.
Local time
Today, 01:02
Joined
Sep 17, 2001
Messages
939
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
582
tecrms
T
E
how to highlight and set color and font for selected ListBox item
Replies
2
Views
1,018
MajP
MajP
Sam Summers
Modal form not closing after code has run
Replies
8
Views
710
gemma-the-husky
gemma-the-husky
S
Hi. I am new to this group and I am not a profesional. I am 66 years old and a retired Bank Officer active with Retired Bank Officers community.
Replies
8
Views
323
Suresh1957
S
Sam Summers
Solved Lost!!!
Replies
1
Views
473
Sam Summers
Sam Summers

Users who are viewing this thread

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