Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hello,

What you want to accomplish can be done with coding a macro to copy the required information from one table to another. However this is not recommended. You already have the information in the primary table and copying it to another table is just duplicating the information. Instead, whenever you need the information, it can be obtained using SQL to join the information from the two tables.

It seems the best approach here is to use a listbox to select the record desired in Table 1 using a table filter, then Table 2 is a sub form of Table 1. A sample of this can be gotten in my answer posted here - ShippingInvoice.odb and more on filters can be found here.

Hello,

What you want to accomplish can be done with coding a macro to copy the required information from one table to another. However this is not recommended. You already have the information in the primary table and copying it to another table is just duplicating the information. Instead, whenever you need the information, it can be obtained using SQL to join the information from the two tables.

It seems the best approach here is to use a listbox to select the record desired in Table 1 using a table filter, then Table 2 is a sub form of Table 1. A sample of this can be gotten in my answer posted here - ShippingInvoice.odb and more on filters can be found here.

Edit 11/30/2017:

Here is a sample based upon your tables. Keep in mind there are a great deal of things which can be done to suit your needs. The LO Base documentation may be of help - click here.

The sample is fairly simple. Three tables: JobTable (client info); FeesTable (services, items, amounts); JobFilter (used for cliten selection). Two Forms: ViewJobFeeData where you can see any client and the items & fees for that client; FeeEntry where new fees/items are entered.

On the FeeEntry form, select a job number from the listbox and press 'Select Customer' button. The client site & name are displayed (per your verification requirements) and the items for that client/site are displayed in the table control below. New items are entered on a new line. Keep in mind this is a sample. You can set this for entry of NEW items only - it is just one of many properties available (see documentation). You can also set this for text boxes instead of a table control. There are too many possibilities to list them all here.

Sample: C:\fakepath\JobSitesAndFees.odb

There are many ways in using SQL to join the data from the two tables to create reports etc.

If this answers your question please click on the ✔ (upper left area of answer).

Hello,

What you want to accomplish can be done with coding a macro to copy the required information from one table to another. However this is not recommended. You already have the information in the primary table and copying it to another table is just duplicating the information. Instead, whenever you need the information, it can be obtained using SQL to join the information from the two tables.

It seems the best approach here is to use a listbox to select the record desired in Table 1 using a table filter, then Table 2 is a sub form of Table 1. A sample of this can be gotten in my answer posted here - ShippingInvoice.odb and more on filters can be found here.

Edit 11/30/2017:

Here is a sample based upon your tables. Keep in mind there are a great deal of things which can be done to suit your needs. The LO Base documentation may be of help - click here.

The sample is fairly simple. Three tables: JobTable (client info); FeesTable (services, items, amounts); JobFilter (used for cliten client selection). Two Forms: ViewJobFeeData where you can see any client and the items & fees for that client; FeeEntry where new fees/items are entered.

On the FeeEntry form, select a job number from the listbox and press 'Select Customer' button. The client site & name are displayed (per your verification requirements) and the items for that client/site are displayed in the table control below. New items are entered on a new line. Keep in mind this is a sample. You can set this for entry of NEW items only - it is just one of many properties available (see documentation). You can also set this for text boxes instead of a table control. There are too many possibilities to list them all here.

Sample: C:\fakepath\JobSitesAndFees.odb

There are many ways in using SQL to join the data from the two tables to create reports etc.

If this answers your question please click on the ✔ (upper left area of answer).

Hello,

What you want to accomplish can be done with coding a macro to copy the required information from one table to another. However this is not recommended. You already have the information in the primary table and copying it to another table is just duplicating the information. Instead, whenever you need the information, it can be obtained using SQL to join the information from the two tables.

It seems the best approach here is to use a listbox to select the record desired in Table 1 using a table filter, then Table 2 is a sub form of Table 1. A sample of this can be gotten in my answer posted here - ShippingInvoice.odb and more on filters can be found here.

Edit 11/30/2017:

Here is a sample based upon your tables. Keep in mind there are a great deal of things which can be done to suit your needs. The LO Base documentation may be of help - click here.

The sample is fairly simple. Three tables: JobTable (client info); FeesTable (services, items, amounts); JobFilter (used for client selection). Two Forms: ViewJobFeeData where you can see any client and the items & fees for that client; FeeEntry where new fees/items are entered.

On the FeeEntry form, select a job number from the listbox and press 'Select Customer' button. The client site & name are displayed (per your verification requirements) and the items for that client/site are displayed in the table control below. New items are entered on a new line. Keep in mind this is a sample. You can set this for entry of NEW items only - it is just one of many properties available (see documentation). You can also set this for text boxes instead of a table control. There are too many possibilities to list them all here.

Another Edit (Updated sample & explanation):

Sample: C:\fakepath\JobSitesAndFees.odb

I did this second edit to demo the use of SQL. Have added one query. When you run it, it combines the information from the two tables. This is why you don't need to (and really shouldn't - dup info) place all the information in one table. Using SQL it can be accessed as you need it.

There are many ways in using SQL to join the data from the two tables to create reports etc.

If this answers your question please click on the ✔ (upper left area of answer).