query in a continuous form (1 Viewer)

stalo

New member
Local time
Today, 05:44
Joined
Aug 1, 2019
Messages
5
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:44
Joined
Aug 30, 2003
Messages
36,118
Would a domain aggregate function do the job? DLookup(), DSum(), etc.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 19, 2002
Messages
42,970
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.
 

Mark_

Longboard on the internet
Local time
Today, 05:44
Joined
Sep 12, 2017
Messages
2,111
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:44
Joined
Aug 30, 2003
Messages
36,118
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])
 

Mark_

Longboard on the internet
Local time
Today, 05:44
Joined
Sep 12, 2017
Messages
2,111
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:44
Joined
Aug 30, 2003
Messages
36,118
Gotcha, sorry.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 19, 2002
Messages
42,970
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.
 

stalo

New member
Local time
Today, 05:44
Joined
Aug 1, 2019
Messages
5
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?
 

Mark_

Longboard on the internet
Local time
Today, 05:44
Joined
Sep 12, 2017
Messages
2,111
What is your code for the DLookup? Please remember that you need to reference controls on the form to have it work properly.
 

stalo

New member
Local time
Today, 05:44
Joined
Aug 1, 2019
Messages
5
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:44
Joined
Feb 19, 2002
Messages
42,970
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.
 

stalo

New member
Local time
Today, 05:44
Joined
Aug 1, 2019
Messages
5
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.
 

Users who are viewing this thread

Top Bottom