Passthrough Query

I have a form in a project DB that its source is a view (csp_UserNames) that 
accepts parameters,but I would like to pass a parameter that the user types 
in the form itself (USRNAME field) and then requery the form. If I put the 
following in its source it works;
Exec csp_UserNames
This returns all values in the users table
If I put:
Exec csp_UserNames alopez
It returns user angel lopez (the login ID is alopez for this user)
But I would like to pass the value in my form (Login_frm) field (USRNAME) to 
the parameter so that when I type jsmith in the USRNAME field (afterupdate) 
I would just do a Requiery of the form.
Any ideas? 


0
Angel
10/15/2007 11:25:15 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
1123 Views

Similar Articles

[PageSpeed] 4

Try rewriting the SQL of the passthrough query once you know the user id. 
Something like:

Private Sub USRNAME_AfterUpdate()

  CurrentDb.QueryDefes("csp_UserNames").SQL = _
    "Exec csp_UserNames '" & Me.USRNAME & "'"

End Sub


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Angel G" <Nomail@please.com> wrote in message 
news:%23ypjqK4DIHA.4880@TK2MSFTNGP03.phx.gbl...
>I have a form in a project DB that its source is a view (csp_UserNames) 
>that accepts parameters,but I would like to pass a parameter that the user 
>types in the form itself (USRNAME field) and then requery the form. If I 
>put the following in its source it works;
> Exec csp_UserNames
> This returns all values in the users table
> If I put:
> Exec csp_UserNames alopez
> It returns user angel lopez (the login ID is alopez for this user)
> But I would like to pass the value in my form (Login_frm) field (USRNAME) 
> to the parameter so that when I type jsmith in the USRNAME field 
> (afterupdate) I would just do a Requiery of the form.
> Any ideas?
> 


0
Douglas
10/16/2007 12:26:21 PM
Reply:

Similar Artilces:

Funky Query Question
I'm building a new query that uses 2 inputs; a query and a table. The input query has 3 fields (a,b,c) that combine to be a unique identifier for each record. The input table has the same 3 fields (a,b,c) that make a unique identifier for each record. I'm trying to match records from each input source using all 3 keys (a,b,c), incldung a field from the table in the new record. But my new query output only includes the first match of the unique identifier (a,b,c), and doesn't pick up the rest of the matches (a,b,d - b,c,f - c,d,h - etc.) I've tried various combination...

Total field on query
Hi, I used the fomular below to sum the fields. I works fine. I need to add the table name (Student) to this fomular. Please show me how. thanks. Chi Total Score: Nz([Section 1 - Pain Intensity],0)+Nz([Section 2 - Personal Care],0)+Nz([Section 3 - Lifting],0)+Nz([Section 4 - Walking],0)+Nz([Section 5 - Sitting],0)+Nz([Section 6 - Standing],0)+Nz([Section 7 - Sleeping],0)+Nz([Section 8 - Social Life],0)+Nz([Section 9 - Traveling],0)+Nz([Section 10 - Changing Degree of Pain],0) Chi, Just include the table name at the beginning of each field in brackets, followed by an exclamation mar...

Check boxes in forms to call a query
I have around 50 queries. These queries are run on a requirement basis. For example, i have query1,query2,query3,query4,query5 If i select query1,query3 through a check box, the selected query should run. How can i create a form and call these queries by selecting through check boxes? Pls help. What kind of queries are they? Select queries or Action queries? -- Dave Hargis, Microsoft Access MVP "Senthil" wrote: > I have around 50 queries. These queries are run on a requirement basis. > For example, i have query1,query2,query3,query4,query5 > > If i select quer...

query to extract the portion of text of fields in a Table
All: I have a table have several fields that I would like to modify from its original text content to only retain its first 25 characters. How to do it in a query such that it could overwrite the fields of that table? Thanks. USE an update query and the Left function. UPDATE YourTable SET FieldA = LEFT([FieldA],25) , FieldB = Left([FieldB],25) This is a PERMANENT change. In the query grid -- Select the fields you want to modify -- Select Query: Update from the menu -- in UPDATE TO under each field you want to change type Left([Name of field],25) -- Select Query: Run from the menu. WARN...

prevent ad-hoc queries?
I think I already know the answer to this question, but is there a way to prevent users from issuing ad-hoc queries via Query Analyzer, Management Studio or some other query tool? I want their access to data to come only from my program. Thanks. You don't say what type of application you are coding but one possibility would be to use an Application role, see: http://articles.techrepublic.com.com/5100-10878_11-5068954.html?tag=rbxccnbtr1 -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent...

Running a query that totals my Columns
I am trying to run a query that will give me a total of the given fields in a given time period. For example, my form consists of several check boxes, they are represented by a 1 if checked and a 0 if unchecked in my table. How can make my query so for the month of January I can see the sum of how many times each box was checked? I have the query set to show me the specific months and subjects for each month but I can't figure out how to get the total for the month in there. Does that make sense? Should I even be doing a query for this, or is this a report function I'm look...

