How to create new fields/columns in a Form to be linked to multiple tables

[jbyuriko]

HI, I am still working on my database. I am trying to make a form to add data to the set of preexisting data ( in an query). The additional data includes items to be stored to tables , not to the query that existing data is refer to.

If someone can show how to create new columns (items) in the subform grid and link them to the tables, I’d very much appreciate.

The form: PaymentEntryForm
the query the main form is based on MainT

The new items are ‘payment’ and ‘note’ in the subform grid . The subform is based on an query, PriceAppendQ
The relationship that I want to create with new items are:
inputs to ‘payment’ —> ‘payment’ in the table “ProcedurejuncT”
inputs to ‘note’ ------> ‘note’ in the table “LogT”

The bottom lie is that when you create a new form, you choose one table/query to refer to. But I need more than one table/query to store the information from the form.

As usual, I cannot appreciate enough for your help!

Yuriko inquiryfile.odb

[Ratslinger]

Hello,

Started to look at your sample & relate it to your question. It appears you have two major problems.

First is you lack of understanding of Base Forms. Now please bear with the terms (not my doing). The Forms section on the main Base screen allows opening a “Form”. This Base Form actually contains internal “Form(s)”, sub forms, sub sub forms etc. It can have MULTIPLE internal “Forms” each having sub forms, sub sub forms etc. Now each of these forms, sub form sub sub forms etc. can be associated with a TABLE. So the bottom line is how these are all set up and possibly linked for proper association.

The next problem is your design. Without going too deep into looking at your structure, it appears you have not separated out data into proper tables. For example, MainT really should be a Customer table with only customer info. Other information such as payments, procedures, billing amounts etc. should be in related tables. This keeps data organized and accessible in a variety of ways. No need for at least some of these queries you have (again only a brief look).

Unfortunately, if you do not address this now it is probable you will only add to more complicated answers in the future.

To give you a minuscule idea of the forms part, I had started to re-do the PaymentEntryForm when seeing the potential problems. The small revision is the PaymentEntryFormModified. Instead of using a query with parameters, the main form is a List Box for last names & Date control for Service Date. These are tied to a Filter table which is used for MainT table display.

Sample ---- FormTableProblems.odb

Note: There are MANY ways to enhance the selection process for this form.

Edit:

You mentioned replacing query with list boxes but that is not true. What was done was to use table filtering in conjunction with the list box/date control. See → Filter/Search with Forms (leveraging SubForms). I have posted many samples in this forum using table filtering.

LO documentation is located here → Documentation/Publications. You will find Base documents, samples & toward the bottom some how-to’s.

SQL is a must for working with databases. It can easily help avoid the need for using macros. The are many sites on the net to help with this. Here is one → w3schools.com.

There is a great deal more but don’t want to overwhelm you. This should get you in the right direction.

[jbyuriko]

Thank you for the quick reply. I was aware that not having a customer table is problematic. I will redo the table setting from scratch and streamline the data so each table can be used as efficient foundation. I also knew that the form can be made with subforms but not that the use of the list box can replace queries… Do you have suggestion where to learn more about BASE, any course, apps, or should I study SQL? I had watched all videos ofYoutube by Frugal ComputerGuy.

[Ratslinger]

ALL of what you said. For lack of space in comments, will append some links in my answer.

[jbyuriko]

Thank you for the additional comment. I will learn from the basics of database. The suggested websites look great. Thanks!