I am writing VBA code behind a form that used SQL SELECTS on various tables - the results of one query determine the next. I`m using the ADODB object library:
strSQL = "SELECT * FROM Table1 WHERE Field1 LIKE " & value1
objRecordSet.Open (strSQL)
Then later:
objRecordSet.Close
strSQL = "SELECT * FROM Table2 WHERE Field1 LIKE " & value2
objRecordSet.Open (strSQL)
But, when I access objRecordSet.Fields.Item(x).Value it shows the results of the first SQL statement, not the new one. I'm trying to find a way to reset the object - is there a way to do this?
The user will be creating a new project that contains a bunch of releases. The releases have standard names which are stored in a table tbl_ReleaseNames It should be noted that the list of names is not static.
The user selects which of the releases pertains to their project and then based on their selections, new records would be created in tbl_RFP_Release and then a subsequent form would open where it would display each of these newly created releases where they could enter additional information. I thought of creating an unbound checkbox associated with each of the standard names, and then checking to see if the checkbox was checked and then creating the new records followed by opening up the new form.
I have a form with a select query running as record source. Once the input for the select query is entered the form runs. Then after that there are some manipulations in the form and it is displayed. In this second phase the form must be displayed without running the same query again. How can I do this?
I have a list box that is correctly listing resources from a table (tblResource). I would like to select one of the list box entry and run a query against the tblResource to show the information for that resource in a form. I have tried to use the lstindex with the control (lstResource) in the where clause of a query to accomplish this with no success.
I have a form which lets the user enter a contract ID and then a combo box that has a list of reports which run against whichever ID is entered.
What I want to know if I put the ID's in a table if theres a way to allow the user to select multiple ID's in one go and run the report against them the range rather than 1 at time?
I'm thinking about utilising the check/tick boxes would this be possible? or another way?
I have a listbox on a form which displays data based on the value of a textbox. I requery the listbox every second or so using the timer event on the form. What I want to accomplish is that whenever the listbox changes, i.e. a new row is added to the list, a subsequent action (in my case, a beep) occurs.
I tried the afterupdate property of the listbox but that doesnt seem to work. The listbox is unbound.
I've design a form as such when i FIRST add a NEW RECORD, I want the system date to be capture into the form. I have a table calls Register. And this table has a field "DATE" which takes the value of the system date(=now). However, when i open the same record the next time, the orginal value(at the time when the record is created) of the field "DATE" will be replaced by the new system date. However, i do not want this to be happen because i want the field "DATE" to take the value of the system date when i first created the record. My question is how to prevent subsequent system date from being updated in the record whenever i open the same record? Thank you.
In a report, I have a text box with variable length text. Can Grow is set to Yes. If the box grows, I would like all the subsequent lines to be indented. Is that possible?
I am trying to create a user friendly form to enter a list of competitors entering an event.
I would like the user to pick from a list of events and that event to be used for subsequent records until the user changes it.
I have used a combo box to pick from a list of events but that needs to re-selected for every new record. I then added a second unbound combo box to give the main combo box its default value. It works but is not very user friendly.
I have a table [PickData] in a WMS (Warehouse Management System) database, that records details of each item picked. The key fields are;
[Movement] - a unique ID for a collection of items to be picked. [ToAssignRef] - the order ID [Product] - the product! [Picked] - the date/time the item was scanned [Pick Actioned] - the date/time the [ToAssignRef] was completed [Reason] - A code to indicate why an item could not be picked - AKA F3'd
An operative would be allocated a movement, connected to the [ToAssign Ref], containing a number of products to pick. If an item can not be picked for some reason the operative presses F3 and selects a reason (no stock, damaged etc). These F3'd items (other stock allowing) will later be picked on a different [Movement]. I need a query to identify the subsequent [Movements] and the associated fields following the occurance of an F3'd [ToAssignRef] & [Product].
I have a query, but it runs very slowly (perhaps due to the DB size currently 780K records). Is this the right approach, is there a better (faster) way to do this?
Code: SELECT PickData.ToAssignRef, PickData.Product, PickData.Picked FROM PickData GROUP BY PickData.ToAssignRef, PickData.Product, PickData.Picked HAVING (((DCount("[Movement]","[PickData]","([ToAssignRef]='" & [ToAssignRef] & "') AND ([Product]='" & [Product] & "')"))<>0));
Can anyone tell me how to get a running balance on a report. I know how to create a running total, by setting the "running sum" property of a text box to "Over all".
I can't however see how I can adapt this to give a running balance (as in a bank statement for example). Attempts to do so end up in failure!!
I have a form that calculates number of hours each month. So a little box for Jan, Feb, etc.
At the bottom I have another field that sums it up. Well Im using just basic + to add the fields and it only works where there is something in each box.
I can't go through and put zeros in for everyone since a 0 and a blank mean two different things. I tried Dsum and just now after searching the forum, I tried nz.
Am I overlooking a real nice function that would assume a blank entry in the sum is a zero? or perhaps some trick I could try? Also, is there anyway to store the calculated total in the associated table, so I can query it later? It already has its own specific field that up until now was hand calculated and entered manually...like the monthly hours.
So I'm trying to create a running total, to be able to graph a cumulative total. After looking around, I think I've figured out that I need to use a DSUM formula.I basically want to get a cumulative sum of profit in the 4th column.
So in my database i'm creating a live form that will open up a list of patients and when they are seen. one of the bits is a button that before they arrive says "Not Arrived" and after they arrive it says "Arrived". The code works fine:
Code: Private Sub Command68_Click() Dim C1 As String Dim C2 As String
[code]...
The problem is after clicking the button on any of the records, it changes the text on all records, to wit: I tried to attach images or links to images, but my post-count is too low. The before-click screenshot is /3QnBkgG on imgur. The after-click screenshot is 9VZWzzp on the same site.
Bear in mind I've only clicked the button (it's white, so it doesn't look like a button) on the top record.The form is set to continuous forms, not sure if that has anything to do with it.how I can get the code to run on individual records, rather than applying the first-record's results across all records?
Is there a way to add a field to a subform that preforms a net value calculation on all the records in that subform?
Fields: Record ID ItemAmt PaidAmt Need a Net field here? (not part of form; description only) 1 500.00 100.00 400.00 500.00-100.00 2 500.00 50.00 350.00 400.00-50.00
The Item Amt is referring to the main form and should be the same amt.I need the Net field to act as a running balance.
I have a form that has a combobox with table names in it. I want to execute a very complicated SQL statement but I want to put the table name (from the combobox) into the SQL statement. I'm planning on making a button to do this, but I don't know how to put the SQL statement in VBS which would use the combobox value as the table name in the SQL statement.
Sorry I am to bother you. But I was wondering whether it is possible to create a running sum in a query (from each previous record just a simple add up)?
I know how to do in report (it is described in the help function) but for a query I cannot find anything.
I have another table, Transaction, that's based off the Loan Number field on the loans table.
Fields: LoanNumber Payment Type (2 choices, payment and advance) Amount
I have a user form where users can specify the payment type, and amount, based on a certain loan number.
I'm attempting to do this:
User enters a transaction into the database. once the transaction is entered, the Principal Balance field for the specified loan number is updated.
If the payment type is advance, the query will subtract the amount from principalbalnce. if payment, the query wll add the amount to the principal balance.
Here's what I have so far.
The userform updates the transactions table with the information correctly.
My next step is to update the principal balance.
I'm thinking I could use some sort of update query, but I don't know where to begin.
HELP!!
If there is a better (normalized) way to accomplish this, I'm definitely open to ideas. Please just provide a basic example so I can understand. :o :cool:
I have a query that shows part numbers with a total qty ordered for a specified time period. This first part of the query is fine and is made into a table.
I am then trying to use this table to create a running sum with the list sorted in descending order, and ultimately break the list where the sum reaches 80% of the total qty across the whole list. I have tried the following formula and the table has been indexed in the descending order it would need to be in. The IndexAlias has also been created and made into a fixed table prior to running the 'Running Sum' query.
I have taken this formula from the microsoft link below, (Method 2), and adapted it to my query. However, although I have a decent understanding of Access I do not understand the '&' in this formula. Removing this and the double quotes on the end returns an error and leaving them in returns zeros all the way down the 'RunTot' field. If anyone can help I would appreciate it as I am stuck on this.
hey there, i am trying to create running sum in a query. i found the function for a report but not i a query
From MS Access Help: ("You can number the items in your report by using a calculated control and the RunningSum property. First, you create a text box and set its ControlSource property to =1. Then, you set the RunningSum property for the text box. If you want the numbering to start over for each group, set the property to Over Group. If you want to accumulate a running sum for the entire report, set the property to Over All")
my overall goal is to number each Employees assignments that they have. That is Employee1 may be associated to 2 assignments, thus resulting in employee1, (assignment number) 1, employee1, (assignment number) 2, and Employee2 may have 4 assignments, employee2, (assignmetn number)1, employee2, (assignmetn number)2,employee2, (assignmetn number)3, employee2, (assignmetn number)4.
I have a small database application which is split in fe and be. Its running fine and i have been making changes to the front end regularly and replacing the file on server successfully with no problems, But today i edited some forms and reports and afterwards i ran it on a client in access runtime produced the on click event error ( which is on log in form which is a startup form ). Its working fine in full version of access even on the server but couldn't log in because the clicking on login button display the error. See attached image.
It is a strange effect I get when switching among records.
In a form I have a TAB Control with some pages, when I open the form everything is ok:
But, when switching continuosly among records, the font becomes strange, looking like being bold.
It seems to happen only when using TAB control or, if you prefer, only on every TAB control's page except for the first one. Tried on different pc with different resolution.
