Make a Label visible when a new record is added to a table (1 Viewer)

chineloogbonna

Registered User.
Local time
Today, 10:48
Joined
Jul 30, 2018
Messages
65
Hi,
I am hoping someone can help. Not sure it can be done. Can't find anything online, so hoping you guys might know.

I have a form with an embedded report. This report populates data from the UpdateTable. I have a requery button on my report that allows me to refresh for any new updates (new records) added to the Update Table.

Is it possible to make a label visible when I hit my report requery button and the report has a new record?

Thank you.
 

Mark_

Longboard on the internet
Local time
Today, 09:48
Joined
Sep 12, 2017
Messages
2,111
Possible...

If you do a DCount of the records that match when you first come in, you can compare that to the number returned when you hit your requery button. If the number is different, unhide the label and save the current value.

As air code;
OnCurrent - aiRecCnt = DCount( *, Table, "where what=ever")

OnButtonClick
Do Requery
aiCurCnt = DCount( *, Table, "where what=ever")
IF aiCurCnt <> aiRecCnt Then
Unhide control
aiRecCnt = aiCurCnt
End IF
 

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,186
First of all using reports as forms is certainly possible but you may lose some form functionality by doing so.

As for your question, on your button click event do something like this
You could use a recordset count instead of DCount if you prefer

Code:
Private Sub MyRefreshButton_Click()

Dim I As Long, J As Long

I = DCount("*","MyTableName")
Me.Requery
J = DCount("*","MyTableName")

If J-I>0 Then
       Me.lblInfo.Caption = (J-I) record(s) added
       Me.lblInfo.Visible=True
End If

End Sub

EDIT: Looks like Mark can type faster - same idea!
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:48
Joined
Feb 28, 2001
Messages
26,999
Mark_ and Ridders gave you good answers. I'll take another approach. The issue is that if YOU added another record, Access knows because the workspace in which that event occurred is local to your system. The front-end (FE) can trap the addition by having an AfterUpdate event on the forms that can make this change and modify the label visibility accordingly.

HOWEVER, if you are dealing with a shared back-end, the problem is that the addition occurred in another workspace. FURTHER, that other workspace does not have any easy way of knowing when some other user is looking at the common BE. Even worse, the label is in the FE which is local to each user. Therefore, you need a way to know that something has happened.

Which leads to the old programmer's rule: Access can't tell you anything you didn't tell it first.

They are using a DCount method that might work just fine - but the question is where you would PUT this code. I might use the DCount but then house it in a common routine shared by placing it in a General Module. Make it relatively fast by limiting what it tries to do, but put something in EVERY Lost_Focus event to call this test. Have it return only a True/False answer and if the answer is FALSE then do nothing. But if the answer is TRUE, make your change to your label.

You ALSO need to decide when this information is no longer important. I.e. make the label go away once you have seen it. I might add an OnClick event for the label itself. (Yes, you CAN click a label with or without an associated text box.) When you click it, you can make the label go dark again.

I am not addressing how to tell if there was a change. I am addressing WHEN to tell there was a change.
 

chineloogbonna

Registered User.
Local time
Today, 10:48
Joined
Jul 30, 2018
Messages
65
Thank you so much for your reply. You def addressed an issue that was in the back of my head. Maybe you can help me with how and why I need the label, for another possible solution that may work better.

I have a _be file "ProcessingUpdates" that allows anyone to add a record(update) to the table. The table contains date, category & newupdate. The _be table is a sub report on the Main database form.

There is also combobox on the main form that allows the user to pick the category that generates the date & newupdate info on the report.

The reason I chose a report was because textbox wont allow multiple lines and I couldn't get the list box to populate with the combobox selection & category correctly.

What I'm looking for: a combobox on my main form that allows the user to select a new update by category. The category selection will generate all updates new or old in a listbox/textbox from the _be file.

