Create Records From A Form
Can someone direct me as to the most efficient way to add records to a table from a form? I have a table of members and a table of invoices and want to automatically create an invoice for each member by clicking a button on a form. Is there a way to do this using a macro, query, or do I write it in VBA?
Thanks much!
View Complete Forum Thread with Replies
Related Forum Messages:
How Create Form That Allows You To Edit But Not Add New Records.
I guess this is a fairly basic one. I have a form that allows people to edit records, but I want to prevent people from adding new records on this form. I thought that if I put 'no' in the 'Allow additions' property, I would achieve this end, but when I do this, all the input boxes dissappear. appreciate help thanks
View Replies !
Pulling A Value From A Form To Create New Records In Append Query
So, I've been searching through this forum and can't seem to find the answer to this one. I would like to capture a value from a main form and have it used as a value in an append query, in order to populate a subform based on the main form, like so: INSERT INTO tblTakenSurveys ( VisitID, SurveyQuestionID, ResponseCodeID) SELECT Forms![frmMyFormName]!VisitID, tblSurveyQuestions.SurveyQuestionID, 66 FROM SurveyQuestions WHERE SurveyID = 3; Might help to explain some of the terms in this statement: tblTakenSurveys is where I need the new data to be entered via the subform. Forms![frmMyFormName]!VisitID is a textbox control bound to a PK in another table that has a one-to-many relationship with tblTakenSurveys. tblTakenSurveys.ResponseCodeID is a foreign key that represents respondents actual answers to questions. 66 is a value for a ResponseCodeID that stands for a dummy value meaning "data not yet entered" As per advice I received from others on this forum, I have set a query like the above to run from a command button to populate the subform (in theory). But I'm sure I've done something wrong within the query because it will not return a value from the form "VisitID" control and therefore will not append the rows. Without the appended rows, my subform will not populate. And this has me running in circles... I pasted the link to another thread below, where I originally received a lot of input as far as the table structure. I did not start this one, but my posts are the most recent (as of now anyway). Pat Hartman had given me a lot of the guidance here. http://www.access-programmers.co.uk/forums/showthread.php?t=100176
View Replies !
How Do I Create A Table That Contains All Records From Two Others
I'm trying to create a table that automatically contains records for every permutation of the two foreign keys it imports. Let me explain with an example: I have a Characters table: CharacterID (Primary Key) CharacterName CharacterClass [...] And, I have a Skills table: SkillID (Primary Key) SkillName SkillDescription [...] I want a third table named CharacterSkills to combine those two tables. It might look like this: CharacterID (Primary Key) SkillID (Primary Key) CharacterSkillSlot [...] That looks a lot like a junction table for a many-to-many relationship. The difference is I want this table automatically filled with every combination of Characters and Skills. For example, if I have three characters and five skills this table would contain fifteen records. If I add a fourth character I'd expect this table to grow by another five records (as one record per skill is automatically added). Here are those same tables with some example data. Characters table: CHR01, King Loo, [...] CHR02, Humble Pie, [...] CHR03, Jayne Bo, [...] Skills table: SK01, Amity, [...] SK02, Diversion, [...] SK03, Banish, [...] SK04, Guilt, [...] SK05, Shame, [...] CharacterSkills table: CHR01, SK01, [...] CHR01, SK02, [...] CHR01, SK03, [...] CHR01, SK04, [...] CHR01, SK05, [...] CHR02, SK01, [...] CHR02, SK02, [...] CHR02, SK03, [...] CHR02, SK04, [...] CHR02, SK05, [...] CHR03, SK01, [...] CHR03, SK02, [...] CHR03, SK03, [...] CHR03, SK04, [...] CHR03, SK05, [...] Any pointers on how to create this magical third table, or whether it' possible at all, will be greatly appreciated.
View Replies !
Automatically Create Records
I have a problem and not sure what the best approach is. Our study enrolls patients and then re-contacts them every month for 1 year. I want to create 12 new records in "Table2" based on the "baseline_date" and "StudyID" of each patient in "Table1". "Baseline_date" is the initial date the patient is enrolled in the study and "StudyID" is a number created based on information from the patient. Both are unique values. What I want to do, is after the "baseline_date" variable is updated with the date of enrollment on a form I want to trigger VB code to automatically create 12 records in "Table2". "Table2" would have the following variables: "StudyID", "Early_Date" and "Late_Date". The records would be created based on the following. Record1{"StudyID", "Early_Date=(baseline_date+30)-3", "Late_date=baseline_date+30)+10; Record2{"StudyID", "Early_Date=(baseline_date+60)-3", "Late_date=baseline_date+60)+10; Record3{"StudyID", "Early_Date=(baseline_date+90)-3", "Late_date=baseline_date+90)+10; Record4{"StudyID", "Early_Date=(baseline_date+120)-3", "Late_date=baseline_date+120)+10; Record5{"StudyID", "Early_Date=(baseline_date+160)-3", "Late_date=baseline_date+160)+10 ...... Record12{"StudyID", "Early_Date=(baseline_date+360)-3", "Late_date=baseline_date+360)+10 The -3, +10 gives us a two week window in which to contact the patient. "Table2" containing the newly created records will have a many to one relationship with "Table1". Any help would be much appreciated.
View Replies !
Automatically Create Records In Subform
I have searched, but found no reference to my exact question. I have a list of drivers I need to create a checklist for each day. The Parent form is the day and the subform is the list of drivers. What I would like to do is have a command button automatically enter all the drivers that are active (denoted by a chechbox) into the tabular subform.
View Replies !
Create Automatically Records Into Subform
Hello there is a field in my form named "numphotos" and I write the number of photos than that folder contains. I have a subform in that main form named "descriptions", here I describe, one for one, all photos of that folder. Itsn´t a problem if the folder contains a few photos, but I have folders that have even 100 photos :confused: then I must to create 100 records in my subform :eek: I would like than Access create those records automatically according to the number of photos that I indicate at the field numphotos. My example in photo: http://farm2.static.flickr.com/1141/1093088982_a084bff95f_o.jpg I only would desire that Access create that many lines to me as I have written on "numphotos". I would write the rest of the information (photographer and description) is it possible? thanx!! windowsXP access 2007
View Replies !
Create Un-aggregated Records Table
Hi, I have a database that contains a table with an administration area and a number associated with the “count” of a certain characteristic of that area. For use in another piece of software I need to convert the count system to individual records. For example I need to go from: AREACOUNT AdminArea13 AdminArea22 AdminArea31 AdminArea43 ... to: AREACOUNT AdminArea13 AdminArea13 AdminArea13 AdminArea22 AdminArea22 AdminArea31 AdminArea43 AdminArea43 AdminArea43 AdminArea43 I actually don’t need to count field in the new table, but it would be useful for my own quick reference. The problem is that I do not have the original raw un-aggregated data – otherwise this would not be a problem. Also there is no way I can get this. I realize that this is working in the opposite way to good normalised databases – however as I said the software that I need to export requires this data in the expanded format. Does anyone know how I could run a query in access to achieve this? I have large tables so will need to do this programmatically rather than manually. Many thanks in advance. Regards, Kevin
View Replies !
Subform To Query Records OR Create A New One
hi, I am trying to write a training register DB for my department basically: There are three tables: trainees, training sessions, and trainees on courses. The training sessions will be mapped out in advance: so that's easy we will plug in the training dates when necessary. the tricky bit is the booking form for trainees. I'd like a form/subform where the trianing session info is at the top and the subform lists trainess for that day. Now here's the rub. we may not have the trainee's name in our database already so I created a combo box with the all the known trainees. This way if they exist their details pop up and we have a new "trainee on course" record for them. So what I need is a macro or some code that will check whether they exist and if not create a new traineee record and then allow us to fill out the booking form. Perhaps I'm rusty and not thinking this through properly. I'[ve attactched the db in case anyone needs a visual. I'd be grateful for any suggestions The problem is when we get a new trainee with no history we cant create them. either I have
View Replies !
Database To Count How Many Records To Create
I have a form which a user selects an activity from a combo box. On some occasions a user may need to enter the same activity a number of times. I want to add a text box/combo box that a user can enter or select a number. When the user hits the submit button i want the records created in the database to match the number specied fied in the text/combo boxes. I think i may need to use an INSERT statement but totally confused how to do this.
View Replies !
Recall - Automatically Create Records
I have a database with over one hundred employees in. Attached to each employee is a relationship where they can do many activities. How can I automatically create one activity record per employee at the beginning of a day and give the date field in the many part of the relationship the current days date. I assume you've got to create a recordset and do something along the line of - do until EOF docmd.acrecnew. But haven't got a clue where to start. Pease help, Recall.
View Replies !
Create An Entry In A Table Based On Records Matching
Where a many to many relationships is resolved with a linking table...Is it possible to create an entry in the linking table where the two linked tables have the same value e.g. create a entry in the linking table where client table has same value as a job profile table i.e. both are in insurance. Therefore want to create an entry in the middle table with foreign keys from either link automatically ta
View Replies !
Create Multiple Records Based On Date Range
I have developed a Debt Management program for work but I am stuck on a feature that we need to add to it. Basically I have a table which holds "planned future transactions". These are ad hoc transactions that need to be added from time to time to the clients budget. For example the client might have $500 a quarter for school fees for the next two years or he might receive a bonus every six months for the next 5 years. Currently we are putting these in manually but it is tedious and is causing complaints. The feature I require is to be able to add a record such as the school fees, nominate that this fee is paid every three months for the next 8 quarters and get the system, say through an append query? to create 8 records, each with the appropriate date 3 months after the previous one. Any suggestions or assistance would be appreciated. Thankyou
View Replies !
Union Query - Create Additional Field / Clear Records
The code pasted below creates a union query for a set of tables (J000171, J000174, J000178 etc) and stores the results of the query in a table called temp. The first piece of code queries the ‘status’ field of a table rjobs for those records with a ‘status’ field of “Live”. Another field within this rjobs table, ‘JobID’, happens to be the name of a table where additional information relating to that job record is held eg. J000178 All of the tables selected in the query on rjobs are then included in the union query. The second piece of code stores this information in a table called temp I would like to be able to do 2 things with this; 1.add an additional field to the union query which holds the JobID field value from rjobs (or alternatively the table name from which the data originates eg J000178 etc as that is the same as the JobID file din rjobs) 2.create an option to clear the info in the temp table. Currently additional info is appended, so whenever the query is refreshed new data is simply added to old data. I would like to be able to clear that data where possible. The union query is run from the on click of a command button on a simple form. Perhaps an additional button could be used to clear the records from the table temp. Any ideas greatly appreciated. Here is the existing code … Option Compare Database Option Explicit Private Sub Command0_Click() Dim db As Database Dim rsRjobs As Recordset Dim rsRapps As Recordset Dim LengthofUnionSQL As Long Dim sql As String Dim UnionSQL As String Set db = CurrentDb Set rsRjobs = db.OpenRecordset("Select * from rjobs where Status = 'Live'", dbOpenSnapshot) Do While Not rsRjobs.EOF UnionSQL = UnionSQL & "Select ObjectID, SearchNo, DateSearched, Consultant, from " & rsRjobs!jobID & " Union " rsRjobs.MoveNext Loop 'following two lines are to remove the trailing word Union from the string unionsql LengthofUnionSQL = Len(UnionSQL) UnionSQL = Mid(UnionSQL, 1, LengthofUnionSQL - 7) ' Now variable Unionsql will hold the value something like ' Select ObjectID, SearchNo, DateSearched, Consultant from J000145 ' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000146 ' Union Select ObjectID, SearchNo, DateSearched, Consultant from J000147 MsgBox UnionSQL Set db = CurrentDb Dim rsUnionquery As Recordset Dim rstemp As Recordset Set rstemp = db.OpenRecordset("temp", dbOpenDynaset, dbSeeChanges) Set rsUnionquery = db.OpenRecordset(UnionSQL) Do While Not rsUnionquery.EOF rstemp.AddNew rstemp!ObjectID = rsUnionquery!ObjectID rstemp!SearchNo = rsUnionquery!SearchNo rstemp!DateSearched = rsUnionquery!DateSearched rstemp!Consultant = rsUnionquery!Consultant rstemp!jobID = rsUnionquery!jobID rstemp.Update rsUnionquery.MoveNext Loop End Sub
View Replies !
|
TRACKER
