I am relatively new with access and this is probably pretty easy for the pros, but here it is.
I have a table with 4 fields. Field A, Field B, Field C and Field D.
I have a form that the user will enter Data into A, B, C from combo boxes or List boxes. A, B, C fields in the table are update.
In Field D, as the user is entering data into A, B, & C, I would like to concatenate A,B,C ( I have an expression that all ready does all that, so don't worry about that) and write back out to the D field in my table.
The concatenation expression works great, but I just can not update the D field on the table.
I know someone will figure this out. Thanks for your help.
1. I need to know how to update the field list of the query after adding a new field into the table and the query? 2. I need to know how to update the records table after adding calculated filed?
I am using an MS Access 2010 table as a data source for a mail merge. I would like to update a field in the table with the date that the letter is printed. Is there a way to do this?
I have a database to track temporary decertification's. I have the expiration and max dates calculated out from the original dates at the top of each box. The temp expiration date is calculated by adding 267 days from the first date . When we enter an extension, the new expiration date is 30 days from the extension date. My question is, how can I make the expiration date update when a new extension is put in.
For ex.
Temp Decert Date: 05 Dec 2014 Temp Decert Extens 1: Temp Decert Extens 2: Temp Decert Extens 3: Temp Experation Date: 31 Aug 2015 Max Temp Date: 04 Dec 2015 how can I make the expiration date update to go 30 days from what is in the extens field 1, 2, and 3 (respectively) instead of 267 days from the original date? So I want it to look like this after updating a field Temp Decert Date: 05 Dec 2014 Temp Decert Extens 1: 30 Aug 2015 Temp Decert Extens 2: Temp Decert Extens 3: Temp Experation Date: 29 Sep 2015 Max Temp Date: 04 Dec 2015
Is it possible to run a SQL command to update a field within a table with random numbers?
More specifically - random long integers linking back to an ID (autonumber) field in another table?
Background to this is, I have multiple static data tables related to each other by long integer identifiers (autonumbers)
The structure is fine but I haven't been provided with the actual data yet - but for development purposes, I need to work on other functionality which requires that this data be present.
So I want to fill my table with dummy data such that I can go off and work on the remaining functionality, but then just go back and clear it all out once I get the actual data.
I have one 'main' static table, which links back to other tables, which I have already populated with dummy static (i.e. company names, locations etc) Now I want to go into my main table and populate those fields in each record with a random ID. I don't mind doing this field-by-field (there's only a handful) but I've a lot of records in there (~1000) so I'd rather not do this record-by-record.
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
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 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!
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
InstalmentAmounts field is populated based on the results of PaymentMethod which is a combo box
Here is the current code
Select Case Me.Payment_Method.Value Case "Three post dated Cheques" 'If three payments by cheque Me.InstalmentAmounts.Value = Round(Me.Total.Value / 3, 2)
In some cases there will be a few pennies that the client "overpays" because the instalment amounts will not add up to the Total. I need to add up the TotalInstalmentPayments to find out what the client actually pays and then add the difference between TotalInstalmentPayments and Total to the OurFee field
So
Total = 500.00 Payment Method = 3 instalments so IstalmentAmounts = 166.67 (rounded) so client pays 500.01 so the 0.01 needs to add to the OurFee field.
I am trying to update a field to say "Yes" or "No" based on whether it is more than thirty days out from the value in another field. I am having trouble doing this for some reason...totally stuck!
I have a parts table with a field that holds a vendor's name which is selected from a combo box on a parts form. I've just noticed that if a vendor name is changed on the vendor form the field in the table isn't updated. I now have to go through all parts to adjust any changes done to the vendor name. Is there a way I can automatically update the vendor name in the parts table if there is a change done to the Vendor name in the vendor table?
I thank you in advance for considering this inquiry.
From within a Form.field (based on a master table query), I desire to trigger two events; one before update and one after update.
This function will serve to document specific form.field value changes to a "log" table for review prior to being committed back to the master table.
What I'm looking for are functions, which I can use in a Macro or VBA code to facilitate a field read and copy before change and a read and copy after change triggering.
I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.
I want to prepare a report from the projects table that lists projects IF they have any open tasks.
In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'
I have a table called Inventory (table1 - PK=INVID) that contains all inventory ID numbers and descriptions. I have another table called Inventory Transactions (table2 - FK=INVID) that gets updated through a form. When a new transaction is made in table2, I want the transaction amount to get added or subtracted to an OnHandQty field in table1. I tried having table2 (transaction table) as the main form and then table2 as the subform, but I couldn't get table2 to update.
Hi.. I am creating a database for a friend.. database is for Money remittance.. Ive attached the first step I did.. my problem is.. everyday money rates changes and i need to find a way how to update the rates so that the encoder dont have to fill the "Peso Rate" everytime they have a transaction.. how could i create an update for the rates only once...and also update the dailyrates that will not affect the previous transaction..for example..yesterday was 32.00 and today is 52.00. i want to preserve the previous transaction rates..but could update daily rates.. sorry if i cant explain it very well.. pls..i need help.. thanks!
Hi.. I am creating a database for a friend.. database is for Money remittance.. Ive attached the first step I did.. my problem is.. everyday money rates changes and i need to find a way how to update the rates so that the encoder dont have to fill the "Peso Rate" everytime they have a transaction.. how could i create an update for the rates only once...and also update the dailyrates that will not affect the previous transaction..for example..yesterday was 32.00 and today is 52.00. i want to preserve the previous transaction rates..but could update daily rates.. sorry if i cant explain it very well.. pls..i need help.. thanks!
