How to update a subform field based on a listbox selection

brnrtclff · February 7, 2018, 7:29pm

Hello,

I could use some guidance on updating a subform field based on a listbox selection. The database holds customer and workorder info.

In the Workorders form, I display data from the Workorders table in the main section–and related WorkorderServices in a Subform. The Subform also contains a ServiceName listbox–which is connected to a Services table.

Tables/ Relationships

Workorders
    WorkorderID pk

WorkorderServices
    WorkorderServicesID pk
    WorkorderID fk
    ServicesID fk
    UnitPrice

Services
    ServicesID pk
    ServiceName - Attached to listbox
    UnitPrice

The listbox “automatically” updates the Workorders.ServicesID field when I select a ServiceName. But, I also want to update the WorkorderServices.UnitPrice field with the Services.UnitPrice value.

Thanks in advance

SalesDev.odb

(edit: changed false-image to base-file (warning! contains macros))

Ratslinger · February 7, 2018, 7:50pm

Hello,

The purpose of relational DB’s is to eliminate duplication. This is seemingly defeating that. Either the price is determined on the service or the work order. What is your reasoning for the duplication of information? The unit price can always be obtained whenever needed through SQL. It seems some info is missing.

brnrtclff · February 7, 2018, 9:35pm

Hi,
I get your confusion. The “default” UnitPrice is stored in the Services table. The WorkorderServices subform allows the user enter service line items and edit the UnitPrice for THAT workorder.
First, the user selects the service from the listbox–and the default price should appear in the WorkorderServices.UnitPrice field. Then, the user enters the unit quantity in the WorkorderServices.Quantity field. The user may then CHANGE the WorkorderServices.UnitPrice, if needed.
Thanks! B

Ratslinger · February 9, 2018, 9:45pm

@brnrtclff It appears you tried to post the sample but it is incorrect. You need to use the “paperclip” item from the toolbar. It seems you used the “Insert Image” item. Actually not sure what you did but it’s not correct.

Ratslinger · February 7, 2018, 10:04pm

Hello,

Cannot see a way of doing this without using a macro. When the selection is made from the listbox, trigger an On change event to execute the macro. The macro then uses the selected item in the listbox and with SQL retrieves the associated Unit price and moves it into the proper field on your form. Specifics on this are dependent upon form and table names.

Edit 2/9/18:

Seeing on how this is a bit of a communication problem, I have put together a sample loosely based upon your information. It is the more difficult of the possibilities - a table control. It is more difficult because you cannot depend upon the conventional “Event” triggers to update the fields. For example, if you are on line #1 of the table & listbox = selection “A” & no changes are made on line #1 but you move to line #2 where that listbox = selection “B” that will trigger an ‘Item Status Changed’ event. There are even more of these situations when dealing with a table control.

To get around that situation, anytime a different record is selected its’ data is saved in Global variables. Then if changes have been made to the record, the before updating event is triggered. The data is compared to the original and when warranted the unit price is modified.

Of course this is an even more simple process if individual fields were used instead of a table control. In that case only a single macro would be needed as there would be no jumping from record to record.

Sample - CopyField.odb

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

brnrtclff · February 7, 2018, 10:17pm

You’ve got it! It allows us to save pricing in the WorkorderServices table without effecting the price in the Services table. So, if I change the price of something in the Services table, it does not effect existing workorders (and vice-versa). I guess WorkorderServices is a helper table. I’ve already headed down the macro/ SQL path. Having trouble figuring out how to get the price from Services based on the listbox selection–and writing that price to the line item record UnitPrice field. thx,B

Ratslinger · February 7, 2018, 10:25pm

How to do this depends entirely upon the form construction & table details. If assistance is needed, will need a sample .odb posted (please no personal/confidential info). Have done similar many times.

brnrtclff · February 7, 2018, 11:02pm

OK. Thanks! I’m using a mySQL back-end with real customer data, so I’ll need to whip something up for you. I’ve been at this for 10 hours now and I’m pulling the plug for the day. I’ll post the .odb tomorrow PM EST. Thank you again for your help. Regards, B

Ratslinger · February 7, 2018, 11:41pm

@brnrtclff Have increased your Karma (points) to allow you to post sample. Not that it matters, but mainly use MySQL here also but have PostgreSQL & others available for testing.

brnrtclff · February 9, 2018, 7:35pm

Shoot! I misread your post. Thanks for the karma points. I’ll upload as soon as I have something portable. Thanks again! B

brnrtclff · February 9, 2018, 9:42pm

I’ve uploaded a skeleton of our database. The mySQL schema is saved in a macro.
Thanks Again! B

Ratslinger · February 12, 2018, 10:18pm

@brnrtclff Haven’t heard anything back since my posting - three days ago. Have you solved your problem?