However, if a new record has been added to the _be file, I would like a red label to show (a flash would be nice, but I know that's asking a lot).

I believe this can be done easily with a subform, if you can assist with getting it to populate in listbox that would be Awesome!:banghead:

Thank you in advance for any help you can give!
 

Mark_

Longboard on the internet
Local time
Today, 09:48
Joined
Sep 12, 2017
Messages
2,111
Subform is the right way to go. This would be a form in continuous mode and you can have conditional formatting.

Create your form with the fields you need.
On the form properties, set it to continuous.
Open the form and make sure it shows what you need how you need it.
Tinker with how you are selecting records unit you get ONLY the ones you want.
On the field you want to change colour, click on the conditional formatting and work through what your criteria is.

Once the form does what you want in stand alone mode, drop it on your main form.
This way you are not worrying about playing with a text box to get the text box to pretend to be a continuous subform, you simply use a continuous subform.
 

chineloogbonna

Registered User.
Local time
Today, 10:48
Joined
Jul 30, 2018
Messages
65
Thank you so much for your reply. You def addressed an issue that was in the back of my head. Maybe you can help me with how and why I need the label, for another possible solution that may work better.

I have a _be file "ProcessingUpdates" that allows anyone to add a record(update) to the table. The table contains date, category & newupdate. The _be table is a sub report on the Main database form.

There is also combobox on the main form that allows the user to pick the category that generates the date & newupdate info on the report.

The reason I chose a report was because textbox wont allow multiple lines and I couldn't get the list box to populate with the combobox selection & category correctly.

What I'm looking for: a combobox on my main form that allows the user to select a new update by category. The category selection will generate all updates new or old in a listbox/textbox from the _be file.

However, if a new record has been added to the _be file, I would like a red label to show (a flash would be nice, but I know that's asking a lot).

I believe this can be done easily with a subform, if you can assist with getting it to populate in listbox that would be Awesome!


Thank you in advance for any help you can give!
 

Mark_

Longboard on the internet
Local time
Today, 09:48
Joined
Sep 12, 2017
Messages
2,111
What coding help do you need?
 

chineloogbonna

Registered User.
Local time
Today, 10:48
Joined
Jul 30, 2018
Messages
65
I've tried this and its still not recognizing the label on another form. Main form will always be open before button is clicked.

alertBtn Form = EditProcessingUpdates
updateAlertlbl Form = Main


Private Sub alertBtn_Click()
Dim I As Long, J As Long

I = DCount("*","ProcessingUpdates
Me.Requery
J = DCount("*","ProcesingUpdates")

If J-I>0 Then
Main.updateAlertlbl.Caption = (J-I) record(s) added
Main.updateAlertlbl.Visible=True
End If
End Sib


Any assistance is greatly appreciated!​
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 12:48
Joined
Oct 17, 2012
Messages
3,276
Are you using Access or SQL Server for your back end? If SQL Server, I do know a way or two you can do this without running a count, but it may require some redesign.
 

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,186
You've made a few mistakes and I'm not convinced that this approach will work across two different forms but try this amended version:

First of all move the following line to the form declarations area at the top
Code:
Dim I As Long, J As Long

Next add this event to count the records before the button is clicked
Code:
Private Sub Form_Current()
    I = DCount("*","ProcessingUpdates[COLOR="Red"]")[/COLOR]
End Sub

Finally alter your button code as shown
Code:
Private Sub alertBtn_Click()

  J = DCount("*","Proces[COLOR="red"]s[/COLOR]ingUpdates")

  If J-I>0 Then
    [COLOR="red"] Forms![/COLOR]Main.updateAlertlbl.Caption = (J-I)[COLOR="red"] & " record(s) added"[/COLOR]
     [COLOR="red"]Forms![/COLOR]Main.updateAlertlbl.Visible=True
     End If

End Sub

Note the changes in RED
This code assumes ProcessingUpdates is a table or query name
 

isladogs

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,186
Still not working, I can't even get the Label to become visible.

Add the following Debug lines to test the output:

Code:
Private Sub Form_Current()
    I = DCount("*","ProcessingUpdates")
    Debug.Print "I = " & I
End Sub

Code:
Private Sub alertBtn_Click()

  J = DCount("*","ProcessingUpdates")
  Debug.Print "J = " & J

  If J-I>0 Then
     Forms!Main.updateAlertlbl.Caption = (J-I) & " record(s) added"
     Forms!Main.updateAlertlbl.Visible=True
     End If

End Sub

The results of the debug lines will be sent to the VBE immediate window
Let me know the results. Upload a screenshot if possible
Has a record been added by your code? In other words is J>I?
 

Users who are viewing this thread

Top Bottom