Autofill (1 Viewer)

TPS · Oct 21, 2019

Hi,

Can anyone help me with autofill? What I want to happen, is that if a drop down is selected and an option clicked, I want that drop same drop down, to be updated in different sections of the form.

Example is below:

So, if a user selects 010 in CRM.Product Group, I want the same product group drop down in Marketing Template.Product Group to update.

Make sense?

Minty · Oct 21, 2019

You don't need that to be stored again in that data if it available in the master record?
If it's visible in the top section why bother repeating it in the subform?

This might be what Pat was discussing about getting your data structure correct in the other thread?

TPS · Oct 21, 2019

Minty said:
You don't need that to be stored again in that data if it available in the master record?
If it's visible in the top section why bother repeating it in the subform?

This might be what Pat was discussing about getting your data structure correct in the other thread?

Managers and director wants it that way. There's no way around it unfortunately. :banghead:

Minty · Oct 21, 2019

There is - simply display it, don't store it again.
What happens if someone inadvertently changes the value in the subform or the top record and the sub-record doesn't get the update at the same time?

This is about structuring your data correctly. If your end-user wants to see it twice on screen then fine display it twice, but don't store it twice.
That leads to all sorts of issues later on.

TPS · Oct 21, 2019

Minty said:
There is - simply display it, don't store it again.
What happens if someone inadvertently changes the value in the subform or the top record and the sub-record doesn't get the update at the same time?

This is about structuring your data correctly. If your end-user wants to see it twice on screen then fine display it twice, but don't store it twice.
That leads to all sorts of issues later on.

The layout is unchangeable, as I said directors/management want it this way. That's why I thought duplicating the selecting/autofill is the best way.

Otherwise, from what you are saying, are you correct in saying that I can just copy the field from the top and paste it into the sub form?

Gasman · Oct 21, 2019

If you think about it, if you put the same value in the subform, you do not want them to change it surely?, so that control should be disabled.?

arnelgp · Oct 21, 2019

it's just a matter of updating all Subform's product group combo.
on after update event of Product_Group_Code combo on main form:

Code:

private sub product_group_code_afterupdate()
with me.subform1.form
    !product_group = me.product_group_code
    .dirty = false
end with
with me.subform2.form
    !product_group = me.product_group_code
    .dirty = false
end with
…
…
end sub

TPS · Oct 21, 2019

Gasman said:
If you think about it, if you put the same value in the subform, you do not want them to change it surely?, so that control should be disabled.?

Must be displayed as I said, it's management/director decision and my hands are tied. Cannot be hidden but it can default to whatever has been selected at the main form.

It can be disabled provided it auto updates.

Minty · Oct 21, 2019

It's simple to display. Make the control unbound, call it txtProductGroup.
Then set its control source to the control in your main form.
You can duplicate this anywhere in all your subforms.

Autosaving the value isn't required because you aren't saving it. You already have it saved in the master record.

I'd still question why you need to see it at all when it's visible 2 inches above where it is duplicated and is wasting valuable screen real estate.
Just because they used to always see it there doesn't mean its still got to be there in your new efficient database.

TPS · Oct 21, 2019

Minty said:
I'd still question why you need to see it at all when it's visible 2 inches above where it is duplicated and is wasting valuable screen real estate.
Just because they used to always see it there doesn't mean its still got to be there in your new efficient database.

Which is what I said but it has to be kept. :banghead:

TPS · Oct 21, 2019

Minty said:
It's simple to display. Make the control unbound, call it txtProductGroup.
Then set its control source to the control in your main form.
You can duplicate this anywhere in all your subforms.

Autosaving the value isn't required because you aren't saving it. You already have it saved in the master record.

I'd still question why you need to see it at all when it's visible 2 inches above where it is duplicated and is wasting valuable screen real estate.
Just because they used to always see it there doesn't mean its still got to be there in your new efficient database.

How do I get it to show the description, rather than the number? I.e. in the text box, it's showing 1 rather than 010 - CONSIGNMENT-UK SALES

bob fitz · Oct 21, 2019

Try:
=Parent.product_group_code.column(1)

TPS · Oct 21, 2019

bob fitz said:
Try:
=Parent.product_group_code.column(1)

It didn't work unfortunately. It just shows a blank.

My data that populates this field, is in a table called tbl_product_group. The field I want to display is called product_group_concat

I tried this code, but again I get a blank:

=[tbl_product_group].[product_group]

bob fitz · Oct 21, 2019

Replace

Product_group_code

With the name of the combo box on the main form

TPS · Oct 21, 2019

bob fitz said:
Replace

Product_group_code

With the name of the combo box on the main form

Doesn't work. The combo Box, on the main form is called Product_Group_Code. Formula used is =[Parent].[Product_Group_Code].[column](1)

Just returns me a blank.

bob fitz · Oct 21, 2019

Show us the SQL statement used for the combo box please.

TPS · Oct 21, 2019

bob fitz said:
Show us the SQL statement used for the combo box please.

The new unbound text box is also set to the same, but it shows a 1 or a blank if I use the code from bob fitz.

arnelgp · Oct 21, 2019

as said post the Rowsource of the combobox.

TPS · Oct 21, 2019

I've got it somewhat working. I made a unbound combobox on the main form and I used this code =[Product_Group_Code].[column](1). It works as if I change the main product group combo box. The new unbound one will also change.

However, the same code doesn't work if I use an unbound combobox in my subform.

Any ideas?

bob fitz · Oct 21, 2019

It needs to be prefixed with:
Parent.
Or
Forms!NameOfMaimForm.

Autofill (1 Viewer)

Banned

AWF VIP

Banned

AWF VIP

Banned

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

Banned

AWF VIP

Banned

Banned

AWF VIP

Banned

AWF VIP

Banned

AWF VIP

Banned

..forever waiting... waiting for jellybean!

Banned

AWF VIP

Similar threads

Users who are viewing this thread