Simply (although not for me) I am trying to build a form that involves a unique identifying number being entered, a button being pressed and relevant field belonging to that unique identifying number being displayed in a subform.
For example, I might want to enter a customer number, press a button and all the orders made by that customer appear in a subform.
I am hoping that someone can help me to get rolling with MS Access.
I have already pursued all of the usual avenues -- so far with little success, and (see below) am up against deadline problems.
I used Access a number of years ago, but I am very rusty on methodology and terminology -- I might as well admit to being a Newbie.
What I am trying to do is to develop a record-keeping system for a small (one-person) Mental Health Clinic.
I need to do this a.s.a.p., because our existing commercial software -- very expensive and unsatisfactory performance-wise -- will soon disappear, and I urgently need a viable replacement.
I have already built the following normalized Tables:
Table 1: Patients -- demographic information: patient name, clinic log number (as distinct from the indexed ID), address, phone, DOB etc.
I have a table with the column Grade and a column Grade_Group In the column Grade I can give a value from 0 - 10 (pull-down). When I grade between 0 and 3 I want the column Grade_Group to be updated to Low (easy, even I can do that with an update query). But if the grading is between 4 and 7 the Grade_Group needs to be Mid and if the grading is between 8 and 10 High. In this case I am able to create reports on High/Mid/Low criteria. If I put this all in (one) query the query changes all data in the Grade_Group to Low, or I get a duplicate error because there is just one update field. Someone, I searched the forums but nothing came up. Thanks in advance!
hello all, i have a table that has 4 fields, i need to somehow store/display the highest value the lowest value and the avg of the 4 values. please help!! i can either display them in a form or store them in my table? what ever is easier
Set iMsg = CreateObject("CDO.Message") Set iConf = CreateObject("CDO.Configuration")
Set Flds = iConf.Fields ' Set the CDOSYS configuration fields to use port 25 on the SMTP server. With Flds .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort 'ToDo: Enter name or IP address of remote SMTP server. .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "usahm204.amer.corp.eds.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 10 .Update End With ' Build HTML for message body. strHTML = "<HTML>" strHTML = strHTML & "<HEAD>" strHTML = strHTML & "<BODY>" strHTML = strHTML & "This is an automated e-mail to let you know that <b><font color=#FF0000>" & [Name of Requestor] & "</b></font> from <b><font color=#FF0000>" & [Department of Requestor] & "</b></font> has submitted a new <b><font color=#FF0000>A/R " & [A/R Code] & "</b></font> request in PERD." strHTML = strHTML & "</BODY>" strHTML = strHTML & "</HTML>" ' Apply the settings to the message. With iMsg Set .Configuration = iConf .To = "<Carol.Patukonis@examhub.exch.eds.com>" 'ToDo: Enter a valid email address. .Cc = "<Elisa.Chandler@examhub.exch.eds.com>;<Deborah.Davis@examhub.exch.eds.com>" .Bcc = "<Carlene.Vitello@examhub.exch.eds.com>;<Jason.Boney@examhub.exch.eds.com>" .From = "PERD Request<Jason.Boney@eds.com>" 'ToDo: Enter a valid email address. .Subject = "New A/R " & [A/R Code] & " Request" .HTMLBody = strHTML .Send End With ' Clean up variables. Set iMsg = Nothing Set iConf = Nothing Set Flds = Nothing '==== A/R 60 Code End ====
I have tried using .Priority = "High" and .Importance = "High", but I keep getting an error stating this object property is invalid. What can I use to set a priority (Low, Normal, High) on the e-mail using the above code?
This is very strange. I have converted two computers from 2003 to 2007. I have also installed 5 2007's on to new computers. All of these are Professional XP sp2 setups. I copied over a database to woek on those computers. Originally the performance was very good, on all of the computers (but not the lowest specified converted laptop ) exitting from a form containing a subform takes 37 seconds. The word Calculating... is on the status bar. The laptop takes 3 seconds par with what it used to do.
I have transferred the 'Data' database, the 'Apps' database, Utility.mda and another .MDA. I have also transferred all the Reference executables to one of the other computers. This has not made a jot of improvement.
Has anyone any pointers. I have exhausted all the resources trying to find a solution to this one.
I have a table with an autonumber field, which of course is indexed with no duplicates. Twice in recent days it has attempted to add a record with an autonumber that is not the high number - it is about 20 numbers below the high number. So we get a 'can't add this record' error.
I can fix this by copying the table to a temporary table and then copying it it back. Then the autonumber works correctly.
What can I do to prevent this? By the way we updated to Access 2010 a couple of weeks ago, but the data is still Access 2003. We are reluctant to update the data yet in case it causes more problems.
I couldn't find a similar question already answered and I am stuck at home due to the blizzard here in Denver Colorado so I can't get any other resources to help. I am working on a select query where I am using two table's and another select query to pull information.
table HOLDINGS HASH CGAAP ADJUSTMENT TABLE: - BEGIN DATE - END DATE - SAP - ACCNTNUM - SUM VALUE
table SAP DETAIL TABLE: - Fiscal Year - Company Code - Account Number - SumofAmt CoCd currency - Cabinet
Currently the join properties include: from SAP DETAIL TABLE the Company Code and Account Number to the HOLDINGS HASH QUERY #3 SAP and ACCNTNUM, from HOLDINGS HASH QUERY #3 SAP and ACCNTNUM to the HOLDINGS HASH CGAAP ADJUSTMENT TABLE SAP and ACCNTNUM.
There are three things that I am trying to accomplish with this query. The first is pulling down the SAP and ACCNTNUM fields which works fine. The second is pulling down the SumOfAmt CoCd currency and SumOfSumOfAS_OF_BOOK_VALUE_4TH which matches the SAP and ACCNTNUM fields which works fine.
What I'm trying to do for the third is to only pull the SUM VALUE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE that matches the SAP and ACCNTNUM fields and also where the AS_OF_DATE from the HOLDINGS HASH QUERY #3 is between the BEGIN DATE and END DATE from the HOLDINGS HASH CGAAP ADJUSTMENT TABLE.
This query will then be the output for a report.
In order to accomplish the third task I have tried to complete a SELECT statement and a IIF statement that have not worked. I am sure the select statement would work but I just can't figure it out. Any help would be very very much appreciated.
I work at a Phamaceutical company in the US (based in London) and have a database that tracks atypical events. It has normally about 10 concurrent users and continually (once a week sometimes more sometimes less) corrupts. I have spent a lot of time validating the database writing the user requirements, functioanal and design specs as well as a 150 page IOPQ.
It is a split database with both front and back ends on a file server. I know this is not the best way to do it but the database is frequently updated with requests for new reports etc and I am not sure how to push down the front ends to all the use pcs. The database is about 40 mb.
I like the database. It is fully audit trailed and does what I need it to do, but the corruption thing is driving me batty. I have the jetutils with the viewer to see who is in the db (by pc name) and then I cross reference that to the person in the db and call them to get out. It is a real pain in the arse and I am just about ready to agree with the IT guys that Access is not a multi user database.
I am currently building a holiday planner for my employers and I would like to try and display the days of the year as rows and the staff as columns (Fields). When someone adds a new member of staff, I would like it to also add them to the calendar table as a new column and name the column the same as the individuals name. Is this possible using VBA or SQL string?
If anyone has a better idea as to how I can display the forthcoming holidays for a team I would really appreciate your comments.
Do i have to give a commit statement when i update tables in backend(insert, delete,update) from a form, say when user clicks submit, it takes the data in fields and inserts it into the table , but till now i never did that but i could see all data when i queried the table, but i am not sure if that would be right, if i have to give Commit, how do i do that using VBA?
I've got a table in which there are 20 numeric columns. For one report, I want to add all of the data in those columns together. I used the expression:
Where dailyactivity is the name of the table, and pkgseq01cnt is the name of the first column, etc.
The expression returns the number 961.
If i make a query where i sum all of the columns individually, and then a query based off of that query where i add all of the sumed columns together, i get the number 965. (which, as far as i can tell, is the right number) the expression in the second of those querries is:
where 'seat' is the name of the first query that sums the columns.
I think those two ways should both come up with the same number - but i can't figure out why the first way is missing four! there are thousands of rows so it is hard to go through the table itself. Also, in both of those ways, the querry includes a 'where' column which limits it to rows where another column equals a certain word - but that's relatively straight forward - dont' know why that would make it different.
I'm trying to write a query that will display my data for anything that has a date between 10/01 and 10/15. I went into the "Build" area and looked at the "functions" available but none of them sound right - and the one book I have on Access is not so great.
What I am doing, is I have various products with 5 basic fields. I then wanted to add an array of text fields to 2 of those original 5 basic fields. I attempted to do this with a subdatasheet, however, a subdatasheet is not record specific, is not directed to a specific field, and really does not do anything that I want.
Say that the basic field is ProductDescription. I want one column of fields to correlate to it, like below. ProductDescription Is blue Is large Is heavy Is strong
Each product will have a different amount of varying descriptor fields. Once understand how to incorporate this with my project, I then will need to understand how to add the subdatasheet to a report easily. Thanks for any help, -SethTherrien
I have a table that is a list of available UPC codes that through forms are being assigned to several different tables (types of items). When the UPC code is assigned, there is a yes/no box which is checked. I need to have a message box appear when the last of these UPC codes has been used (or better yet perhaps when there are a specific number left - such as 10 - so the user isn't all of a sudden locked out of any more work).
It could even be triggered when a specific UPC code is reached - they will be in numerical order (ignoring the check digit). Is there anyway to do this globally or would the check need to be on each form as the UPC is assigned?
Hello dear experts! Will this table structure work? - Please help! I need to be able to see if certain members have paid subscription, see if stakeholders have paid for their room bookings, etc. Will it work, does it look ok to you?
Im trying to brush up my normalisation skills and logic.
I have a query that pulls up information on employees when they receive warning notices. I would like the query to give me some type of warning (report, email, etc.) when an employee has three or more notices.
I have changed my approach for my database, and i think a query is what i needed all along. But i am not sure how to go about it.
Is it possible to have query results on a form?
more precise, i have a table full products, alot of information per product (about 30-50 fields)
The amount of actual products isn't many probably around 10, and it's likely to stay at around 10 products.
Can i have a drop down list listing all the products, so it looks these up from the product table, you select the product, that product field value becomes the criterea for the query, and all the associated form fields get filled out to do with the product selected.
To summarise, can i have this all done in a form? so it's easy for staff to simple go the to product drop down list, select a product, all the info relating to the product is then displayed on the form.
I have two tables, categories and items. I have a form that is linked to the categories table and a subform that is linked to the items table. The user selects a category on the main form then an item to add to the category on the subform. (all of this is functioning correctly - but you need to understand for my question.)
Both the form and the subform have a text box that displays a record from their respective table, for the example we will call this color.
What I need to do is create code that checks the color text box from the category (main form) against the text box for the item (subform) and if they are not a match I need to display a message box - from where I can then add the necessary actions.
EDIT: note this is not a query on an entire table or 2 it is just comparing the two text boxes on the form and subform
