Beginners Question - Auto Update Fields Based On Fields In Another Table
Nov 26, 2007
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.
This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.
What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.
If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)
Private Sub Form_Load() If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then
Me.CASETIF.Visible = True Else Me.CASETIF.Visible = False End If End Sub
And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form
Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap
Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria
Is there an easy way to auto-populate a Junction table [in access 2010] given the following two tables with a many-to-many relationship for Tasks? The two tables are
My situation is this. I have 3 tables that I have imported from my mainframe system, between these 3 tables I have the data of product code, description,supplier code, supplier name, order method, and ABC code.
I am trying to create another table that I can capture daily Out Of Stock data for products.
What I would like to do is to enter the product number in the first field of my new table, and then the remaining fields will auto populate with the correct details based on the product data stored in the parent tables that I have imported.
I understand right off the bat if you're reaction is "don't duplicate data!!" -- mine would be too (don't fret, I know my normalization).
I've linked a table in my db to my Global Address Book in Outlook 2007 and, upon entering an employee number as a new record, would like to verify that the number entered is listed in the GAL and then pull in the associated name and location info.
The key is that I don't want this info to rely on the GAL going forward. For example, if an employee leaves or is no longer listed in the GAL, I don't want to lose the employee info (past data is needed for audit purposes). Note: I will be creating a report later to show if there are discrepancies between the GAL and my table, but that's another story...
So, what would be the best auto-fill options in Access 2007?
I have a copy of the back-end that gets a search key error 3709 on two records. In other words, I can duplicate the problem.
The interesting part is that I can update any other field on both these records and save the record, but when I try to change two specific fields, I get a Search Key Error and have to ESC out to continue (basically UNDO the change). Both fields are text fields with lengths of 7 characters and 255 characters, and both are COMBO Boxes on the form.
I tried to focus on the form think there was an issue in the code. I can definitely TRAP the 3709 error on the ON ERROR event on the form using "if dataerr = 3709", but then I tried something even simplier.
I went directly to the table and to each of the records. Again I can update any other field in the record but these two specific fields. When I try to change either of them and move to another record, you get a Search Key Error 3709.
By going to the table record directly I'm as low level as I can get. There are no validation rules on either field at the database level. If it was truly CORRUPT would it let me update any of the other fields on either of these records? One is an empid (not a primary key but is indexed with duplicates okay and not required), and one is status code (not a primary key but is indexed with duplicates okay and not required) so they're no critical fields, but something is keeping them from CHANGING.
Just tried something else; deleted the INDEXES on both the fields. Now it works! I am completely confused now because it really wasn't a corrupt record, but the indexes are causing the problem. Do I need to update the indexes somehow when the users selects a new empid or status code?
Could someone please shed some light on any methods on a way to update fields automatically in different tables but with the same field name when data is entered into one of the fields in any table?
I have a table that is used for project progress. One of the fields is named "Completed". I would like to know if it's possible to auto fill the data in the "completed" field based on three other fields within the same table.
ex: If field 1 = Yes, and field 2 = Yes, and filed 3 = Yes, then field "completed" = Yes
I'm open for any ideas, I'm new to access and am not sure how to get this started.
i am the biggest of all newbies when it comes to access and understand only the graphical parts of the process (no SQL knowledge etc.).
how would i go about auto filling a certain field based on what's in another field? for example, i have a field called TimeZone that i want filled with either West, East, Central, or Mountain based on another field for State. can i build a query to automatically take care of any empty fields for TimeZone that haven't already been filled out by me manually?
if i need to use SQL, that's fine too as long as the directions are clear and precise as i have no prior knowledge.
In this table I have two fields Seats Available and Seats Remaining
When I register a student to a Course in the Courses table I would like the corresponding Seats Remaining field for that course to decrease automatically.
I can, of course, run a report or a query to see how many are enrolled in the course and then manually update the Seats Remaining field (which is what I currently do), but I would like this process to be automated each time I register a new student.
I'm making up a stock system for the independent jewellers I work for. Each stock pattern needs an individual code which can quickly be recognised read as tickets are often taken off the jewellery and can be mixed up easily. To this end we categorise based on two criteria:
1. Material 2. Type
At the moment these are as follows:
MATERIAL
IDMaterial 0Base and Miscellaneous 1Silver 2Gold 3Palladium 4Platinum 5Pewter
TYPE
IDStock Type 0Previously Owned 1Ring 2Pendant 3Chain 4Necklace 5Bracelet or Bangle 6Earrings 7Brooch 8Gents 9Gift, Clock or Miscellaneous
So if I had three pairs of silver earrings I want the first to be No. 161, the next 162, the next 163. If I had three Gold Bracelets I'd want them No. 251, 252, 253 and so on...Material and type are both selected form separate Comboboxes on a form.
I am trying to build a form in the MoldReq section so when the user selects the mold number(combo box) it will auto populate the Part_Name and Part_No fields by pulling the data from the Molds table.
I have tried following this video where I create a change event:
Code: Private Sub cboCombo31_Change() Me.txtPart_Name.Value = Me.cboMold_No.Column(3) Me.txtPart_No.Value = Me.cboMold_No.Column(4) End Sub
How can I auto populate fields based on a payment term & amount?
example: client has 9 monthly payment of $150 term due every 15th of the month. (I would like it to be able to max out at 30 months)
So what I want to do is populate 9 monthly fields with dates and $150 payments fields next to it. Now, the payment fields even after auto populate, must be flexible in the sense for me to add a different amount just in case client makes an over payment that month too. In, addition I would like a check mark box to auto populate along if possible based on the 9 month term, this way I can manually check TRUE when payment is received in office.
how to Add/Update record including existence of record based on single unique field.Now I trying to learn how to check existence of record on multiple field before adding (at least two) and how to update the record with multiple field record
Below is my testing table fields
Year Month Working Days 2013 Jan 20 2013 Feb 17 2013 Mar 22
Now two situations adding a new record and updating the existing record Which means the unique fields are (Year + Month)
I'm building a customer form using a list box of summary customer info on the left side of the form (CustId and Names) and customer details on the right hand side. I want the details shown on the right to be those for the customer selected in the list box on the left. I'm having problems getting the details to update when a different record in the list box is selected or clicked.
I originally set the data source for the detail records to the base customer table but couldn't work out how to update them when the list box was clicked!
I then tried creating a query with the select clause conditional on the list box (CustId = Me.qryContactDetails) and set the data control source for the detail record to the query (=[qryContactDetails]![FirstName]). The query works as expected but the field in the form just shows '#Name?'.
I tried adding a field requery on the List31_Click() event using Me.[FieldName].ReQuery but that didn't seem to change anything
I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
How can I get some mainform fields' data to be the first entry in a subform? From the mainform, I would like the Head of Household name and date of birth to be carried over to the first entry in the HouseholdMembers subform.
The two forms are tied to separate tables. For each household member I need to be able to enter full legal name, date of birth and some additional pieces of information such as income and source of income.
What I'd like to avoid is making the users enter the Head of Household's name and date of birth twice. Is there a way to carry that information over to the subform?
I have a field called uniqueID which I would like to update based on 2 other fields that are already populated in my recordset (from running previous queries). I heard it is not possible to do an Update Select like shown below.
select max(uniqueID) from myTable where a = "value of field a from first record in record set" and b = "value of field b from first record in record set"
If it is true that I cannot do an Update Select then I am trying to do something in VBA. How can I Loop each row in the record set and store the values from fields a and b. I would obviously then need to pass those values into the sql above and store the result in a variable. I would then do a straight update to put the value of uniqueID into myTable.
I have a simple table called CUSTOMER with contact details on there...First NameSurnameDate of BirthAddress 1Address 2Address 3Town/CityCountyPost CodeemailetcI have a second table called TOWN_COUNTY_REGION that lists all the towns and cities in the UK. Each Town/City is listed with its corresponding County and Region. This table has 3 fields...Town/CityCountyRegionThe TOWN field in the CUSTOMER table is populated by the user selecting from a list of towns in the TOWN_COUNTY_REGION table. When a user clicks the town that they are in, I would like the appropriate COUNTY and REGION fields to be automatically populated in the CUSTOMER table. So, for example, if the user clicks LIVERPOOL from the list of towns and cities, Merseyside and Northwest should automatically be populated in the other 2 fields.How do I do this ??Many thanksAllan
First post :) found some great tips on here. Anyway here is my story and question.
I am new to Access development. I know what I want to do, I just can not find a way of doing it :( .
1) I would like my form to only be editable when an edit button is clicked. I am not sure if I can do this on a form level or if I have to change each individual part. Any ideas?
2) I capture some clients details, title, first name and last name. I then need to merge the above 3 fields into one to create a 'policies in the name of' field. Is this posisble? I have tried using the initial value expression but am having no luck. Any ideas?
Sorry for posting into another thread about this, but here's my problem:
Hello All. Finally getting my pride out of the way and asking this. I've searched and this is the closest question pertaining to the problem I'm having. I have created a form to populate a table in Access. I've created Lookup fields for parts of the table that will get data from another table/tables. I want a certain part of the form to auto-populate data based off of the last name I put in the initial Drop-down box. Say, I have a person with a last name (and there are several with the same last names), I want the remainder of that line in the form/report to populate with his/her data needed for the form letter. Unfortunately right now, I have it where we have to select each from a drop down menu, for both last names, first names and other data needed. I've tore my brain up trying to figure out how to link the data in each one of the combo boxes to the first combo box. Any help would be appreciated. These are driving me nuts as I should be able to figure this out and it's just escaping my vision I guess. Thanks in advance for the help.
I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. How I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.
Eg. Record 1 - [inv No] 1 [Vno] 0005 Record 2 - [Inv No] 2 [Vno] 0006
