Automatically Updating Yes/No Fields By Referencing Other Tables.
Jan 10, 2008
I am working on a database at my workplace. We have a list of available IP adresses that can be used for printers, not all of which are in use currently.
I have a table for the printers at each branch, and that printer's IP address.
I have an IP addresses table that contains all available IPs, a domain name field, and a yes/no field entitled "In Use?"
I want my IP address table to check my printer table, and put 'yes' in the "In Use?" field if the IP address exists in both tables, or a 'no' in the "In Use?" field if the IP address is not currently assigned to a printer.
Can this be done, and if so, how?
Much appreciated,
~Mike
View Replies
ADVERTISEMENT
Jun 4, 2015
I have a training database with multiple training codes. I would like to be able to update the training date for multiple codes. These trainings are normally completed at the same time and I want to be able to reduce data entry by auto updating the date of training for the employees for multiple codes automatically. How can I link certain codes together?
View 1 Replies
View Related
Apr 17, 2015
I'm still very new to Access.I have a form with subforms.Some of the fields are calculated fields.I cannot figure out what some of the fields will not update automatcally. Once I click out of the form and then back in, it will update.Tried many things like, Refresh, Requery both on the form and on the text box(es).I wish I could figure out how to attach my test database to show you.
View 11 Replies
View Related
Apr 2, 2015
I created tables that have lookup fields referencing another field. Actually I have several tables that all have relationships and object dependencies in my database. Now that I am trying to create some different reports, when I run the report I get the ID rather than the contents of the field. Also, I have a 'Report Dashboard' so to speak that I can run different reports from. On the form I have Combo boxes that reference one of the tables. I can generate the report showing the information, however I tried using a text box (criteria)(=[Forms]![Reports Form]![Combo47]+" County") in the header of the report to reference the selection made in the form combo box. When I run the report I generate the ID in the header.how do I get rid of the lookup fields in the tables, or is there another work around?
View 5 Replies
View Related
Oct 23, 2013
I am working on a database which has two tables used as part of a registration and login process.
I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.
The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.
View 1 Replies
View Related
Feb 11, 2014
I am having trouble figuring out the method to automatically update some fields in SubForm from 2 other SubForms.I have attached 2 pics, the first GradeEntry1 shows what the tblTopic_Class_Grade form looks like after I manually enter everything into it. GradeEntry2 is what the form looks like when I fill out the Form starting at the top.
I'd like the tblTopics_Class_Grade form auto-populate the TrainingClassID (it currently does this), TopicClassID, StudentID, TrainingTopicID based off the entry from the above forms.My end goal is that I need to have a grade for each student on each training topic for each class. Like:
Class1-Student1-Topic1-GradeX
Class1-Student1-Topic2-GradeX
Class1-Student2-Topic1-GradeX
Class1-Student2-Topic1-GradeX
View 6 Replies
View Related
Aug 7, 2014
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
View 2 Replies
View Related
Oct 9, 2014
I am in the process of building Append Queries for new records, and I know I can do an Update Query to enter specific information. However, how can I update multiple records from a (externally sourced) linked table to fill in blanks of an existing table? I have created a query to identify records with 1 or more matching criteria which contain the blank fields. I now want to update those blank fields. The data in each blank field is different for each record (same type, just different data).
View 1 Replies
View Related
Mar 11, 2008
Another problem has thrust its ugly head into my personal universe.
I have a report to make that is to show a break down of employees hired in each month since Oct. '05 and to show what percentage of the total for that month remain.
I have created one query that grabs each of the relevant personnel;
SELECT Right(Str(Year([dbo_personnel]![originalapptdate])),4)+" -"+Str(Month([dbo_personnel]![originalapptdate]))+" : "+MonthName(Month([dbo_personnel]![originalapptdate]),True)+" '"+Right(Str(Year([dbo_personnel]![originalapptdate])),2)+" Hires" AS hire_month, [dbo_personnel]![lname]+"; "+[dbo_personnel]![fname]+IIf(IsNull([dbo_personnel]![mname]),""," "+Left([dbo_personnel]![mname],1)+".") AS name, dbo_personnel.badge, dbo_personnel.originalapptdate, IIf(IsNull([dbo_personnel]![dateoftermination]),[dbo_personnel]![assignedorg],"EOS'd") AS EOS
FROM dbo_personnel
WHERE ((Not ((dbo_personnel.badge) Like "R*" Or (dbo_personnel.badge) Like "9*")) AND ((dbo_personnel.originalapptdate)>=#10/1/2005#))
ORDER BY dbo_personnel.originalapptdate;
I've saved this query as "2-yr Service Check Baseline."
And then this query that does the count;
SELECT [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active") AS EOS, Count([2-yr Service Check Baseline].EOS) AS CountOfEOS
FROM [2-yr Service Check Baseline]
GROUP BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active")
ORDER BY [2-yr Service Check Baseline].hire_month, IIf([2-yr Service Check Baseline]![EOS]="EOS'd","EOS'd","Active");
The output from the second query stores the Active count on odd numbered records and the terminated ones on the even numbered records.
So, to get the percent left, I'd need to sum the two count fields from the two relevant records and then divide this into the Active count.
But, in the report, how do I reference a field from a record other than the current one?
Thanx in advance for any help!
View 3 Replies
View Related
Sep 29, 2004
I am still creating my first ever Access Database for our small business.
I would like to be able to use a table or form to update more than one table.
For example, by entering the name of a company in a form/table, it is automatically added into a table of companies (ie another table).
How do I do this?
Anyone, anyone??
Frustrated.
Deano T.
View 1 Replies
View Related
Dec 30, 2004
I'm working on an MS Access inventory database for something similiar to a convenience store shop. All items are bought by the employer, therefore I don't have to worry about suppliers, not any customers. I just want to keep a running total of what's on the shelf, what's in storage, and be able to update what has been sold daily.
I've been using the Northwind sample database and modifying it as I go, but whenever I change the quantity in the order details on an orders form, the count for that product does not change. Any idea how I can automatically update the numeric value of a field in one table (products) by entering a numeric value for a field in another table (orders).
Thanks
View 1 Replies
View Related
Apr 29, 2008
I'm fairly new to access and using access 2003.
I have a main table and 3 other lookup tables. i have created the form using the main table. there are about ten fields.
what i need is, when i select one field, another four fields should automatically update.
is this possible? if it is possible how do i go about doing it?
any help would be greatly appreciated.
thanks
dem125
View 9 Replies
View Related
Mar 9, 2005
Hi,
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers]
but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
Thanks very much!
View 3 Replies
View Related
Dec 16, 2014
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
View 1 Replies
View Related
Oct 22, 2006
This may be a simple question, but I'm not sure what to even search for. I have a form with multiple subforms. All the subforms have related fields that are linked via relationships. But one subform has no fields in common with the parent form.
I need to be able to have the subform update itself whenever the record is changed on the parent form. I can't figure out which event procedure to use. It updates correctly if I assign my procedure to the form_click sub, and then click each time I want it updated, but that's a pain. I've tried on load, on data change, on activate, on data set change, and a couple others, but nothing works automatically.
How can I make the subform run a subroutine each time the parent form changes records?
View 4 Replies
View Related
Feb 4, 2014
Using A2010, for some reason, whenever I created a new Access Object and save it, the Navigation Pane doesn't automatically update to list the new object. Same thing for if I delete an object. This only started happening about halfway into the process of creating this database. For now, I've been compacting and repairing the database to "refresh" the file and then the Navigation pane updates.
View 4 Replies
View Related
May 8, 2006
Hi all,
Firstly I am trying to retrieve data from three tables in a format like this
Table Data Fields Descirption
Table 1 A,B PC Details
Table 2 B,C Software/PC
Table 3 C,1,2 Software Details
Where the B's are linked as are the C's and these both form primary keys in their respective tables.
My form has on sub form in it. The main for refers to Table 1, displaying Fields A and B. My subform is linked through child/master fields by B and is used to edit which software titles are installed on each PC. However, as C, the primary key for the software, is a code, it is not obvious what it refers to exactly. Therefore I use a combo box who's row source is query containing the software title (1) and the column widths precceding the software title are set to 0 so that software title (1) is visible in the field in the subform. Hence, although you change the title name on the form, the actual change is made in Table 2 altering the Software Code (C). This is fine, except that there is more than one version (2) of some of the software titles. The versions are shown in the drop down list for the software title combo as an extra colum, so you know you are choosing the correct one. But i also want a seperate field which actually shows the version chosen. I cannot link a normal text box to the same query as it doesn't have the relevant properties, but I can do it with a combo as I am effectively doing the same as for the software title.
This is all very well, I could just lock the combo box and be done with it, but it doesn't look very professional and is a bit confusing to the user. Is there away that I can get around this problem?
Cheers,
Matt
View 7 Replies
View Related
Jul 22, 2015
The code below bring back the results I want however.....
SELECT dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode,
Count(dbo_MF_PATIENT.HEYNo) AS CountOfHEYNo
FROM dbo_MF_PATIENT
GROUP BY dbo_MF_PATIENT.Forename,
dbo_MF_PATIENT.Surname,
dbo_MF_PATIENT.DOB,
dbo_MF_PATIENT.Postcode
HAVING (((Count(dbo_MF_PATIENT.HEYNo)) > 1));
My issue is I want to run this bit of code but only if the dbo_MF_PATIENT.MFPatientID appears in any of the 3 tables below:
dbo_ED_ATTENDANCE, dbo_OP_APPOINTMENT, dbo_IP_ADMISSION
Suppose im unsure on the joining because there is only ever one patient in the dbo_MF_PATIENT table but they could appear dozens of times in any of the other 3 tables.
View 3 Replies
View Related
Oct 21, 2014
I am designing a form based on specific criteria from a table. I have one table "country/region" showing: Country, Region, and then primary numeric ID. I have another table called "contacts" with more info. What I am looking to do is create two combo boxes on the form, one for country and the other for region which will pull from the "country/region" table and feed into the "contacts" table". When the user writes in the specific country, the corresponding region in the combo box next to it should update automatically.
View 1 Replies
View Related
Dec 4, 2014
I have a split database that is working pretty well, but I've been asked to add a functionality to it. I have a table, product1, that contains basic product information. One of the values is a "class", in that if a product was created at a certain time, it fits into a class; the other value in this table that is applicable is the time the product was created.
I have another table, class1, that lists the beginning and end times of the class periods and the class numbers.
So, my users will "ingest" a product, including the time the product was created. I need the query (i think so anyway) to fill in the "class" value in product1, by determining which class it is in by seeing which class beginning and end time it is between in class1 and using the class number found in class1.
View 1 Replies
View Related
May 31, 2014
I have a table with only two fields and one record: BegDate and EndDate (beginning and end date of the reporting period respectively). I also made a function with variables that look up those values for use as a date parameter in a query.
Here is the code:
Option Compare Database
Option Explicit
Public Function getCurrentRepDates() As Date
Dim dtBegDate As Date
dtBegDate = DLookup("BegDate", "tblCurrentRepDates")
[Code] ....
I am getting a syntax error for the line marked red. How can I use "Between" function in VBA code? Access 2010
View 5 Replies
View Related
Feb 27, 2014
how I can automatically change the Client settings either with a macro or code. Specifically for the three confirm actions (Record Changes, Document deletions and Action queries). I have a database that is distributed to end users in different offices around the world. The old way lazy way, was to go to each computer and set these settings to "off" manually, but this isn't possible. I want to be able to run delete queries and action queries in the back end without the end users knowing its happening and having to select yes all the time.
View 3 Replies
View Related
Jun 20, 2006
Hey Everyone,
I am stuck trying to figure out this problem. I have a main form "frm_tirelog_600" which has 4 combo boxes "cboleg",
"cbocar", "cbopos", "cboserial" on it. The first 3 combo boxes are used as criteria on 1 of 3 subforms that I have.
The fourth combo box "cboserial" is used for the criteria in a query on another subform that I have called "frm_mount_600_subform",
which is independant from the main form, I hope I have explained that clearly enough. What I need to be able to do is
have the subform fill in 3 of the fields on it "leg", "car", "pos" with the value from the 3 combo boxes on my main form. I
can get it to show in the fields but not write to the table. I have searched the forum and have not been able to locate anything
that would work and really need any assistance with this. Im not real strong in the coding department which is where I
beleive this could be done.
I have attached a copy of my DB which I hope will better explain it. Any help would be greatly appreciated
View 8 Replies
View Related
Aug 28, 2006
Hey,
The database I am working on, I split a while ago to give it some security. Now i'm updating a related form, and i'm finding that if I delete and add fields in the BE, the FE fields (being the fields that I need to insert into the form so the data entered propogates to the DB) are not updated.
How do I update the FE?
Thanks.
View 1 Replies
View Related
Dec 3, 2013
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code:
SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE,
(SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL]
WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID
ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
[code]....
View 6 Replies
View Related
Nov 7, 2007
hello all, any help greatly appreciated.....
I have two tables in the same Access 2003 database.
Table1 is called "Clients" and has address type fields, e.g. street_name, postcode, etc. It also has a field called CYPAN_area.
Table 2 is called CYPAN_Postcodes and has two fields; postcodes and the CYPAN area that postcode belongs to.
I want to create a function so that when someone enters the postcode in table1, the database searches Table2 and identifies which CYPAN area that postcode belongs to and automatically populates the "CYPAN_area" field in Table 1.
any tips greatly appreciated
Harry
PS I'm only a novice so I don't know anything about VBA etc, but I understand excel type formula
View 3 Replies
View Related
