In my database (attached), I need to be able to store multiple notes for tasks performed in various cities -- each note has a small description (more on that at the bottom of this post). For the "San Antonio, TX" task, notes "Heartbleed" and "BAT Scan" apply. From what I understand, this is a many-to-many relationship which will require the use of a junction box. My structure is like this:
tbTasks
TaskID (PK - autonumber)
Task
tbNotes
NotesID (PK - autonumber)
Notes
Details
jcttbTaskNotes
TaskID (FK from tbTasks)
NotesID ( FK from tbNotes)
My mainform (fmTasks) is based off the tbTasks table; my subform (subfmTaskNotes -- continous form) is based off the jcttbTaskNotes junction table. The Master/Child link for the subform control is TaskID. In the subform I have a combo box bound to the NotesID field in the junction table that allows me to choose a note.
My problem: I also have a "Details" text box in the subform that I want to be populated with the description of the Note selected in the combobox mentioned above. For example: if I choose the "Heartbleed" note, then I want that "Details" field to be populated with "Mitigate vulnerability associated with CVE-2014-0160". I assume it has to be somehow tied to the "Details" field of the tbNotes table, but how to implement that. How do I get the Details text box to display the details for each note?
I am fairly new to using access and am currently creating a database for a small bakery. They have new legislation whereby they have to list all their product ingredients and any allergens in the ingredients must be shown in bold.
one to input ingredients and a checkbox if it is an allergen
The other has a main form and subform to get ProductName, Photo and then a combo box in the subform to add ingredients.
Everything appears to be working ok but I have an issue with my subform. The combo box has no duplicates and lists the ingredients in alphabetical order as I require. However, if I view the subform separately from the main form I see that it is storing all the ingredients selected for each product. Therefore it is storing a lot of duplicate information.
I know that you shouldn't store totals in Access, but I can't think of a way around what I want to achieve and wondered if anyone had any ideas.
An item on stock will move from one location to another, I will need to list the 'balances' of this stock at each location, easy enough to do, but there are hundreds of item movements per day and the users do not want a history of movements to be kept but do require an ongoing balance.
e.g. 100 of the item moves from A to B, this movement record gets deleted, how would I know that the item stock at A has been reduced by 100 and B raised by 100?
I am pretty new to access, I will get right to it.
I am scoring rating scales. I want to be able to enter just the responses from a person, say on 80 questions (preferably into a form), where all the answers are 0 to 3 and have Access add the questions into the right groups to calculate all the sub-scores of the rating scale. For example, Scale A may consist of Questions 1, 6, 15, 29, 35, and 70 I need access to add those up and save them so I can use them in a report. I also need access to add the frequency of certain questions that were responded to with the answer 3. I have gotten Access to calculate the scores in a form by adding extra variables to the form, erasing the variable name out of the white box and putting in the syntax =[Q1]+[Q6]+[Q15], etc. in place of a variable name but it just sits there on the form and doesn't store it anywhere. Thanks
Normally I would gather these up into an array using ASP and email the split up comma separated values out to the client via an email.
Code:drinks=Request("drinks")drinks_split=split(drinks,",")'email drinks to client
However, this time I need to store those values in an access database. I am assuming that the client may want to run queries on the different array values, for example, how may people chose coca cola and sprite.
Is it better to store the value separately - one Ms access field for coca cola, one for sprite (which is what I thought I would have to do to create queries on this) - or can they be stored under one field heading "drinks" and queries be performed on different values within that field?
Could someone explain a quick way of doing the latter i possible in MS access and the SQL for querying?
I understand the conventional wisdom of not storing calculated values on the table, but I have a need to do do so. On my Input Screen I have a generated Certificate # derived from 5 single value fields keyed onto the screen. This certificate # is unique to those 5 single-value fields which should not be repeated. I want to store it on my Table as the Primary key with "no duplicates" so that if a keyer keys the same 5 single-digit values , thus creating the same Certificate #, he will get a MsgBox back telling him he cannot add a duplicate entry.
I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?
I have been struggling with this and would appreciate any help. It sounds so simple!!! I have 4 fields (Unit Price 1, Unit Price 2, Unit Price 3, Unit Price 4) in a Orders table that I want to add together and store in a 5th field (Sub Total) in the same table using a form. I am having problems with the sum and I am unsure how to get it to show in the Sub Total box in the form as well as the table. Thanks for your help!!
I am facing an issue with an access table.I have a field named "Contact Dates" , supposedly that field will store in a drop down list all the dates that the contact with the related company was made, how can I store it ? I am using in the current moment a memo data type to store the data and a comma for splitting them. How to create a drop down list for storing and displaying all the recorded dates and recording all the new data values as date format.
how I can set multiple default values in a ComboBox in a form. The ComboBox is based on a table and my main table also uses the ComboBox.
Example:
Main table has a column "Brands". The ComboBox is based on another table which includes the following items (and thus the ComboBox also shows them):
- CocaCola - Fanta - Sprite
The form has the same ComboBox which is linked to the main table.
Now, if a user creates a new form entry, all ComboBox items should be preselected (checked). It does work with one item over the standard "Default" property.
I have created a database that has both English and French forms feeding information into the same table. I'm not concerned if the info stored in the table is stored in French or in English. It all works fine other then the drop down boxes in the forms, here I only seem to be able to either have the English or the French options visible on both the English and the French form. Is there a way to have the English options on the English form and the French options on the French form, both feeding the results into the same field in the table.
1) show the number of records in the subform on the main form 2) count the number of records in the subform where a value [Public] is True 3) count the number of records in the subform where another value [Analyst] is True.
I can achieve the first two by using the following VBA on the Main form current event -
Quote:
Private Sub Form_Current() Dim rst As DAO.Recordset Dim lngCount As Long
[Code]....
when i try to get number 3 done I get the same value as for [Public] (using lngCount for both...not surprising really!)
how i can get a count done for [Analyst] = True in the same event?
I have created a form that contains data from two tables. Each drop down on the form, for each table, contains multipl values. I want to be able to make multiple selections from each down (usually this is holding down the ctrl key and making multiple selections). However; this method does not work in Access. Is there a way to select multiple values at one time in Access similar to holding down the ctrl key?
I am designing a book collection database where each book can contain multiple authors. I used three tables; tblBooks, tblAuthors, tblBookAuthorJOIN. The tblBookAuthorJOIN allows me to create a subform with drop down combo boxes in the Books form linking multiple authors to each books indexed field. The Author table contains the usual FirstName, LastName, MiddleName fields. I would to be able to display the names of each author in the forms header but I am having difficulty.
For example a book may have two authors such as:
Authors table would contain values such as
Last Name: Grisham First Name: John
Last Name: Twain First Name: Mark
Textbox in header would display: John Grisham; Mark Twain
The authors would be displayed in the following format in the same order as listed in the subform datasheet.
I have a database and on the main table one of the columns is "Status". The values in that column can be either "Worked, Unworked, or Overdue".
I would like to be able to create a form so that the user can run a query and select which Status' they want to see. For example maybe they only want to see Unworked and Overdue so they can select those and run the query.
I have a search form that has 8 criteria fields, of which the user can fill (from combo boxes and txt boxes) any number of them to narrow or widen the search.This by the way works perfectly. What I want to have is a message box pop up if the user does not enter any criteria and click search.
I tried stringing multiple if isnull statements together but realised that because I have an onload function that sets all fields to "" the isnull function won't work, so have changed this to the Len function.But when I run it, it just skips straight past the len line and opens the search detail form regardless. I suspect I have the order of things wrong, as it does not throw up any errors when run.
I wanted to create a form where you can select multiple values from the table "years" and on a button it would open a query displaying all the records in "students" in those years selected.
I have a multiple column combo box on my form, that is correctly populating. When I make a selection, it displays the result from the first column. Bound column seems correct, as my table is being populated correctly.
1 - Can I display the values from both columns after the selection has been made?
2 - If not, can I choose to display the second column (not the first) after the user has made a selection?
I have a form where the user will choose a contact name or names from a multivalue combobox and I want the email address field to update with the corresponding emails separated by "; ".
I have a table in which there are approximately 15k records. The name field has approximately 2500 unique values. I need to search the table for multiple names and return the results from the table. This would then be used as a report. Due to the users being very novice, a form is needed for the search.
Example:
Search for Smith, John; Wilson, Bill; Jones, Jeff
Return Name Year Brand Make Smith, John 1999 Oldsmobile 88 Smith, John 2001 Chrysler 300s Wilson, Bill 1994 Porsche Carrera Wilson, Bill 2007 Kia Sporty Jones, Jeff 2004 Chevrolet Camaro Jones, Jeff 2011 Chevrolet Impala
My knowledge of forms is basic, however, I can be a quick learner if on the right track.
I am trying to improve a work process using an existing Access DB.We have a form with multiple texts boxes on it. I need to search through these boxes to determine the total number of occurrences of a specific value. This is not tied to a table.
The text boxes I'd be searching through all have related names: "Element0" to "Element40". And I'd most likely be looking for a "/" within the value in the boxes (value could be 12345/01, for example).Would then be using the result in VBA to apply some conditions, so I would prefer if I could do the count in VBA (the count/sum is the part that is hanging me up.)
I've found multiple ways to accomplish this from a table, but nothing for what I have to work with.I am unclear in my description of what I am trying to do, let me know and I will try to provide more information.
Currently we track areas of non-conformance for a fleet of flight simulators. Each flight simulator has a particular ID number. In some instance an area of non-conformance is associated with a single simulator, at other times it is a fleet wide issue and applies to all or some simulators. In order to track as well as advise leadership and the contractor responsible for maintenance of the simulators of the situation we generate individual response letters.
In order to track each instance of non-conformance my idea was to create a new record for each deficiency. In the event that it is applicable to multiple simulators I would like to fill out the form with all pertinent data and then place a checkbox associated with each simulator and when the record is saved, it creates one record for each simulator with a checkbox ticked.
Once the deficiency on each simulator is fixed, I would check a box for a field called rescinded, which would remove that particular deficiency on that particular simulator from the active list of deficiencies but the others would still remain because they are associated with unique records.
I need to enter workload counts for 10 people, and it is done on a monthly basis. So I have a table of Months (Jan-Dec), a table of names, and a joined table with the months, names and a field for the workload counts.
I would like to make a form where I could select the month and all the names show up so I could go and enter the counts for everyone at the same time. I've attached a diagram to show what it would look like
I have a data base with names, email IDs, phone numbers of 5 types(example quality, security, IT,finance etc) of person from ten different project teams. I created a basic form for the end user to enter values and created fields for each of the person's name, email ID etc based on the personal type( liek quality, IT, finance etc) on each team. The background table contains records of almost 200 people now.
All of these people will be trained for one task and some will be trained for multiple tasks. I know how to make the combo box allow multiple values/selections but, i cannot list out all 200 personal names in the combobox's rowsource/value list. Is it possible to combine all different name fields and list out all personal(of one team), so that the end user can just select multiple names for that team who have been trained and so on. i need to set the rowsource of the combobox to the values of all personal from one team so the team leader can just go select who has been trained and who has not.
Access 2010 - I would like to use DLookup to show results values from a table and display in a unbound textbox on a form. the results from each column in the table need to be on seperate lines, a break if you may. Here is the code I have so far.
