Auto Update Fields
Feb 17, 2006Could 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?
Thanks
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?
Thanks
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)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
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.
thank you
-Tim
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.
The "PersonalDetails" table is related to the "Application" table using the autonumber from table, "PersonalDetails".
The "PersonalDetails" table has "StudentID" (autonumber), "First Name" and "Last Name" fields.
The "Application" table also has "StudentID", "First Name" and "Last Name" fields.
The "Application" table is related to the "PersonalDetails" table using the "StudentID" field.
How do I make it so that the first and last names on the "Application" table are automatically updated when the StudentID is entered?
Hi all!
I was wondering if you guys can help me out with this one:
I would like to have data auto-inserted from a particular colum in table 'x' in to a defined column in table 'y'. similary, I'd like to achieve auto-updation of this data as well as deletion. Can anyone please provide some guidelines?
A little more detail :
I have a column "Client ID" in the table client_personal_info, the rows of which I'd like to have updated in the "Client ID" column in the client_business_detail table.
So if say, ClientID_1 is a row that has been added by the user, I'd like to have it auto-inserted in the client ID column in the client_business_detail table
thanks in advance for your help
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
Thanks
Rahul
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?
Thank you, Charlotte
Hello all,
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?
Thanks all :)
If am a developing the db and just making alterations to forms and reports etc on a developing copy. Is there anyway i can auto pull and replace the ones in the live db rather than going into the live db, deleting all the forms etc and then importing it?? can it be scripted or somethign like that?
Thanks
hi all
i searched for this but didnt get it clear. how can i update the FE for my users?
Hi...Im working with this database..its an order tracking database.. everytime we received the shipments we have to update the rcvd items.. i have an attachment here... pls i really need help...
How could i do these automated:
1: if i update the Rcvd status (Y/N) "Y" ...how could i make the Date Rcvd automated.. coz what happens is that.. i have to put date in each items that we rcvd and its a waste of time.. what if.. i have lots of rcvd items..
2. how could i put row count.. so i could know how many items i have and on what row are they...
pls help..
thanks in advance....
Eric
I have a form I need to enter basic info into, and would like to know Iif there is a way based on the primary key id when I enter info on an individual's account, the rest of the info will get updated, i.e. contact info, full name, etc.?
Thanks.
Sory about my english.I have a table that records data.Name-Surname-adress-payment time etc.The payment time is very important because all event is progress around this.Is that possible to remind me before the payment time?Thank you very much for your help.
PS: I send a photo about my question.
I am building this little project for work, and came up with this problem.
It's basically a database of the works in the workshop.
Each car comes in, the service advisor adds a new job in to the database.
And at the other end of the workshop, ppl can see it on the screen.
What I have managed to do now is that if someone modified any details of the existing jobs, it will be automatically updated on the other computer.
(ie, computer 2 has a form opened and showing all the current records in a continuous form view, computer 1 modified 1 record, then a couple seconds later, the form will update itself with the new data on computer 2)
The problem now is, if computer 1 ADDs a new record, it will not show up/auto update on computer 2's form, until I close the form and reopen it again.
Now I have no knowledge in VB and have no idea on how it work together with Access. I do however has some skills in other programming language like Java and C, so learning shouldn't be a problem if that has to be done through VB.
Any suggestion?
I have a database with a Source Table related to a Product table in a Master/Child relationship. The source table has a SourceID field as the primary key and a SourceName field. The Product table has a SourceID field as a foreign key and is related in a many to one relationship to the Source table (the source table is the one and the Product table is the many).
I have a form used to update the Product table. The form includes Combo Box controls for both the SourceName field (which fills in a Source field in the Source field in the Product table) from the Source table and the SourceID field from the Source table (which fills in the SourceID field in the Product table). I need to know how to automatically fill in the SourceID control based on the selection made in the SourceName control.
I would also like to know how to automatically open a form to update the Source table if I enter a SourceName in the Product.SourceName Combo Box that isn't already in the Source table.
If you can help me, please send your replies to james.ballotti@gmail.com
thank you
i have a complete access database with forms and queries and reports.
the only problem i am facing is that when i create a new report it only takes the data already entered.
if i modify or change a record in the form or table the report doesn't update the modifed data it stays as it is before and i have to re-create a new report.
is there any way to deal with this and make the same report auto refresh the data modified or no ?
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?
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies View RelatedHello,
I have added this to my database but dont see how it would be used. I have a shortcut people use to access the database which ties in my workgroup file infomation so that they can open it.
Now when I send the MDE file to people they get the "dont have permission" typical response.
Now if people are goin to use the shortcut to the either my .mdb or .mde then couldnt I just make adjustments to those and then replace the file on the shared network?
Am I missing something or not seeing how this would be useful or needed?
Thanks for any insight.
I was looking at Bob Larson's utility posted here (http://www.access-programmers.co.uk/forums/showthread.php?t=111132) and I have a question. Maybe I'm being dense (I probably am) but the loading page on my form looks like this:
http://farm1.static.flickr.com/177/382115209_23c11d6896.jpg
I was just wondering if Bob (or someone else) can tell me what the difference is between the first and third options on this page?
Hi after I searched the forum for a solution similar to my problem I did not find anything that worked.
I have a form that it is used for hiring DVDs.
There are fields among others like customer_id, copy_id and a hired field wich is true or false.
I also have a submit button which saves the record before you finish. I would like to make the hired field for this record updated to value "true" when I click the submit button.
Hi all
I've followed the instructions on this post:
http://www.access-programmers.co.uk/forums/showthread.php?t=85898&highlight=updating+field
But, unfortunately doesn't seem to be working for me.
I added the equivalant field to 'Hired' after I had previously made tables etc and started added code to forms etc.
When I type Me. a list of options available to type in appear, and 'Confirm' (my equivalant to Hired) does not appear, yet all of the other field names in the form associated to this table DO appear. Does anyone know how to get around this?
I've also set my field to be "Yes/No" - does this mean that the code is:
Me.Confirm = True
OR
Me.Confirm = -1
?
Many thanks
Hi.
I am new in using Access, so forgive me if the solution for my problem is easy.
I wrote for customers a little program, which receives data via RS-232 serial port and then place readed data into MS Access database. After i put the data into field in table (database, table and field are set in my program), there is some kind of script that continue the process of data manipulation.
The problem is, that customers want to activate that script automatically, when i place data in the field. So, i made a form, which contains bound control to this field. Every 2 seconds i am refreshing this form, so when i change the data in the table field, i can see the change in the form control. Now, how can i make the OnChange event to work, when the data changes? I tried to do it, but nothing happens, except if i am writing from the keyboard something directly in the form.
Can this be done and if, how must i do it, so when a new data arrives to run automatically the client script?
Thank you in advance
i have set up a database to help track the vehicles my company owns. However on the form i have created when i type in the license plate number want the work area that is responsible for the vehicle to pop up so i can inspect it.the form also has the different things i would look at but i don't want to continue to change the work area that has the possession of the vehicle or have to look it up every time i have an inspection come due. Any help would be appreciated
View 1 Replies View RelatedIs it possible to automatically update a date field in a form record without ever clicking in to that date field, but only because you have altered something else on that individual form record or an embedded, linked subform record? eg1. Staff details record - a) create a record for Bob Smith and 'Last Updated' field automatically inserts date b) amend record to say 'Robert Smith' and 'Last Updated' field changes to show new date, even though you never touched that date field. eg2. Risk register - main form record contains name, details, and 'Last Updated'; embedded subform allows you to add individual records of actions taken for each risk. I want the 'Last Updated' field to update each time I add an action, without amending the 'parent' risk record.
View 10 Replies View RelatedWe keep a backend and a master frontend of a database on a network drive and distributed a .mde version of the frontend to the users who save a copy of it on their hard drive. There is a separate version table on the frontend and backend of the database for version comparsion. Whenever there is change on the master frontend, I shall change the version # on those tables and make a new .mde file on the network.
When user open their own mde frontend, there are scripts to check its version with the master frontend on the network and any mismatch will trigger off the following scripts (on the default form) to replace the old version with a new one from the network.
Private Sub Form_Load()
Dim strFEMaster As String
Dim strFE As String
Dim strMasterLocation As String
Dim strFilePath As String
' looks up the version of the front-end as listed in the backend
[code]....
This process works fine for everyone in Access 2010. However, these scripts appear to have conflicts with Win 7 when users upgraded to the O/S. They keep getting database unstable error message and the process fails to work. Whether we can change the scripts to make them compatible with Win 7?