I have a form (Access 2000) on which one of the fields shows the date on which the record was last updated.
The idea is that when the the form is closed (or the user navigates to another record), this field is automatically set to the current system date, but ONLY if any of the other fields on the form have been changed. If the user has merely viewed the form without changing any data, then the 'Date Updated' field would not be changed.
I'm sure this is a common enough scenario, but I'm struggling! Any suggestions most gratefully received!
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.
The second one is a "child" table for Network_Element.
For these tables I've created two forms: 01_NE and 02_Data, which is a subform for 01_NE.
The procedure of data completion is this: 1. Choose Network Element 2. Open NE_Data for this element (here opens the NE_Data with a filter for this element) 3. View record or add new
The NE_Data record consists of date, number, NE_number, port_number_old, port_number_delta and port_number_new fields.
Now if I add new record, I want NE_number field to fill automaticly with a value of last record (NE_number of current Network Element).
Second "wish" :-) has to do with port_number_x fields.
After creating new record, port_number_old should be filled with port_number_new from previous record and port_number_new should be a sum of port_number_old and port_number_delta, wich user will complete.
i have several fields i would like automatically updating with data from a combo box. at the moment i can only update one. the combo box relates to a query on one table, the fields i want updating are on a second table. is this possible?
I have made up a database to record generations of birds; along with others I have the following fields:
Ring number (primary key) Sex Year Colour
Data entry via a form view.
For new entries I pick up from a combo box the parents, what I need to do now is create a relationship between 1, 3&4 and 2, 5&6 so that the data will automatically slot in the relevant fields.
1Parent Cock 2Parent Hen 3Grand Parent Cock C/S 4Grand Parent Hen C/S 5Grand Parent Cock H/S 6Grand Parent Hen H/S
would I do it via a query.’ And would one cover relevant generations
Right, disclaimer over, hopefully someone can help me with this.
Essentially, I have a table of employees who have something called an OUC - this is a group identifier which allows us to recognise certain info about them. The first three digits relate to the line of business (LOB) that the employee is in.
ie. John Doe - JPGA2X; Joe Bloggs - JPGB3Y; Frank McRank - JDH3B;
John Doe and Joe Bloggs both are in the IT LOB (as defined by JPG), and Frank is in Finance (as defined by JDH).
I currently put the OUC in the employee table, and have got a translation table for OUC->LOB (ie the left three digits of ouc's, and how they equate to the correct LOB). What i can't work out how to do is get the two tables to interact. How do i get the employee table to automatically work out which LOB the employee is linked to?
I hope this is clear, and that someone is able to work out why i am being such an idiot about this! (it's probably basic principles of Access that i've skipped over in my haste!!).
My company requires a query to aid distribution, however I am experiencing some problems implementing the system.
We have purchased a large database of addresses and need to be able to query a town/street to find out how many houses are in that street.
I have the query and form set up to display the street/town and list of house numbers when searched for, however the houses are grouped by postcode rather than street, with multiple house numbers in the same field (see example).
Street Main Avenue
POSTCODE HG23 0DF
Houses 89;91;93
POSTCODE HG23 1DF
Houses 95;97;98
----
Basically, I need a total of how many individual houses there are in a street, regardless of postcode. In this case, it should treat the ";"s as dividers and return the result of '6'. Or in some cases ignore the semicolons as some fields look like ";;;;;;;;13". It should then add both the total for one postcode to the other postcode to display a complete total of houses in one road.
Is it possible to ensure that if a particular field has a specific value, ie, 'yes' then the reaminaing fields on the page are automatically set to 'no' by default?
If No is selected in the field in question then this would subsequently allow the other fields to be set to any value.
I have a form that when I insert a registration number I would like the value of the field "nom" the name automatically appears in the "nom" field of the form. The form uses a query.
I am an amature with databases and a bit rusty too. Can anyone help me with this problem?
basically I want to automatically enter a unique asset code for one table that is based on product and manuafacturer's codes from other tables
e.g. for audio visual eqipment
The first table would be a manufacturers table with the manufacturers code as the primary key e.g. JVC, HTC (hitachi), SHP (sharp) etc
The second table would be a product table with the product code as the primary key e.g. AMP, DVD, LCD (lcd tv) etc.
( i would also have a customer table and an asset repair table)
In my asset table I would like to enter an asset code as the primary key of the type JVC-DVD-001, JVC-DVD-002, HTC-LCD-001 etc by looking up codes from the other tables and then adding the last number. (Ideally once the manufacturer's code has been selected only the product codes for products made by that manufacturer would be displayed). (Also it would be ideal if referential integrity could be set up so that the asset code components can only contain valid manufacturer and product keys.)
The asset code cannot be a compound key as I want to have a repair records table in which the asset code is entered as a single field with referential integrity to the asset field in the asset table.
When entering repairs I would like the lookup field to display only the assets belonging to a particular customer.
I have a memo field on a form. I am trying to autopopulate it when entered into. For example: I enter in clients info and then tab to the memo field and it automatically enters date and time. Then later when I go back to that same record to append to the memo field it adds the current date. This way I can go back and see all the dates and times when that person called. Any idea how to do this? Thank you
I would like to create a memo field which automatically enters the date prior to any text input from the user. Also allow dated updates in the same field
I have a database that contains 13,772 records and each record contains 34 fields. The database is used for historical research purposes. As I am constantly changing the content in one or more fields I would like to be able to have a field that would show the date of any changes that are made to any of the fields in any given record. Additionally I would like to be able to run a query, lets say every six months and extract all the updated records.
I've got most of what he wants sorted but this last task I am completely flummoxed. All of his projects are allocated an ID (named Project Reference), starting from P010010 and increasing by 1 each time. I've made a form that allows a new project to be recorded by entering all the details and hitting the 'record' button, but he wants the Project Reference field to be automatically filled in each time (understandably), increasing by 1 from the last record.
So if the last record was P010311, then when the form opens the Project Reference should automatically be P010312.
I've looked into this and found many guides talking about DMax and DIM and strCriteria and whatnot, but no matter how many of them I follow and try to adapt to my own database I can't get it to work at all.
The table the ID comes from is called General, and the field is Project Reference. The ID should automatically be filled into a text box called txtRef whenever the form opens and a button to add a new record is pressed, being 1 higher than the previous ID.
I am putting together a simple database to do with monitoring maintenace of buildings. I was the building number to automatcially populate when the building name is select.
I have got the the point where I have building name and number in the building name combo box but i'm stuck with the after update code builer part.
how to automatically populate a certain field. To add some context, I have a form which registers the details of a contact with standard information of contact details. There is a subform which shows the different products that the client from the main form is interested in. This is a actually a data sheet which returns the results of a query (selecting from the relevant table the client in question and the products he/she wants).
I have added a button which opens up another form and allows a product (and hence a new record) to be added for that particular client. I would like that the form automatically populates one of the fields in the form that is the client id. Given that the subform is opened from a form which already identifies the client, how do I do this?
I am looking to add a field to my table with a timestamp for each new entry.
I am working with a split database. All of the records are inputted by forms (in the back end by other users). I want to be able to see the exact time that a record was added, but without having to add this field to the actual form. I just want to be able to have a field in the table that shows me when the user submitted an entry. Is there a way to add an automatic timestamp field to the table?
I am wondering if Access 2010 has the capability of automatically filling in a field from info keyed into previous fields. For example, I want a confirmation number issued which is made up of all the characters keyed in the previous three fields. For example, assume the previous three fields are:
Block = 01 Building = 125 Room = A Confirmation Number =
Therefore, the confirmation number would automatically be filled in as 01125A.
When I import a column from Excel into a new table in Access using the Docmd.transferspreadsheet function, Access automatically assigns a field name of "F1"; and if I try to import a range of columns (Eg A1:B10), then Access saves them into a table with 2 columns with field names "F1" and "F2". The table is created as part of the Docmd function, and so I don't have control over the field names.
Is this going to always be the case? Can I depend on Access to always use this naming convention? Because I am about to start writing more code that will depend on these field names.
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.
