Fill a field in function of two other

[Lily21]

Hi,

I want my field to be filled automatically according to two other fields. The three are all from the same main table.

Poste : filled with a query (a list )

Service : filled from a table (a list)

Supplier : i need that to be filled automatically when I choose Poste and Service.

I tried that query it work when I run it and I write a Poste and a Service it return me the right Name of the Supplier :

SELECT Table_FPS.name_supplier
FROM Table_FPS
WHERE (((Table_FPS.Service)=[Form]![Form_FPS]![Service]) AND ((Table_FPS.Poste)= [Form]![Form_FPS]![Poste]

But when I Put my field with this query in the form it return me nothing when I choose the service and the poste.

Thank you !

[Welshgasman]

I would have thought you would need a DLookUp()

[Gicu]

Lily21,

I think you're confusing data entry with data retrieval. If your form is bound to the Table_FPS table there is no way to know when adding a new record that the supplier for a poste\service combination will be the same as an existing record or it would be a totally new one.

But if the form is meant to populate some other table in which you need to store poste and service and display the associated supplier I think you actually should do it via a combo box to select supplier with all three columns visible in the drop-down list. Then to show the poste and service simply use Me.cboSupplier.Column(1) and Me.cboSupplier.Column(2) for the control sources of the poste and service textboxes (assumes the supplier is the first column of the combo).

Cheers,

[Lily21]

I Tried DLookUp

DLookUp("Supplier","Table_FPS","Service =" & [Service] AND "Poste =" & [Poste])

But it return this : #Error

[Lily21]

Lily21,

I think you're confusing data entry with data retrieval. If your form is bound to the Table_FPS table there is no way to know when adding a new record that the supplier for a poste\service combination will be the same as an existing record or it would be a totally new one.

But if the form is meant to populate some other table in which you need to store poste and service and display the associated supplier I think you actually should do it via a combo box to select supplier with all three columns visible in the drop-down list. Then to show the poste and service simply use Me.cboSupplier.Column(1) and Me.cboSupplier.Column(2) for the control sources of the poste and service textboxes (assumes the supplier is the first column of the combo).

Cheers,

Hi Gicu,


My Form is Bound with another table (MAIN_TABLE)

And its a form to enter data in the MAIN_TABLE

and I Have another Table Where there is the information of : Supplier, Poste and Service
So I Want When I select a Poste (a list) and Service (a list) in my form it will return me the name of the supplier from the table TABLE_FPS

[Welshgasman]

I Tried DLookUp

DLookUp("Supplier","Table_FPS","Service =" & [Service] AND "Poste =" & [Poste])

But it return this : #Error

You cannot just mix text with fields? You need to concatenate them.

DLookUp("Supplier","Table_FPS","Service =" & [Service]  & " AND Poste = " & [Poste])

and that assumes both are numeric?
If not, the fields need to be surrounded(normally by single quotes ')

I tend to put the criteria into a string variable and then you can debug.print it to check it is correct.?
Even if just the control source, then you need to test.?

[Gicu]

So I would then go with my earlier suggestion of either selecting the supplier from a three-column and display the poste and service in two textboxes bound to the second and third columns of the supplier combo or have two cascading combos for poste and service and display the supplier in a textbox bound to the supplier column in the second service combo.

I think it would be better to save one field (the supplier) in the MAIN_TABLE instead of two (Poste and Service).

Cheers,

[Lily21]

Hi Welshgasman

DLookUp('Supplie',"Table_FPS",'Service =' & [Service] AND 'Poste =' & [Poste])

So I tried With just ' and its the same result : #Error

[Welshgasman]

Hi Welshgasman

DLookUp('Supplie',"Table_FPS",'Service =' & [Service] AND 'Poste =' & [Poste])

So I tried With just ' and its the same result : #Error

Not the text, the fields

DLookUp("Supplier","Table_FPS","Service ='" & [Service] & "' AND Poste = '" & [Poste] & "'")

You missed r off supplier and put the AND back in with the fields?

[Lily21]

Gicu,

I want to save all of them : supplier, poste and service in my MAIN_TABLE

And I'm not sure to hunderstand what I have to do with your suggestion :

I have to put in the control source of the combobox :
=Me.cboSupplier.Column(1) in the combobox (Service)
=Me.cboSupplier.Column(2) in the combobox (Poste)

and it will charge the textbox of the name of my supplier?

[Gicu]

You want to save them all but that is the wrong approach as you are duplicating data already existing in the TABLE_FPS. What if the supplier changes in the future for a poste\service combination; you will have to update all the MAIN_TABLE records to update that info. All you need to store in the MAIN_TABLE is the primary key (unique record identifier) of the TABLE_FPS that you want to associate with the MAIN_TABLE record. In a query you then link those two fields and you can retrieve all three fields.

Please have a look at the sample I just made for you to see how the combo\textboxes would work.

Cheers,
Vlad

[Lily21]

Sorry maybe I didn't explain well

But the user dont know what's the name of the supplier, he know only the poste and the service so thats why I want that to be automatically filled.

And I want all of the information in the main_table because this table will be export to excel to do other manipulations.

I dont know if its possbile with DlookUp or a query but I Tried both and it dont work. Maybe because of my field of Poste and Service?, because both of them are a list comming from other table and filtered with a query

[Gicu]

You don't need them in the table as you can create a query linking MAIN_TABLE to TABLE_FPS and bring the fields in for the export to Excel. But anyway please have a look at the updated sample that does what you want. The two combos are "cascading" meaning the Service one shows only the entries for the already selected Poste; in the AfterUpdate event of the service combo there is code that updates the supplier to the second column of the chosen service.

Cheers,
Vlad

[Lily21]

Thank you so much for your help Gicu it work

[Gicu]

Cheers,