UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Text Box Used For Record Count Not Updating On Change Of Record, Access 2016    
 
   
brastedhouse
post Sep 13 2019, 05:38 PM
Post#1



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


Hi, I have two text boxes that I use to display the count of records in their respective adjoining combo boxes. Unfortunately I am saddled with two multivalue fields. I am slowing doing a rewrite and will eventually replace them. But until then I need to get a count of records. Below is the code I am trying to use to get that count to display in a text box. The counts are derived from queries that work perfectly. But the text boxes in the form do not change once the form is loaded. It gives the correct count for the field that the is displayed when the form is opened. But then the text boxes do not update as I change records. I put the code in the after update event of the combo boxes as well as the on load and on current events of the form.

CODE
Private Sub cboCallMember_AfterUpdate()
'NumberUsers is a number
Me.txtMemberCount = (DLookup("NumberUsers", "qryDeptCountMembers"))
End Sub

Private Sub cboCallEquipment_AfterUpdate()
'NumberEquip is a number
Me.txtEquipmentCount = (DLookup("NumberEquip", "qryDeptCountEquipment"))
End Sub

Private Sub Form_Load()
'NumberUsers is a number
Me.txtMemberCount = (DLookup("NumberUsers", "qryDeptCountMembers"))
'NumberEqiup is a number
Me.txtEquipmentCount = (DLookup("NumberEquip", "qryDeptCountEquipment"))
End Sub

Private Sub Form_Current()
'NumberUsers is a number
Me.txtMemberCount = (DLookup("NumberUsers", "qryDeptCountMembers"))
'NumberEqiup is a number
Me.txtEquipmentCount = (DLookup("NumberEquip", "qryDeptCountEquipment"))
End Sub


Here is the SQL of the two queries:

CODE
SELECT tblCall.callID, First(tblCall.callMember) AS SelectedUsers, Count(tblCall.callMember.Value) AS NumberUsers
FROM tblCall
GROUP BY tblCall.callID;


CODE
SELECT tblCall.callID, First(tblCall.callEquipment) AS SelectedEquip, Count(tblCall.callEquipment.Value) AS NumberEquip
FROM tblCall
GROUP BY tblCall.callID;


Can any one tell me what I am doing wrong?

Thanks, Scott
Go to the top of the page
 
brastedhouse
post Sep 13 2019, 06:15 PM
Post#2



Posts: 130
Joined: 16-March 15
From: Chautauqua, NY


I think I am missing a where clause that points to which record in the query should be used here. Hmmm. I think it mist go in the DLookup string. I need some guidance on what to use in the where clause. Thanks
This post has been edited by brastedhouse: Sep 13 2019, 06:20 PM
Go to the top of the page
 
orange999
post Sep 14 2019, 03:23 PM
Post#3



Posts: 1,987
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


I am not a fan nor experienced with multivalued fields. But your post prompted me to take a look. I'm using O365 Access.

I created a table with an mv field.


Attached File  tblTestMV_Design.PNG ( 20.92K )Number of downloads: 3



I did some searching and found this on multivalued fields via google. Then did some testing with the code provided in that link.

Output of code with my tblTestMV
CODE
affix   Count :2
              NectarOfTheGods
              SmoothAsSilk
apply   Count :3
              RefinedVinegar
              TomsSpecialOld
              UnderReview
generate    Count :1
              RefinedVinegar
refresh Count :4
              RefinedVinegar
              TabascoSauce
              TomsSpecialOld
              UnderReview



Then using your query (adapted for my table structure) created query QVMTasks
CODE
SELECT tblTestMV.ID
, First(tblTestMV.taskname) AS SelectedTask
, Count(taskItems_MV.Value) AS NumberOfItems
FROM tblTestMV
GROUP BY tblTestMV.ID;


with this output
QUOTE
ID SelectedTask NumberOfItems
1 affix 2
2 apply 3
3 generate 1
4 refresh 4


** I found that using First, Last, Min and Max as the function in the query did not change the query output???

Then created a Form with a textbox, text7 to display the Count of Items in the mv field for this record.
I renamed the label to Count.

This is the Form's Current Event
CODE
Private Sub Form_Current()
Me.Text7 = DLookup("NumberOfItems", "QMV_Tasks", "SelectedTask ='" & Me.taskname & "'")
End Sub


The WHERE part of the DLookup criteria means that the
SelectedTask from the Query is equal to Taskname of the Current record.

I think this is the answer to your WHERE clause question.


** The textbox on the form has the proper count as I move from record to record. No need to requery etc.
This post has been edited by orange999: Sep 14 2019, 03:50 PM
Attached File(s)
Attached File  Form_for_TestMV.PNG ( 10.78K )Number of downloads: 4
 

--------------------
Good luck with your project!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 05:22 PM