SEARCH YOUR SOLUTION HERE  

query a query in vba?

hi ,

how would i go about in vba to query a query .



this what i have

while not rsInv.eof
set rsSal = currentdb.openrecordset("select * from tblSales where field1 = '" & rsinv!field1 & "' ")
Wend

now tblSales is a linked table, so it takes a long time to go threw it if rsinv has over 500 records.(somtimes it just crashes)

i would like to set the whole recordset into rsSal before the While statment but im not sure how i would go about to making another query in that recorset.

Posted On: Monday 22nd of October 2012 05:58:35 AM Total Views:  2972
View Complete with Replies




Related Messages:

Calculate Yes/No fields in a query   (280 Views)
In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.
Replacing query   (253 Views)
hi. i are replacing queries in my tables to get the database ready to convert to SQLServer. Am having a problem replacing a query that is brings in a table for a drop down and inserting the selection in the record. during creation, the record shows a date, id, task, hours. the datatable needs date, id, task_code, task_name, task_job, hours. On "task" the drop-down allows for selection of several choices that are 3 columns wide. i can get the columns to show but only the first column currently goes in my new table. the table's columns that the selection comes from is "task_code,task_name, task_job". columns are needed as different areas use the same task code but use different jobs or names. I was told to use a insert into statement, however am having problems with it. have as follows: SQLStatement = "SELECT [Task_code], [Job_name], [Task_name] FROM tablea; " rec.Open SQLStatement, , adOpenDynamic job = rec(1) task = rec(2) rec.Close SQLStatement = " Insert into [tableb]([Job_name],[Task_name]) " & _ " values (" & job & ", " & task & " ) " rec2.Open SQLStatement, , adOpenDynamic db.Execute (SQLStatement) any point in the right direction would be greatly appreciated.
ERROR: Operation must use an updateable query.   (244 Views)
asp code: Code: error: Code: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. /site/content/register_act.asp, line 13 It's says it has something to do with permissions... I set writing & reading permissions for the db and the directory that contains it through contorl panel->Administrative Tools->Internet Information Services I have no "security tab" when I rightclick->properties the db file or it's directory even though the file system is NTFS in spite all these when I rightclick->properties the directory that contains the db I see a "read only" square marked in the general tab-attributes, when I try to unmark it seems to be unmarked but when I rightclick->properties again it's marked again... what should I do
Relationships query   (241 Views)
hi.. Im struggling to get the relationships correct within my database. (4 tables) It's for a shopping cart application Can someone please help: tblcustomerdetails: custid forename surname addline1 addline2 postcode email telno tbllogin customerid username password tblorder orderid orderdate orderqty orderprice tblproduct productid productname productprice productquantity productimage
access query - continued   (281 Views)
I sent message before I finished explaining my problem! The query to get the totals includes queries with the employee, vehicle, material, and barricade costs all sorted by activity code but for it drops all activities that don't have costs in each column--if there weren't any costs entered for material or barricades for the month, they get dropped. How do I get everything even with some fields are null
Update query help   (253 Views)
I have a table with trailer numbers currently on the yard. I also have a prompt to enter trailers as they are leaving. I would like an update query to then remove the record from the OnYard table. Is this possible
Lookup value from a table to input in a query   (253 Views)
I am building a mortgage/amortization database and I need to look up the "points paid" from another table and also the "adjustment to margin" based on the points paid and the index, "MTA" or "COFI". I have no idea how or where to write this lookup. Any suggestions Thank you! KellyJo
Automatically update value in the subform from Crossstab query   (239 Views)
, I have a main form which has 2 subforms. The main form is the name of the Student. The first subform is the data entry form. It has 2 fields. One for the Trimester - a drop down which has "1, 2, 3" listed. And the second field is the benchmarks (again drop down). Now the second subform is a more elaborate way of viewing (only for viewing purposes) which standards have been chosen for which trimester. Now this form's control source is a Crossstab query, which updates the Trimester field for each of the benchmarks for the particular student. Now my problem is I am trying to update this subform which is for viewing purposes only, automatically, so that the moment a benchmark is chosen on the other subform, the [trimester] field automatcially gets updated on this form. the benchmarks are listed on this form (view form) and a field which is the trimester field. Data entry is not allowed in this form. So the information has to get updated automatically. It does when I close and open the form, but thats not how it should work. I tried everything I thought possible (requery, refresh, a button to click) but couldn't get the form to update. Please help! I would really appreciate if you could help me find a solution.
Requery value of combo to listbox   (256 Views)
, I have a form with a text box, a combo and a listbox. The textbox stores the name of the user. The combobox gets its data from a query. I would like that when I open my form, by default the list box will show all the records of a table named Master and then, I would use the combo to requery the list by selecting another user. records under that criteria, will have to show up in the list box.
Concatenation of query text   (234 Views)
Can anyone please give me an Access 2000 VB module that will concatenate [Portia - 1 query.text1] with [Portia - 1 query.text2] and type the result in textbox [text]
Listbox selections requery subfrm   (226 Views)
I have a form that has names with a subform with information. The contacts are in a list box and the subform (in datasheet view) shows the phone number/email/etc.. of the selected contact person. I am using the form as a quick look up of a persons information. Before changing it to a list box, it was previously a combo box, and everything worked fine. i was able to requery the subform and the cooresponding info for the person would come up. However, its now a list box and i am having problems with the code to make this happen. I have a different button sending the names selected from the list box to a report...and that works fine, but i am obvisouly missing something to make it work with the subfrm requery. Below is the code i got so far. Any help would greatly be appreciated. its driving me nuts! Private Sub QuickLookup_Click() Dim varItem As Variant Dim strWhere As String strWhere = "[memberID] = " For Each varItem In Me.MemberID.ItemsSelected strWhere = strWhere & Me.MemberID.ItemData(varItem) & " OR [memberID] = " Next varItem strWhere = Left(strWhere, Len(strWhere) - 17) DoCmd.Requery "subfrmqryindividual"
Locking a table based on query results   (240 Views)
I have a query that checks an expiration date field and displays the word "Expired" in another field if applicable. This query is used to look up items, then the user would enter whether or not it is approved right in the resulting data grid thereby entering that approval into the table being used for the query. Can I do something with the query so that if the item returned has expired, a user would not be able to enter anything into the approval field Something like, if field1 = "expired" then lock the table I can't use a form, I have to just do it in the basic query or forget it. I would very much appreciate any suggestions!
Help on query   (244 Views)
i have this query Code: SELECT Group, ((Progress.DaysSpanned/(SELECT SUM(Progress.DaysSpanned) FROM Progress))*100)*((Progress.PercentCompleted*Weight)*100) AS WeightedProgress FROM Progress ORDER BY Group; it should reutrn the progress as a percent based on two weights (days spanned on task, and percent completed) the prob i have is if the percent is 100% complete it does not show in the progress, a value of less then 100% (due to the weights) how would i rewirte the query so that if percent completed is 100% it displays that, basically if the percent completed is already 100% it should ignore all other calculations and just return 100%
Form using dynamic query results   (242 Views)
hope this is a reasonable thing for semi-beginner to intermediate person to do. Our MS Access 2000 db shared on a server stores projects, categories of projects, clients, contractors doing the projects. Contractors are offered projects within their chosen categories based on their order on the list (last company who was offered a contract goes to bottom of list, like that). Currently we print out a phone list of the contractors (in desc. date order of the most recent offer accepted or refused), then we phone down the list until someone accepts. Later we enter all those offers in order on frmOffers to store the date & time stamp of that offer. Problem is, several contracts can be on offer on a given day, so when a company accepts/declines Project A, that company is still showing as high on the (paper) list for Project B. So when we have 3 people phoning they don't know they've offered multiple contracts to the same company this morning. Is it possible for a form "Offers" to be based on a query that keeps updating like that For example, you open frmProject to display details of a certain Project, cmd button to open frmOffers (continuous form, showing all offers so far with company, phone #, "accept"/"decline" & date/time stamp). Then, when you tab to the next record, can you have it show which company is next in line (even as the person beside you has updated their frmOffers in the meantime) Would you need to have a separate table storing only the most recent offer for each company If so, how would you get that updated all the time Anything you could offer to point me in the right direction would be very much appreciated!!
Open query or form   (257 Views)
access 2000 have a form with a list box some items in the list box after i click on it i want to open a query and the others i want to open a form help
Append query to different table   (249 Views)
Friends, I would like some help with a code that allows me to run an append query only if a specific field is not already stored into another table. Example. I add a new customer to my database using table1. I type his SSN. The code should check if this SSN is already present in another table (table2). If yes, so the code should skip the append query and just update any modified record included the SSN. If not, the code should run the append query. this should be done in the background while working in a form with its recors source to the table1. Can anyone help me
How to calculate cumulative values of this query fields?   (212 Views)
Hi everybody. I got a access 2000 query that lists : 1)weekno 2)year 3)project (project number ) 4)QweekylyReportHeader (project description ) 5)customer (customer that requested this project) 6)department (department number and name that implements this project) 7)Projectleader ( project leader name and number that is responsible for this project) 8)Task (Task number that is done for this project ) 9)task description (description of task ) 10)employee ( employee number who is working in this project ) 11)name (Employee name and initial and last that works for this project ) 12)hours ( number of hours employee worked in this task ) ==> i want cumulative for this 13)salary (amount of salary given to this employee) ===>i want cumulative for this I want to create another query that lists : A)cumulative value of hours worked on particular project task up that point. b)cumulative value for wages given for that project task up that point. http://i5.photobucket.com/albums/y180/method007/weeklyprojectdata2.jpg ( query output sample) The above query ONLY lists hours worked and wages gives for particular project task only during each week.But i want hours worked and wages give for particle project task up to that point in week. For example a project task might have implemented last week but not this so i want to take that in calculation as well. I be happy if some expert show me how i can calculate the cumulative value for hours worked and wages given for particular project task. Notes: - There is a possibility that during a particular week no task been implement for particular project. - One employee can work in more then one project - One employee can have more then one salary (amount) for the same project because he might get raise in salary! - Only tasks carried this week will be printed in the weekly report http://i5.photobucket.com/albums/y180/method007/constraint.jpg ( pic of database) http://i5.photobucket.com/albums/y180/method007/hourlywagesroportfinal.jpg ( query output population) http://i5.photobucket.com/albums/y180/method007/queryindesign.jpg (query in design view) query that display hourly wages of certain project during each week Code: SELECT querythisweek.weekno, querythisweek.Year, querythisweek.Project, QweeklyReportHeader.Customer, QweeklyReportHeader.Department, QweeklyReportHeader.description, QweeklyReportHeader.ProjectLeader, querythisweek.Task, dbo_Task.description, querythisweek.Employee, [lastname] & ' ' & [initials] & ' ' & [insertion] AS Name, querythisweek.hours, querythisweek.Salary FROM dbo_Task INNER JOIN ((QweeklyReportHeader INNER JOIN querythisweek ON QweeklyReportHeader.projectno = querythisweek.Project) INNER JOIN dbo_Employee ON querythisweek.Employee = dbo_Employee.employeeno) ON dbo_Task.taskcode = querythisweek.Task; code for querythis week( calcualte the salary and hours worked) Code: SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hours_worked.hours, (select a.amount * dbo_Hours_worked.hours from dbo_Hourly_wages a where dbo_Hours_worked.Employee = a.Employee and dbo_Hours_worked.Project = a.Project and a.Year * 100 + a.weekno = (select max(b.Year *100 + b.weekno) from dbo_Hourly_wages b where b.Year < dbo_Hours_worked.Year or (b.Year = dbo_Hours_worked.Year and b.weekno
hardcoded parameters in querys--can I find all?   (218 Views)
We have a tbldepartments. At this point (has been a few years)...many dept. have merged or changed names. We want to 'change' the names of some and delete or add others. Ok on the delete/add. However, in many of our queries, we have the dept. name/number hardcoded in. And trust me, there are tons and tons of them...this is a big database. Short of going through each query, is there a way to 'search/replace' either in each query or in all To update these easily suggestions
Export query to Excel   (234 Views)
, I have a button on a form and when I press it I would like this to happen: Some code will be run that makes a copy of an templatefile (Excel), puts the values that comes from my query into the document and the saves it with a name that comes from two textboxes. I would also like to be able to put in some other text in the document, like two dates that I have on my form. Does anyone know how to do this
query using 2 tables   (313 Views)
I have a query using 2 tables. However the second table is not always required and I notice that if the second table is not used then the info from just the one table is not included in the response. How can I rectify this problem