Problem with IF and (1 Viewer)

[toqilula]

Greetings,
im struggling with this logic since three days now. I really need your help guys, something is wrong with me.
My problem is that im trying to change a picture from table.gif to tablex.gif, if meet some criteria. please find what i did till now

Private Sub Form_Load()
Dim nCount1 As Integer
Dim nCount2 As Integer
nCount1 = DCount("Qty", "[openTables]", "[Table] = 1")
nCount2 = DCount("Qty", "[openTables]", "[Waiter] = '" & Me.userlookup.Caption & "'")
 
[COLOR=red]'this code below works, but doesent take in count '& Me.userlookup.Caption &'[/COLOR]
 
[COLOR=red]'If DCount("Qty", "[openTables]", "[Table] = 1") = 0 And DCount("Qty", "[openTables]", "[Waiter] = '" & Me.userlookup.Caption & "'") = 0 Then[/COLOR]
 
[COLOR=black]If nCount1 > 0 And nCount2 > 0 Then[/COLOR]
Me.tvl1.Picture = "tablex.gif"
Else
Me.tvl1.Picture = "table.gif"
End If
End Sub

thank you in advance

 

[clive2002]

Are you sure you want .caption

can give the value of the ncount variable and Me.userlookup.Caption

 

[toqilula]

Hi Clive2002, thank you for your reply, yes i have tried that and swiched from label to txtbox, but i get the same thin, i mean there is no error its just not doing what is suppose to do. i know i have somewhere a logic problem i just cant figure it out where.
i have created a txtbox and in control source i put both my dcounts and for
DCount("Qty", "[openTables]", "

= 1") is displaying the number
and for
DCount("Qty", "[openTables]", "[Waiter] = '" & Me.userlookup.Caption & "'")
or for
DCount("Qty", "[openTables]", "[Waiter] = '" & Me.userlookup.Value & "'")
i get the #Name?
im affraid the problem lies deeper somewhere.

any other suggestion guys?

I appreciat it

 

[Brianwarnock]

I think that you should be using Forms!formname method not the ME.

brian

 

[toqilula]

Hi,

I have tried that to

 

[vbaInet]

Since you tried it in the Control Source of a textbox, then this -->

DCount("Qty", "[openTables]", "[Waiter] = '" & Me.userlookup.Value & "'")

Should be this -->

=DCount("Qty", "[openTables]", "[Waiter] = '" & [userlookup] & "'")

Same as what Brian mentioned.

 

[toqilula]

vbaInet

AWF VIP
Hi VbaInet,

yes both of you are right, it was better to do it like you said.
I have it now like this

If DCount("Qty", "[openTables]", "

= 1 and [Waiter] = '" & [Forms]![main1]![userlookup].[Value] & "'") = 0 Then

but very strange thing happens, first time when the form opens its working fine and changing the pic from green to red, but second time its not reacting at all!!!!
should i put any requery or reload or repaint ?

this is code for onload ? should i put it on other event ?!
thank you guys for helping me

Private Sub Form_Load()
DoCmd.Maximize
'If DCount("Qty", "[openTables]", "[Table] = 1 and [Waiter] = '" & [Forms]![main1]![userlookup].[Value] & "'") = 0 Then
 
If DCount("Qty", "[openTables]", "[Table] = 1 and [Waiter] = '" & [userlookup] & "'") = 0 Then

Me.tvl1.Picture = "table.gif"
Else
Me.tvl1.Picture = "tablex.gif"
End If
End Sub

 

[vbaInet]

You mean it doesn't work when you move between records? If that's so, put the code in the On Current event of the form.

 

[toqilula]

actualy the form is closing and reopening again. i have tried to put it on almost all events, on open, on current, on active ..etc all the same. is there somewhere that saves cash or something, or maby doing a repaint or reload ?!

hmmmm i think i know what the problem is:

the password form that calles(opens) this main1 form, gives the value to the [Forms]![main1]![userlookup].[Value]. so i think its calculating before the value is assignes, so next time when its opend its for the if statement the value is 0. is ther e a way to delay , meaning first to load the form completely and after that doing the if statment.

P.S. its a single form

 

[vbaInet]

Is this a Single, Continuous or Datasheet form?

 

[toqilula]

actualy the form is closing and reopening again. i have tried to put it on almost all events, on open, on current, on active ..etc all the same. is there somewhere that saves cash or something, or maby doing a repaint or reload ?!

hmmmm i think i know what the problem is:

the password form that calles(opens) this main1 form, gives the value to the [Forms]![main1]![userlookup].[Value]. so i think its calculating before the value is assignes, so next time when its opend its for the if statement the value is 0. is ther e a way to delay , meaning first to load the form completely and after that doing the if statment.

P.S. its a single form

 

[vbaInet]

It would be easier if I see your database.

 

[toqilula]

its stupid 123 Mb file
anyway i figure it out , well partialy, now the main form loads and display the correct picture:

what i did i just put the code in the password form, the one that opens the main1 form:

Private Sub bntlogin_Click()
   
If passwordtxt = DLookup("password", "password", "shift = 'admin'") Then
        DoCmd.Close
        DoCmd.OpenForm "admin"
   ElseIf passwordtxt = DLookup("password", "password", "shift = 'kresha'") Then
        DoCmd.Close
        DoCmd.OpenForm "kresha"
   ElseIf passwordtxt = DLookup("password", "password", "shift = 'nje'") Then
        DoCmd.Close
        DoCmd.OpenForm "main1"
        [Forms]![main1]![userlookup].Value = DLookup("[user]", "password", "[ID]=2")
        If DCount("Qty", "[openTables]", "[Table] = 1 and [Waiter] = '" & [Forms]![main1]![userlookup] & "'") = 0 Then
        [Forms]![main1]![tvl1].Picture = "table.gif"
        Else
        [Forms]![main1]![tvl1].Picture = "tablex.gif"
        End If
    ElseIf passwordtxt = DLookup("password", "password", "shift = 'dy'") Then
        DoCmd.Close
        DoCmd.OpenForm "main1"
        [Forms]![main1]![userlookup].Value = DLookup("[user]", "password", "[ID]=3")
        If DCount("Qty", "[openTables]", "[Table] = 1 and [Waiter] = '" & [Forms]![main1]![userlookup] & "'") = 0 Then
        [Forms]![main1]![tvl1].Picture = "table.gif"
        Else
        [Forms]![main1]![tvl1].Picture = "tablex.gif"
        End If
    Else
        Me.passwordtxt.Value = ""
        Me.wrongpass.Visible = True
   End If
End Sub

So this works now, the problem is when I make the accde file, when opening the main form, when need to change the picture its displaying erorr msg saying that cant find tablex.gif
any sugestion on how to save the img somewhere where the form can access it ?

I appreciate a lot for your time

 

[vbaInet]

You need to the full path to the image -->

Me.tvl1.Picture = CurrentProject.Path & "\" & "tablex.gif"

By the way you will find it easier to manage if you change your IF...ELSEIF block of code to SELECT CASE block.

 

[toqilula]

This does it. its working as it should. your the best my friend.
thank you for your time.
Best Regards

 

[vbaInet]

Glad we could help!