If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Need help with Query parameters by form
Works like a charm......
Ok...here is another one for you having to do with this same report. In the sorting and grouping on the report, I have the report grouped by Date, then Client, then Market. I have the keep together set to whole group for date and client. I am reading this to mean that if I select whole group that a new page to the report will start with each change in date (or client... market is set to no). This is not what is happening. Is there any way to get the report to start a new page with each change in date as well as each change in client? Thank you so much for the help with the report selection. This is a major deal for me. One of the biggest things that "they" wanted the db to be able to do. It is much appreciated. emily -- Message posted via http://www.accessmonster.com |
#12
|
|||
|
|||
Need help with Query parameters by form
Here is the code for the After Update Events. I have a total of 6 combo
boxes on this form with AfterUpdate on the first 5. Option Compare Database Private Sub ctrl_ClientSelector_AfterUpdate() Me.ctrl_CompanySelector.Requery Me.ctrl_CollectorCompanySelector.Requery Me.ctrl_CollectorNameSelector.Requery Me.ctrl_DonorNameSelector.Requery End Sub Private Sub ctrl_CollectorCompanySelector_AfterUpdate() Me.ctrl_CollectorNameSelector.Requery Me.ctrl_DonorNameSelector.Requery End Sub Private Sub ctrl_CollectorNameSelector_AfterUpdate() Me.ctrl_DonorNameSelector.Requery End Sub Private Sub ctrl_CompanySelector_AfterUpdate() Me.ctrl_CollectorCompanySelector.Requery Me.ctrl_CollectorNameSelector.Requery Me.ctrl_DonorNameSelector.Requery End Sub Private Sub ctrl_MarketSelector_AfterUpdate() Me.ctrl_ClientSelector.Requery Me.ctrl_CompanySelector.Requery Me.ctrl_CollectorCompanySelector.Requery Me.ctrl_CollectorNameSelector.Requery Me.ctrl_DonorNameSelector.Requery End Sub Will post other code and info separately. ea -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200610/1 |
#13
|
|||
|
|||
Need help with Query parameters by form
Here is On Click Event for the command buttons on the form.
CLOSE FORM Command Button Private Sub Btn_CloseForm_Click() On Error GoTo Err_Btn_CloseForm_Click DoCmd.Close Exit_Btn_CloseForm_Click: Exit Sub Err_Btn_CloseForm_Click: MsgBox Err.Description Resume Exit_Btn_CloseForm_Click End Sub OPEN UPDATE LIST Command Button Private Sub Btn_OpenUpdateList_Click() On Error GoTo Err_Btn_OpenUpdateList_Click Dim strWhere As String If Not IsNull(Me.ctrl_MarketSelector) Then strWhere = strWhere & " AND " & _ "Market = '" & Me.ctrl_MarketSelector & "'" End If If Not IsNull(Me.ctrl_ClientSelector) Then strWhere = strWhere & " AND " & _ "Client = '" & Me.ctrl_ClientSelector & "'" End If If Not IsNull(Me.ctrl_CompanySelector) Then strWhere = strWhere & " AND " & _ "Company = '" & Me.ctrl_CompanySelector & "'" End If If Not IsNull(Me.ctrl_CollectorCompanySelector) Then strWhere = strWhere & " AND " & _ "CollectorCompany = '" & Me.ctrl_CollectorCompanySelector & "'" End If If Not IsNull(Me.ctrl_CollectorNameSelector) Then strWhere = strWhere & " AND " & _ "CollectorName = '" & Me.ctrl_CollectorNameSelector & "'" End If If Not IsNull(Me.ctrl_DonorNameSelector) Then strWhere = strWhere & " AND " & _ "DonorName = '" & Me.ctrl_DonorNameSelector & "'" End If If Len(strWhere) 4 Then strWhere = Right(strWhere, Len(strWhere) - 5) DoCmd.OpenForm "Form-UpdateSearch", , , strWhere Exit_Btn_OpenUpdateList_Click: Exit Sub Err_Btn_OpenUpdateList_Click: MsgBox Err.Description Resume Exit_Btn_OpenUpdateList_Click End Sub CLEAR SELECTIONS Command Button Private Sub Btn_ClearSelections_Click() Me.ctrl_MarketSelector = Null Me.ctrl_ClientSelector = Null Me.ctrl_CompanySelector = Null Me.ctrl_CollectorCompanySelector = Null Me.ctrl_CollectorNameSelector = Null Me.ctrl_DonorNameSelector = Null End Sub -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200610/1 |
#14
|
|||
|
|||
Need help with Query parameters by form
Row Source for Combo Boxes on form.
Hopefully with all this information you can tell me which direction to go in so that I can select any combination of these 6 boxes and click my open form button. I have this open form button directed to a continuous form that will show multiple records. From here, an "update" button that will bring up the full detail of the record to make changes. Thanks again for all the help you have provided me. ea ctrl-MarketSelector SELECT DISTINCT [Table-CallLogData].Market FROM [Table-CallLogData] ORDER BY [Table-CallLogData].Market; ctrl-ClientSelector SELECT DISTINCT [Table-CallLogData].Client FROM [Table-CallLogData] WHERE ((([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl- MarketSelector])) ORDER BY [Table-CallLogData].Client; ctrl-CompanySelector SELECT DISTINCT [Table-CallLogData].Company FROM [Table-CallLogData] WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]! [ctrl-MarketSelector])) ORDER BY [Table-CallLogData].Company; ctrl-CollectorCompany SELECT DISTINCT [Table-CallLogData].CollectorCompany FROM [Table-CallLogData] WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl- MarketSelector]) AND (([Table-CallLogData].Company)=[Forms]![Form-UpdateSrch]![ctrl- CompanySelector])) ORDER BY [Table-CallLogData].CollectorCompany; ctrl-CollectorNameSelector SELECT DISTINCT [Table-CallLogData].CollectorName FROM [Table-CallLogData] WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl- MarketSelector]) AND (([Table-CallLogData].Company)=[Forms]![Form-UpdateSrch]![ctrl- CompanySelector]) AND (([Table-CallLogData].CollectorCompany)=[Forms]![Form-UpdateSrch]![ctrl- CollectorCompanySelector])) ORDER BY [Table-CallLogData].CollectorName; ctrl-DonorNameSelector SELECT DISTINCT [Table-CallLogData].DonorName FROM [Table-CallLogData] WHERE ((([Table-CallLogData].Client)=[Forms]![Form-UpdateSrch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl- MarketSelector]) AND (([Table-CallLogData].Company)=[Forms]![Form-UpdateSrch]![ctrl- CompanySelector]) AND (([Table-CallLogData].CollectorCompany)=[Forms]![Form-UpdateSrch]![ctrl- CollectorCompanySelector]) AND (([Table-CallLogData].CollectorName)=[Forms]![Form-UpdateSrch]![ctrl- CollectorNameSelector])) ORDER BY [Table-CallLogData].DonorName; -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200610/1 |
#15
|
|||
|
|||
Need help with Query parameters by form
OK... one trick is to change the the criteria in the WHERE clause of the
SQL expressions so they also work with nulls; here's the idea: where you originally had: SELECT DISTINCT [Table-CallLogData].Client FROM [Table-CallLogData] WHERE ((([Table-CallLogData].Market)=[Forms]![Form-UpdateSrch]![ctrl- MarketSelector])) ORDER BY [Table-CallLogData].Client; Change the WHERE clause to: WHERE [Table-CallLogData].Market) Like Iif(IsNull([Forms]![Form-UpdateSrch]![ctrl-MarketSelector]), "*", [Forms]![Form-UpdateSrch]![ctrl-MarketSelector]) (watch out for wrapping in the newsreader, there should be no line feed in there!) If this is too long to grasp, look at it this way: WHERE Market Like Iif(IsNull([Combo1]), "*", [Combo1]) The trick is that if nothing is selected in the first combo, so it returns null, you effectively get Market = "*" , i.e. all Markets, otherwise you get Market = [Combo1], i.e. matches. Note the use of operator Like instead of =, it won't work with the latter. Change all references to preceding combos in all SQL expressions in this fashion, and the job is done. HTH, Nikos |
#16
|
|||
|
|||
Need help with Query parameters by form
In the sorting and grouping on the report, I have the report grouped by Date, then Client, then Market. I have the keep together set to whole group for date and client. I am reading this to mean that if I select whole group that a new page to the report will start with each change in date (or client... market is set to no). Wrong reading. This means that if a group is bigger than the space available on the page it is supposed to start in (middle of page, not beginning), then it will be forced to start on a new page, so as to be in one page (provided it fits in one). Is there any way to get the report to start a new page with each change in date as well as each change in client? While in report design, click on the bar that reads Date Header or on an empty spot in the Date header section (so as to select the section), and display its properties; the one you want is Force New Page, top one on the Format tab; experiment with it and see what you get. Likewise for the other sections. HTH, Nikos P.S. Having solved one problem and moving on to another, it is advisable to start a new thread, for two reasons: (a) the subject reflects the actual question, and so helps others potentially having the same question locate it and benefit form the answers you get, and (b) it greatly increases your chances of getting many more people's attention, as it moves to the top of the heap (whereas now it seems you're stuck with me, and willing as I may be to help I might not aways have the best answer, or I might get overloaded with work and disappear for a week or two). |
#17
|
|||
|
|||
Need help with Query parameters by form
I am back again. Something isn't working right. I made the changes to the
Where clause on all my combos. Now, when I try to select any of them (other than market...which is the first in the "pecking" order) I get an "Enter Parameter Value" box. The info above the text entry area looks to be a double quote. I have tried to look at whether or not I messed up the SQL somewhere, but it all looks like it should. Here is the SQL for all the combos. I hope you have an idea of what to do to make it work because I haven't. Thanks, Em ctrl-MarketSelector SELECT DISTINCT [Table-CallLogData].Market FROM [Table-CallLogData] ORDER BY [Table-CallLogData].Market; ctrl-ClientSelector SELECT DISTINCT [Table-CallLogData].Client FROM [Table-CallLogData] WHERE (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-MarketSelector]),“*”,[Forms]![Form-UpdateSearch]![ctrl- MarketSelector])) ORDER BY [Table-CallLogData].Client; ctrl-CompanySelector SELECT DISTINCT [Table-CallLogData].Company FROM [Table-CallLogData] WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-ClientSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]! [ctrl-MarketSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector] ))) ORDER BY [Table-CallLogData].Company; ctrl-CollectorCompany SELECT DISTINCT [Table-CallLogData].CollectorCompany FROM [Table-CallLogData] WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-ClientSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]! [ctrl-MarketSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector] ) AND (([Table-CallLogData].Company) Like IIF(IsNull([Forms]![Form-UpdateSearch] ![ctrl-CompanySelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl- CompanySelector])))) ORDER BY [Table-CallLogData].CollectorCompany; ctrl-CollectorNameSelector SELECT DISTINCT [Table-CallLogData].CollectorName FROM [Table-CallLogData] WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-ClientSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]! [ctrl-MarketSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector] ) AND (([Table-CallLogData].Company) Like IIF(IsNull([Forms]![Form-UpdateSearch] ![ctrl-CompanySelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl- CompanySelector]) AND (([Table-CallLogData].CollectorCompany) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-CollectorCompanySelector]), “*”, [Forms]![Form- UpdateSearch]![ctrl-CollectorCompanySelector]))))) ORDER BY [Table-CallLogData].CollectorName; ctrl-DonorNameSelector SELECT DISTINCT [Table-CallLogData].DonorName FROM [Table-CallLogData] WHERE (([Table-CallLogData].Client) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-ClientSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl- ClientSelector]) AND (([Table-CallLogData].Market) Like IIF(IsNull([Forms]![Form-UpdateSearch]! [ctrl-MarketSelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl-MarketSelector] ) AND (([Table-CallLogData].Company) Like IIF(IsNull([Forms]![Form-UpdateSearch] ![ctrl-CompanySelector]), “*”, [Forms]![Form-UpdateSearch]![ctrl- CompanySelector]) AND (([Table-CallLogData].CollectorCompany) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-CollectorCompanySelector]), “*”, [Forms]![Form- UpdateSearch]![ctrl-CollectorCompanySelector]) AND (([Table-CallLogData].CollectorName) Like IIF(IsNull([Forms]![Form- UpdateSearch]![ctrl-CollectorNameSelector]), “*”, [Forms]![Form-UpdateSearch]! [ctrl-CollectorNameSelector])))))) ORDER BY [Table-CallLogData].DonorName; -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200610/1 |
#18
|
|||
|
|||
Need help with Query parameters by form
disregard this.....I got it to work.
Since I figured it would be a few hours until you replied (based on your previous help) I did a search on the boards for "Like Is Null" and found an old post and tried what it suggested. I changed you suggestion a bit..... made it read Select.... Where.....table.field = combo OR combo IS NULL. works like a charm now. good for me because I have a status meeting on Monday and it will be good that this is working now. Thanks so much for all your help over the past week or so. You have been a real life saver for me. Emily -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200610/1 |
|
Thread Tools | |
Display Modes | |
|
|