Autocomplete a field in a form, based on criteria elsewhere?

[Chaz]

Sorry, not sure what title to use.

I have a form with multiple tabs. The one tab is Hardware. This form, based on a query, shows all hardware orders related to this 'TNE Number'. This TNE Number is on the 'header' of the main form.

So, when there are records, normally 3-5, these show no problem.

When there are no records, its up to the user to fill in the details.

A simple request from them, when they complete each line, can the TNE number field in the hardware tab be 'autocompleted' by looking up the TNE number in the header / main form area?

I dont know if this is a bit of a double negative - you find 'no' records on the basis of the query (which is correct) but yet look for a system that knows it should add this TNE Number when someone starts to add records under the hardware tab.

Thanks in advance.

 

[Chaz]

Maybe an image will help explain what im trying to do.

On the right - you can see the records shown on the basis of the queried field.

On the left, if someone started to add a new record here, how could I get it to 'autopopulate' the TNE number field?

 

[Chaz]

Anyone please?

 

[John Big Booty]

Given that your Sub Form is linked to the main form by an ID Number of some kind, I'm assuming that the TNE Number field on your Sub Form is unbound; so you can use the following as it's Control Source;

=Forms!MainFormName!TNENumber

 

[Chaz]

Given that your Sub Form is linked to the main form by an ID Number of some kind, I'm assuming that the TNE Number field on your Sub Form is unbound; so you can use the following as it's Control Source;

=Forms!MainFormName!TNENumber

The subform is not linked as such. The data for the subform is created by a query that looks up the TNE number in the top header and then displays it there.

Ill try the code that you have put - seems reasonable but not sure if it will work. The data in the core form and all the tabs on the left of hardware come from t_services and the hardware data is looked up from t_hardware_orders on the basis of the query of what is in the header combo box.

 

[Chaz]

This is the query that is used to show the hardware orders, specific to this TNE (Technical Network Element - fwiw).

SELECT t_hardware_orders.*, t_hardware_orders.HW_TNE_Number
FROM t_hardware_orders
WHERE (((t_hardware_orders.HW_TNE_Number)=[Forms]![f_tne_master]![cb_tne_number]));

 

[Chaz]

A further issue is that the control source of the TNE field is already set.

t_hardware_orders.HW_TNE_Number

So I would assume that I cannot overwrite it to say =Form(details elsewhere) as I would lose the link between the TNE number and the table?

 

[Chaz]

Just tested - it works as JBG said, but the link is then broken back into the table. so what happens is that new records are created in the hardware table, but the TNE number is not carried through.

 

[John Big Booty]

Is the TNE number the number that is linking the parent and child records?

 

[Chaz]

Yes, but there is no relationship - perhaps there should be but I was getting 'recordset cannot be updated' issues when I tried to co-join the two tables.

So at the moment the TNE number in the hardware tab is shown after a query is run to show what TNE number in the top combo field.

Perhaps my current setup simply does not allow me to do what I want to do?

 

[John Big Booty]

Could be a Normalisation error.

 

[Chaz]

Could be a Normalisation error.

Tend to agree. I think Id need to rewrite the database based on stuff I did wrong and learnt along the way. Its likely Im leaving this company (I hope) in which case I wont need to do any work on it - so will see what happens before I redo it.