I have 2 tables, master & child. with a one to many relationship.
On one of my forms I want to display some of the fields from the master table and only the last entry from my child table.
How would i accomplish this?
I have a form which I am using child/master fields to create a filter (see attached pic)
For the date ranges I need these to be
>= Start Date and <= Actual Finish Date
How can I set the criteria for these two fields, so that when someone picks a date it knows it's from or before the date entered.
Hello,
I have a form and a subform in MS Access 2003. I have made some changes to database structure, so I decided to change the subform also. When I changed the Link child and link master fields, the controls of the subdatasheet dissapear- they show only in design view. If I clear the contents of Link child and link master fields they appear again, but the records are not binded.
Is there a setting on the parent form that also has to be changed, to make the new binding?
Thanks,
Aleksander
I hv a relationship as follows:
Person 1 ------- N Assignment
Person
(PersonID, lastName, firstName...)
Assignment
(AssignmentID, PersonID, AssignmentDate, Score)
I would like to show the Score of the latest assignment in the Person Form, how can I do it?
Thx in advance!!
I am building a user creation form. I have built it with 3 subforms
Subform 1 is voor general information about the user.
Subform 2 is some more specific information about the user
Subform 3 is more info about the same user.
Now when I create a user (record) in subform 1 it gets the primary key lets say 14. Then when i add information in subform 2. I want it to grab the Primary key 14 from subform 1. So that the information which is inputted in subform 2 will be related to information from subform 1. I get it working if I make a Main form and then put subform 2 and 3 in a subform and then let them grab the primary key from the main form. But how does this work with 3 subforms and no mainform.
How to structure some MS Access 2007 Forms for the following scenario.
My table structure is as follows:
Product
++++++
ID (pk)
ProductName
...
Tag
++++
ID (pk)
TagName
ProductTag
+++++++++
ProductID (fk)
TagID (fk)
I want to have a main form that allows me to search for and displays a list of all products (and the dozen or so attributes for each product) that meet that criteria (I'm thinking a continuous form view of products).
So far so good. No problems.
Now I want a "tagging" form to the right of my continuous forms product list that is linked to the product which currently has focus. The tag panel should always show a complete list of all Tags. I want a checkbox next to each tag that I can switch on and off whether that tag applies to the currently selected Product. I also want to be able to add tags on the fly (without leaving the main form).
I think I have three forms in play :
Main Form
Product Form (subform to Main) my product search criteria is probably on this form header.
Tag Form (also subform to Main)
But with this approach I'm have trouble linking the two sub forms to each other. Actually, I'm having huge trouble figuring out how to display the list of all tags with a check box that when checked on and off creates / deletes a record in the ProductTag table and I really want Access to do as much of the CRUD as possible. I would prefer to avoid writing screeds of SQL INSERTS/DELETES on events. Am I asking too much of Access?
I have a form, frmProjects, with a continuous subform, sfrPeopleOnThisProject. They have a master-child relationship. The form shows one Project record at a time, while the subform shows all the People records for that project. This works as expected - the correct records show up in the subform as you move through the form records.
To make it easier to glance through the list of subform records (as they can sometimes be quite long), I've added a navigation combobox to the header of the subform. If I understand correctly, as the combobox is within the subform, it should obey the master-child relationship: it should only show the subform records associated with the main form's current record. It does not. Instead, the combo box collects *all* of the records from the table that the subform is based on. So, choose the record for Project 1 in the main form. Up pops the 2 people on Project 1, but the combo box lists both the 2 people on Project 1 and the 3 people on Project 2.
The row source for the subform combo box is:
SELECT tblPersonOnProject.PersonOnProjectID, tblPerson.Fullname FROM tblPersonOnProject INNER JOIN tblPerson ON tblPersonOnProject.PersonFK=tblPerson.PersonID ORDER BY Fullname;
I can make the combo box perform as required by adding "WHERE Project=Forms!frmProject!ProjectID" to the row source and placing a hidden textbox on the main form for ProjectID (the autogenerated primary key) to sit. Have I misunderstood the point of the master-child relationship in the subform? This hidden textbox thing can't be correct.
I'm having problems with an Update query. I have data coming in on one file that I am loading to a temp table that I want to update on another table in my database. The problem is that when I run my update query, it is wiping out everything that DOESN'T match. I have 5 fields that MUST be equal before the update can occur. I'm trying to update 3 fields in my "master" table.
Here is my SQL:
UPDATE NPPDcopy LEFT JOIN tempNPPD ON (NPPDcopy.Hour_Ending = tempNPPD.Hour_Ending) AND (NPPDcopy.TieLineName = tempNPPD.TieLineName) AND (NPPDcopy.AdjBalancingAuthority = tempNPPD.AdjBalancingAuthority) AND (NPPDcopy.BalancingAuthority = tempNPPD.BalancingAuthority) AND (NPPDcopy.OperatingDay = tempNPPD.OperatingDay) SET NPPDcopy.Import = [tempNPPD].[Import], NPPDcopy.Export = [tempNPPD].[Export], NPPDcopy.Net = [tempNPPD].[Net];
I'm trying to build a form based on a table where the user can also enter data to update the table but with data fields not originally in the table. Below is a summary of what I have and what I need:
1.) A file that will be uploaded daily into a Table (ie name Denials extract table).
2.) A form bound to table Denials extract
3.) Additional fields that will be updated by users that is not included in this table b/c they are not available in the system we are getting the data extract from.
4.) The user will look up a key field that will allow them to review it in our system. Once they "work" the record, there will be multiple fields that they will update with this information that are not in the bound recordsource (table Denials extract).
Do I create another table with these fields even though they will be blank at first and make them a subform that will have a "primary key field" that will link the main form with the subform? Will they need to "update" a whole new table? Not really sure how that would work since they will be updating fields not in the bound table.
I have a from which asks for a date, then displays the records from a table that match that date. On that form, I can make changes. Then I hit a recalculate button which has an EVENT which attempts to manipulate those field names from the table, BUT those field names in a debug.print are coming up blank. How do I get the values from the form/Table to be available to the Event? Probably simple, I'm just not sure how.
View 1 Replies View RelatedHI everyone, I have a Sales Invoice form with a Receipts subform showing what receipts have been lodged against that invoice. It works fine as long as there is one receipt or more.
The problem is when no receipts have been lodged and there are no receipts to show in the subform. When I open the Sales invoice form I get a "you entered an expression with no value" error, then the Sales invoice form opens. I tried suppressing the warning with DoCmd.SetWarning False, but that hasn't worked.
Anyone know a workaround?
Hi,
We all know we're not supposed to duplicate data right? So was wondering how people deal with the deletion of Master records.
For instance let's say I have 3 tables.
tblSales
tblSalesSub
tblProducts
tblSales - lists the sales summary - stuff like SaleId, SaleDate, SaleTotal
tblSalesSub - lists the products contained within the sale.
tblProducts - is the Master list of products storing the product specifics
Now to prevent duplication of data I should just store the productId in tblSalesSub then reference that to tblProducts with an innner JOIN of some kind.
But what if the Master record gets deleted? The reference is broken and NULL values are returned.
How do you deal with this?
Don't allow deletion of these records?But just hide them when deleted?
Duplicate data by storing the product title and description within tblSalesSub?
Tell the user - tough cheese you shouldn't have deleted it should you?
Any other ideas?
Dan
When trying to link parent to child in master and subform, I am getting a pop-up:
Subform Field Linker
"Object variable or With block variable not set"
I have an unbound new contact data entry form that adds records to many different tables. I have no problems adding one record to a child table at a time, but one of the things we are collecting is a list of online networks a person belongs to, and I want to be able to stick a "check all that apply box" on my form and have it add multiple records to the child table. Here's the basic set up, I have a parent table with the main contact information with a primary key field "IID", a lookup table with "Facebook, LinkedIn, Twitter..." etc in it with a primary key field "online_id", and a bridge table to link the two which should have multiple entries for IID, one for each online_id. I want to use a listbox (or something like it) that the user can select multiple online networks and then have records added to the bridge table. I can figure out how to add the listbox on the form, what I can't figure out is how to get the values out of the listbox. T
View 13 Replies View RelatedI have a form, a subform and a master unique table. I need the unique table to updated in the following manner: Form has certain fields that need to be assigned to every record created in the subform[id] and [info1] Subform has a unique [caseid], and contains further [info2] but needs to be bound into [id] in the main form. Example of the master unique table:
id info1 caseid info2
1 aaa 1001 asd
1 aaa 1002 dfg
2 bbb 1003 fff
3 ccc 1004 ffg
3 ccc 1005 ggh
I've managed to accomplish this with a linked table&subform structure, but the master unique table looks like crap because it collapses the subform values into sub-records in the master table when the [id] is similar. The data is in the rigth place, i just want it not to be collapsed/expanded, but every unique recors shows in the table in the regular format
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...
*Child_ID
Parent_First_Name
*Primary/Secondary/Other
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click()
Dim strSQL As String
Dim RevisionDate As String
Dim RevisionRevisedBy As String
Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
I'm Access 2010 newbie. I need to transfer Excel program into Access.I have a .csv file (data extracted from emails) and a Master Excel sheet. Master Excel fields are updated from the .csv - if the primary keys match, else the new records are added. Also, the .csv contains colour names, which must be translated into corresponding peoples' names.
View 3 Replies View RelatedI am working on creating an access database for tracking physical assets linked to locations. I need to make a combo box list to show items other than the current location of the asset. Basically I need it to refer to last enery of the user and define the new possible entries. so we have a unique relationship between location and asset. The assets and location will always remain fixed and there is never going to be any addition. I am creating a web form so that it can be uploaded into sharepoint.
View 3 Replies View RelatedI have a simple table called CUSTOMER with contact details on there...First NameSurnameDate of BirthAddress 1Address 2Address 3Town/CityCountyPost CodeemailetcI have a second table called TOWN_COUNTY_REGION that lists all the towns and cities in the UK. Each Town/City is listed with its corresponding County and Region. This table has 3 fields...Town/CityCountyRegionThe TOWN field in the CUSTOMER table is populated by the user selecting from a list of towns in the TOWN_COUNTY_REGION table. When a user clicks the town that they are in, I would like the appropriate COUNTY and REGION fields to be automatically populated in the CUSTOMER table. So, for example, if the user clicks LIVERPOOL from the list of towns and cities, Merseyside and Northwest should automatically be populated in the other 2 fields.How do I do this ??Many thanksAllan
View 13 Replies View RelatedA SELECT works:
SELECT *
from mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
But none of these do:
DELETE
FROM mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
**Incorrect syntax near the keyword 'INNER'.**
DELETE
FROM mastertable JOIN secondarytable
ON (mastertable.Identicalfield = secondarytable.Identicalfield)
**Incorrect syntax near the keyword 'JOIN'.**
DELETE
FROM mastertable
WHERE (mastertable.Identicalfield = secondarytable.Identicalfield)
**The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**
DELETE
FROM mastertable
WHERE IN (SELECT *
FROM secondarytable)
**Incorrect syntax near the keyword 'IN'.**
Can anyone help? This should be easy, shouldn't it?!
Thank you.
I have a form that I am working on.
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox).
:confused:
I have two tables that are formatted identically....
Table1 = MasterTable
Table2 = TempTable
I am taking a copy of one of the entry from the master table and Copying it to the temp table.I then open a form on the TempTable that enables the user to modify the content without affecting the information in the MasterTable.On Completion and Save I wish to take this modified content and update the MasterTable Using the Field "ID"
the tables for example look like this
ID NAME AGE DATE1 DATE2
I am guessing I need to use a
Code:
DoCmd.RunSQL UPDATE "MasterTable" WHERE "TempTable"
Type of command but not to sure on the correct syntax and as everything is the same the use of wildcards for all fields