Insert Row Into Secondary Table From Maintenance Form.

Sep 24, 2005

Hi,
I can't seem to get this right...any help much appreciated:

I have a two tables LESSON<PAYMENT (One to Many)

LESSON
Lesson_ID (PK autonumber)
Lesson_Date (Date)

PAYMENT
Payment_ID (PK autonumber)
Lesson_ID (FK)
Payment_Date (Date)

I am maintaining LESSON using a simple form. On creating each new LESSON record, I also wish to insert a row into PAYMENT, using values from the LESSON table - Payment_ID(autonumber), Lesson_ID = LESSON.Lesson_ID, Payment_Date = LESSON.Lesson_Date

What is the best way to do this?
I have tried to add an Event Procedure to 'Before Insert' but I can't seem to get the syntax correct. Also, for this to work, do I need to paint all fields on LESSON form including (hidden) PK?

I have simplified the tables above but they are relevant to what I am trying to do. Any help would be much appreciated- am new to Access...rather frustrating.

Many thanks,
Simon.

PS I have searched through existing messages but can't see one that answers this, I apologise if this has been answered before- just point me to the orig post.

View Replies


ADVERTISEMENT

Tables :: Creating A Secondary Table

Oct 27, 2014

I have an Access database, where one of the tables is near the maximum amount of fields (241 of 255), was just wondering if I could set up a secondary table that would be linked to the original table by a specific field and would store the other fields that would like to add to the table.

View 7 Replies View Related

Copy Table Post And Secondary Table

Jun 26, 2007

I have a problem... I have four tables(but my problem is limited to just two)
The tables are Orders, Customers, Items and OrderedItems
The two more detailed below creates the problem when trying to copy an already existing order to a new since the customers usally order the samethings over and over again.

Orders
IDOrders
IDCustomers
strSalesperson
strWhen
strSent

OrderedItems
IDOrderedItems
IDOrders
IDItems
iNumber
bPacked

So I have made a copy button on the order form. The copy button should do this:
1. First save the old orders IDOrders and IDCustomers (which it does)
2. Create a new order with old data (which doesnt do)
3. Copy all post in OrderedItems with a new IDOrdered using INSERT INTO and using columns

My SQL statement is like follows:
sSQL ="INSERT INTO Orders(IDCustomers, strSalesperson, strWhen, strSent, strComment) VALUES (" & itmpIDCustomers & ",'" & strSalesperson & "', '" & strWhen & "','" & strSent & "');"
But the reply I got is "Could not find output table 'Orders'" but I have checked the names over and over again but it doesnt work.

View 1 Replies View Related

Min/max From Secondary Table Without Returning Extra Rows From Primary

Mar 9, 2006

Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.

I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.

I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?

View 2 Replies View Related

Queries :: Append Records From Main To Secondary Table

May 14, 2014

I built an Append Query to take records of 'Leavers' from my Primary Table and add them to a Secondary Table named 'Leavers. This worked perfectly, but on reflection I determined that I needed to append a further column 'Notes' which exists in the Main Table but not in the Secondary Table.I amended the SQL statement as follows, but the query now fails stating that it doesn't recognize the field 'Notes'.

INSERT INTO Leavers ( [Member ID], Surname, [First Name], [Address 1], [Address 2], Town, PostCode, Phone, [E-Mail], Notes )
SELECT [Mail List].[Member ID], [Mail List].Surname, [Mail List].[First Name], [Mail List].[Address 1], [Mail List].[Address 2], [Mail List].Town, [Mail List].PostCode, [Mail List].Phone, [Mail List].[E-Mail], [Mail List].Notes
FROM [Mail List]
WHERE ((([Mail List].Leaving)=True));

Does this mean that one would need to recreate a new Secondary Table to incorporate the additional field? I have attempted to edit the secondary table by merely adding the 'Notes' field but that doesn't seem to be possible.

View 3 Replies View Related

Associating A Secondary Form

Mar 15, 2005

The main form (frmMain) is fed by qryMain. I'd like to put a memo field on a separate form (frmMemoField) to avoid cluttering the main form.

Is there a way to ensure that frmMemoField offers the same record currently displayed on frmMain, please, Experts?

View 2 Replies View Related

Sorting A Secondary Form

Feb 17, 2005

Hello

