I am using Office/Access 2003. I have created a report that functions fine within access. The report is based on a query requesting the users Representative ID. It functions as expected. We then creayed a web page using the same query. Again this works quite well. The user clicks on the link - Access pops up the criteria dialog asking for the rep id. What our problem is that we are trying to determine where we can put the Rep ID in the page so it will automatically fill in the Criteria Dialog. We see no place in the access page where the criteria can passed. We have the criteria because they use it when they sign in to the sight. I can pass it as a hidden field, an application variable, or even a server variable but how do I get the web page to see it and stuff it into the criteria dialog.
Frustrated in Tucson! Thanks for your help in advance
I have recently started using queries to base my forms on. Even for basic forms i am using the query as the record source.
If for example i have a standard query with no criteria e.g. a employee form. How would i then use that query to create a report that filters for example a list of employees for a certain company?
Would i have to create a seperate query to base the report on? because the form when opened will always filter that criteria?
If this is the case, is there a way to keep a query set up with no criteria but when the object being used is opened some code will pass to the query the criteria e.g. a combo box on a form list all the companies and when one is selected it passes this to the query e.g. without the criteria already being in the query?
I have a combo box in a form which allows users to select a Client Group. One of the choices in the combo box is ALL.
I have some code in a STEP1 query that says SELECT IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]) AS Expr1 FROM MPI_PERSON GROUP BY IIf(Forms![Date Picker].[Client Group]="ALL","'SEDP' Or 'LD' Or 'MH'",Forms![Date Picker].[Client Group]);
I have a STEP2 query to select all records on the MPI_PERSON table and do some slight processing.
Now, in a STEP3 query, I want to select all records from the STEP2 query where the field [Client Group] satisfies the STEP1 criteria. If the Client Group chosen in the Combo box is one of the existing categories i.e. SEDP or MD or LH, it works but where the ALL has been entered, it is returning no records in the final query.
I have tried pasting the results of the STEP1 query into the criteria box as a test - and in that case it does return all records I want. What do I need to do to get the criteria recognised in the QBE box - do I need to use Eval or something?
Here is the SQL for the final query SELECT [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) AS Expr2 FROM [Find all Clients STEP 2], [Find all Clients STEP 1] GROUP BY [Find all Clients STEP 2].[Paris ID], [Find all Clients STEP 2].DOB, [Find all Clients STEP 2].[DOB Estimated], [Find all Clients STEP 2].Gender, [Find all Clients STEP 2].[Status From Date], [Find all Clients STEP 2].[Status To Date], [Find all Clients STEP 2].STS_MAIN, [Find all Clients STEP 2].Title, [Find all Clients STEP 2].Name, [Find all Clients STEP 2].NAM_FROM, [Find all Clients STEP 2].NAM_TO, [Find all Clients STEP 2].[Client Group], [Find all Clients STEP 1].Expr1, Len([Expr1]) HAVING ((([Find all Clients STEP 2].[Client Group])=[Find all Clients STEP 1].[Expr1]));
Hope there isn't too much "Social Services speak" in there - I can also strip down non-crucial fields if it makes it easier to follow what I'm on about.
i have a query in design view to which i'd like to pass a criteria value for one field... I have some routine in VBA which creates a string as below:
"SupplierName1" And "SupplierName2" And "SupplierName3"
I am passing this to a textbox on the form exactly as shown above.
I would now like to use this value as criteria input for the query. However, it seems that Access doesn't recognise this....if I write just SupplierName1 in the textbox, the query works fine but as soon as I put in the quotes or else more than one supplier name, it does not work...
any ideas how can i resolve this?
Just FYI, the string of names of suppliers is being generated through string manipulation from a listbox using a value list.
Not sure which forum this was under, but I figure Access might be it
I have a stored procedure already written which works fine, if I supply the criteria to it before or at manual execution. I want it to use a field on a form in an Access Data Project as it's criteria (as a form is built off the results of the procedure). I can't find any documentation on how to pass criteria to a stored procedure for use in SELECT WHERE statements.
Can somebody point me in the right direction? Here is my stored procedure:
When i execute this manually I get the dialog prompt to enter the value for @parHomePhone, which is what I want to automatically pull from txtPhone on the frmSearch form.
I'm trying to pass values from a combo box to the criteria for a select query. The first 2 columns of the lstCompany combo box are Serial Number and Company Name. I have tried a number of different ways to no avail. Anyone know?
I have an append query that contains a parameter [PTIdent] under the field named PTID. I would like to be able to set the parameter through VBA from a control on a form eg Form!PTID
I use the following code as normal Private Sub Command19_Click() On Error GoTo Err_Command19_Click
Is there a way within this code to do this? I realise I could set the form criteria within the query itself. However I wanted to be able to use this query from multiple forms.
IIf([Forms]![FrmUserSelection]![PricingType]=3,1 Or 2,[Forms]![FrmUserSelection]![PricingType])
My field is Pricing Type and holds records 1 or 2..I would like to pass value from my group option from a form.It works fine if group option has a value 1 or 2 but it displays no records if option value is 3 What is wrong with my code.
I need to pass values of my two check boxes on my Form
Check box US and Check Box Canada if both checked the criteria would be "UD", "ud',"b","B","us","US","CD","cd" if Canada check box checked criteria would be "cd","CD" if US only checked criteria would be UD", "ud',"b","B","us","US"
how to write the criteria statement on the query?this probably would be a complicated iff statement?
My problem is as follows, i have created a report that calculates the total volume of FSC Materials. The user picks two dates from Calender controls that the report will range from. However the needs have now changed and i am required to make the report filter further based on user input, the problem i'm facing is that i cannot figure out a way to pass values from different variables to the report separate from another here is the code i would usually use to pass data to a query/report:
Code: Private Sub MonthlyFSC_Click() Msg = MsgBox("Select the Start and Finish Dates you wish to Query.", , "Start / Finish") Start = adhDoCalendar() Finish = adhDoCalendar()
[Code] ....
However i am now trying to do this, but it gives me an error as it is trying to pass the values to one field:
It is performing incorrectly within the case select and passing the wrong criteria, as it will only display results that meet the default values' criteria. However the date criteria is not be passed either.
I need to pass a criteria to a query from my option group control to my query.
It contains three options 1,2 and 3.
If option 3 then Pricing Type 1 and 2
How do I make the code below working?
IIf([Forms]![FrmUserSelection]![PricingType] Like 3,([dbo_AGPricingDiscounts].[PricingType])="2" Or ([dbo_AGPricingDiscounts].[PricingType])="1",",[Forms]![FrmUserSelection]![PricingType]")
I am trying to pass parameters to my qury thru my combo selection. I keep getting this error "Data type mismatch criteria expression", does anyone have an idea why? WHERE (((fShiftWorked([tblTimeLog].[timeStart])=[Forms]![frmOperatorWorkDone]![cboShift] Or IsNull([Forms]![frmOperatorWorkDone]![cboShift]))=True));
I have spent so much time onthis already and i am sick of it :mad:
In my accounting database, I have a form for the requisition and a subform with the line items of the order. When a user has completed their changes, they click a button which closes the form and changes a value in the main table (as in the requisition status is now "submitted" or "approved"). Now I'd like to pass that status value to the line items in the sub-form (based on a different table, of course).
I have another subform that displays the requisition record id, the line item record id, and the status for the line item. In the On Current field of that subform, I pass the value from the main form to the subform. The subform is requeried when the user pushes the button that changes the requisition's status.
The problem is when I have more than one line item for a requisition. The first line item always receives the requisition's status value. Any other line items are not updated. Naturally, if I scroll through the records, they refresh and their status is correctly updated. But this subform won't even be visible to the user, and I certainly don't want them to have to scroll through it anyway. Is there anyway to pass the value to each of the records instead of just the first?
Here is the code I'm using in On Current in my status subform: Me![process_status_rec_id] = Me.Parent!req_process_status_rec_id
I need to find a way of passing a value from an Access form to an .asp page...
Page1.asp is viewable on the web and asks a user for a code and password - these are checked against a database and when a match is found page2.asp is opened showing values based on SQL views which match the criteria from page1.asp.
What I want to do is be able to open page2.asp from a button in Access - the button should therefore force the opening of page2.asp in a web browser and pass to it the values from that record on the database (code and password).
What code would sit on the button to get it to do this?
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
I have written a simple module that sorts and compares two strings. Next I am trying to create an event procedure for a button that will send all the values from three selected colums to that function for comparison.
Function Check(sImp As String, sAns As String, iOpt As Integer) As Integer
Dim lImp As Long 'Imported values (unsorted) Dim lAns As Long 'Entered values (unsorted) Dim lAcnt As Long Dim lCcnt As Long Dim sAnswer As String 'Correct Dim iMini As Integer Dim sImpSort As String Dim sAnsSort As String
For lAcnt = 1 To lImp sAnswer = Mid(sImpSort, lAcnt, 1)
' option Select Case iOpt ' all Case 1 Check = 1 Exit Function
' or Case 2 For lCcnt = 1 To lAns If Mid(sAnsSort, lCcnt, 1) = sAnswer Then Check = 1 Exit Function End If Next
' and Case 3 If sImpSort = sAnsSort Then Check = 1 Exit Function End If Case Else End Select
Next
End Function
What would be the best way to send these values to the function? loop through a recordset? (Note: Not sure how many records the DB will eventually store. Lets just say 10,000 for now)
DoCmd.RunSQL "INSERT INTO " & StrTable & i & sqlstring
Next i
I am not sure how to get the table names stored in the string variables into the SQL statement. When I try the above it looks for a variable named StrTable, not StrTable1, StrTable2 etc.
I have a table on which i must work at row level, one by one. I put the table into a recordset and i scroll the table row by row from the top until the bottom. The work process - a test - is executed for each row and returns an integer. I would like to put the process into a function and simply call the function when i move to the next row.
I defined : Function TestScore(ByVal MyTable as Recordset) as Byte
As it is so slow and the results are not always correct, I wonder if the WHOLE table might be passed to the function, and not only the current row.
Does anyone has an idea on passing a single row from a recordset to a function ?
I have a bit of a dilemma in my related tables (screen shot of relationships attached).
Basically at the moment I am using a 1-1 relationship between my tables using the primary key of CYID. The CYID is populated using an auto number at the moment but the problem I have is if the tables become out of sync with the numbering.
What I would like to do is when I create a new record in tblClientDetails is create a new record in each corresponding table that is linked and then pass the CYID across to each table. Can anyone offer any assistance or give me any suggestions for better ways of doing this?
After years and years of working with Jet, I am now trying to use SQL Server backands and am having problems passing parameters to a stored procedure.
How can I build a query which where I can say "WHERE ([AField]=""" & Forms!BlarBlaretc.value & """)" as I can in access because as I understand it you can not look at front end information from the SQL backend.
Any help (and start at the beginning) would be much appreciated.
In my Access Database I have a query (lets say qry1) and in this query i have 2 fields for start and end date, which is provided by 2 Get functions.
also i have qry2 based on qry1 then qry3 based on qry2 and finally qry4(using sql in code) based on qry3, and non of these 3 queries have the start and end date fields.
now here is the problem: I am trying to set the criteria in qry4 and then open a record set on this query(qry4) to use the data that it pulls out...
strsql = "" strsql = "SELECT Sum AS AREA_TOTAL " _ & "FROM qry3 " _ & "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strsql)
but when the last line is executed I get this error message:
Runtime Error '3061': Too few parameters, expected 2.
i also tried doing this:
strsql = "" strsql = "SELECT Sum AS AREA_TOTAL " _ & "FROM qry3 " _ & "WHERE ENERGY_AREA like '" & Area & "';"
Set MyDB = CurrentDb
MyDB.QueryDefs("qry4").sql = strsql
Set rst = MyDB.OpenRecordset("qry4")
but when the last line is executed it gives me an error message saying that the query does not exist or the name is not spelled correctly. (Ps. I have created the query and the criteria does update once the Select statement is run in the code!)
again the reason for this is that the query has not been populated as the main query (qry1) needs 'strat date' and 'end date'!
Is there anyway I can pass these 2 parameters to qry4 directly using code? If there is a way then this will definitly work as i tried opening the query manually in the Query window and after I input the 2 dates in the input box the query ran successfully!
I would appreciate any help/suggestion guys, I need to sort this out quickly as i have a deadline... Cheers
