Forms :: Implement Form For Many-to-many Relationship?
May 5, 2014
how to best implement a form for this very simple database (attached). I have a database that keeps track of just first names and colors. Not every first name needs a color assigned to it, and vice versa.
I want my continuous form (fmFirstColor) to show all first name records and have a drop-down to assign a color for each record individually. That's it -- two fields. With this form, I want to be able to create new first name records with or without colors assigned to them, and I want to be able to edit first names and change what colors are assigned for each name.
Again, very simple and small database, but I'm not sure where to run with this. I want to keep everything normalized and within best practice procedures. Is the junction table necessary or can it even be used in this instance?
I have a relationship between two tables; the parent table is "Client Information" with a primary key of "Client_ID", the child table is "Event Information" and the foreign key is "Client_ID".
I know that within the Event Information table I can select which Client_ID to use as the link. I want to have that done within a form, however. I have a form for Client Information with a subform for Event Information. If I go into the table and manually set the Client_ID then everything displays fine.
How can I set the Client_ID in the subform to automatically match the Client_ID in the main form? I can only assume that these solutions require the parent/child relationship to already have been established.
I've been tasked with creating an Access database..We have a main table that includes information found on every lease, however there are 3 leases types. Each which lead to another table or two. Basically, I want to create a form that allows me to link the main table with the corresponding variable tables.
I have a Splash Screen type deal that loads after the user logs in I wanted to know if there was a way that I could add a Loading Bar that would say different things like Are you having a nice day?
I have a main form (A) with a subform that is another form with a datasheet(A_SUB)
On the form A is an undo button which rewinds any changes that occur to A that I do not wish to commit. However when I make a change to A_SUB the changes are not rewound. I considered implementing an UNDO for A_SUB but this is messy. What would be a better solution?
1.st Job: I have an access 2003 db. I want to implement barcode system to the DB in which I can print barcodes in any kind of barcode printer and also when imputing an order read data from barcode scanner etc.
2nd Job: I want to put access db to a server so I can view reports, forms, imput data and retrive data from internet explorer window (with a password).
I will give my DB to you so you can work on it. Please do the pricing seperate for each job.
Please PM or Reply if you have more than 500 posts. PLEASE RESPOND IF YOU REALLY KNOW HOW TO DO IT.
I'd like to know how to implement a pop-reminder that runs reminding the user of the database of :
1. some to-do actions for the current day or, for example, a week ahead of a certain event, that is related to some date previously entered in a table.
2. this should be within the access database.
3. I saw something like that in the [URL]... website, but actually couldn't understand it.
I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.
SELECT "rubriek, SUM(verkoopprijs) as prijzen FROM voorwerp (inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp) inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau where rubriek in (select rubrieknummer from rubriek where rubriek = variable name ) group by rubriek"
Do I have to treat it as a normal select query or is there something speical that I have to do ?
is there a way i can use substitution variables in access 2007, i wrote a query that returns rows which have a word okeu in them but i want to use substitution variables so that i can retrieve any word i want and if the word i want is not there i want to pop a message saying word not there its easier to do it in oracle sqlplus but how can i apply it in access using sql queries? This is my sql i made in access
Code:
SELECT MENU.NAME_REC, MENU.C_REC, AUTO.C_GROUP FROM MENU, AUTO WHERE (((MENU.NAME_REC)="okeu"));
Code: ID studentname lectureplace lecturesubject 1 jack A Biology 2 steve A politics 3 jack B math 4 steve A math 5 jack B politics 6 joe A politics . . . . . . . . 154 Jane B Geography
We need a query to implement two criteria to extract the following records:
1-Show lectureplace A AND politics lecturesubject 2-Show only those with lectureplace B regardless of the subject
In case the query is run we will have the following result:
Code: ID studentname lectureplace lecturesubject 2 steve A politics 3 jack B math 5 jack B politics 6 joe A politics
My problem is I need to make it easy for my users by authenticating themselves once when they open the database. User-level security is adequate for that job, but when I need to use the authentication information for connection string, I'm a bit antsy about storing it somewhere in Access, as I understand that passwords shouldn't be stored in the database itself (and as far as I can tell, it's not even encrypted or hashed!)
I am wondering if converting it to MDE would be sufficient to encrypt the password or will it still be apparent using a hex editor? I also know that Jet can try to use its own security to authenicate to the back-end, but not sure what I need to do to prompt Jet to pass along the credentials.
Alternatively, would it be better to somehow implement login using backend's security model, if even possible?
I'm creating an equipment hire database, and I was wondering how I would not permit overlaps of equipment hire ie. equipment being borrowed before it is returned. I have a query that lists all bookings that overlap, but I am unsure of how to implement this into the table to prevent overlaps.
I have 2 tables: TblCustomers (one) [field: MblNo; CustName] and TblDetails (many) [field: MblNo; TalkPlan; BillingAddress] with (1 isto many) relationship. The TblCustomers has primary key [MblNo] that is foreign key in TblDetails.
They have 2 bound forms frmTblCustomers and frmTblDetails. Now frmTblDetails is inserted as subform in the frmTblCustomers.
I would like to keep on adding [MblNo; CustName] in TblCustomers and customer details in TblDetails (ie Talkplan, etc). Now, if I try to enter an already entered [MblNo] in TblCustomers it will not allow and generate error. But a customer may appear more than once in TblDetails (as he might keep on changing his TalkPlan or BillingAddress).
Any idea on how should the forms be built so that whenever I attempt to add an already entered MblNo in TblCustomers it will alert me that "the MblNo that you are attempting is Duplicate" and the focus of the control will be set to frmTblDetails so that I can contnue adding details in the [TalkPlan]; [BillingAddress]. Any help is appreciated. Thanks. Prodigy...
I have started a new project where I have tables of people, organization, and roles, as well as one that is supposed to contain the relationships among them. In all four tables there is an AutoNumber ID field as the primary key. The fourth table, the relationship table, has as foreign keys, the primary keys from the first three.
I have created a form with a combo box for each of the three main tables. The intent is for the user to select a person, an organization, and a role, and then click on a button control that will create the relationship in the relationship table. What I am trying to do in the code is, using an AfterUpdate event procedure for each of the combo boxes, open the appropriate table as a recordset, find the selected record, and set a form-local variable to the value of the ID field for that record. So far, I can't get past the first combo box. Here is the code I have written, including both the form variable declarations and the AfterUpdate code:
Option Compare Database Dim personID As Integer Dim orgID As Integer Dim roleID As Integer Dim rsPerson As Recordset Dim rsOrg As Recordset Dim rsRole As Recordset Dim rsRel As Recordset
[code]...
The error message I get is Compile error: Object required, with the personID reference in the last statement highlighted. (qryPerson is a query based on tblPeople that combines first and last names into LNFN.)
I have a Workorder Form that holds all the fields that apply to the entire project (payment, customer, etc).
Then I have a subform for Stock. This allows there to be several different stock items (and production information relevant to that stock) assigned to a Workorder.
I have successfully linked the two so that when a Workorder is opened, it shows any related Stock in the subform that has already been created.
The problem is new stock entries. I need it to only allow Stock additions to the current open related Workorder.
I currently have it where you can type in the WorkorderID (autonumber) on the Stock subform new entry and it will work. But I do not trust employees to type in the correct number every time they need to add stock to a Workorder. I can't release it this way.
I mean, the whole point is for the database to promote as little error as possible, right?
I am creating a database for a claims management company. They want a system where one customer can have multiple claims assigned to them. So as far as i know this means i need one table for the customer and all their details (tblCustomer), and another for the case (tblCase). I need tblCase and tblCustomer to have a many to one relationship....with referential integrity?
Ideally i would like to search to find a customer then have a very brief list of the claims they've had. with each claim being clickable for full info/ editing.
Customer Table > Customer ID (pk) 1 Issues Table > Customer ID (FK) Many Complaints Table > Customer ID (FK) Many
Each table has Customer ID as you can see, I have linked the Customer ID From Issues & Complaints table to the Customer ID field to the Customer Table using a 1 to Many relationship.
Am creating a form on the customers table so an admin can add a customer issue against the customer this is working fine, The trouble is when I link the Complaints table as well. its asking for subdatasheets.
I have a main form (stud_details) also I have a subform called (pay_det_sub).The subform is displayed on the main form. I want to create a relationship between the stud_details and pay_det_sub, with the aid of a field on the stud_details called numhus (which is the primary key).
So that when a student with a specific numhus value is displayed it brings about the payers details on the subform.
What's the best way of doing it??? Is it necessary to have numhus value stored also in the subform table to create a relationship....
I have a "Main" form called frmProjects that sports multiple tabs. One of those tabs is labelled "Milestones", into which I inserted a datasheet subform called frmSubMilestones. The table that feeds the subform is tblMilestones. I established the parent / child relationship between frmProject and frmSubMilestones, and everything is working just fine...
To summarize: frmProject as main form -> Milestones Tab on main form -> frmSubMilestones as datasheet subform on the Milestones Tab (there are other tabs that are not subforms).
I have been asked, for each milestone in the frmSubMilestones subform's datasheet, to capture the number of days spent by employees on a monthly basis. The Milestones table and the PersonDays table have a primary key and a foreign key that are similar.
To summarize: frmSubMilestones as subform -> txtAggregatePersonDays as calculated Textbox fetching data from tblPersonDays -> OnClick event -> FormLoad of frmPersonDays as datasheet PopUp form.
DoCmd.FormLoad is supplied with the usual parameters to make the PopUp form appear, filtered on the currently highlighted milestone. Everything works fine so far... well almost.
Problem 1: When I introduce new rows in the frmPersonsDays datasheet, everything seems fine at the surface, BUT the primary key of the calling form (i.e. frmSubMilestones) does not appear in the tblPersonDays table; this is normal because there is no form-based parent / child relationship between the two forms. As one might expect, I am trying, from frmPersonDays, to get the primary key value from the "parent" frmSubMilestones using the Forms collection. No dice. I surfed and surfed, tried the bang and the dot operators, drank scotch, but nothing worked. I had to add foreing keys manually in tblPersonDays to further my tests. I can't figure out how to reach any control on the calling form, which is a subform.
Problem 2: The calculated txtAggregatePersonDays on the frmSubMilestones works wonders for existing data in tblPersonDays. However, when I introduce new rows in the PopUp form, I also realized that the calculated Textbox in frmSubMilestones is not updated when I close the frmPersonDays PopUp. I have to close the PopUp form and move the cursor in the Main form (frmProjects) to refresh its associated milestones.
I am creating an access database for recording calls and follow ups for a company
relevant tables mst_customers card_call
ID from customers and customer_id from card_call have a one to many with all records of card_call
Although its more elaborate than this, lets get down to the problem
I created a form with a subform that should basically allow selection of customer and based on this, the creation of records in card_call as per customer selected. form and subform are linked by customer_id
Everything is working except a peculiar issue where if i select a customer_id it shows all relevant records + an additional record (usually 1 or 2) that just changes the customer_id for that record.
Example
customer_ID = 2 (combobox bound to column1 and showing name/column2)
output call_ID | Customer_ID 1 | 2 2 | 2 3| 2
changing customer_id to 4 will change to 1 | 4 (new) |
I am wandering if anyone can help me here. I have a form which is linked to a table called tbl_File. Within in this form & tbl_File there is a field called Country_Code. In the Country_Code field the user places a number that corresponds to their country they want.
Now Country_Code & the corresponding country is defined in a seperate table called tbl_CountryCodes.
What I would like to do is in my form once the user has inputed a number in the Country_Code field (it will be a combo box), a label or textbox in the SAME form will then do a search of the corresponding Country name in tbl_CountryCodes and then display it to the user.
I used the Control Wizard to create a macro on a command button to do record navigation i.e. go to First, Next, Previous and last record. The macro code, in the macro editor, is inserted on a command button on the form, asks whether it is a table, query form etc. I chose form and entered the form name but the error says the form needs to be open before I can run the macro. This happens when I open the form from a Navigation Control and Click the button containing the Macro code on the form.
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.