I have a form that is bound to a table called "date". Date has to fields "date_id" which is a numeric value and "date_description" which is the name of the month.
A secondary form is bound to a table called "Employees" which has fields regarding data about the emploees of a company (eg "surname", name" etc). I use these 2 formes together to select the month of year and show/edit the data of the employees. Everything work perfectly except one last thing. Is it possible that after I edit (or add) a record in the secondary form (table "employees") the secondaty form to be sorted by "surname"? How can I do such a thing? I would be grateful if you give me an example.
Thank you in advance

View 6 Replies View Related

Tables :: Limit Combo Box Results Based On Secondary Table

Dec 4, 2013

I have a DB with 4 tables:

Property (used for look-up values)
Service Type (used for look-up values)
Rate Table (uses the Property Table and Service Type Table and is used as a rate look-up table for the daily service table noted below)
Daily Service

Using the Daily Service table, I need to choose a property, choose a service type that is limited to the property (100's of service types, but a property may only have a limited number), and enter a quantity, and have the Rate Table return the specific cost of the service. I didn't think this was too hard in Access (2013), but I seem to be hitting a wall and spending too much time figuring it out. Now I am behind in my project.

I have been trying to do this in a table format, but have been unsuccessful in limiting the results of the Service Type combo box based on the property chosen.

View 2 Replies View Related

Forms :: Display Data On Form From Secondary Tables

Apr 17, 2013

I have a form where I am entering orders. For this, there is only 1 item and 1 customer per order. The Order_Table is the 'general' of the form. However, I want a Customer_name dropdown on the form, and return data to 'display' on the form (but capturing and ONLY storing the Customer_no), once the user makes a selection.

Additionally, in the same vein, I want to have an Item_name list, where the user selects one from the listing, and display (again, no edit) the name. Again, only want to store the Customer & Item numbers on the Order table.Here's the 'general' table layout (I've taken out what doesn't matter):

Table: Order_Table
Order_no
Customer_no
Item_no

Table: Customer_Table
Customer_no
Customer_name

Table: Item_Table
Item_no
Item_name

View 1 Replies View Related

Queries :: Access 2007 - Insert Into Table Where Source Is Another Table And Form

Feb 3, 2015

If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?

So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.

View 5 Replies View Related

Tables :: Use Insert Into Command To Insert Filepath Into Table That Adds Blank Label

Aug 28, 2014

I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.

|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|

If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.

View 1 Replies View Related

Insert Form Variable Into Table

Apr 7, 2005

Hi
I have a form that displays line items and their cost. At the bottom of the form I have a text field called text17 that uses a sum to total the value of all the line item costs with this : =Sum([Line Cost])

How would I pass that value into a database table?

View 14 Replies View Related

Insert Into Another Table When A Field In Form Update

Apr 6, 2006

i have a form, which is based on query from multiple tables.. in this form i want to add a unbound textbox in the detail section.. whenever a user leaves this records, the system should insert a record in another table with values from this record including this field... how to do this?

the record is from a query where as the field is unbound, still we need to insert the values from record and this field at the same time into another table..

example: i have item master and item production table.... from joining these table i get a query which will give me item A and prod qty .. when i use this query as form datasource, the detail section has these two fields.. now i want to have an unbound field budget.. so i have the followig in detail sectin

item A production qty budget qty

when users enter values in budgetqty field and moves to next recod,, the system should insert into another table xyz values (itemA, production qty, budget Qty)

how to do this..which event will be good.

urgent reply is expected.

View 2 Replies View Related

Database Maintenance

Aug 17, 2007

Hi folks,

I have searched on this, but did not find any article that is comprehensive enough. I wonder if anyone can advise on database maintenance routines particularly for Microsoft Access.

I know:


Regular Back ups
Compact and repair database


Please fell free to add on to this list.

Thanks,
B

View 4 Replies View Related

Maintenance Scheduler

Feb 13, 2006

Morning guys and gals

I am trying to create a sceduling calendar for my department which displays given events day by day.
I have created this using subforms for each day and a combo box to select the month.
For display purposes it works fine but I am having a little difficulty with the functionality.
I want to be able to click into any of the subforms, and depending on whether there is data within, open a specific document.
1)If there is an item in the schedule the I want to open a report filtered for that date.
2)If the day is blank then I want to open a form to input an item. (This works but I cannot get the date to pre-fill)
I have created a seperate piece of code to do this but it doesn't want to work. It may be because my knowledge of VB isn't what it was or it may be that I am completely barking up the wrong tree.
Hopefully someone can have a look and let me know where I am going wrong.

Code

Private Sub OpenCalRep(date1 As Date)
If date1 = Null Then
DoCmd.OpenForm "frmmaintenance", , , , acFormAdd
Forms!frmmaintenance.txtDate = Forms!frmcalsite.txtDate
Else
DoCmd.OpenReport "rptmaintenance", acViewPreview, , "Tables!tblmaintenance.txtdate" = "forms!frmcalmain.sf1.form!txtdate"
End If

