• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2522
  • Last Modified:

Do while, loop etc

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
fmew
Asked:
fmew
  • 4
  • 3
  • 2
  • +2
2 Solutions
 
frankyteeCommented:
try
If nz(ba_Stat_Bp_4.Value,"") Then
             ba_Stat_Bp_4.Visible = False

.... etc
0
 
frankyteeCommented:
typo, it should be
If nz(ba_Stat_Bp_4.Value,"") = "" Then
             ba_Stat_Bp_4.Visible = False
0
 
fmewAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
fmewAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What exactly are you trying to do with this code? Please tell us what the loop is supposed to do ...
0
 
MikeTooleCommented:
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
 
MikeTooleCommented:
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
 
fmewAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
MikeTooleCommented:
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
 
fmewAuthor Commented:
Thank you all.

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

Erik
0
 
bhushanvinayCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now