 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / December 2005ADP Help
Hello, This is my first attempt to try and migrate an existing Access database (a standard Access database mdb file) to an Access Project adp file. All data transfer, table, view, stored procedure and function creation aside, I'm having problems with a form that contains two combo boxes (an employee list and a shift list) and one check box (terminated). The Shift combo box used a value list and I used the query defined below fill in the Employee combo box. SELECT EmployeeID, LastName & [, ] & FirstName & [ ] & MiddleInt AS Employee FROM tbl_Employee WHERE Terminated = [Forms]![frm_EmployeeSelector]![chk_Terminated] AND Shift = [Forms]![frm_EmployeeSelector]![cbo_Shifts] ORDER BY LastName & [, ] & FirstName & [ ] & MiddleInt ASC What I get is an error stating that it doesn't like the "!" signs and basically I need to re-write my code to use the Input Parameters. I do not have any Row Source assigned to the form, I don't need any to be or at least I didn't with the mdb format. All I'm trying to do here is to filter out all unwanted employeesloaded in. I want to be able to display in the Employee combo box, all employees for 1st shift (Shift combo box set to 1), that are not terminated (the Terminated check box not checked). Can someone help me here? Thanks in advance, Randel Bjorkquist Randel Assume the name of your employee pick list form field is cbxEmployees. Add this code to OnCurrent event handler for the form (the SQL is standard MSSQL): Dim strEmplSource As String strEmplSource = "SELECT " & _ " EmployeeID, " & _ " LastName + ', ' + FirstName + ' ' + MiddleInit AS Employee " & _ " FROM tbl_Employee " & _ " WHERE Terminated = " & [Forms]![frm_EmployeeSelector]![chk_Terminated] & _ " AND Shift = " & [Forms]![frm_EmployeeSelector]![cbo_Shifts] & ";" cbxEmployees.RowSource = strEmplSource If either of these fields contain non-numeric data, simply add the apostrophes to the constant, e.g. " AND Shift = '" & [Forms]![frm_EmployeeSelector]![cbo_Shifts] & "';" HTH, Joe De Moor Hey Joe, Thanks for the help. You put me onto the right path. I'm actually ended up putting the code in the OnEnter event of the Combo Box. I am now just starting to run into more issues with how my old queries were build to how they are now on the SQL Server. An example of what I mean is this. I have a parameter query that uses a Start and Stop date from the main form. I use that query as a base query for almost all of my reports. Access, if the query was written correct as shown below in the "WHERE" clause, would automatically fill in the parameters, but now I'll have to figure out a better/different way of doing this. If you have any ideas, I'll be more then happy to hear them. WHERE [tbl_Employee].[Terminated] = [Forms]![frm_EmployeeSelector]![chk_Terminated] AND [tbl_Employee].[Shift] = [Forms]![frm_EmployeeSelector]![cbo_Shifts] I hope you understand and again thank you for your help, Randel Bjorkquist > Randel > [quoted text clipped - 22 lines] > HTH, > Joe De Moor If you are using parameterized queries for your reports, add the following to the input parameters property: @chk_Terminated datetime = Forms!frm_EmployeeSelector!chk_Terminated, @cbo_Shifts datetime = Forms!frm_EmployeeSelector!cbo_Shifts Replace the comma "," with whatever is good if you have set up the list separator to something else in the regional settings of your computer (this is often the semi-comma). You can also set up a Server Filter but I don't use them personally; however, it might be a good solution in your case.  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Hey Joe, > [quoted text clipped - 47 lines] >> HTH, >> Joe De Moor Hello Sylvain, Thanks for the information; I'll give it a try. However, I'm still a little bit confused here. Just to help clarify something here, I think I may need to give you a bit more information. The form I've been talking about has no data source assigned to it. The form is only used to create a simple employee selection form, selecting a single employee from the combo box, "cbo_Employee". I was using the check box "chk_Terminated" and combo box "cbo_Shifts" to filter down the number of employee names that were displayed/listed. Back to you suggestion. When I've try referencing a control on a from, like "Forms!frm_EmployeeSelector!chk_Terminated", I get an error saying something like the SQL statement can not understand/contain the "!" and that I should set the parameter values another way. But my question is this, if all the SQL statements are on the SQL Server, how would the SQL statement know how to resolve the form referencing? How would it know what "Forms!frm_EmployeeSelector!chk_Terminated" is? Does that make sense? I'm looking forward to your reply, Randel > If you are using parameterized queries for your reports, add the following > to the input parameters property: [quoted text clipped - 60 lines] >>> HTH, >>> Joe De Moor You're right, the the SQL-Server doesn't know anything about « Forms!frm_EmployeeSelector!chk_Terminated ». This is to be used on the client side only, inside the property "Input Parameters" of a form or report and in association with a SQL-Server parameter for a stored procedure: Me.InputParameters = "@ComboDate1 datetime = Forms!f_Rp!ComboDate1, ..... " And the stored procedure would be something like: CREATE PROCEDURE dbo.MyStoredProcedure (@ComboDate1 datetime) AS Select * from A where A.date1 = @ComboDate1 GO You cannot use ADP if you don't know T-SQL. Besides SP, you can also use Views and User Defined Functions (UDF) but you cannot pass them parameters from ADP; so you must create the whole string as the record source (either literaly or using the operator &), for example: Me.RecordSource = "Select * from A where A.Date = '2005/01/01' " or: Me.RecordSource = "Select * from MyUDF ('2005/01/01') " or: Me.RecordSource = "exec dbo.MyStoredProcedure '2005/01/01' " or: Me.RecordSource = "exec dbo.MyStoredProcedure '" & MyDate & "' " SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Hello Sylvain, > [quoted text clipped - 85 lines] >>>> HTH, >>>> Joe De Moor Sylvain, Okay, I don't think you have said anything that surprises me or that I don't understand. But I still have a question. Lets say I have two queries as defined below ------------------------------------------------------------- Query 1 (Saved as "Query1") SELECT ALL FROM tbl_Order WHERE tbl_Order.OrderDate BETWEEN Forms!frm_OrderLookup!dtp_StartDate AND Forms!frm_OrderLookup!dtp_EndDate ------------------------------------------------------------- Query 2 (Saved as "Query2") SELECT tbl_Customer.CustomerName, tbl_Order.OrderDate FROM tbl_Customer INNER JOIN Query1 ON tbl_Customer.CustomerID = Query1.OrderDate ------------------------------------------------------------- As you can Query2 uses Query1. How does the to parameters of Query1 get set? I know this is an extremely easy query, but its the concept that I just cant seem to graps. I look forward to hearing back from you soon, Randel > You're right, the the SQL-Server doesn't know anything about « > Forms!frm_EmployeeSelector!chk_Terminated ». This is to be used on the [quoted text clipped - 118 lines] >>>>> HTH, >>>>> Joe De Moor Randel > As you can Query2 uses Query1. How does the to parameters of Query1 get > set? You can use Query1 as a subquery in Query2 making a single query or, if Query1 is used in a number of other queries, you can setup a parameter table (a table with a row that holds only your selection criteria) and set up a View that accesses that table for its criteria. > FROM > tbl_Customer > INNER JOIN Query1 > ON tbl_Customer.CustomerID = Query1.OrderDate I don't think you intended to JOIN ON CustomerID = OrderDate, huh? (hehe) Joe Hey Joe, Yep, you are correct, I think it would be hard to tie those to attributes together. The weekend is just about here, so I'll look into this a bit next week. But thanks for the information. Randel > Randel > [quoted text clipped - 15 lines] > > Joe Well, the location for setting the record source and refreshing the queries when necessary will depends heavily on the overall design of the form and the method that you will use for passing the parameters. If you are using the InputParameters method, there is nothing to be do when the opening the form but you will have to requery the second combobox when the first value for the first one will change. This is usually done on the OnChange event of the first combobox: Private Sub Combo1_AfterUpdate() Me.Combo2.Requery Call Combo2_AfterUpdate End Sub If you building the query string yourself using the operator &, then you don't have to make a call to the Requery method as the control is automatically refreshed when its data source is changed. Notice that the name of the data source for controls is RowSource and not RecordSource as it is for forms: Private Sub Combo1_AfterUpdate() Me.Combo2.RowSource = "exec storedprocedure1 " & Combo1.Value & ....... Call Combo2_AfterUpdate End Sub Notice that the new value of the first combobox is used to change the row source of the second combobox and then its AfterUpdate event is called. If you also want to build the the record source string for the form instead of using the InputParameters property, then this is usually done in the OnOpen event of the form or in the AfterUpdate event of the last ComboBox to be called. If you have subforms that you also want to change, then usually you will do it in the OnCurrent event of the main form. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: http://cerbermail.com/?QugbLEWINF > Sylvain, > [quoted text clipped - 164 lines] >>>>>> HTH, >>>>>> Joe De Moor Sylvain, Thanks for the information. I think I might be starting to understand. But the weekend is just about here, and my work has me punching a clock, so I'll look at it some more next week. Thanks again, Randel > Well, the location for setting the record source and refreshing the > queries when necessary will depends heavily on the overall design of the [quoted text clipped - 207 lines] >>>>>>> HTH, >>>>>>> Joe De Moor i disagree with sylvain ADP is a tool to use to learn TSQL. I mean-- are you going to learn TSQL by using MDB? ADP is the right tool for the right job. I mean-- it helps you to write TSQL.. you can download SQL Server Books Online for a full reference.. it's a great book; by far the most read book in the industry lol i just love havnig a tool that comes with office; i mean-- in the oracle world; you have to either buy toad and / or buy oracle forms and oracle reports. ADP is just an awesome platform. Randel > I'm actually ended up > putting the code in the OnEnter event of the Combo Box. If that works for you, great. I choose OnCurrent so that it only happens once per time the content of form's underlying row changes. OnEnter would rebuild every time you use the pick list. There are obviously pros and cons to each way, but I find with ADO, you have to think about performance. > I have a parameter query that uses a > Start and Stop date from the main form. I use that query as a base query > for almost all of my reports. Access, if the query was written correct as > shown below in the "WHERE" clause, would automatically fill in the > parameters, but now I'll have to figure out a better/different way of doing > this. If you have any ideas, I'll be more then happy to hear them. Sylvain's approach (see his post in this thread) is the most straight-forward way. However, since your query is used so extensively, you might also consider using a stored procedure, especially if the criteria were always on the same few columns. The ADO help has good write-ups on setting up parameters that way. Joe i still believe that you can do all of this without any inputParameters; at least i've done it this way before. a) if your sproc is lookig for parameter @PLU it will auto-resolve to a control named PLU b) if your sproc is looking for a parameter @txtPLU it will auto resolve to a control named txtPLU you get the picture? i hope that is still the case; i haven't had to do anything along those lines in a while i only write sprocs and all that anymore -Aaron Joe, I did finally understand, using the OnCurrent event, what you were trying. Unfortunately, I did not thoroughly explain what I was trying to do with this form. So just as an FYI, the form I was talking about, "frm_EmployeeSelector", is simply used to retrieve an Employee's ID, I have no data source assigned to the form. The returned ID is then used to fill "EmployeeID" parameters of any query used by an employee based report. My goal was to create a simple user interface, that did not display/list unnecessarily hundreds of employee names. This is where the "cbo_Shifts" combo box and "chk_Terminated" check box help filter down the list. Sorry about the confusion, I didn't think to add all this information and also didn't want to add unnecessary information. But again, thanks for the help. Randel > Randel > [quoted text clipped - 24 lines] > > Joe "Randel Bjorkquist" <rbjorkquist@coilcraft.com> дÈëÏûÏ¢ÐÂÎÅ:ehTt0%23S6FHA.1148@tk2msftngp13.phx.gbl... > Hello, > [quoted text clipped - 33 lines] > > Randel Bjorkquist |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.