Field Update Using DSUM: Field Joins Are Not Working Correctly
Sep 11, 2007
I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.
The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.
Query (GetTxnVolAmtTR"):
SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id
FROM ft_payees AS p, ft_txn_summary AS t
WHERE p.payee_id=t.payee_id And p.market=t.market
GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;
Update statement (references the query above):
UPDATE tmp_ft_component AS rc
SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "")
WHERE rc.component_name='Total Revenue';
as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!
Why update query isn't working when trying to update an multivalued field. In this table, there are 647 records and only 9 of which already have a value set in the "AssociatedProject" field. More than one AssociatedProject is associated with a vendor, so this field allows for multiple entries. I've looked at all the information on update queries and updating a multivalued field, but it will only update the existing 9 records. It does not recognize the remaining ones (they have no values yet).
Here's the SQL:
UPDATE Tbl_Vendor SET Tbl_Vendor.AssociatedProject.[Value] = 3 WHERE (((Tbl_Vendor.AssociatedProject.Value) Is Null));
I'm trying to create a simlpe database that is use to store time worked for clients (see attached).
If you run the form frmClientEntry i have two issues.
1 - you will see that the title is automatically selected from the combo box. However is i try and enter the form I'm told 'You cannot add or change a record because a related record is required in table 'tblTitle'. If I select one of the entries I get rid of this. I would like the default to be selected...
2 - There is no entry in the hourly rate column field even though there is an entry in the table. How do I get my hourly rate to show? Ideally this will only be a single text box and not a comboBox as the hourly rate will be the same for every client.
If this is a table design issue please let me know and I'll post there.
I have a query based continuous form that looks up a serial number entered by the user and allows them to view the matching data. When a user enters a serial number that is not in the database I want a message box to pop up to warn them there is no match.
The code I am using makes the msgbox pop up on every entry. I have tried to put this code in a variety of form and control events with no luck. Can someone tell me what I am doing wrong?
Private Sub Form_AfterUpdate() Dim intRespone As Integer
If DCount("[packing station scan].[serial_number]", "match with last test results", _ "[drive test results].[serial_number] = ' & Forms![match with last test results].[Serial_Number] & '") < 1 _ Then intresponse = MsgBox("Stop! Serial Number has no test data!", vbYesNo, "No data found") Select Case intresponse
I have been trying to figure out why my subreport is only showing sales price for only one of the records on my subform. Everything else works as it should, but it only displays the sales price for the record which is active on the subform.Attached is the database with the subreport called rptProposalItems with the field 'Sales Price' which is experiencing the issue.
hi... firstly, i created some report based on query. then in my form i create list and combox to list down all primary key that had in query.. what i want to do is open report, based on criteria that i selected from combobox or list box... i create both cbo and list box to compare which 1 is better... but both not working. i mean it works but not show the data as i want to. it open / list all data at report..
I have a field in my DB called "Category". Users are allowed to imput any text in that field. I have a particular problem in which the LIKE command isn't working as I think it should with the limited help files of Access explanation of use. The category field has multiple entries, for example - HEQ, PBH5, PBH4, SWA, SWA2, ALL - etc. I'm trying to pull the records from that field with specifics.... in other words I may need to get All the records that have "PBH5" in them. However, if if PBH5 isn't the FIRST entry in that field the LIKE criteria doesn't work. I have tried Like "PBH5*" LIke "PBH4* etc to no avail. Any ideas? It can't be that difficult, but I cannot find anything in any book I've purchased on it, or any of the knowledge base information at Microsoft.com.
I have a report, and the "reason" field is one that can grow as needed. The following string is in the field: "MISSING IMPLEMENTATION PLAN, MISSING SERVICE LOGS FOR DATE OF SERVICE". I bolded MISSING, where the space hits the border. My standard setting for the border style is Transparent, and the space is causing the growing to not work properly. TransparentBorder.pdf shows the incorrect growing. When I change the border style to Solid, the growing works correctly, as shown in the SolidBorder.pdf file.why the growing is not working properly with the Transparent border, but does work correctly with the Solid border?
I'm a bit new to Access but have managed to build a very simple database.
I have a main table which I need to add 4 columns onto the end of, the data for which comes from 4 tables linked to it. Below is how they are linked.
Main table------> Table 1 Main table------> Table 2 Main table------> Table 3 Main table------> Table 4
All 4 relationships have a join type of 2 (Include ALL records from 'Main table' and only those records from 'Table1/2/3/4' where the joined fields are equal)
However, 3 of them pull back the correct data when I refresh the Main table, but the 4th one doesn't - which appears to be to be set up in the exact same way. It does bring back data, but it's the wrong data or in the wrong order.
For example, if the related field in the Main table is "Sarah" - It needs to bring back "Programme", but some Sarah's pull through Programme, some are blank and some are another option altogether. Table 4 has no duplicates or typos etc and I've tried deleting it, loading it in again and creating a new relationship but nothing seems to work.
I am working on developing a database that develops requests to send to another department. There are a lot of forms, macros, reports and queries. A user inputs information into a form, and then selects a command button that is attached to a macro that builds the request. Midway through the build, another form comes up to complete missing information. I was going along just fine - at the testing stage for this part of the process - when the database stopped working like it should. Some of the issues I am seeing:
1) If you open a new query and try to close it without saving it, it just keeps asking you if you want to save over and over even though you select No.
2) If you open an existing query and make a change, it closes it and saves the changes without any prompting at all.
3) Macros and forms do not work right. I have a macro that deletes the records from one table. The form for that table now shows #Deleted in all the fields instead of blanks.
If you compact/repair the database is good for one run through but then it doesn't work correctly again. I have had this issue in the past but have no clue how I break my databases! I have a general knowledge of Access, I am not really a VBA coder if I can avoid it at all costs. There must be something in the relationship between all my tables, queries, forms, etc but I have no clue even where to start to troubleshoot. HELP!!
I have a from which has a sub Form in it which uses a query of a table which is filtered, In the main form I want a sum of the data in the subform for a particular field if TRUE.
In the main form I have a text field with the Syntax =DSum("[Cost]","RepairInformation","[Labour] =.True."), but this returns an error, I have no issues for the total cost for with I have a Text Box in the subform which returns the Total cost in the main form.
What are my options to have a conditional sum for a particular condition.
I can't figure out what I have wrong in my code. I've searched all over the forums and the help files in Access. I'm sure it's something simple. Could someone please take a look?
I'm trying to get a sum of all defect quantities between a date range from a user form. Rather than summing all the defect quantities for a defect code, it's summing up the total quantites for each quantity. I'm sure that doesn't make sense so here's an example:
The domain is a query because the dates come from a different table than the defect quantities, so the query is pulling all the relevant data to be summed.
Every time i try to get info from a sub form or its table ,Things never sem to work.Is there something special you have to do for eg maybe going via the main form?I am trying to use dsum as an alternative to multiplying info in the sub form.This is the code
The formula works fine up to, and including, December. Then, for the last three months of the financial year (Jan, Feb and Mar), the running total of the 'Num' field stops and the entry for January is just January's 'Num' with the running total starting again for February and March.
Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.
I have two forms, both of which have a field called JobID (Form A is the primary Key and Form B is the linked field - Relationship: One to Many).
Form A = FrmJobs Form B = FrmPurchaseOrders
I have put a command button on Form A. When I click the button I need it to open Form B and then automatically fill the field JobID in Form B with the same value in Form A from which it was opened. I have tried the following:
Private Sub Command214_Click() Me.Refresh On Error GoTo Err_Command214_Click Dim stDocName As String Dim stLinkCriteria As String
[code]...
The above code works on my old database but not on my new database. I get the following message "Object does not support this property or method".
I have 3 peices of data that I am working with: Group number, plans and benefit elections. On my form I have fields for the group number and plan and 2 queries. When I enter a group number the first query displays a list of plan descriptions for that group. From there I can enter the plan description into the form's plan field and that runs the second query to give me a list of valid elections for that plan. This all works fine but I want to make the plan selection a little less tedious. What I would like is to be able to double click the query field housing the plan description and have it copied to the form's plan field. I could use the ID instead of the plan description but I work in a production environment and very key stroke counts so I would really like to have a simple double click process.
Is this even possible? If so how would I set that up? I tried using the double click on event macro builder but it does not seem to have this kind of option.
My form contains fields for group number and Plan. The same form houses 2 querys, one that pulls plans based on the group number and the other to pull elections based on the plan field (not the plan query). Would like to double click a plan within the plan query and have that description populated into the form's plan field.
tblTreatment contains Cost for different AppointmentTypes.
The above two tables are linked as 1-M relationship (1 Treatment can have Many Appointments)
Entering data in the tblAppointment - AppointmentType and AppointmentCost are the fields in tblAppointment. AppointmentType a drop down box, so that a specific AppointmentType can be selected.
Problem: Now depending on the AppointmentType selected the AppointmentCost field should automatically display the correct Cost.
i have 2 fields in a form - 'Balance' and 'Date'. Quite simply I need the date field to change automatically to todays date each time the data in the 'Balance' field is changed. Can anyone advise me on how to accomplish this.
I have a table that has been converted into a form for an input. Now suppose I have two columns. Say I enter value in first column. Now the second column is a lookup to a data from another table.
What I want is to see only those data in 2nd Column which have the value filled in Column 1. This is should be in run time mode. I fill in the value and then in the 2nd column I directly get to see only common values.
I am wanting to update data in one field which is being pulled in from another table based on an entry in another field in a form
Attached is the database. In the sales form I want to enter a customer ID which will then pull in the customer name from the customer table and put it in the Customer Field in the sales table.
I know I am duplicating the data by having customer name in both tables which is bad database design!
Ok. I've got a table named SIPATable and some of the fields contain summed values. Let's say for instance I have a field named "GaDRaw", which represents a raw score. now...I need to update a field called "GaDPer", based upon the value in "GaDRaw". So...If "GaDRaw" <=16, then I would want "GaDPer" to be updated to = 35. Further, if "GaDRaw" = 17. then I want "GaDPer" to = 37. and so on....
I think it's an If Then Else statement, but not sure how or where to place it. Id like it to be as code under a command button, and update the table all at once. Any help would be appreciated. Thanks in advance, Freud52