Querying a Linked table
Hi: I am trying to do this: SELECT * FROM MySybaseLinkedTable WHERE dtDateTimeField > (SELECT MAX(dtDateTimeField) FROM MyAccessTable) When I run this query without the subselect and manually put the date in like #5/26/2007# then the query comes back immediately. When I try to do the above it crashes Access. Is what I am trying to do possible? If not, what is the best way to get a MAX value from an Access table and pass it into the linked table query? Thanks, Kayda On 28 May 2007 21:35:36 -0700, Kayda <blairjee@gmail.com> wrote: >Hi: > >I am trying to do this: > &g...

Query filter by code
I cannot find similar answer... I am interested for correct syntax to filter query by VBA function. Query filter: Between [Table1].[ID1]+1 And [Table1].[ID1]+4 When I use function this way: Query filter: fGetFilter([Table1].[ID1]) Function fGetFilter (intID As Integer) fGetFilter = "Between " & intID+1 & " And " & intID+4 End Function Or Function fGetFilter (intID As Integer) fGetFilter = "Between [t_properties].[group]+ " & 1 & _ " And [t_properties].[group] +" & 4 End Function This does not work. How to properly use...

Active Directory Query
Guys, I have a list of users in a .csv file. The users are listed via firstName, lastName Is there anyway I can use dsquery or any other tools to grab the user's login id after it's compared to the name? Normally for a single name I would do something like dsquery user -name "Nik Test" | dsget user -samid Win2003 sp2 Thanks use for loop in a batch script or u can use Power Shell too (that will give u very good formatting option) for /F "delims=;" %i in (User_List.txt) do dsquery user -name "%i" | dsget user -samid >> C:\Temp.tx...

Query Expressions
If I have a record that contains a field that is a type-code and another field a number, is there an expression that will allow me to state if certain information is present in one field of a record, then change the quantity to a negative value? For example, in my inventory transaction table one field of each record is a transaction type that has a either an "I" or an "O" (in or out) and another field contains a positive quantity. What expression can I use (in a new field) that will change quantities to negative for all records that have "O" in the tran...

Date calculation in a Query
I have this expression in a form and it works perfect DateAdd("d",63-Weekday(FirstContact,7),FirstContact) But, where should I write it in a query? The query should show all the items that shoul be removed from the list on the calculated date, and update the "removed from the list" the field from there. Thanks Ioia Add the expression as a calculated field in the query Field: Removal: DateAdd("d",63-Weekday(FirstContact,7),FirstContact) Criteria: = #2009-11-27# Then you can apply criteria against the calculated field. Another option would be...

Queries with loops and variables (query vs. vba)
Again, I am new to access but was wondering about queries versus going to VBA. I have a sql server script (containing some loops and if statements) that I would like to run after the user enters information into a form. Does access queries allow you to do loops with variables, etc or is that when you need to start using VBA. Is there someone to find a tutorial on access queries that is more advanced then the normal select, insert, update or delete. Any information would be great. Thanks, AJ On Tue, 13 Nov 2007 19:24:00 -0800, AJ <AJ@discussions.microsoft.com> wrote: Access quer...

m_strFilter version of SQL query?
How is the following done with an m_strFilter in MFC: (RID is a double or int, '1' is a String) SELECT * FROM [dbo].[ADB] WHERE CAST(RID AS CHAR)='1' (this works in SQL) OR SELECT * FROM [dbo].[ADB] WHERE RID=1 (this works in SQL) SELECT * FROM [dbo].[ADB] WHERE RID='1' (this works in SQL) I've tried strings like any of the above but none work in mfc. Thanks. "CharlesC" <Charles@bscinforma.com> wrote in message news:eqg$%23dREIHA.3980@TK2MSFTNGP03.phx.gbl... > How is the following done with an m_strFilter in MFC: > (RID is ...

Displaying Data From Joined Queries
In the Account form, it displays the Primary Contact. Which is really the primarycontactid field. Can I display other info from the Contact on the Acount form, like Contact Job Title? Is there a way to grab data from other tables that are related/joined (in a query) in v1.2 or v3.0? James This would not appear to be possible. I have tried to get this in serveral ways. In a form you could do this using an Iframe and some aspx, but in lists I can find a way to do this. I would like to see this functionality (out of the box) pretty much especially in lists. "James" wrote: &g...

IF statement in a query....
I am fairly new to using Access and I need some assistance in working with an expression.... Working with Access 2007 I have a table that has a column (Name) with several clients names in it and I would like to write an expression that isolates Bank of America and labels all other clients as "Misc". Example: Current Return Desired Name (Column Name) Name (Column Name) BofA BofA BofA BofA BofA ...

