Modules & VBA :: Adding New Record To Related Table In SubForm
May 13, 2015
I am currently working on an Access Database that houses our security clearance information. Most of the system is up and running but the most recent form has got me spinning my wheels. I have a Word User Form that users will download and complete, once completed the macros will automatically send us the document to be added to our database. Most of this is working the problem is that this portion of the database has multiple related tables and at any given time a user may require multiple records in the related tables. I have created the code to copy most of the information but am getting stuck adding a new record on the sub-form when multiple items are required. Here is a breakdown of the scenario
Word Doc Table 1 = Basic Organization Info
Word Doc Table 2+ = Sites to Visit (There could be more than 1 table added here)
Word Doc Table 3+ = People to go on site (this might not be the third table based on user interaction for sites)
So far I can get Table 1 and Table 2 data but if there are more than 1 site I cant seem to get the system to create a new record on the related table it is creating a new record on the main form. Here are the lines I used to try to create the related record..
When I put just the above code on a button it seems to have worked as the sub-form showed an additional record was created but when using this on my macro the sub-form is not taking the focus for some reason.
Is it possible to have the control tip show related records from another table?
I've been looking around the internet, and I've seen many examples for showing concatenated fields on current record, but had no luck with what I'm trying to do.
For example.
I have a continuous form, and on it shows top line data. Where a record in this form shows as "Split" it means I have more data related to this record on another table.
Is it possible to show however many fields are related to this record, when I control tip, or mouseover?
I've been playing with
Code: Me.textbox.ControlTipText = Me.textbox
but was wondering, if this is possible, how I'd reference the other table,
If said column of continuous form is "split"... lookup related table by ID number show however many columns of data..
I have a parent form which has a class variable (class module instance) to store the form' status and more.... and when i add a new record to the subform it resets the class variable field' data. but this only happens on first transaction, but if i re-run the steps (re-set the variable field value) it's not happening again.
I am working with a database that deals with trailers. What is happening is a salesman takes an order for a make and model for a trailer for a customer. The customer can then add some customization to the trailer such as more tail lights or tie down straps. They add the customization on a form called frmCustomQuote. On frmCustomQuote there is a subform called sfQuoteConfigs. On sfQuoteConfigs there is fields such as category and sub catergory that get populated bases on what was selected.
On frmCustomQuote there is a button called "Copy Quote" this will allow the salesman to make an exact copy of the trailer and customization. The quotes are held in a table called tblQuotes and the customization is held in tbQuoteConfigs. Now my problem is when I click on Copy Quote it only copies the first record into tbQuoteConfigs. I can't figure out a way to tell my code to move to the next record within sfQuoteConfigs.
My code:
Code: Option Compare Database Option Explicit 'Setup ADODB connection to the tblQuotes Dim adoQuotesCustomQuote As New ADODB.Recordset 'setup ADODB connection to the tbQuoteConfigs Dim adoQuoteConfigsCustom As New ADODB.Recordset 'Dim the Variables
Hello I need to add daily records to a related table using a form, from a button or subform displayed on a form updating the master table. This would enter the related key to the new form ready for insertion etc. Can this be done?
I am a beginner in access and I want to populate a new field (REPUN_1 which corresponds to SEGMT_ID in the other table) in my table and this field is coming from another table. The values of the fields need to correspond to the row of my actual table (JMTable) having the same CO_ID, MOVEPLANCD and TTY_NO as the table I want to extract the values from (DI_Treaty_Crosswalk).
Here is my query:
UPDATE JMTable SET [REPUN_1] = DI_Treaty_Crosswalk.SEGMT_ID WHERE JMTable.TTY_NO = DI_Treaty_Crosswalk.TTY_NO AND JMTable.CO_ID = DI_Treaty_Crosswalk.CO_ID AND JMTable.MOVEPLANCD = DI_Treaty_Crosswalk.MOVE_PLAN_CD
and it doesn't work since access ask me to enter a parameter value.
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
I have a form to add a record to a table. How can I give the user a way to close this form without saving the just created record.
I tried
If me.dirty then DoCmd.RunCommand acCmdDeleteRecord endif
This works, but gives a messagebox in return to confirm the command, and I don't want that. And I try to avoid sendkeys. I also don't want to change the options of access.
I'm currently building a bookings database and have encountered an alert message that I cant seem to rectify -
"You cannot add or change a record because a related record is required in the table 'Booking Details'"
Basically - I have a 'Customer Database' form that is linked (via command button) to a 'Booking Details' form. Within 'Booking Details' I have 2 sub forms - 'Booking Quote' and 'Booking Payments'. Both subforms are linked to the 'Booking Details' form by the 'booking ref' field with RI.
I have no problem updating information in the 'Booking Quote' subform, but when I try to add information to 'Booking Payments' it states the above message.
Can anyone please advise as to how I can prevent this happening? I'm slowly losing my mind....!!!
Currently I keep getting this error: "You cannot add or change a record because a related record is required in table"..My current tables are this:
Primary Table with persons info:
Primary Key - Auto number generated Name Address Email Phone
I have 4 other tables with use check boxes.
ex:
Table 1 - Geographic locations visited
ID - Auto generated USA CANADA ASIA ECT...
Table 2 - Languages Spoken ID - Auto generated Spanish Chinese English
Table 3 - Skills ID - Auto generated Hunting Dance Singing Weaving
Is this not a genuine one-to-one relationship table? I mean No two people would have had visited the same places and speak the same language no? I tried to create a one to one relationship with the primary key to the auto generated ID of the child tables but I'm sure that is not how you do it. Also when I try to save the check boxes in my form and I close it and come back it doesnt save and is blank again. Is it because my form gets its information from a query that takes all the information from all the tables.
how I can get this to work properly? Am i to make use of a foreign key? I've read a lot about it online and watched youtube videos but I dont see why I need it here in this case. Is there a way to set the IDs in the child tables to be the ones from the primary table? Or do I have to use a foreign key and manually input the primary ID into them?
Or would it be better to have all these child tables in the primary table and have one large table instead? I just didnt do that because one of them has like 20 checkboxes with cities and locations
I have created three tables, all of them are connected by one-to-one relationship by same field, as you can see in the screenshot. and at the same time I have created three forms for each table. then I brought two forms in one remaining form. so whenever I entered data in first form and click on the next tab in which another form exist, it gives me this error: "you cannot add or change a record because a related record is in table".
I have a problem with my access form, it said "You cannot add or change a record because a related record is required in table". I have attached the access file.
I have a main form with several tabs. Each of those tab has one subform linked to its related table. I would like to be able to enter a new item in one of the table via the subform and I used the following code:
Code: Private Sub Form_Load() DoCmd.GoToRecord acDataForm, Me.tblEmetteurs_AddItems_subform.Name, acNewRec End Sub
I obtain a msg error from the debogger: "Compile error: Method or data member not found."
I have a table that has the list of "Project design" choices, and I enter in there the choices that a project can be. I then have that table related to a junction table that has the "Project design choices" linked to the "project code." Anyway, I THOUGHT that one of the bonuses to using Access was that if you see something spelled wrong, you could fix it in one table and it would fix it everywhere. However, when I see that I spelled something wrong in "TBLProjectDesignChoices" and I want to fix it, it tells me that I can't because it contains related records.... ok so, if this is bad design.. I may have to leave it because I have spent countless hours doing data entry for this..
I was creating a program using ms access. consist of 2 table of database called employee and userlevel.. in employee table have ID, name, username, password,userlevel, and others related employee profile.. and in userlevel table, it consist of admin level and normal user level.. Userlevel table allow to differenciate admin and normal user(employee) .. in my program each employee having their own loginID and pass to access their own data.. the problem with my program now is displaying the data that belong to the logged in employee. its mean that it only show the data that owned by currently user that logged in..
Hi everyone, I am using Access 2007 and I have a database with 2 tables. The original table has a primary key field designated. I have a relationship between that table and a second table and that relationship has "enforce referential integrity" checked.
In the secondary table the field that the relationship is bound to is not a primary key but has the same field properties.
I created a new record in the original table and created the primary key of "T-124". When I attempted to add the same data to the field in the second table, I recieved the following error message: "You cannot add or change a record because a related record is required" in the other table.
I have 2 tables, one is like a main table, containing all of the main data, such as a Job Number, Customer, Quantity, etc. I have a second, related, table that acts a breakdown of information. There may be several related records to one main record, it entirely depends on the nature of the job.
What I'd like to do is run a function that looks at a main record, checks if all the related records COMPLETED field is ticked and then tick a field in the main record. I only want it to do this for records where all of the related records are COMPLETED.
I want to Double-Click on a row of a subform to open a related table/form that are connected with a common ID. So far, I tried the following code, but it does not work.
Private Sub PackingSlipT_subform_DblClick(Cancel As Integer) Dim stDocName As String Dim stLinkCriteria As String stDocName = "ProductT" stLinkCriteria = "[PackingSlip_ID]=" & Me.PackingSlipT_subform.Column(1) DoCmd.OpenForm stDocName, , , stLinkCriteria End Sub
this works fine and i can search using a requery button
however as deptartments are stored in tbldepts
when i change the query to retrieve the dept name instead of number directly from the table and i try to change this on the datasheet subform it changes it in tbldepts instead?
how can i change what dept the employee is in (as in change the number in tblemployee - but display the actual name?)
I am adding new record into subform via recordsetclone method. The problem is that record is added but on save it does not appear in the table. If add this record manual using subform everything works. When record added manually update of the record works fine.
C 'Add Wastage value to flooring area section Private Sub Wastage_AfterUpdate() Dim rsFlArea As DAO.Recordset Dim Wastage As Double Dim Item As String Set rsFlArea = Me.OrderFloorAreaEdit.Form.RecordsetClone
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code: Private Sub Command3_Click() Dim dbs As Database Dim sqlstr As String Set dbs = CurrentDb Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "") Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
tblAcq and tblAcqDetail In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity. tblAcq [AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
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.
I am trying to export text to a 2010 word document from an access 2010 database with DAO. I have successfully been able to export text from the main table tblLandSales via variables (below) and then subsequently a document. I used the following code:
Dim objWord As Word.Application Dim docm As Word.Document Dim rst As New ADODB.Recordset Dim strSQL As String Dim strLandSalesID As String
[Code] ....
Now I want to export other data from a one to many related table where [fk_tblLandSalesID] is the foreign key in the related table and tblLandSalesID is the primary key in the main table.
I have 2 tables. 1- customers table with 2 fields : customername,customerno 2-conversationstable with 4 fields: date,customername,customerno,details
The conversations table is for keeping memo of telephone conversations with the customers.
I built a simple form deriving from the conversations table. And added to it a combobox with 2 columns from customers table to select the customername an customerno for the form.
While deleting the record in the conversations table,through the form, I saw that the customername and customerno in the customers table of that particular customer record are deleted also. I made no links between both tables.
