empty field in eepoet (1 Viewer)

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
Just came back from VA. Doctor said my report was too confusing. Just made a new one but can’t get the empty fields to post a (big X). I tried with a (IF THEN) but it’s not working. What am I missing? or should I use something else? See attachment. Thanks
 

Attachments

  • DicksvitalsFromScratch.zip
    91.6 KB · Views: 98

jdraw

Super Moderator
Staff member
Local time
Today, 09:54
Joined
Jan 23, 2006
Messages
15,364
Dick,

Not sure what you are asking. Your macro wants you to go to a new record --hence empty fields.
If you go to previous records(record selector bottom left) the data is displayed.
Perhaps I've missed your key point..
 

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
Dick,

Not sure what you are asking. Your macro wants you to go to a new record --hence empty fields.
If you go to previous records(record selector bottom left) the data is displayed.
Perhaps I've missed your key point..

My autroexec opens my entry form (frmMain)
My query (qry3Months) will sort the dates my doctor wants
My report (rpt3Months) has the query as its data source.
Report does a good job. If the data source is empty for the (novlin field) the report shows nothing of course.
(see VBA on report) (on load) of report I tried getting report to show a big X when novlin field is empty with a (IF THEN)
Once I get it to do that for (novlin field), I will do the same for all other input fields.
 

June7

AWF VIP
Local time
Today, 05:54
Joined
Mar 9, 2014
Messages
5,425
Can't set Text or Value properties of control in report event.

Calculate in query or textbox on report: Nz([novlin], "X")

Why is quantifiable data in text field instead of number type?

Why do the fields not even show color or border when there is no data? Not finding any VBA or conditional formatting.
 
Last edited:

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
Can't set Text or Value properties of control in report event.

THANKS FOR ANSWERING
Calculate in query or textbox on report: Nz([novlin], "X")
I WAS EXPERIMENTING AND FOERGOT TO TAKE IT OUT.

Why is quantifiable data in text field instead of number type?
BECAUSE I READ SOMEPLACE THAT NUMBER FIELDS CAN'T BE REPLACED WITH TEXT LIKE A TEXT FIELD CAN.
WOULD THERE BE SOME ADVANTAGE IN THIS CASE TO HAVING A NUMBER FIELD? JUST WONDERING!

Why do the fields not even show color or border when there is no data? Not finding any VBA or conditional formatting.
BECAUSE I NEED THIS SOON AND DID NOT WANT TO SPEND TIME LEARNING ALL OVER AGAIN HOW TO DO CONDITIONAL FORMATTING. ITS BEEN AT LEAST 10 YEARS SINCE I HAVE DONE IT. WASN'T EVEN AWARE THAT VBA COULD FILL IN THE FIELDS AND BORDERS COULD BE COLORED WHEN NO DATA PRESENT. THIS IS THE REASON I WAS TRYING TO PUT N/A IN NULL FIELDS SO COLORS WOULD SHOW UP. ONCE I GET MY DOCTOR'S REQUEST SATISFIED I HOPE TO GO BACK AND STUDY ALL OF THIS. I SAY HOPE AS I HAVE A TRIP IN NOVEMBER TO Guadalajara, Mexico TO GET READY FOR AND A MEETING IN LA IN DECEMBER, AND A STOP
OVER WITH DOC_MAN! ONCE AGAIN, THANKS AGAIN FOR THE HELP
 

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
Just found a short term remedy to my problem. I set for each fields default value N/A. Now when I make an entry in just one or two fields the nulls still have data, and the colors still show. However, I still want to go back and study conditional formatting and VBA for situation like this. When I enter a correct data it of course overrides the N/A.
 

June7

AWF VIP
Local time
Today, 05:54
Joined
Mar 9, 2014
Messages
5,425
If you wanted to sort or filter or do arithmetic with numeric values will have issues if they are in a text type field. 15 will sort before 2 because alpha sort applies - 1 character at a time.

I still don't know why controls don't have color or border just because field is null. This does not happen in my db without VBA or CF neither of which I saw in your db.
 

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
If you wanted to sort or filter or do arithmetic with numeric values will have issues if they are in a text type field. 15 will sort before 2 because alpha sort applies - 1 character at a time.

I still don't know why controls don't have color or border just because field is null. This does not happen in my db without VBA or CF neither of which I saw in your db.

Wow! when that happen I thought it was normal. Anybody else have a clue?
 

June7

AWF VIP
Local time
Today, 05:54
Joined
Mar 9, 2014
Messages
5,425
Ooops, have to retract my earlier statement. I was looking at a form, not a report. Yes, controls with Null do not show color or borders. Dang, I've never noticed that before. Maybe because I usually set BorderStyle and BackStyle transparent or field has value.

I would still use number type fields for quantifiable data and deal with nulls in report design. Can still use an expression in query or textbox to provide substitute value.
 
Last edited:

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
Ooops, have to retract my earlier statement. I was looking at a form, not a report. Yes, controls with Null do not show color or borders. Dang, I've never noticed that before. Maybe because I usually set BorderStyle and BackStyle transparent or field has value.

I would still use number type fields for quantifiable data and deal with nulls in report design. Can still use an expression in query or textbox to provide substitute value.

29 years ago when I went into computers (forced kicking and screaming by Navy) I heard something said by a young boy that still holds true today (especially Access) and I quote, "My computers will not do what I want it to do. Only what I tell it to do".
 

isladogs

