I need to make one database with information about torque values according to one defined table. I make 3N so I can avoid duplicating data, by my problem now is how to enter data into the related tables and save the data using a save button. I want to have control about when data is saved that is the reason because I used unbound controls to enter data and I want to use also a new button to add new records.
View RepliesI have a combobox on my form with fields related to it (ie. when an option is chosen in the drop-down, the next two fields are also populated with data based on the selection made - I know this is duplication of data, it's just the way the 'powers that be' wanted it. I'm changing it in my next version of the database!)
The two fields that are populated as a result of the selection in the combobox are migrating into the related table fine, but the actual selection in the combobox isn't - it's showing up as blank in each new record in the table.
I want to know a way of populating unbound textboxes with data from a table without using the easy assigning data in the property of the box. I want it to be all done with code, which to me isnt a huge deal but I've yet to find answers on the web pertaining to this issue.
for example:
Say i was searching a primary key in one box and based on what ever is typed in i'd like it to populate the rest of the textboxes.
I have created a table that acts as a header for my data and a second table that acts as line item data. What I need to do now is add a second child table that uses the line item data as its parent table and stores associated line items for each record. Is this possible?
This is a skeleton view of what I'm going for:
Master Table:
tlbAuditReportHeader
- AuditDate
- AuditArea
- Auditor
Sub Table:
tblDiscrepancy
- Discrepancy
- CorrectiveAction
- ActualCompletionDate
- VerifiedDate
- Notes
Sub table to Sub Table
tblFollowUp
- FollowUpDate
- AssignedTo
- SpokeWith
- EstimatedCompletionDate
- Notes
Sometimes tasks change hands or are pushed back depending on work load. It would be nice to be able to track something like this.
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
I have a form displaying the 11 fields of the parent/primary table using a selection from a combo box. I am using queries and vba code modules respective to form, combo box and command buttons. I have initial code that uses the two fields from the combo box selection to append same to a new parent/primary archive table. I now want to add to the append SQL the remaining fields to the parent/primary archive table. When I add the second sql string for the remaining fields to the same procedure and execute I keep getting 'null in primary key'. If I copy the primary record and paste same into the archive table it works.
Private Sub Command26_Click()
On Error GoTo Err_Archive_Primary_Click
Dim strSQL As String
Dim strSQL2 As String
strSQL = "INSERT INTO ARC_289325045 ([Survey Point ID], [Survey Area Detail], [Date On Site]) " & _
"VALUES ('" & Me.cboAreaDetailDate.Column(0) & "','" & Me.cboAreaDetailDate.Column(1) & "'," & _
"#" & Me.cboAreaDetailDate.Column(2) & "#)"
CurrentDb.Execute strSQL, dbFailOnError
'strSQL2 = "INSERT INTO ARC_289325045 (RecordID, UnitID, UserName, [TimeStamp], [Survey Point - Area], Measurement, NewArea, [EXIT Form] ) " & _
'"SELECT FORM_ID_289325045.RecordID, FORM_ID_289325045.UnitID, FORM_ID_289325045.UserName, FORM_ID_289325045.TimeStamp, FORM_ID_289325045.[Survey Point - Area], FORM_ID_289325045.Measurement, FORM_ID_289325045.NewArea, FORM_ID_289325045.[EXIT Form] " & _
'"FROM FORM_ID_289325045"
'CurrentDb.Execute strSQL2, dbFailOnError
Exit_Archive_Primary_Click:
Exit Sub
Err_Archive_Primary_Click:
MsgBox Err.Description
Resume Exit_Archive_Primary_Click
End Sub
The next step is to do the same for the child table and append related records to the child archive table.
Edit/Delete Message Reply With Quote Quick reply to this message
JJKramer
View Public Profile
Send a private message to JJKramer
Find all posts by JJKramer
Add JJKramer to Your Buddy List
Sponsored Links
I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.
View 1 Replies View RelatedI'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.
This is a database of pregnancies and deliveries.
The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.
Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.
This is because each mother can deliver more than once, and each delivery can have more than one baby.
I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.
The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.
I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.
I have to make a Costing System but for that I need to enter our Expense details in database according to Fiscal year and months.
I need a table for Fixed expenses and one for Variable Expenses and then I need one or more Forms to update data in those tables. Now I've created a table with Fixed Expenses. I have to update Year and Amount in that. Now it is only letting me one entry per Expense.
I want multiple entries for one Expense say 'Advertising' for different years. I'm thinking may be I need to make more than one Table, may be one for Expenses with ExpenseID and other for Years with Year and YearID and the third one for Amount with columns Amount, Method of Payment, Date and Notes. I did tried this but I think I'm not creating proper relation may be because its only updating for one year.
I'm using Access 2003.
I am having an issue where a small percentage of my records do not follow my related Tables.
ex: 3 tables
tblEmployee
-intEmployeeID
-intWorkCenterID
tblWorkcenter
-intWorkCenterID
tblManager
-intManagerID
-intEmployeeID
-intWorkCenterID
where:
tblemployee.intworkcenterID -- tblworkcenter.intWorkcenterID -- tblManager.intWorkCenterID
Basically I have my general employee information in the tblemployee table, the workcenter information in the tblworkcenter, and the manager of the Workcenter in the tblManager. This is correct for 99% of the employee population. however, there are a few employees whose manager isn't the 'general' manager for the employees assigned workcenter.
How I can adjust an individual employees manager, while maintaining my structure? or would i have to go back and add the managers ID to a field in the tblEmployee Table?
I have a built a database to record and collate info about clinical trials that are run within my department. I have one large table [Trial Info] which contains all trial info and have created a second and third table [Milestones] & [Comments] to collect dates of various milestones and a notes that occur throughout the course of a study.
The milestones (dates) are recorded in the 2nd table and the comments are recorded in the 3rd table.
All data is entered by staff on forms. The first form captures basic info about the study, once this has been entered, the idea is to click a button to open the second form to document the trial milestones, this form also contains a subform for listing any free text comments required during the life of the study which are stored in the 3rd table.
I have setup the relationships between tables as best I can.
The main table with basic trial info is the parent table, linking the primary key to the foreign key in the second table, the foreign key of the third table is related to the primary key in the second table so these tables should be looking up the correct clinical trial as far as I can tell.
The form containing data from the main table has a button which I have set to open the Milestones & Comments form, and I have set this to open to the record that relates to the record in view in the primary form. The primary key is related to the foreign look-up key in the milestones table.
This opens the form fine, however this is not updating the foreign look-up key, this remains '0' which I assume is the cause of the following error when I try to save changes to the record
Quote:
You cannot add or change a record because a related record is require in table 'Trial Info'
How do I trigger the foreign key to update to the primary key so that the first time a milestone page is opened for a new study it creates a matching record?
I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.
View 6 Replies View RelatedI have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.
View 5 Replies View RelatedSo I have a form, it has a few fields from a related table in it, related by the name. I click 'new' on the form, but when I click save Access complains about the related table does not have a matching record. How do I have it create a new record?
View 2 Replies View RelatedHi everyone!
My names james, im 18 and im a data manager for a school.
My schools database stores student data such as names, classes and exam grades. I have a table containing each students personal details such as name and address. Each pupil is assigned a unique PupilID which is the primary key. I then have a number of other tables linked to it with one to many relationships. Each of these tables holds a different set of exam results. The tables are linked through PupilID. The primary keys for the other tables are auto numbers.
The problem is this: when i add a new student to the Students table i cannot get their PupilID to automatically be entered into the other tables containing grades as part of a new field. How can i make this happen?
Thanks.
Ok due to user error (and my own fault for not archiving an old database) we now have two databases which have had data entered into with the same table structure.
The table structure we have at the moment is with the related field in []:
Propertytbl[Propertycode] is related to Systemtbl[sysID] which is related to Assessmenttbl.
There is also another Temperaturetbl linked to Systemtbl by [sysID]
The problem i have is sysID is an autonumber. I can't simply paste in the difference in data as the assessmenttbl records and Temperaturetbl records will point to a different system (as the sysID will change in the Systemtbl data)
Can anyone think how i can do this either through using queries or programmatically so that i don't have to enter 5000 records manually.
I've tried using append queries but this produces the same affect as above.
Many thanks
Matt Collins
I have a combo box in my main form that gets its list of physician names from tblPhysician. When the physician is chosen and the record saved, it saves the PhysicianID to my main table, tblOncReg.
When I delete a physician, any record that had that name chosen continues to have that name in the record despite it being deleted from tblPhysician.
I thought that setting relationships would solve it, but when I select "Enforce Referential Integrity" and "Cascade Update Related Fields", I get an error saying "Invalid field definition 'ID' in definition of index or relationship.", and it doesn't set the relationship.
Would setting the relationship solve that problem?
In a case where two tables are related, can the field names which relate them be the same? Or is it necessary to use different field names?
Robert
whats the best way of appending tbl_orders and the related data in tbl_order details to the two identical archive tables when the "COMPLETE" column is "YES" in tbl_orders
View 1 Replies View RelatedHello, I apologize in advance if there have been lots of questions like this, but the search won't let me use AND as a search term!
I have two related 1 to many tables. The parent table contains clients, and the child table contains categories, each client can have many categories. (i originally intended to have the categories be columns in the client table, in which case what i want to do is easy, however, then it becomes a nightmare when the user wants to add a new category hence the related situation described.)
Is there an easy way to find all the clients who have both category 7 and category 10? I can do it writing a query to find all the 7s, then another to find all the 10s, and a third to find those which have both. I am hoping there is an easier way, as i need to give the user a way to search via categories in whatever combination they fancy. The OR's i can do easily it's the AND's that are causing the problem.
The only idea i have at the moment is to make a temp table with the the clientid, and a long field holding each of the category ids, seperated by commas, and then searching using like "*7*" and like "*10*".
Does anyone have any better ideas, i'm hoping i'm missing something really obvious......
Thanks, Anna
Hi,
I have a database with a couple of tables. All of which have a 1:1 relationship to another table, patient:
- patient (id (auto_increment), name, address, etc)
- nutrition (patient_id, some, stuff, etc)
- sleep (patient_id, foo, bar)
The patient_id in nutrition and sleep is a primary key and corresponds with the id of a patient (my 1:1 relationships)
I have a couple of forms. One for each table actually. In this form the user should be able to add/modify a patient's data. These forms are not to be used by themselves.
I have another form(the main form), which has a tab-control element on it. There are 3 tabs on this and on these tabs the 3 different forms are displayed.
Now for the problem:
- when I add a new record to the database using the main form, there is no problem when adding a new patient. Woohoo! When I switch to another tab, , however, the problems start: I can enter new information, but when I leave the tab I get an error: "Index or Primary key cannot contain a null value." Blech.
I want Access to realise that it should use the id that is given to the patient in the first tab, but it seems it cannot get it automatically. (I am not sure, but I think the problem didn't occur at first, so I think I may have done something which is causing this problem.)
Any help here is appreciated.
PS I was unable to find any posts on this forum that can help me with this. The closest I could find was: http://www.access-programmers.co.uk/forums/showthread.php?t=39933
I have created a query from two tables. One table is Job information the other is job notes. How do i populate the query with the job # and job prefix from the job information table? I have the query(field) structure I want but its just gives me blank fields in the query form. How do I populate the fields with the info from the tables?
Thank you!
I have two tables created. One contains only names of people, the other contains the names along with other information stored about those names. The names consist of first, middle, and last on both forms, but for some reason, I can only get the middle name onto the names only table and not onto the other table with all the other information. if you'd like to see the db I have it posted to yahoo briefcase, just send me a message for the username and pw.
View 9 Replies View RelatedHere's the scenario:
I create a temp table structure, which works fine.
I create a recordset from SQL pulling data from a DB2 connection. This works fine.
I want to put the entire recordset result into the temp table.
I have a working means of doing this, but it is very inefficient and leaves the user staring at an hourglass for a minute or two.
What I am currently doing is iterating through each record of the recordset and appending it to the temp table. Desired results, yes, but takes way too much time.
Is there any way to simply 'dump' an entire dataset into the table, instead of on a record-by-record basis?
Here's some code from how I am currently doing things:
Set cmdP1 = New ADODB.Command
Set cmdP1.ActiveConnection = cnnP1
cmdP1.CommandText = "SELECT DISTINCT distribution_id FROM " & sDB & " ORDER BY distribution_id"
Set rstP1 = cmdP1.Execute
Do Until rstP1.EOF
With rst_Temp
.AddNew
.Fields!distribution_id = rstP1!distribution_id
.Update
End With
rstP1.MoveNext
Loop
Any help will be most appreciated! Thanks!
John
I have two tables in my Db: tblMaster & tblAddresses. They are joined with a one-to-many relationship, with the addresses being the "one" side. Many entries in tblMaster have the same address.
I use a form, based on a query, to add records which creates the new vendor in tblMaster, and (if I have contact details), a record in tblAddresses. The problem is that while the PK is created in tblAddresses once I add the address info, the related FK field in tblMaster is not populated with that #.
I have a table of 'things' and two related tables, type and subtype. Each thing has a type and a subtype. The types table contains simply Index (autonumber) and Type (text). The Subtype table Index (autonumber), type (number) and subtype (text), where index and type form the primary key and type is a foreign key to the types table. Thus each type has its own sub-set of subtypes.
In the design of the Things table I have set the lookup for Type and Subtype to be a dropdown, but of course the subtype drop-down shows all subtypes, not just valid ones. If I select a subtype that is incorrect for the selected type then of course I get an error, but it would be nice to set the query in the subtype lookup's Row Source to just display the valid subtypes. At the moment it just says "SELECT index, type FROM subtype" How do I put in a WHERE clause that references the Type field for the current row: "SELECT index, type FROM subtype WHERE type=<type selected in current row>"?