Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-01-2019, 07:38 AM   #1
stalo
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
stalo is on a distinguished road
query in a continuous form

I have a subform in continuous form. I want to run a query in each row and display the result in a combo-box or in a text box. What I did is to create a combo-box and in the "ENTER" property I use the OPENQUERY statement. It returns the result on a pop-up window and then it is displayed in all the rows of the form. I don't want the pop-up window and the result to be displayed on the combo-box of the specific row. Can anyone help on how to do this? Thanks.

stalo is offline   Reply With Quote
Old 08-01-2019, 07:49 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,024
Thanks: 13
Thanked 4,063 Times in 3,998 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: query in a continuous form

Would a domain aggregate function do the job? DLookup(), DSum(), etc.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 08-01-2019, 07:58 AM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,946
Thanks: 13
Thanked 1,531 Times in 1,457 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: query in a continuous form

If the subform does not have to be updateable, then create a totals query and join it with the main query. If the subform does need to be updateable, then use the domain function in the query. If you do it as the control source of a control, all the rows will always show the same value.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-01-2019, 08:11 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,024
Thanks: 13
Thanked 4,063 Times in 3,998 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: query in a continuous form

Quote:
Originally Posted by Pat Hartman View Post
If you do it as the control source of a control, all the rows will always show the same value.
I don't think that's necessarily the case Pat.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 08-01-2019, 11:04 AM   #5
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,000
Thanks: 20
Thanked 375 Times in 368 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: query in a continuous form

If you have an unbound control, all instances of the continuous form will display the same value. You can see this by adding an unbound textbox to your form and entering any value in it. Sometimes this is useful but I find it to be problematic mostly.
Mark_ is offline   Reply With Quote
Old 08-01-2019, 11:10 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,024
Thanks: 13
Thanked 4,063 Times in 3,998 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: query in a continuous form

Quote:
Originally Posted by Mark_ View Post
If you have an unbound control, all instances of the continuous form will display the same value. You can see this by adding an unbound textbox to your form and entering any value in it. Sometimes this is useful but I find it to be problematic mostly.
If that's directed at me, I'm well aware of that behavior. With a domain aggregate function in the control source, it will show the value appropriate to each record presuming the record is referred to in the criteria:

=DSum("Field","Table","FieldName= " & [FieldName])
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 08-01-2019, 11:14 AM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,000
Thanks: 20
Thanked 375 Times in 368 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: query in a continuous form

Paul,

It was for the OP so they can see what is happening. When I first started having fun with ACCESS it was behavior that caught me off guard.

Mark_ is offline   Reply With Quote
Old 08-01-2019, 11:20 AM   #8
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,024
Thanks: 13
Thanked 4,063 Times in 3,998 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: query in a continuous form

Gotcha, sorry.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is online now   Reply With Quote
Old 08-01-2019, 11:21 AM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,946
Thanks: 13
Thanked 1,531 Times in 1,457 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: query in a continuous form

I just tested it and amazingly, the Dlookup() does work as expected. I still wouldn't do it since using domain functions inside loops like this is very inefficient. Each row runs a completely separate query.

Out of curiosity, if MS made this work, why can't they fix the cascading combo issue? That is a very common problem.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-01-2019, 12:41 PM   #10
stalo
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
stalo is on a distinguished road
Re: query in a continuous form

Thanks for your replies. I have used the DLookup function in the Control Source property of the textbox. It gives the correct result but only for the first row. The rest of the rows are empty. Can you please let me know how to solve this problem?
stalo is offline   Reply With Quote
Old 08-01-2019, 12:47 PM   #11
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,000
Thanks: 20
Thanked 375 Times in 368 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: query in a continuous form

What is your code for the DLookup? Please remember that you need to reference controls on the form to have it work properly.
Mark_ is offline   Reply With Quote
Old 08-15-2019, 01:16 AM   #12
stalo
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
stalo is on a distinguished road
Re: query in a continuous form

Hi everyone!
Sorry for my late response.
What I did is to create a text box and in the Control Source I have put the following DLOOKUP function
=DLookUp("GROUP","FD-VISIT-GROUP","[VISIT ID] = " & [Forms]![PATIENT TABLE]![Text112])
Also I have created a button which is displayed for each row and in the "click" event i have put the same DLOOKUP function as mentioned above.
Once the form is displayed the group of the first row is shown. For the rest rows the group is displayed once the button is clicked.
The ideal would be to have all the information for all the rows displayed once the form appears.
stalo is offline   Reply With Quote
Old 08-15-2019, 09:28 AM   #13
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,946
Thanks: 13
Thanked 1,531 Times in 1,457 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: query in a continuous form

Isn't the field you are looking up in the same record you are currently on? Using the reference you have shown, it looks like you are refering to a different record in a different form.

I would have expected the expression to be:

=DLookUp("GROUP","FD-VISIT-GROUP","[VISIT ID] = " & [Text112])

And that brings up another point:
ALWAYS give your objects meaningful names. Text112 means nothing and just makes it more difficult for you and anyone who has to maintain this app when you're gone. Save yourself some brain cells (and hair) and adopt a naming standard and stick with it.

PS - if you change a control name AFTER you have used it, the change isn't propagated so you will have to manually find and change all references. So make it a habit to do it AS you create the control rather than later as an afterthought regardless of whether or not you will ever have code or a query that references this control.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-18-2019, 01:57 AM   #14
stalo
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
stalo is on a distinguished road
Re: query in a continuous form

Thanks Pat for your reply.
I have master form and a continuous form which is a subform. The information (the "GROUP") has to be displayed in the subform. This information is returned as a result of the FD-VISIT-GROUP query based on the criteria to be equal with the VISIT ID of the master form which is the [Forms]![PATIENT TABLE]![Text112].
You are right that is should use meaningful names.
As soon as the subform appears the "Group" appears in the first row. In order to have it displayed for the rest of the rows, the button next to each row has to be pressed where in the "click" event there is the DLOOKUP function. The same function is used in the "Control Source" of the required field.

stalo is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
continuous form with query exaccess Forms 5 04-07-2014 12:25 AM
Continuous subform within a continuous form - datasheet alternatives? toast Forms 2 08-10-2012 12:52 PM
Continuous Form Query louisa Forms 3 07-20-2010 04:50 AM
Continuous Form Query gary.newport Forms 1 10-27-2009 01:38 AM
Creating continuous form with query lcook1974 Forms 5 02-15-2008 06:18 AM




All times are GMT -8. The time now is 02:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World