?
Solved

Do while, loop etc

Posted on 2007-10-02
12
Medium Priority
?
2,517 Views
Last Modified: 2008-01-09
Hi there,

I am trying to adjust

Private Sub Form_Timer()

   Dim strSql As String

    strSql = " Select * from P_BA"
 
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSql, dbOpenDynaset)

Do While Not rs.EOF
   
       If ba_Stat_Bp_4.Value Is Null Then
             ba_Stat_Bp_4.Visible = False

      Else

       rs.MoveNext
       End If

Exit Do
Loop
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

So its about the:
       If ba_Stat_Bp_4.Value Is Null Then
             ba_Stat_Bp_4.Visible = False
This doesnt work. In fact I tried other code. Nothiing works. I just can not do it.
What kind of code should I use to let this go to the recordset

Erik
0
Comment
Question by:fmew
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 19996751
try
If nz(ba_Stat_Bp_4.Value,"") Then
             ba_Stat_Bp_4.Visible = False

.... etc
0
 
LVL 19

Expert Comment

by:frankytee
ID: 19996753
typo, it should be
If nz(ba_Stat_Bp_4.Value,"") = "" Then
             ba_Stat_Bp_4.Visible = False
0
 

Author Comment

by:fmew
ID: 19996790
Hi,
Problem is the code stops at If Nz(ba_Stat_Bp_4.Value, "") = "" Then
No error, just stops
So thats something in my loop?

Erik
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:fmew
ID: 19996832
Its because the first value it finds is ba_Stat_Bp_4.Value, "") = "E"
When I change the first record to E, the code runs , but makes all the  ba_Stat_Bp_4 field invisible, incluis the ones which are empty
0
 
LVL 85
ID: 19997401
What exactly are you trying to do with this code? Please tell us what the loop is supposed to do ...
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 19997405
The IS NULL sysntax applies only to objects. For normal values you can use the IsNull() function:
E.g. If IsNull(ba_Stat_Bp_4.Value) Then

You can set the visible property in a single step without an IF statement:

ba_Stat_Bp_4.Visible = not IsNull(ba_Stat_Bp_4.Value)

The loop doen't look quite right:    
<    Do While Not rs.EOF
         If ba_Stat_Bp_4.Value Is Null Then
             ba_Stat_Bp_4.Visible = False
         Else
             rs.MoveNext      ' <======= this will never execute after a null is found
         End If
         Exit Do                   ' <======= exits the loop first time through
    Loop >
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 19997415
LMSConsulting:
Yes, that was the next point, It looks like an attempt to show/hide a control on different rows of a continuous form, which won't in any case work via the visible property of the control.
0
 

Author Comment

by:fmew
ID: 19999295
Yes MikeToole,
Thats what Iam trying to do.
But it hasnt have to be a visible property, it may be something else
The field maybe Null, I can change the default to say .  So theres a value than.
The loop would work then? Still not with the visible property?


Erik
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 19999547
If this code is running in a Continuous form, then you cannot format the individual rows of the form in any manner. Formatting you apply to one "row" is applied to all rows.
0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 500 total points
ID: 20000474
Erik,

For a bound field, you can't assign a value other than the value that is in the database - that would be an update.

There is only one Format property for a control on a datasheet or continuous form and that affects all the rows. But, you can control format on a row by row basis using Conditional Formatting on any control:

In the form's design mode, select the control you want to work with and choose Format=>Conditional Formatting... from the menu. You can then add up to 3 conditions to show different formatting based on the content of the control - or event the content in other controls.
0
 

Author Comment

by:fmew
ID: 20001036
Thank you all.

Mike and LSM, I will split points. You both helped me to get things clear and done

Erik
0
 

Expert Comment

by:bhushanvinay
ID: 20067570
i dont know if i make sense

can you try your sql query to be tuned.


Select * from P_BA where ba_Stat_Bp_4 is not null
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question