Inserting Data From Two Tables Into Another Table Using A Form
Oct 22, 2007
I am new to access (using Access 2003) and am having trouble working with forms. Here's what I want my form to do:
-Use a combo box to select a specific system
-Given the selected system, pull up ID numbers and descriptions (in separate text boxes) from two separate tables corresponding to that one system
-Navigate through those ID numbers/descriptions from each table independently to find ones that match
-Store the ID numbers of the ones that match into another linking table
The biggest problem right now is being able to navigate through the different table ID numbers/descriptions and add both ID numbers to a row in a different table. I've tried using a combo box with the INSERT INTO statement into the code builder, but I keep getting syntax errors.
Does anyone have any suggestions on a better way to do this?
i have a problem adding the data from the fiels on the form into a table. I know you can just click on the navigation arrows and it will save the record but i want to use a submit button as this is more user-friendly and suitable for the work i'm doing.
I have attached the database... the form that needs the code for submit button is 'frmNew_JobStatus' and the table i'm trying to insert the data into is 'tblJobStatus'
can anybody have a look at what i've done so far and suggest how i can fix this... sample code would be very usefull as i'm not an access expert.
I want to design a form , please note I have beginners information regarding Access, I have 2 tables , 1 table has Project No, Document No, DEpt NO. with all the information
There is another table-2 with Project No, Document No. and Resourcrce. There is not data in this table
What I want to do is , I want to create a form where in I select the Project No. and Dept No. When I select these items the form should list me the data in Table 1 for that Project no and DEpt No. , after this data is listed , I will add the REsource data and all this data has to be stored in table-2. I hope some one will be able to guide me on how to do this.
Hi, this dilemma really has me in a bind - say I have table1 and table2, when I enter data/update table1, I need to also enter the same data into table2. I know this sounds royally weird, but if there is a way to do this I would appreciate any and all help asap!
Hi, I have a problem with a form based on a query. It draws content out of the table Repairs, and also shows the customer info for each repair (based on the link between CustomerID field in both the Repairs table and the Customers table) But when I add a new record using the form, a new entry is added to both tables. Can anyone please explain why this happens and how i can fix it? I dont want a new entry in the Customers table, only in the Repairs table. Hope this question makes sense. Vauneen
Is there a way to insert a hyperlink in a field title on a table? For example, I have a field that is a check box for whether the person has taken a class. What I would like to do is insert a link in the field title for that class so I can click on it and bring up the supporting documentation in a PDF file for that class.
I am new to Access and i am designing a payroll system for my company. I have created a Table with
EMP Id, Name, Basic Pay, Total Working Days, Actual Work Days, Earned Salary, OT days, OT Hrs, OT Pay..
Now i have created a form with all the above fields.and calculated the earned salary from the control source where Earned Salary= Basic pay / Total Working Days*Actual Work Days and OT Pay =[Basic Pay/Total Work Days/8]*150%*OT Hrs.
Now my problem is i am not able to populate the fields in the table.
I am trying to read from an excel file that has the same format but changes data every day due to reports that I run from a different program. I just export them to this excel file called test.xls
What I want to do is import this data from the excel file and insert it into existing data in my table. I need to this every day.
Currently I cannot get the data to append. I can only insert the data once and cannot get it to append. I tried using macros, but think it would be better to use VB.
In the above example student 1234 has a missing record in year 2004 which supposed to be Term2 in the previous year (i.e. 003) and Term2 supposed to be Term1 value in the following year (i.e. 004). Similarly for student 3311
In a Access 2003 database, I have an "Inscriptions" (subscription) database with a primary key on 2 fields idPersonnel (employee) and idSession. I have made a form so that user can select a session (in a listbox), then one or more employee (another listbox) and suscribe them to that session by using a button, which, on VBA side, first check that there is enough room on the session (defined by "MaxParticipants" field on "Sessions" table, linked to "Inscriptions" table on idSession), then insert data in "Inscriptions" table
This is working fine in a single-user environnement, but fails if 2 people want to join some employees on the same session at the same time, as I have a confirmation message between check and insertion. Therefore 2 users can select employees, get the confirmation message (at this point both are told there is enough room), resulting in having more people than expected joined to the session. Fortuneatly, if both users try to insert the same employee(s) to that table, one will get a duplicate error, but insertion will be made if employees are different.
On another DB engine, such as SQL server, I would use a stored procedure that would lock the table, do the check and the insertion then unlock the table.
But it does not seem to be possible in MS Access. What are the possibilities in MS Access to prevent a session from having more than maximum number of participants ?
I am getting method of data member not found for the following code when inserting values to a table. Form has three frames. Add/Insert is on OnClick Event.
Hi all, Really, I am very happy to be a member in this great forum with those great members....
I have a form with three text boxes and one button "Submit". This form is build based on a table with three columns. How can I insert records into this table through that form by clicking that button "Submit"....
Hi all, Really, I am very happy to be a member in this great forum with those great members....
I have a form with two text boxes and one button "Submit". And also, I have a table with two columns. How can I insert records into this table through that form by clicking that button "Submit".... I think it is simple, but I am very beginner in Access.... See the attached file....
I am building an application using MS ACCESS for a local volunteer ambulance corps and have almost complete it except for the last challenge. I have built many applications like this, with an form to add, modify and delete option for a single table. This time, the accountant asked for data to be saved before being deleted to another table so he can review it after it was deleted by a user and then delete it from that "backup" table. I am at a loss to write the code. However, being a reader of this forum taught me many things in the past and I decided to join and ask this query of the community. I know it is an insert command and I have created a mirror table for the real one with all of the twenty or so fields. The fields are the usual name, address, city state and zip with telephone numbers and some other info.
I have a form for member details that comes from the member table and I want to include a button on the form that will remove the member details from the member table to a member history table and then delete the details from the member table. When moving the record I also want to include details from another table called 'promotion', this table is linked to the member table via the member ID (Foriegn Key).
Firstly I want to know if this is possible and if so how I would achieve this. I am having trouble getting the SQL query to identify the current record that the form is displaying so I can select additional information and move it into the Member History table.
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ******** Public Function GetTargetType() As Variant GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]") End Function ************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ******** Private Sub Form_BeforeInsert(Cancel As Integer) Me!target_group = GetTargetType() 'Forms!frmNmsConsumptionEntry!target_group = GetTargetType() '[tblNmsConsumption.target_group] = GetTargetType() End Sub ************************************************** ********
making sure I can insert this value once retrieved.
adding digital signatures to a form or table? my dilemma is this:
we issue out badges to people based on their clearance. if someone has a Top Secret Clearance, they get a green badge. If they don't have a TS Clearance, then they get a Red Badge. When we issue out badges, we take their CAC from them to hold in place of the badge in case they never return the badge, and we have them sign out the badge # in a paper log. I wanna get rid of the paper log and replace it with something digital if possible.
What I'm wanting is an Access table that can hold their digital signature from their CAC. They insert their card into the CAC Reader, type in their pin and press enter, done. Access inserts their digital signature into to the table and locks the record so that editing of that particular record cannot be possible.
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
I have a form, has some fields, one of them is the current date, so when the user click (save )button , which make (add new record )to the only table I have the problem that .all fields are inserted in the table , except the current date !! it is a text box ( Now() )!!
I have a table called "WorkRequest" consisting of some fields such as WR Number, WR Date, WR Time, WR Requested by and WR Work Requested.Once a work request is completed, I want to open a form called "JobCards" from a table called "JobCards". When I click on the pulldown box for the Job card number, it lists the "WorkRequest.WR Numbers" which is what I want.
My problem is: As soon as I select the WR Number and it displays in the jobcard form in the JobCardNumber field, and I press ENTER or TAB, it must automatically populate the corresponding fields on the JobCard Form. These fields are defined identically in bot the "WorkRequest" and the "JobCard" tables.
I have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
I am using an unbound form to insert data into several tables, all related, at the same time. Please let me know if what I am trying to achieve is too ambitious!
I am developing a material sample library... any sample could be one material, two materials or three materials. Let us say that A, B, and C are three primary materials. I have three Tables: tblX, tblY, tblZ. tblX stores details for each individual material. Its structure is as follows:
tblX XPK | ID | value1 | value2 | 01 | A | asdfas | asdfdf 02 | B | dfasdfa | sdfaf 03 | C | asdfffd | asdfg (here, XPK is the primary key, ID is the name of the material and value1, vlaue2 are other descriptive fields...)
tblY stores materials that are combinations of two primary materials: tblY YPK | 1st | 2nd | ID | value1 | 101 | 01 | 03 | M | keiury | 102 | 02 | 03 | N | kjgeiih | 103 | 03 | 01 | P | djlkgoi |
(here, YPK is the unique ID for a sample, the '1st' and '2nd' fields are simply the primary keys from the tblX. ID is the name of this new material and value1 has descriptive values for samples M, N, etc.)
tblZ is the next level of material, made of combinations of materials from both tables tblX and tblY. it is like this:
(here, ZPK is the unique identifier, the primary key. as you can see, 1st, 2nd, 3rd, 4th are references to materials from either tblX or tblY or none, ID is the name of the material and so on.)
So, going back to the original primary materials, a sample could be a combination like A + (A+C) + B...
I hope I am clear enough till this point.
My unbound form (which is a very complicated one by now!) is where a user will start with such a sample and start putting in details for each of the primary materials. As he/she logs each individual material, I store that data in a temporary table (tbltemp). When all the primary materials are fed in, the user hits a 'Save' button, which is supposed to do the following:
1) save each primary material from tbltemp to tblX 2) extract their primary keys and then insert that into the next level table, either tblY or tblZ.
I have been working with ADO recordsets to 'addnew' data to tables and am adept with that. I have never simultaneously extracted data from one table and inserted into another. similarly, i have never extracted more than one record, and inserted them into a single record in another table, etc.
I will appreciate any guidance, both at conceptual level and at operational level, that I can get. If you would like to see my database, I could arrange to have it accessible; there is nothing confidential (only higly complicated, I think!).
If you are still reading, I already owe you a ton of thanks!!
hi all, I have created the database with multiple tables. I've created one main table to store all records from other tables. Then I have make several forms to enter record into several tables. What I'd like to make is that when I enter new record to any one of those other tables, then it will be automitically add to the main table also. The record no of main table will be automatically increase by itself when the new record came in. Can anyone help me with it? thanks.
I've searched all over and can't seem to figure out an answer to this, so I'm putting it in front of the experts (who'll probably have a simple answer in about 10 seconds).
I'm doing a survey that has both radio buttons and check boxes. If it were all radio, it'd be easy, but the check boxes are making it complicated for me.
Here's the insertion code I'm working with: Code:SurveyInsert2 = "INSERT INTO data (answerID, surveyID) VALUES ('" & Request.Form(fld) & "', '" & currentsurvey & "')"oSurveyAdd.open SurveyInsert2, "dsn=survey" For Each fld in Request.FormoSurveyAdd.Execute(SurveyInsert2) Next which is spewing the answer: Code:Request object error 'ASP 0102 : 80004005'Expecting string input/survey_process.asp, line 44The function expects a string as input.
I've got a table - "Products" - in my database, with a text field - "Info" - which contains info about products.
Within this field I would like to have footnotes. To do this, I think the best way to do it is by putting numbers inside the text at the location of where I want the footnote to refer to. These numbers will actually be foreign keys to a table called 'Footnotes'.
I can then program the forms and reports to show any numbers as superscripts or whatever.
(Of course, if the user will actually want to insert a number into the text field which is NOT a reference to a footnote, I will have to make a workaround e.g. by making access put a symbol in front of the number, so access will know the number is just part of the text (and I will program the form to not show the symbol in front of the number)).
Is it wrong to have foreign keys within a text field? I think if executed correctly, it should work perfectly.