End Sub

I realise that there is no counter to increment the subform name yet (SF1, SF2, etc) but I want to get the code to work for just the first box initially so I know that I'm heading in the right direction.

This code is the prefilled from the subform by using:

Private Sub SF1_Enter()
OpenCalRep (Forms!frmcalmain.SF1.Form!txtDate)
End Sub

I realise that there is no counter to increment the subform name yet (SF1, SF2, etc) but I want to get the code to work for just the first box initially so I know that I'm heading in the right direction.

Regards

Jason

View 14 Replies View Related

Modules & VBA :: Insert Value Of 30 Text Boxes On A Form In A Table

Sep 30, 2013

I am working on a database tool for quality team.

I have around 30 text box on a form and trying to insert the value of those 30 text boxes in a table from vba.

Here is my code

Private Sub Submit_Click()
Dim a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z, aa, bb, cc, dd As String
Dim SQL As String
a = Month
b = Week
c = Type_of_Contact
d = Country

[Code] ....

From above code I am not able to get my working done

Can we use a variable in referring a field in insert query?

I am using variable because I am not able to write the whole code in a single line and when i am pressing enter it gives me a error.

View 4 Replies View Related

Record Maintenance Across Forms

Jun 7, 2007

My database consists of several forms linked by open form buttons that function as "previous" and "next" navigators through this series of forms. Each time I switch from one form to the other, the record jumps back to the first record.

Question:
When entering a new record, how can I make it so that when I switch from the first form to subsequent forms, the record on the subsequent forms will match the record on the form prior to it without having to use the record navigation buttons at the bottom of the form.

View 5 Replies View Related

Forms :: Access Form To Insert Data In Sql Server Table

Jan 21, 2015

how to create a form in access to insert/update/delete data from a table in sql server?

View 4 Replies View Related

Modules & VBA :: Open Form After INSERT INTO - Refresh Data In Table

Apr 16, 2014

Is there any possibilities to open form after INSERT INTO? I think Ms Access can't fast refresh data in the table after that, so form opens up clean:/

Code:
...
strSQL = "INSERT INTO tblZlecenia (id_zlecenia_info, DataPrzyjecia) VALUES ('" & ostateczne & "', Date())"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Formularz2", WhereCondition:="ID_Zlecenia=" & ostateczne

View 5 Replies View Related

Modules & VBA :: Insert Two Values From A Form In Table And Check If They Already Exist

Jan 27, 2015

I'm trying to write some Code with the target to take a value from a textbox (out of a form) and a pregiven value (in this case = 2) and enter them in a new record in a table. But only if there is not already a record with the exact same combination of these to values. When done, the same button should proceed you to the next form (but this code is not already implemented in the fragment below) even if no values are added because they already exist in this combination.

Code:
Public Function GetID_PatientStudiesGroup&()
GetID_PatientStudiesGroup = [Forms]![frm_PatientStudiesGroupTZP]![ID_PatientStudiesGroup]
End Function
Private Sub BPRS_T1_Button_Click()
Call GetID_PatientStudiesGroup&
Dim strSQL As String

[Code] ....

As you can see, I tried to use a function to refer to the value out of the form, because I didn't find out how to refer to a form in an SQL Code.

Just to give you a better overview: The table where the information should be added is named "tbl_PatientStudiesGroupTZP". The form where you find the Textbox "ID_PatientStudiesGroup" (with the value I need to transport) is named "frm_PatientStudiesGroupTZP". Every part (except the WHERE NOT EXISTS part) worked perfectly for itself, but not when thrown together.

View 10 Replies View Related

Yearly Planned Maintenance Report

Apr 4, 2007

Got a little system going whereby when a job is created it will display the date that the job is next due by.

I'm trying to work out how to create a qry that will display all due dates & not just the next one.

Here is how it should work.
If a job is created on the 1st of April 2007 & the type of job is MONTHLY then I want it to list the rest of the monthly orders that are planned for the year.

Here are the different PPM types that I'm using to calculate with
8784= Yearly
43848= 5 Yearly
2208= 3 Monthly
744= Monthly
168= Weekly

Has anyone done anything similar cause I can't see how it can be done?

This is what I'm using to create the next date due

NextDue: DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported])

