Data Entry Problem To Related Problems?
Sep 15, 2005
Hi there,
We have designed a database which has six tables all joined together through one master table by one-to-one relationships, We have designed forms based on these related tables too.
However, it will let us type in any data to any field in one table but when we try to add data to another related table it comes up with the following message:
"you cannot add or change a record because a related record is required in table "xxxx".
This happens no matter where we try to enter data.
Referential integrity is set as are the cascade options,
Please help as this is an urgetn issue for us
Thanks
S
View Replies
ADVERTISEMENT
Jul 24, 2006
I need help. I am not sure if this is a form issue or a logic issue i have a db for tracking students the classes they take and their notetakers for classes. Each class is most likely unique to each student but sometime there are multiple students in each class that need a notetaker and there should only be one notetaker per class. I have 3 tables and they are all linked through a unique iD called CRN (number).
I am looking for a way to add a student to a class and create a class at the same time in one form. I am trying to limit the redunancy by adding a class each time and then adding a student to the class.
Any suggestions would be great. I have gone through 10+ redesign and am just at a loss on how to accomplish this.
View 1 Replies
View Related
Sep 5, 2006
Hello,
I have a form based on 1 table and I am trying to find out if I can display data from another table if I have a relationship between the 2 tables?
Thanks
View 1 Replies
View Related
Jun 30, 2005
Hi,
I've searched but cant find the answer,
I have an Account form (Form1) and it displays account information along with their many addresses for the account (in a datagrid embedded form). I hit "update or add address" button to launch form2 (Address Form). From this point I want to query the Address and AddressAccountLink table with a passed in Global Variable (AccountID) from Form 1 so that just the Addresses pertaining to that Account show up, instead of all the Addresses for all accounts.
How do I do this? I've tried a String Query in the Private Sub Form_Load() but it gives me errors, here that is:
sSQLQueryAddr = "SELECT tblLinkAccAdd.AccountID, tblLinkAccAdd.AddressID, tblAddress.* FROM tblAddress INNER JOIN tblLinkAccAdd ON tblAddress.AddressID = tblLinkAccAdd.AddressID WHERE (((tblLinkAccAdd.AccountID)=" & v_accountid & "))" (it's on one line in the code)
followed by:
DoCmd.SetWarnings False
DoCmd.RunSQL sSQLQueryAddr
DoCmd.SetWarnings True
Any ideas? Am I on the right track? Do I move this query elsewhere? The address form is made from a query -- querying the Address and address/account link table.
Oh, the v_accountid does have the correct value passed to it...
Please help , I'm going nuts here!! :confused:
Mike
View 12 Replies
View Related
Dec 11, 2004
I want to insert a huge number of data (Customer and their adress) into 2 related tables in Access. Each table in access is related by some kind of ID (Autonumber-Primary key). The first table is CUSTOMER , the other one is CUSTOMER_ADRESS . Access relate each Customer to his Adress, so if I import data into one table and then import data into another table how would the database know that all the data pertains to the same person. How to insert the new list of customer and adress without using a form?
Thanks for your help.
View 6 Replies
View Related
Nov 30, 2005
Hi!
I have a table with several fields. I would like to have a lookup in one field (combo list - get data from another table field) and for 2 other related field have the coressponding data appear automatically according to the selection in the lookup field.
eg.
Table1
|Field1...................|Field2...............|F ield3...............|
|LookupField1Table2 |AutoField2Table2|AutoField3Table2|
Table2
Field1...|Field2... |Field3...|
Data1...|Data2...|Data3...|
I am not quite sure if my question is clear... I had trouble in describing it in my post...
View 2 Replies
View Related
Oct 18, 2011
I am new to access 2010. I need to change the primary key in a table ("stations", Primary key is "station number"), because future entries will result in double entries for this primary key.
I have another table ("species&stations") which has records of Species for each "station number".
So I created a new Primary key for the "stations" table, now called "species id", being a bit better in defining a unique station (as it contains the year and the species number, i.e. "451_2010")
Now the table "species&stations" references/(is in relationship with) the old primary key, identifying a station number for each record.
How do I get it to recognise the new primary key from the "stations" table, and keeping the records (over 1000...) with all there info.
Approach so far: I tried to create a new field in "species&stations", which should look up the new primary key from "stations" and then autofill the field. i did not manage this though
I attached a screenshot to make the relationships more clear!
View 2 Replies
View Related
Oct 10, 2012
I have 2 tables one, Contacts, primary key ContactID this table contains names, addresses etc and one group called Form primary key Form ID, foreign Key Contact ID. I want to store the related contact ID in my form but display firstname ad surname from Contaacts list. This works fine when I am selecting name. List box shows 2 columns with correct data but when I recall the form after making other entries it only displays the firstname. The correct ContactID is stored. Why does this happen.
If I can get this working I would like to concatenate the Firstname and surname to make it more readable but don't know where to begin.
View 3 Replies
View Related
May 22, 2006
Hi all, I'm so tired of looking... please help me!!
I have an Access Data Access Page based on a query which is not updatable.
The query is based on a many-to-many table (e.g. OfficeProducts with foreign keys for OfficeID and ProductID and an Amount field). The query is not updatable as I have included outer joins to the Office and Product tables to get all their records back.
(I'm not sure if I've done this the best way, but need to be able to list all the possible Products for each Office whether they use them or not.)
I want the user to be able to update the Amount field, so e.g. if it is currently set to 50 to be able to change it to 40, or if it is currently null (as there is no record so far for that OfficeProduct) then to insert a record into OfficeProducts with the OfficeID, ProductID and Amount value entered.
I thought this would be straightforward by creating a new textbox in the same section as the current Amount field, calling it NewAmount and letting the user update this, fire an event trigger to either update or insert into the table, and refresh the query and record on the form/DAP so that Amount shows the newly updated/inserted value.
I have been searching for ages... cannot locate either the best event or events to use for this nor the code to enter!! Please please help!!!!!
(This is actually for a charity helping people suffering from emergencies in developing countries, not OfficeProducts, so your spot in heaven will be reserved if you can help ;) Thanks)
Jen
View 4 Replies
View Related
Sep 12, 2013
I have a form within my database in which the user will enter data which will go into 2 separate tables. These 2 tables (Job and Client) are related. At the moment I have a subform in which the user enters Job information, and the main form where the user will enter client information.
The problem being is that the 2 sets of data do not associate themselves with each other, despite being related (The Client will be related to a job number. A client can have many jobs but a job can only have one client etc). It has to be done manually in the table which is not ideal as the DB will be split and rolled out to users via Access Runtime. I have been working on this DB for a while now and the problem is most likely right in front of me but I cannot see it!
View 4 Replies
View Related
Aug 19, 2013
I have a form based on a table which includes the mid field. I want to have a macro that takes the value of the current mid, and makes a new record in a 1-many related table (consisting of record id (auto), mid and trmntdate), paste the mid and insert the current date.For the life of me I cannot get it to work? The process should be something like:copy mid value, add new record to related table, paste value in mid, insert current date in trmntdate, save. I've tried append queries, experimented with copy etc, dabbled blindly with VBA and not got anywhere.
View 14 Replies
View Related
Mar 26, 2014
I would like to create a report that would really impress my supervisors, i just started at the company. I'm trying to create a call action plan, so i'm recording clients information on one table, and meetings we have had with each respective client on another table. Some clients will have multiple meetings, some few, and some none. I have a relationship set between them from the client's id number on the client table to the ClientID on the meetings table. one to many.
When I go to create the report, only the clients with meetings show up on the report, I would like client info to always show up on the report and meeting info to only show up under each respective client when it exists. I have worked out how to shrink and hide any text box without any info on it. It just seems like the existence of a meeting dictates where the client will show up at all in the report.
View 1 Replies
View Related
Mar 3, 2014
I have a database I made to store a list of users and information about each user.
I have a UserDetail table, languages table, previous experience table, current experience table.
UserDetail table as follows:
UserID (PK)
First Name
Last Name
Full Name (calculated)
Department (using a multi select combo box. There are 3 departments and some people are in both).
I need to do the same thing on each table and each table is very similar so I'll just list one. This is the Previous Experience table:
PreviousXPID (PK)
Previous experience (e.g. IT, Marketing, Chemistry)
I have three junction tables. One for each Previous Experience, current experience and language table. The Previous experience junction table has:
ID (PK)
UserID
PreviousXPID
I created a relationship between the userdetail table and the junction table then the junction table to the Previous Experience table.
Now what I'd like to be able to do is edit people to add previous experience, current experience and languages to each. I already have a form that lets me add a new user to the database. It's just based on the UserDetails table. If I could have a way (perhaps using 3 multi select list boxes. One box for previous experience, one for current experience and one for languages) on that form to add the other details to that person, that would be ideal. I'd like to create a new user. E.g. Joe Bloggs in department 1 who speaks Dutch, used to have Marketing, Chemistry and aeronautical experience and now works in IT.
I can create the new user by putting last first/last name and selecting the department. But to add experience or languages, I have to go into that junction table and add the numbers myself. e.g. user 1, has experience 1,3,4 and 5. I'd like to see the name that relates to the ID and be able to select it from the list in the table I have.
View 11 Replies
View Related
Mar 9, 2006
First I would like to give thanks to all the knowledgeable folks here who have helped me with my DB to date. It is working and every one is very happy and I have learned a lot.
So now I would like to add some more functionality to this existing project.
My DB is for data input of customers for a drawing. It has the following fields: Id, account number, first name, last name, date/time, score1, score2.
I t is taking a great deal of time for the users to enter in hundreds of entries a day. Most of the entries are customers who are already in the DB. I would like to get the fields to auto fill the data for existing customers say after the account number is entered. So after you put in the account the name and any other pertinent data would shows up saving users from typing it in again.
The first problem I am having is that this is still a data entry form and I can’t figure out how to be able to see the account information and still add new data to the record? The new data is a daily score they get.
Second I haven’t figured out how to call up the customers information from just the account field.
I’ve googled this and haven’t found anything terribly helpful.
View 2 Replies
View Related
Mar 5, 2015
I an trying to create a data entry form (IndividualsEntryFm) to input data for fields such as (First Name),(Birthdate) etc., these to be saved to the (IndividualsTbl)
I also have another table (NamesTbl) which has family names etc. The two tables are linked by a (MainID) field. I want a combo box on the individualsEntryFm so that I can select the family name. Then I wish the empty fields for the IndividualsTbl to be available to enter data.When I press the save button I then want this data saved, together with the MainID from the combo box to the IndividualsTbl.
I have set the IndividualsTbl with a (PersonID) field as an auto number each individual therefore has a unique PersonID but may well share the MainID. I'm trying to link many people to the same address.
View 1 Replies
View Related
Feb 9, 2015
I want to use the same form in datasheet mode for data entry and retrieval. When retrieving, all controls are disabled and locked. I am trying to enable and unlock them for modifying but that isn't working.
<code>
DoCmd.OpenForm "PO_Practice Data", , , , acFormEdit, acHidden
Forms![PO_Practice Data]!PO_Name.Enabled = True
Forms![PO_Practice Data]!PO_Name.Locked = False
Forms![PO_Practice Data]!Practice_Name.Enabled = True
Forms![PO_Practice Data]!Practice_Name.Locked = False
[code]...
View 3 Replies
View Related
Dec 18, 2014
I am trying to save specific data from a data entry form to a specific depending on what the user selects from a dropdown/combo box.
So if the user selects "test company from the combo box, that data (from that form) will save to the Test company data table.
View 5 Replies
View Related
Feb 18, 2015
My aim is to have my forms open to a new record, which I have done, but if my users need to then update or edit data in previous entries, they can click a button that allows this.
My thoughts were to add a button, then put in code so that the necessary properties changed the form from displaying a new record or records entered since opening it, to showing all records in the associated table....
As an example there is a table for purchase orders. When clicking on this from the main menu form, it opens up the purchase order form to create a brand new PO. At times though, we will need to revisit an order to attach a copy of an invoice, or update the cost of whatever was purchased.
View 5 Replies
View Related
Jan 5, 2013
My question is this: I have a table where I'm entering employees' hours worked. Basically, it's something like this:
ID WorkerNumberDateworkedTimeStartTimeEnded
121/2/201310:00:00 AM3:00:00 PM
221/3/20132:00:00 AM11:00:00 AM
321/4/201312:15:00 AM11:30:00 AM
421/5/201310:25:00 PM11:00:00 AM
531/2/201311:00:00 AM3:30:00 PM
631/3/201312:00:00 PM10:00:00 PM
731/10/20137:00:00 AM4:00:00 PM
I have a query that (easily) determines how many hours an employee has worked on any given day. What I can't figure out at all, is how to write a query that can figure out how much time an employee had off in between shifts.
Thus far I'm able to run a query that separates this main table into individual workers by their id numbers, but can't figure out how to determine time off between shifts - as the last hour worked one day, and the first hour worked the next day are on two different lines (they are two different table entries).
View 4 Replies
View Related
Sep 28, 2014
My db has multiple linked tables. As time passes and work on a project continues data needs to be added to a separate table that is linked to the first table to be populated.
i.e., Initial design data is entered first, when drilling is complete more data is added to a linked table, when drilling is surveyed yet more data is added to the second table.
What I want to be able to do is to query for a particular set of existing design data, display this on a form and on the same line enable data entry to other fields from other tables which are linked to the existing design data.
View 7 Replies
View Related
Mar 5, 2008
I have a shared db where there is a requirement for more than 1 user to enter data in the same form and hence the same table. Up till now there has only been 1 data entry clerk so there has not been a problem.
Would accessing the same form by different users cause a problem, assumming they are inputting different records?
View 9 Replies
View Related
Jul 6, 2005
This has most definitely been asked before but I really cant find anything on the forum! Ive created a data entry form which has a save button which the user must click each time to save the record. However how do I refresh the form i.e. empty all data from the previous record without exiting the form. Ive tried to combine the undo macro but that simply deletes the previous record. Any help please!!!
View 3 Replies
View Related
Jul 4, 2014
I have a form with a tabbed control on it. The tabbed control has 4 tabs that each have a report on them. When I select a company name on the main form, I requery these 4 reports to show data related to that company.
For some strange reason, 2 of my reports requery with no problem, and 2 of them give me a message that the report can't be found. Here is the code:
Code:
Private Sub cboCompanyName_AfterUpdate()
On Error GoTo cboCompanyName_AfterUpdate_Err
' DoCmd.SearchForRecord , "", acFirst, "[CompanyID] = " & str(Nz(Screen.ActiveControl, 0))
' After selecting a company, requery the subreports to display the appropriate data
' These 2 work
Forms!frmCompanyHistory!RptCompany_Projects_Brief.Requery
Forms!frmCompanyHistory!RptEmployeesSpecificCompany.Requery
'These 2 Fail
Forms!frmCompanyHistory!SubRptCompany_Payback.Requery
Forms!frmCompanyHistory!SubRptCompany_Revenue.Requery
'Other things I've tried
'Forms!frmCompanyHistory!TabCtl66!SubRptCompany_Payback.Requery
'Me.TabCtl66!SubRptCompany_Revenue.Requery
As a side note, if I chose the company, say "ok" to the error, and then go run the queries that these reports are based on, I get the correct records. Also, if I chose the company and run the 2 failing reports standalone, they work fine, so I know the problem isn't the reports or the queries themselves.
I have also checked and rechecked for any spelling mistakes and have found none. I should mention that the error message is "Programs cannot find the field 'SubrptCompany_Payback' referred to in your expression".
View 8 Replies
View Related
Aug 7, 2014
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
View 2 Replies
View Related
Aug 30, 2006
I have a list of class sessions. To help with maintaining attendance, each session has its own roster. I just need to add a functionality so user can make sure the attendance roster is synchronized with the past sessions and future sessions in events of last minute additions/drop or updating names/contact infos. The sessions are related to themselves as below:
ClassID PreviousClassID
1 Null
2 1
3 2
4 Null
5 4
So therefore to propagate the changes into all sessions how would I instruct the query to look for each related ID then checks if it's also related...
I'm thinking I may have to use VBA to do what, but I hope that can be done with a append query.
Thanks!
View 14 Replies
View Related
Aug 14, 2014
See attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.
In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.
View 1 Replies
View Related
