I am trying to get a form to display certain information based on the data in another box. I have to input some exam results and want to assign a level to them. So, if they score < 13, the result would read "Below Entry 3" if between 13 and 21 "Entry 3" etc If this was a spreadsheet I would use an if function, but I am not sure that I can do this here
View RepliesI'm trying to understand how data is passed using foreign keys.
I'm using Allen Browne's 'Don't use Yes/No fields to store preferences' at: [URL] ....
I've also downloaded his sample DB, RelationBasics, to use as a guide.
Attached is my version of the Student / Sport DB as described on the webpage.
I use 2010 at home (saved as 2003 version) and 2003 at work (JPNSE OS). Both result in the same thing.
The problem I'm having is I cannot get the actual sports to display in the combo-box, only the Sport_ID number.
I've tried building both forms with and without actual data in the TBL_Student & TBL_Sports tables, but no mater what the result is the same.
I have a searchable form that display information on agreements other companies have with us. If a company requests an inspection we have 30 days to go out and complete it.
I am trying to create a message box that will display all inspections that are due within 5 days when the form loads. No luck so far, only broken dreams.
Is it possible for message boxes to use expressions and display information that fits the criteria?
There is a form which the user fills in 8 fields and saves it. Once saved, a new record opens.
However, I want to modify the save button to save the previous record and display a new record with only a limited no of fields to display from previous record, say 4 fields display from previous record, and the user fills the remaining 4 and saves it. Thus, the save cmd should continue to display 4 fields from the previous record.Already tried below code for save button but it displays all data of previous record as it is, doesn't display blank space for combo.118 and text2
DoCmd.GoToRecord , , acNewRec
DoCmd.GoToRecord , , acPrevious
Combo118.Text = ""
text2.Text=""
I've created a query called "JobHistoryQuery" and a form called "JobHistoryMultiForm", this displays all Jobs related to an Asset and all the information about that job. There are some fields (BilledMonth, BilledYear, BilledValue, Parts Required, PartsUsed), however, that I would only like to see if I need to see them (as there is quite a lot of information on the form and ideally, I'd like to be able to see the key information from each job from that asset with ease). What I would like is to create a button that says "Billing" and "Parts" for each job and it brings up a pop up window displaying that information.
View 11 Replies View RelatedI've established a many to many relationship using a junction table.
So I have 3 tables (A for "materials", B for "batches", and J for "junction")
Form A is linked to table A, and contains a subtable linked to a query from table J. This allows me to input materials into table A and then list all of the batches it may be used in that are in table B. I successfully got this to input all the batches and materials combinations in table J.
Now on form B, which is linked to table B, displays the batch information, with the subtable J.
My problem, is that only the materials primary key is showing, not the other information that should be linked from table A.
Is there a way to display/hide the information on a subform using a checkbox?So, when the checkbox is checked, the fields are blank but when the checkbox is unchecked, the information displays?
View 5 Replies View RelatedI would like to first state that I am new to Access and trying my best to pick up on skills, so pardon if my questions seemed redundant or illogical. I'm trying to create a sort of quick-access/all-in-one Access form that..
1) Displays contact details stored in an excel sheet
2) Opens application on click
3) etc..
I am able to get pointer 2 sorted out, however am having trouble with pointer 1.
I have the contact details stored in an excel sheet, of which I am aware of how to import it into Access. However, my idea was to have a tab where firstly there are 4 buttons, say buttonA to buttonD.
I want it to perform an action such that when i click buttonA, 5 other buttons (say button1 to button5) appear on the right of the 4 existing buttons.
If I press buttonB however, button6 to button10 will appear and replace the button1 to button5 at the same spot.
After which, if I press button1 etc, there will be a field where contact details are displayed (name/number/etc) and it switches as I press different buttons.
I'm trying to figure out a way to get my access database to auto-populate data into a field based on the information I type into the first record.
So what I'm looking to do is that when I type a name into the UserName Field it will automatically fill in the rest of the field with the same information.
To clarify I'm looking for a way so that when I type something into one record it will fill the rest of the field in with that same information.
On my form I would like the user to be able to select a company, and once the company has been selected I would like a box to display all the contact information for the specific company. I would also like this box to record the contact information onto my database along with the other information that will be gathered on the same form.The Contacts and Companies are linked with the COSYSNO and CoSysNo fields.
View 1 Replies View RelatedI'm trying to add hidden information to a record and need to know the best way to do it.
I have a visible table that all users enter data into using a form.
I also have a hidden table that contains 2 fields "linked Table ID" and "notes"
I have a box at the top right of my form which is white (I also have a white background so it's impossible to see).
When you click the box it changes a textbox on my form visible property from false to true.
then I plan to use a separate button that the user currently uses to save a record to store ID number and hidden textbox information in the hidden table but I'm sure how to do this.
Finally I will use a hidden query which will display all the fields from both tables using the linked Table ID from the hidden table and ID field from the non hidden table.
So i have 2 questions:
1 how do I save information the user entered into a form created from one table into a different (hidden) table
2. Is this the right approach to this problem?
Creating a form against a table with the following fields
EC
Project
Originator
Title
Full description
Effectivity
Etc....
Easy enough to create the Project combo box, problem comes with trying to systematically assign the next EC number.
When I select a Project from a combo box I want an EC assigned with format ECXXXX-### where XXXX is the project number from the combo box and ### is a sequential number. ECs should start at -001 for all projects.
Do I need to add an EC suffix field to hold just the ###? Then what?
I created a table to keep contact information such as address, phone number, and email address of instructors. I also modified the New Instructor Entry form to add an email address, since we usually have this when hired.
The problem arises when no such information exists for an instructor. I have attached a "sample" database with all the important components and "sample" data.
When I choose an instructor in which some contact information exists, the Instructor Contact Entry form performs as expected. On the other hand, if no information for an instructor exists, the information can be entered, even though the Employee Number, Family Name and Given Name fields are blank, contrary to the first case. Not only can the information can be "saved" but no data appears in the table.
How do I have to set this up to get it to work for an instructor not in the table?
Issue: We are trying to remove duplicate information for one fiscal year in an ODBC then import and append the correct information from an Excel spreadsheet. The process we have followed is
Copy Master Access file and paste new file (to test, so we do not loose original data)Rename new file TEST.accdbCreate a delete query for Fiscal Year dataDelete information for duplicated Fiscal yearImport correct Fiscal Year information to a new table from ExcelAppend table to master table (ODBC)
Questions:
After being granted permission to delete information, it worked once and now no longer works. When the permissions are reviewed it appears that everything is still granted but the following message is received:
Why does Access change all copies of SDA, even if filenames are different and in different folders?
When we append table, why does it duplicate information?
What I would like to do is create a list box that will only display the information from another form that is related to the current form.So I have a form call Equipment Catalog and that form is related to Equipment features 1 to M relationship and the Equipment Features is related to a Features form M to 1.
So what I want to do is display all the related equipment features in a listbox that is related to the current PK of that form.So if there is only one feature on one form the list box will only display that one item however is there is 6 features on another it will display all 6.I have been trying SQL and Queries but I still can't get it to work.
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.
Hello, I'm trying to use information based on items selected in a combobox in the main form. Once the user selects the desired info in the comboboxes on the main form, I want the subform to display the data that matches the search criteria in the main form. For instance, if I select a job number, I want everything that matches that job number to be displayed the subform (along with the corresponding job name, version, etc.). I also want to get more specific in a search where I could select a job number, job name, and job version from the comboboxes in the main form to where all the data that matches the search criteria in the main form will be displayed in the subform.
Attached is what I have so far. The form I'm trying to perform this in is the SearchByResults form and the subform is the qryk95 form. I'm trying to populate this using the K95-Template table. I performed a query (qryk95) to select only the fields I want to display in my subform. Any help would be much appreciated.
I had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.
The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.
I have a graph chart and my row source type is a Query. So I retrieved data from the Query that I built.
However , I want to get the data from the uploaded excel file.
I have a form that will prompt user in the beginning to upload the excel file and replaced the old table in Access- "CrossSystemData"
Basically , I want to pull data from Excel file , Run the query(Data from excel file) then excute to "display a chart based on my query" ... Is it possible ?
Query SQL: "PolyWrongRegInsCount"
Code:
SELECT GROUPING.INSTITUTION, Count(*) AS NO_OF_GROUP
FROM (SELECT DISTINCT CATEGORY, GROUP_NO, INSTITUTION, IIF(Left(INSTITUTION,1)="P","POLYCLINIC","HOSPITAL") AS INS_TYPE FROM CrossSystemData WHERE INSTITUTION NOT LIKE "*UNKNOWN*") AS GROUPING
WHERE (((GROUPING.CATEGORY)="WRONG REG") AND ((GROUPING.INS_TYPE)="POLYCLINIC"))
GROUP BY GROUPING.INSTITUTION;
My Chart SQL :
Code:
SELECT PolyWrongRegInsCount.[INSTITUTION], PolyWrongRegInsCount.[NO_OF_GROUP]
FROM PolyWrongRegInsCount;
I am using Access 97 and I have the Table and text box formats set to fixed and decimal place is 1. Access appears to be rounding.
Is this normal or id there something I can change?
Thanks for your help.
I designed a simple form that has a listbox and a subform. I am using Northwind database for testing.
I would like to be able to display on a subform only records based on a value of a list box.
I created a listbox using a wizard and selected an option 'find a record on my form based on the value I selected in my list box'. This kind of works but it does not display all records matching criteria.
Attached print screen 1 shows my table and there are six records for 'Las Vegas' but my sub-form displays only three for Karen Toh (print screen 2) and all records for John Edwards are missing. I am not really sure how to fix this problem.
Ideally I would like to take this a step further and do another list box with Last Name and then filter data even further down so for example if I select Last Vegas and Edwards then the form would show me only matching records.
Is there a way to make a form that will display all records based on a single date, at the same time, in the same format each and every time?I have a table which has the following fields:
ID (Autonumber, PK)
ServiceDate
RunningNumber
BonnetNumber
Deallocate (yes/no)
Now, I would quite like to keep the form in a style similar to all the others I have, not least as I have to cater for users of all age and abilities, so keeping things as simple. I have attached an image - each row to represent a record basically, I would like the form to open and show the same layout on each day (I would place the textboxes etc in route groups); a null value would not be allowed for at least one field in each record, I could force the records to populate the form in the same way each day?
I have a form that has 3 combo boxes and a text box that I want to be a lookup. I have a little experience with DLookUp, but I do not think that will work here. The three combo boxes are linked to queries, and Tables. The first combo box is for choosing "Precious" or "Base" metals. The second combo box is filtered from the first and displays the "Metal Names". The third combo box is to choose a "Metal Market". There are currently four options for that. I want the text box to display the "Metal Market Price" based on the second and third combo box selections.
Here is a screenshot of the table with all of the metals and their market prices filled in with dummy values for now:
metals screenshot 1.PNG
Metals screenshot 2.PNG
Here is a screenshot of the relevant Form: Metals form screenshot.PNG
Here is a screenshot of the Table that relates to the relevant form: Metals table design.PNG
Here is a screenshot of the Metal Market table that the Query is based on: Metal Market Table.PNG
I am trying to show an image on a form based on a user choice from a drop down box. For example, the user needs to choose a priority for a task. If the user chooses "Immediate" I want an image of an exclamation point to show up to draw attention that this is important.
View 2 Replies View RelatedI have set up a form and only want to display the date and time that will appear automatically based on when the record was created. I definitely don't know how to do this.
The format that I'm looking for is: 07/24/13 11:45:44pm
From what I can see I would need two separate fields to accomplish this. I would prefer to only use one if possible. I would like to execute the value within the new record as it is entered. That date and time from that point should never change.
I will want to use the date and time in the future for quality control.
i have a subform name(SUBSAL)in this subform i get reocrds for some employee as select combobox Name(CMBEMP).some employee having 2 record or some having 6 record as their transaction
i have sum of transaction at subform footer field but when only 2 records shows in subform it shows a major gap or blank space in subform detail section.