query or link?? wont update with parent file
I have been using a foxpro query (modified) to pull in a list of part numbers with associated data. The data is set to update in the satellite spreadsheet when the business system has new part numbers added. The problem with this is that it gives us fits with vlookups (sometimes the imports are text, sometimes numbers- so it is a bit troublesome to Instead, I thought I would embed a good excel file in this bill of material worksheet, but now I find it does not update at all- I hit refresh and it will not refresh. At first I thought it was having autofilter turned on- but we seem to be ...

query relationship text --> Number 18,6 type mismatch
I need to pull a list of posted invoice with prepaid terms (sql below) using two tables and INNER JOIN [ARTrans] ON [InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER. [InvoiceHeaderHistory].Invoice is 18,6 Number [ARTrans].TRANS_NUMBER is text How can I overcome the yype mismatch? SELECT [InvoiceHeaderHistory].[CustID], [InvoiceHeaderHistory]. [CustNm], [InvoiceHeaderHistory].[Terms Desc] AS OPEN_T, [InvoiceHeaderHistory].[InvoiceDate], [ARTrans].balance FROM [InvoiceHeaderHistory] INNER JOIN [ARTrans] ON [InvoiceHeaderHistory].Invoice = [ARTrans].TRANS_NUMBER WHERE ((([...

Using a form to create dynamic Query and chart
I was hoping someone could give me a steer to whether I should use Excel or Access... I have a SQL Server database that has data that I need to query from. I am trying to build a query form, in excel or Access, that will allow the user to choose certain criterial. This criteria is the basis for a select statment and data to create a chart. I am far more familiar with Excel's object model than Access...the interesting thing is that Access has an ability to use a query as a data source whereas excel has to select the data, put on a spreadhsheet and then graph. Any thoughts on the best way...

Change an update query
I have an update query I need to make changes to but when I open the design view it onlt shows the field that is updating. Can anyone tell me how view the other fields? Are you in query design view (with the grid)? If so, you should see the table that you are updating and be able to see the fields. If you are basing the update query on another query, check the base query to see if it has the other fields in it. If the upper portion of the query is missing (tables area) then you need to drag down the section of selecting fields and setting criteria. If that does not work fo...

Problems with Multiple Rows in Query for Report
Hi I have a database that captures two distinct pieces of information One table (tblPayWeek) captures the timesheets for employees and costs, this is captured by employeeID and also by ProjectID, the project being the large job being worked on. We also have sub jobs to the large job(Project), and the sales information is captured here(tblSalesDetails) with an index field called SalesID, along with (if they record it) the hours the men have worked on a sub job. The costs for labour, materials are stored in seperate tables using the Field SalesID as the foreign key link back t...

Access form tab control query
Hi, I have a form in MS Access. This form has 3 subforms -- sf1, sf2, sf3. Is it possible to have each of these subforms displayed inside the tab control? When user clicks on tab1, user will see sf1. When user clicks on tab2, user will see sf2. When user clicks on tab3, user will see sf3. Any help or sample codes will be greatly appreciated.... Ang In news:1182741889.549843.297930@e16g2000pri.googlegroups.com, angsapurai@gmail.com <angsapurai@gmail.com> wrote: > Hi, > > I have a form in MS Access. This form has 3 subforms -- sf1, sf2, sf3. > > Is it possible to have...

Querying data
I have to create a report for the query below SELECT ClericalMetrics.PS_ID, PerformanceSupervisor.PerformanceSupervisor, ClericalMetrics.[Time Sheet Errors], Count(ClericalMetrics.[Time Sheet Errors]) AS [CountOfTime Sheet Errors], ClericalMetrics.TimeSheetErrors2, Count(ClericalMetrics.TimeSheetErrors2) AS CountOfTimeSheetErrors2, ClericalMetrics.TimeSheetErrors3, Count(ClericalMetrics.TimeSheetErrors3) AS CountOfTimeSheetErrors3, ClericalMetrics.TimeSheetErrors4, Count(ClericalMetrics.TimeSheetErrors4) AS CountOfTimeSheetErrors4 FROM ClericalMetrics LEFT JOIN PerformanceSuper...

Using Access query data lookup values
Hi, I have a data base with names and address. The street addresses are stored in two fields [AddrHouseNum] and [AddrRoadCode] the [AddRoadCode] field is stored as a three character code, for example Locust Lane is LOC. When I display the fields separately, they show correctly as i.e. "1234" and "Locust Lane". When I try to concatenate the the house number and road name into a query field I get "1234 LOC". My question is how do I get the look-up field [AddRoadCode], in this case "Locust Lane" to show, rather than the code "LOC"?...

Fonts AA Excel Report from Distribution Query Wizard
The Distribution Query Analyzer generates a font of Arial Regular 8 and I need to go to an Arial 11 on the Excel report. Can this be done? ...

Form Controls unavailable to query field expression
Hi All - This is a follow-up post to Subject: ‘Pass form parameters to query expression’ dated 1/3/08. Given: 1. Stand-alone Access 2003 mdb database. 2. Custom dialog form with two textboxes for user input (latitude & longitude). 3. The btnOK on the form sets Me.Visible = False, but does not close the form. 4. A stored Select query named based on an underlying table. 5. An expression in the stored query that uses fully qualified references to the latitude and longitude textbox controls on the form. The expression is: Delta: Abs(Abs([Latitude])-Abs(Forms!frmParameters!tbxLat))+Abs(A...