MVP / VIP
Local time
Today, 13:54
Joined
Jan 14, 2017
Messages
18,186
I was surprised by comments in this thread so just tested in a report with null values using a coloured fill and border for each field.
Also tested in a form
As you can see from the attached image, I'm not getting the same results.
Tested using A2010

EDIT:
I've just created a quick badly laid out report in Dick's db based on the same query & added coloured fills to three of the fields.
The background & border are displayed when the values are null.
However, I've no idea what makes that behaviour different in the original report
 

Attachments

  • ReportWithNulls.PNG
    ReportWithNulls.PNG
    67.7 KB · Views: 71
  • DicksvitalsFromScratch_v2.zip
    39.4 KB · Views: 68
Last edited:

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
I was surprised by comments in this thread so just tested in a report with null values using a coloured fill and border for each field.
Also tested in a form
As you can see from the attached image, I'm not getting the same results.
Tested using A2010

EDIT:
I've just created a quick badly laid out report in Dick's db based on the same query & added coloured fills to three of the fields.
The background & border are displayed when the values are null.
However, I've no idea what makes that behaviour different in the original report

I see the difference why mine didn't work and yours did. You used colour and I used color. :). Maybe I need to buy my next computer in the UK. :eek:
 

isladogs

MVP / VIP
Local time
Today, 13:54
Joined
Jan 14, 2017
Messages
18,186
LOL :D
I suggest you try and make a new report or just adapt the one I made to make it look like what you want.
I'm fairly sure there was some kind of corruption involved ... or an obscure setting on your computer.
 

June7

AWF VIP
Local time
Today, 05:54
Joined
Mar 9, 2014
Messages
5,425
Okay, so something has gone haywire with specific report. I just tested by creating a new report with same data and it works fine. So it's not an app or computer setting.

I copied existing textbox on report and bound to field with some nulls. This textbox loses border and color when Null. I create a new textbox from ribbon or field list and it always has border and color. Also, this new textbox has same height setting as existing but actually renders as a fraction taller. When I push footer section up against textboxes, bottom border is lost on new control, not old ones. Same things happen with Dick's db.

Database started life in Access 2007. No idea if this behavior exhibited in 2007.
 
Last edited:

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
LOL :D
I suggest you try and make a new report or just adapt the one I made to make it look like what you want.
I'm fairly sure there was some kind of corruption involved ... or an obscure setting on your computer.

No need to. I have a finished version. (see attachment) Originally when I saw that the report was not putting colors in nulls, and even with color, empty fields where miss leading to the eye and made it a bit more difficult to read, (and thinking that was normal) I decided to see if I could get VBA or a query to add N/A to all the nulls. When I wasn’t able, probable due to a lack of knowledge, I started looking for a different way. That’s when I came up with instead of trying to put N/A in nulls after the fact I remembered that I could put N/A in before the fact and put N/A in all the [default values] Now my doctor will not think I forgot a an entry someplace when she sees a N/A. Now how to convince my doctor that it was necessary for me to eat some of the so-called forbidden foods is another problem, but even VBA and queries can’t fix that.
 

Attachments

  • DicksvitalsVer1_1.zip
    105.8 KB · Views: 86

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
Wow! The best & the brightest and the rest of us, and Access is got us in its grips. I will bet someone in Redman, Washington is laughing there butt off.
 

isladogs

MVP / VIP
Local time
Today, 13:54
Joined
Jan 14, 2017
Messages
18,186
No worries. As long as you have a solution that works for you. ;)
 

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
Thought I had put this baby to bed. Unfortunately I caught the programing bug again.
what is the better way called to program the VBA that I have below? I need to refresh on it but can't recall the name.

Code:
Private Sub activity_GotFocus()
If Me.activity.Text = "check sugar" Then
Me.bloodSugar.Visible = True
Else
Me.bloodSugar.Visible = False

If Me.activity.Text = "lunch" Then
Me.Food.Visible = True
Else
Me.Food.Visible = False

If Me.activity.Text = "sugar/Lunch" Then
Me.bloodSugar.Visible = True
Me.Food.Visible = True
End If
End If
End If
End Sub
 

June7

AWF VIP
Local time
Today, 05:54
Joined
Mar 9, 2014
Messages
5,425
You want Value of textbox. Value is default property for data controls. I think you want the AfterUpdate event of activity.

Consider this simpler code structure.

Private Sub activity_AfterUpdate()
Me.bloodSugar.Visible = Me.activity LIKE "*sugar*"
Me.Food.Visible = Me.activity LIKE "*lunch*"
End Sub

Probably also need this code in form Current event.

Alternatively, use Conditional Formatting to enable/disable textboxes and neither event code is needed. They will still be visible but greyed out and not editable.
 

Dick7Access

Dick S
Local time
Today, 09:54
Joined
Jun 9, 2009
Messages
4,197
You want Value of textbox. Value is default property for data controls. I think you want the AfterUpdate event of activity.

Consider this simpler code structure.

Private Sub activity_AfterUpdate()
Me.bloodSugar.Visible = Me.activity LIKE "*sugar*"
Me.Food.Visible = Me.activity LIKE "*lunch*"
End Sub

Probably also need this code in form Current event.

Alternatively, use Conditional Formatting to enable/disable textboxes and neither event code is needed. They will still be visible but greyed out and not editable.

I didn't make myself clear. Its been about 10 years since I used it, and my mind is going, I just turned 77, so I can remember what its called, but its something to do with choices instead of having 15 or more if cases.
 

Users who are viewing this thread

Top Bottom