Hi to all,
I need your help please whith two questions I have:
1. I have one table which contains a number of item in stock and I have created a form in which I input a number, what I need is that the number from the form will be decreased from the number in the table (e.g. total number of items in stock - number of items used = the outcome will be updated in the table to be the new value of items in stock)
2. is there any way to make the autonumber value to start not in 1, 2, 3 ...etc but for an example: 5323, 5324, 5325 ... etc or 0005323, 0005324, 0005325, ...etc ?
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!
Forgive me if I am asking a question which to some of you may seem obvious, but I am new to developing Access DB's.
I need to be able to do the following in a soccer league database and am after some tips on how to achieve it:
1) I need to be able to have the DB know that it must add goals scored to an existing value in a table, for example if Team A has scored 3 goals, it needs to add 3 goals to the running total of goals scored for the season 2) Can Access understand if Team A has Beaten Team B by 3-1, can it be told to add 3 points to a running total of points for the season? 3) How do i prevent a user from entering a duplicate fixture that has already been entered into the database?
I should also mention that I am trying to achieve all of this from an ASP website!
Thanks in adavance and sorry if these are real beginner questions!
I have a table with about 5000 records. Now i decided to add a new field to this table and call it "Rating". Eventually this field is going to be a combo box, which get its values from a lookup table called tblRating.
At the moment in the table with 5000 records, i have this field blank for all the records. Now i would like to assign the "Not Rated" value in each of the 5000 record rather than leave it blank. How do i go about doing this ?
I've tried to do it with an update query by putting the "is null" in the criteria and 1 (the id of the value 'Not Rated') in the update to ... however, to no avail.
Is there any way to update a field with previous value of a combo box before updating a new value to the combo field. If my question is not clear please go through the sample db with this .
