Infopath question in regards to creating rules (contains)

%3CLINGO-SUB%20id%3D%22lingo-sub-1138225%22%20slang%3D%22en-US%22%3EInfopath%20question%20in%20regards%20to%20creating%20rules%20(contains)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1138225%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20question%20in%20relation%20to%20a%20infopath.%20So%20I'm%20trying%20to%20figure%20out%20how%20to%20auto%20populate%20fields%20based%20on%20conditions.%20So%20I%20have%20purchase%20order%20form%20that%20I'm%20creating%20where%20certain%20fields%20will%20auto%20populate%20based%20on%20what%20I%20select%20from%20a%20drop%20down%20box.%20The%20main%20condition%20is%20the%20supplier%20I%20choose%2C%20and%20what%20will%20auto%20populate%20will%20be%20the%20supplier's%20address%2C%20country%2C%20phone%20number%2C%20fax%2C%20the%20main%20contact%2C%20and%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20the%20form%20for%20reference.%20I've%20been%20trying%20to%20create%20a%20rule%20that%20links%20to%20the%20supplier%20name%20field%2C%20where%20the%20condition%20is%20if%20that%20field%20contains%20a%20supplier%20name%20then%20%22run%20these%20actions%22%20which%20will%20display%20the%20correct%20address%20for%20that%20particular%20supplier.%20So%20I%20believe%20the%20rule%20type%20should%20be%20%22Action%22%2C%20which%20then%20prompts%20the%20rule%20detail%2C%20which%20should%20be%20the%20field%3A%20which%20is%20the%20SupplierName%20field%2C%20and%20the%20value%20which%20would%20be%20the%20supplier's%20address.%20Although%20this%20is%20not%20displaying%20in%20the%20preview%20mode%20when%20I%20select%20the%20certain%20supplier%20from%20the%20drop%20down%20box.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20a%20picture%20showing%20what%20I'm%20trying%20to%20do.%20Again%20the%20contain%20is%20SupplierName%20(in%20this%20particular%20situation)%20contains%3A%26nbsp%3BOehderstrasse%2028%20D-42289%20Wuppertal%2C%20GER%2042289%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20the%20rule%20type%20is%20action%20where%20if%20this%20is%20satisfied%20then%20it%20will%20display%20the%20supplier's%20address.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20let%20me%20know%20if%20I'm%20doing%20this%20correct%2C%20if%20not%20please%20tell%20me%20know%20to%20execute%20this%20properly.%3C%2FP%3E%3CP%3EThanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1138225%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1172339%22%20slang%3D%22en-US%22%3ERe%3A%20Infopath%20question%20in%20regards%20to%20creating%20rules%20(contains)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1172339%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F480499%22%20target%3D%22_blank%22%3E%40Dexlee46%3C%2FA%3E%3C%2FP%3E%3CP%3EI%20think%20you%20need%20to%20connect%20code%20to%20the%20after_update%20event.%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20run%20for%20every%20field%20you%20need%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eme.FldAdress.text%20%3D%20dlookup(%22Adress%22%20%2C%22TblSuppliers%22%20%2C%22ID%3D%22%20%26amp%3Bme.%20cmboOfSupplier.value%3C%2FP%3E%3CP%3Eme.FldPlace.text%20%3D%20dlookup(%22Place%22%20%2C%22TblSuppliers%22%20%2C%22ID%3D%22%20%26amp%3Bme.%20cmboOfSupplier.value%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20this%20is%20most%20easy%20to%20understand%20but%20creates%20for%20every%20field%20a%20run%20on%20the%20database.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOther%20option%20is%20to%20extend%20the%20combo%20where%20you%20select%20the%20supplier%20with%20more%20columns%20and%20make%20them%20invisible%20with%200cm%20width%20and%20point%20to%20them%20with%20VBA%20with%20the%20after_update%20event.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThird%20option%20is%20to%20open%20a%20recordset%20with%20VBA%20and%20collect%20the%20needed%20fields.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22hljs-keyword%22%3EDim%3C%2FSPAN%3E%3CSPAN%3E%20dbs%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22hljs-keyword%22%3EAs%3C%2FSPAN%3E%3CSPAN%3E%20DAO.Database%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22hljs-keyword%22%3EDim%3C%2FSPAN%3E%3CSPAN%3E%20rsSQL%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22hljs-keyword%22%3EAs%3C%2FSPAN%3E%3CSPAN%3E%20DAO.Recordset%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22hljs-keyword%22%3EDim%3C%2FSPAN%3E%3CSPAN%3E%20strSQL%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22hljs-keyword%22%3EAs%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22hljs-built_in%22%3EString%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22hljs-keyword%22%3ESet%3C%2FSPAN%3E%3CSPAN%3E%20dbs%20%3D%20CurrentDb%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EstrSQL%20%3D%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22hljs-string%22%3E%22SELECT%20*%20FROM%20tblSuppliers%20WHERE%20ID%20%3D%22%20%26amp%3B%20me.cmboSupplier.value%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22hljs-keyword%22%3ESet%3C%2FSPAN%3E%3CSPAN%3E%20rsSQL%20%3D%20dbs.OpenRecordset(strSQL%2C%20dbOpenSnapshot)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eif%20rsSQL.recordcount%20%26gt%3B%200%20then%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BrsSQL.movefirst%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bme.fldAdress.text%20%3D%20rsSQL!Adress%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3Bme.fldPlace.text%20%3D%20rsSQL!Place%3C%2FP%3E%3CP%3E%26nbsp%3B%20'%20and%20so%20on%3C%2FP%3E%3CP%3Eend%20if%3C%2FP%3E%3CP%3ErsSQL.close%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22hljs-keyword%22%3ESet%3C%2FSPAN%3E%3CSPAN%3E%20rsSQL%20%3D%20nothing%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1191601%22%20slang%3D%22en-US%22%3ERe%3A%20Infopath%20question%20in%20regards%20to%20creating%20rules%20(contains)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1191601%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F381533%22%20target%3D%22_blank%22%3E%40testmuts%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20chance%20you%20could%20show%20me%20how%20to%20do%20those%20tasks%20on%20a%20screen%20video%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Contributor

Hello,

 

I have a question in relation to a infopath. So I'm trying to figure out how to auto populate fields based on conditions. So I have purchase order form that I'm creating where certain fields will auto populate based on what I select from a drop down box. The main condition is the supplier I choose, and what will auto populate will be the supplier's address, country, phone number, fax, the main contact, and so on. 

 

I attached the form for reference. I've been trying to create a rule that links to the supplier name field, where the condition is if that field contains a supplier name then "run these actions" which will display the correct address for that particular supplier. So I believe the rule type should be "Action", which then prompts the rule detail, which should be the field: which is the SupplierName field, and the value which would be the supplier's address. Although this is not displaying in the preview mode when I select the certain supplier from the drop down box.

 

I attached a picture showing what I'm trying to do. Again the contain is SupplierName (in this particular situation) contains: Oehderstrasse 28 D-42289 Wuppertal, GER 42289

 

and the rule type is action where if this is satisfied then it will display the supplier's address.

 

 

Can let me know if I'm doing this correct, if not please tell me know to execute this properly.

Thanks.

2 Replies
Highlighted

@Dexlee46

I think you need to connect code to the after_update event. 

You could run for every field you need 

 

me.FldAdress.text = dlookup("Adress" ,"TblSuppliers" ,"ID=" &me. cmboOfSupplier.value

me.FldPlace.text = dlookup("Place" ,"TblSuppliers" ,"ID=" &me. cmboOfSupplier.value

 

I think this is most easy to understand but creates for every field a run on the database. 

 

Other option is to extend the combo where you select the supplier with more columns and make them invisible with 0cm width and point to them with VBA with the after_update event.

 

Third option is to open a recordset with VBA and collect the needed fields. 

 

Dim dbs As DAO.Database

Dim rsSQL As DAO.Recordset

Dim strSQL As String

 

Set dbs = CurrentDb

strSQL = "SELECT * FROM tblSuppliers WHERE ID =" & me.cmboSupplier.value

Set rsSQL = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

if rsSQL.recordcount > 0 then

     rsSQL.movefirst

     me.fldAdress.text = rsSQL!Adress

     me.fldPlace.text = rsSQL!Place

  ' and so on

end if

rsSQL.close

Set rsSQL = nothing

 

Highlighted

@testmuts 

 

Any chance you could show me how to do those tasks on a screen video?