How can I edit a query from code? (1 Viewer)

[sirkistova]

Hello,
There is an "Options" form to configure some values (prices).
Upon hitting OK, I would like to run a piece of vb code that edits a query with the updates values.
How can I edit a query from code?
(The query is the data source of a report)
Thanks
Tova

 

[Jeanette Cunningham]

If I understand your question, you want to change some values in the table/s that the query uses. To do this, use an update query.

 

[sirkistova]

Doesn't work
I get "query is too complex" when I try to open the report.
The reason I want to edit the query from code is to workaround "query is too complex".
Thanks
Tova

 

[Galaxiom]

Most people create a dynamic query and apply it to the RecordSource of the report.

 strSQL = "SELECT field1, field2 FROM tablename WHERE field1=" & somevariable & " ORDER BY field2;"
 
Me.RecordSource = strSQL

It can also be done by setting this as a static Record Source

SELECT field1, field2 FROM tablename WHERE field1= Forms!formname.controlname ORDER BY field2;

 

[Jeanette Cunningham]

Do you have a query as the record source for the report?
Are you trying to set the report's record source using code?
Post the query here for us to look at - open the query in design view, highlight the sql and copy and paste it here.
We will need to see the tables used in the query, their primary keys and foreign keys and the relationships between those tables.

 

[sirkistova]

The 2 queries:

SELECT IIf(ReciprocityCount>FG,(ReciprocityCount-FG),0) AS ReciprocityBillCount,
ReciprocityBillCount*PG AS ReciprocityCost, RegularCount*PG AS RegularCost,
(ReciprocityCost+RegularCost+ReciprocityOptCost) AS TotalCost, (TotalCost-AmountPaid) AS BalanceDue,
(AmountPaid*1) AS AmountPaidCur, *
FROM qryInvoiceSendCnts;

SELECT MEMBER.LASTNAME+', '+MEMBER.FIRSTNAME AS FullName,
(select count(SenderReciever.RecieverMemberCode) from SenderReciever where SenderReciever.Reciprocity = False and SenderReciever.SenderMemberCode = MEMBER.MemberCode) AS RegularCount,
(select count(SenderReciever.RecieverMemberCode) from SenderReciever where SenderReciever.Reciprocity = True and SenderReciever.SenderMemberCode = MEMBER.MemberCode) AS ReciprocityCount,
IIf(MEMBER.Reciprocity,RC,0) AS ReciprocityOptCost, Nz(MEMBER.AmountPaid,0) AS AmountPaid, PetekFooter
FROM MEMBER, Prices;

The 3 sizes that need to be softcoded: RC, FG, PG.

If I make them fileds in a table, the report fails - "query is too complex".
That's Y I want to rewrite the 2 queries open OK hit after RC, FG, PG change values.
Thanks.

 

[Jeanette Cunningham]

Reports don't need (and often don't like) a complicated query like the ones you posted.
Use the reports Grouping and Sorting button to set up the totals instead of doing it in the query.

You can probably also take the IIf statements out of the query and put them in calculated controls - textboxes in the report.

To let user choose the values for RC, FG and PG, use a selector form that opens before the report.
It will have dropdowns for user to choose rc, fg and pg.
After user clicks the OK button to open the report, your code puts the chosen values for rc, fg, and pg into a where clause which is used in the OpenReport method.

 

[sirkistova]

Hello,
I found out how to set a query - using CreateQueryDef .
Thanks
Tova