Problem with field being un-editable/invisible in Base form

[RonBaker]

Hello,

I’m currently having an issue where the quantity field in my subform is not displaying and can not be edited/selected. I have included a screenshot. I have a total field which generates based on the product of item price * QTY. The QTY field be modified through the table view and the final accurately depicts the result of this calculation regardless of the field being invisible in the form; However for the sake of usability, I need QTY to be modifiable in the form itself. The item and price fields exist in my “ITEMS” table and QTY is from the “SOLD” table. The sold table tracks individual items by linking them to a particular invoice and also tracks QTY per item. I’ve included an image of the Query for the subform and a image of the subform itself.

[Album] Subform and subform query

query: query and subform - Album on Imgur

[Ratslinger]

Hello,

What database are you using? Sample would be easier to work with. Hard to read image and not certain where the data is from - Query?

Edit: If this is from a query, is Qty an alias?

[Ratslinger]

I may not understand correctly so please help. From what I see, you are going to want to do updates to the sold table. The sold table contains the field Qty but where is that selected in the Query? This query should have all necessary fields for updating the table. Do not see ID selected either.

[RonBaker]

@Ratslinger Using MySQL through jdbc. Here is an image of the relationships: relationships - Album on Imgur

[Ratslinger]

@RonBaker,

That does not cover the Qty field not being in your query or the ID field. Please see my answer with sample.

Edit:

The relationship in the sample is basically the same as the one in your image.

[Ratslinger]

@RonBaker,

Have also come up with a number of other issues. Instead of constantly going back & forth, I put together a Base file (used HSQLDB embedded - hope you have Java installed for that).

It occurs to me as looking at the images (tough way to debug) there was an item missing. There was customers and items to be selected and sold items. However did not see any Invoices.

A customer can have many different invoices. Each invoice can have many items. So generated the attached with another level in between the customer and the items sold - invoices.

There are two forms. The first is simply getting all to display (note my information is much less than the number of items you have). This form is customers. Then created a second form elaborating on the sold section. This is the customers_modified form. It has an additional form below the sold items for invoice totals. When you modify the quantity as you wanted, the results can be updated by a button press. This simply refreshes the SubSubForm and presents updated totals.

There is a lot more which can be done here but this, hopefully, will give you some direction.

Please let me know if this is way off base for what you want.

Sample ---- Customer Invoices.odb

Edit:

In case you do not have Java installed, here is a Firebird embedded sample which should work:

Sample ---- Customer Invoices Firebird.odb

Edit 2021-01-05:

Responding to comment by @RonBaker on 2021-01-05 at 21:46:58 +0100

Stated:

I’ll send over an updated schema of the invoice table

After waiting over 5 hours without an update, will post what I can here.

Would you be willing to do a screenshare session?

Not at this point.

I’m a bit confused as to some of your responses as they don’t necessarily relate to the functionality of the form.

You do not specify these responses. Have stated multiple time that your Query for the Subform where you have the problem with Qty not displaying is because it is not selected in the Query. Do not see how this is not understood. Here is the image you posted in the question for the Query:

In addition, here is the form you posted with the SQL used for this sub form:

Neither of these contains the field Qty.

You stated:

I’m currently having an issue where the quantity field in my subform is not displaying and can not be edited/selected.

You cannot display or edit a field which is never selected in the query for the sub form. Plain and simple. Did state this in my comment. Also stated there the ‘ID’ field is also not selected. If you want to edit the field you will need the primary key of the table. Also presented a working sample of this in my Answer before you ever presented your form or tables. Basic stuff as stated in the comment.

You state:

Every aspect of the form works elegantly, except for the QTY field.

I still don’t have enough to make the form work but looking at items I cannot agree with your statement. This:

button to add item to invoice specifies to refresh form. How is that? This is just one item.

There are naming problems - many. There are two SubForm2s. The same total field in multiple places. Labels for a field on one form placed on another form. Query is missing for Invoice list box. And more.

You state:

Structurally, everything should be sound, which is why I’m wondering if something is getting lost in translation here.

I am just wondering if you are looking at what you are posting because what I see, and have posted some of here, is not what you are stating.

Even multiple table names are different - 3 at last count. Got the hint when you posted about the mail merge situation (afraid to respond to that with my answer). Seems you are changing your items and not updating what is posted for this question.

Hard to think about helping or doing work for someone when you neither get what is needed for the job nor does the person seem to comprehend the answers provided.

[RonBaker]

Here is a link containing all of the schemas I’m using for the form along with the form itself.
https://drive.google.com/drive/folders/1pwVG68A7FZNYhx2164DeNl4JeXDiPOt7?usp=sharing

Your solution was very elegant, however, I’m wondering if there is a way to modify my current form to correctly display quantity and maintain its current structure? I can go into table view and input QTY manually and the integer for total will correctly reflect based on the quantity I’ve inputted. I’m wondering if there would be a way to adjust the query so the QTY field is actually modifiable in the form? Sorry if I’m not totally following your suggestions, my knowledge of Base is limited. If you have an opportunity to look over the form and how it’s currently designed I would greatly appreciate it. Additionally, if you would be able to do a screenshare and assist further I could compensate you for your time. Thank you.

[Ratslinger]

@RonBaker,

Will look at this. Initial reaction says something on your form needs to change.

[Ratslinger]

@RonBaker,

BTW the suggestion is quite simple. Your Query is incorrect. You never select the Qty field. Since this is not selected how can it be displayed or updated? Also ID is not selected. Again, how can you expect to update the record if you do not even have the ID to access it? These were items first noted in my 2020-12-28 second comment and once again in my last comment.

The sample provided was not to suggest your form should look as the one in the sample, it was simply to show how to get, display, and update the Qty field in the subform using a query.

Sorry to say but this is kind of basic stuff.

[Ratslinger]

@RonBaker,

Trying to make something out of what you posted but I frankly give up. What you posted has its’ own problems. The first is on your main form. It specifies the table odbc_dbase1.office but in odbc_dbase1_office2020.sql the name of the table is main. Another problem is that odbc_dbase1_invoice.sql doesn’t even contain anything to create a table. It only (of value) has:

LOCK TABLES `invoice` WRITE;
/*!40000 ALTER TABLE `invoice` DISABLE KEYS */;
INSERT INTO `invoice` VALUES (1,0,NULL,NULL,NULL,NULL,1,NULL);
/*!40000 ALTER TABLE `invoice` ENABLE KEYS */;
UNLOCK TABLES;

This is just some of the initial reaction. This doesn’t even cover the mess in the form itself. Would seem best to redo most and this could take many hours because all the potential problems have yet to be uncovered.

[RonBaker]

I’ll send over an updated schema of the invoice table, however, none of my tables are lacking the creation criteria, I think I sent you the wrong file by mistake. Again, if possible, Would you be willing to do a screenshare session? I’m a bit confused as to some of your responses as they don’t necessarily relate to the functionality of the form. Every aspect of the form works elegantly, except for the QTY field. The invoice and sold fields populate correctly and there is not problems with the office2020 or client tables. Structurally, everything should be sound, which is why I’m wondering if something is getting lost in translation here. If you happen to have fiverrr or upwork account please let me know and I’d love to work with you further or at least have you take a look at the form within my actual environment.