Here is my qry that the above code is in.
SELECT tblPPMType.PPMID, tblBuildings.PropertyName, tblPPMType.PPMType, tblTasks.DateReported, tblPPMType.PPMNextDue, DateAdd("h",DLookUp("[PPMNextDue]","[tblPPMType]","[tblPPMType]![PPMID]=" & [PPMID]),[DateReported]) AS NextDue, tblContracts.ConDescription
FROM tblContracts INNER JOIN (tblBuildings INNER JOIN (tblPPMType INNER JOIN tblTasks ON tblPPMType.PPMType = tblTasks.JobDetails) ON tblBuildings.ProID = tblTasks.BuildingID) ON tblContracts.ContID = tblTasks.ContractNo
ORDER BY tblPPMType.PPMType;


This is the result.

DateReported---------PPMNextDue--------NextDue
14/03/2007 14:35:34----8784----14/03/2008 14:35:34
15/03/2007 15:08:00----8784----15/03/2008 15:08:00
15/03/2007 10:07:11----8784----15/03/2008 10:07:11
14/03/2007 14:52:56----2208----14/06/2007 14:52:56
15/03/2007 15:10:21----2208----15/06/2007 15:10:21
15/03/2007 10:19:35----43848----15/03/2012 10:19:35
14/03/2007 14:54:13----43848----14/03/2012 14:54:13
14/03/2007 14:55:15----4416----14/09/2007 14:55:15
15/03/2007 22:36:27----744----15/04/2007 22:36:27
14/03/2007 14:56:30----744----14/04/2007 14:56:30
15/03/2007 20:34:27----744----15/04/2007 20:34:27
18/03/2007 22:38:41----744----18/04/2007 22:38:41
15/03/2007 13:39:02----744----15/04/2007 13:39:02
15/03/2007 10:58:55----168----22/03/2007 10:58:55
14/03/2007 14:59:21----168----21/03/2007 14:59:21


Any suggestions will be welcomed

thanks :)

View 2 Replies View Related

General :: Schedule Maintenance - Reminder By Date

Jul 15, 2015

I have a table called schedule maintenance.

In that table I have a "date" field

Is it possible that if " todays" date = a date in the field date a e-mail can be sent to me to remind me there is a scheduled maintenance to be done.

View 6 Replies View Related

General :: Determine Rate For Charging Application Maintenance And Support Fees

Aug 17, 2013

I've built and implemented an Access 2010 application for a corporate client. It's entirely menu driven, with no user access to the navigation pane. It's split with multiple BEs on a share drive and the FEs on individual PCs (distributed via Auto FE Updater). Two hundred users have been defined to the application but so far there are only up to 10 max concurrent users at a time.

The clients want to add more users to the system but after that will go into maintenance and support mode. The question is how much I should charge for the maintenance and support service on a monthly basis.

I'm not looking for a $ amount as much as strategies/guidelines for pricing. The clients anticipate using the application for about four years, at which point a new, corporate-wide application should be rolled out and my app will get replaced by it. They will probably need to convert the data from my app into the new app, and will want/need occasional enhancements to the system over the next four years as well.

View 1 Replies View Related

Secondary Sorting

Jan 23, 2006

Hey everyone,

Is there any way that you can do a secondary sort on a field?
Any help would be of great!

Thanks - edawson

View 2 Replies View Related

List Box Primary/secondary

Apr 25, 2006

Afternoon all,

ac2003

I have a problem concerning how to display records dependant or depending on the current record.

Basically I have a seperate area within the db that gives you the option to set which record(s) depends on or dependant to another record(s) as primary and secondary

When trying to display this info, it is fine for a direct primary - secondary within my listbox, but the complication occurs when the secondary record could also be a primary record of another record (if that makes sense).

for example. The data is as such,

PrimaryID SecondaryID
10 12
12 13
15 10


So from this data, my list box should show 1 record that this record depends on (15) , and in another list box 2 records than depend on this record (12,13)

I'm wondering if some kind of loop code would do the trick but not sure what angle to attack this at.

I would appreciate it if anyone knows a simple solution to this.

Thanks
Kempes.

View 13 Replies View Related

Main And Secondary Forms

Oct 9, 2006

Hi
I have a main table in my database. For each record in this table there may exist 0, 1 or many records linked to it in a secondary table.
I have created a form for viewing the records in the main table, and now want to link to a second form that displays the records in the secondary table, if indeed any exist for the main record being displayed on form1.
Specifically . .
If I can detect whether secondary records exist for each record in the first, how can I pass a parameter (ie Table1id) to the secondary form so that we can view only the secondary records associated with the main record selected ?
thanks
ajm

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved