Ask Your Question
1

How to update a subform field based on a listbox selection

asked 2018-02-07 20:29:43 +0200

brnrtclff gravatar image

updated 2018-02-09 22:41:34 +0200

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 advanceC:\fakepath\SalesDev.odb

edit retag flag offensive close merge delete

Comments

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.

Ratslinger gravatar imageRatslinger ( 2018-02-07 20:50:04 +0200 )edit

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

brnrtclff gravatar imagebrnrtclff ( 2018-02-07 22:35:05 +0200 )edit

@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 gravatar imageRatslinger ( 2018-02-09 22:45:26 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2018-02-07 23:04:34 +0200

Ratslinger gravatar image

updated 2018-02-10 00:55:29 +0200

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.

edit flag offensive delete link more

Comments

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

brnrtclff gravatar imagebrnrtclff ( 2018-02-07 23:17:32 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2018-02-07 23:25:47 +0200 )edit

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

brnrtclff gravatar imagebrnrtclff ( 2018-02-08 00:02:33 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2018-02-08 00:41:41 +0200 )edit

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

brnrtclff gravatar imagebrnrtclff ( 2018-02-09 20:35:00 +0200 )edit

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

brnrtclff gravatar imagebrnrtclff ( 2018-02-09 22:42:57 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2018-02-12 23:18:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-02-07 20:29:43 +0200

Seen: 257 times

Last updated: Feb 10 '18