Ask Your Question

Using selection to populate data in form

asked 2017-11-28 18:13:52 +0200

oli@sf gravatar image

updated 2017-11-28 18:16:10 +0200

What I am wanting to do seems very simple but due to my lack of experience I must be missing some basic understanding, please help.

Table 1 So I have a job list table: job number (primary key), site name, client name.

Table 2 I then have a fees list table: fee ID (primary key) job number, site number, client name, department, service, fee.

So I want to create a form, so when a new fee instruction is confirmed the user can insert or select the job number in the form, which will then pull data from table 1 (site name and client name), and insert all three bits of information into the form (job number, site name and client name), which the user can then add the information for the department, service and fee sections, and it all gets saved to table 2.

I have been trying to do this without a sub form. I have been using a list box which allows selection of only job numbers in table 1, however once that is selected, I obviously want the corresponding site and client names based on that job number to be inserted. This is to remove human error from the user being able to put wrong site and client names to job numbers.

For example is it possible to filter listbox 2 and 3 based on the selection in list box 1??

Thanks in advance Oliver

edit retag flag offensive close merge delete


First, when you say, "to remove human error from the user being able to put wrong site and client names to job numbers." It makes me wonder if you should not design table 1 as: Job ID, Job number, Site name, Client name. Why? Because this allows you to edit a job number, e.g. to fix a data entry error, without affecting the pointer from table 2 to table 1.

EasyTrieve gravatar imageEasyTrieve ( 2017-12-01 17:28:17 +0200 )edit

Also your copying ("pull from") does not structurally make sense to me. The very reason to use the relations of a relational data base is to avoid "data duplication", where the same data (e.g. text) is held in more than one place. This was one of the important concepts that I read about in a database book over 30 years ago, and it is a concept that has served me well over those years. As you want it Table 2 is not always correct if a spelling error is fixed in one place but not all places.

EasyTrieve gravatar imageEasyTrieve ( 2017-12-01 17:37:53 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-11-28 19:33:28 +0200

Ratslinger gravatar image

updated 2017-12-01 03:49:41 +0200


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).

edit flag offensive delete link more


Great thanks for the response I will take a look at the links you provided today.


oli@sf gravatar imageoli@sf ( 2017-11-30 12:08:46 +0200 )edit

Ok, from looking at the links you sent me:

So I am really trying to use Table 1 (list of jobs with 'job num', 'site name' and 'clinet name') as a kind of filter table. Table 2 (List of fees) is really the table where I want all the data. So without using a subform, if I could just have a form where 'list box 1' allowed the user to select the 'job number' (primary key) from 'Table 1',

oli@sf gravatar imageoli@sf ( 2017-11-30 13:01:28 +0200 )edit

Then the next two boxes/fields were autofilled with the corresponding 'site name' and 'client name', or were listboxes which were filtered so that they only displayed the 'site name' and 'client name' from 'Table 1' based on the 'job number selected' at the top of the form.

oli@sf gravatar imageoli@sf ( 2017-11-30 13:02:54 +0200 )edit

The sample with listboxes are to demo that Table 1 data stays there but allows a direct connection to data in Table 2 without the need for duplication. Your Table 1 is not a filter table. Filter tables contain only one record. If you are determined to use your two table setup with duplicate data in each, then as stated before you need to use macros written specifically for your setup. It is not an efficient method and can lead to other problems as things may change.

Ratslinger gravatar imageRatslinger ( 2017-11-30 16:41:15 +0200 )edit

Hi Thanks, so Table 1 is just for the purpose of providing information in a listbox in a form. So that users can only select 'job numbers', 'site names' or 'client names' that are in table 1 (i.e. cant put one that dosn't exist in that table 1 into 'Table 2'). 'Table 2' is where I want all the data stored.

oli@sf gravatar imageoli@sf ( 2017-11-30 16:48:46 +0200 )edit

The basic problem is I dont want users who are recording sales data to insert 'job numbers', 'site names' and 'client names' that should not go with each other. As each site has a specific client and job number.

oli@sf gravatar imageoli@sf ( 2017-11-30 16:50:49 +0200 )edit

But each job number and corresponding 'client name' and 'site name' need to be used multiple times (for each new item sold to that site)

oli@sf gravatar imageoli@sf ( 2017-11-30 16:52:12 +0200 )edit

They also when filling out the form, if say they put just the 'job number' in, need to be able to confirm the site name and client name (just in case they put the wrong job number in by accident).

oli@sf gravatar imageoli@sf ( 2017-11-30 16:54:35 +0200 )edit

so I though if they could select the job number (primary key table 1), then site name and client name (colmns 2 and 3 from table 1) would be displayed, and then also recorded into Table 2 when the form is saved. To me it seems like a very common task.

oli@sf gravatar imageoli@sf ( 2017-11-30 16:56:43 +0200 )edit

If you look at the sample, without duplicating data from one table to another, AND without the user needing to enter possible errors by duplicating entries the sample works. I have no idea why you think you need to duplicate data in the tables. Can you give a reason for this? Do you need to see a specific sample using your table design?

Ratslinger gravatar imageRatslinger ( 2017-11-30 16:58:56 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-28 18:13:52 +0200

Seen: 683 times

Last updated: Dec 01 '17