Autofill

[TPS]

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]

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]

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]

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]

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]

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]

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:

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]

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]

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]

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]

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]

Try:
=Parent.product_group_code.column(1)

 

[TPS]

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]

Replace

Product_group_code

With the name of the combo box on the main form

 

[TPS]

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]

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

 

[TPS]

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]

as said post the Rowsource of the combobox.

 

[TPS]

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]

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