Concatenated address in query

[TR21Mark]

Its me again!

on my frmLetters form I have a combobox listing General Contractors from tblGCont. I have a second textbox which when the GC is selected, I would like to have their full mailing address concantenated ( sp?)

=DLookup is not working for me so vb code should be the best bet. All items are in the same tblGCont.

tblGCont = table name
GC = field Gen Cont name is stored
GCAdress = Field for street name and suite no
City = City
State = State
Zip = Zip


Thanks to all for any help given.

 

[cstlaurent]

Private Sub command_click(Cancel As Integer)


Dim db As DAO.Database
Dim rstadd As DAO.Recordset
dim strgc as string

strgc = "select * from TBLGCOnt where Id = forms!formname!combox!"

Set db = Application.CurrentDb
Set rstadd = db.OpenRecordset(strgc, dbOpenDynaset)

txtGC = rstadd("GC")
txtadd = rstadd("GCAdress")
txtcity = rstadd("city")
txtst = rstadd("state")
txtzip = rstadd("zip")

rstadd.Close

End Sub

this code will fill each txt. if you want it all togetter it will look like that
txt = rstadd("GC") & rstadd("GCAdress") & rstadd("city") & rstadd("state") & rstadd("zip")
good luck, it's fairly straight foward...

basically you open a recordset and asking it to pull the address of the contractor.

 

[TR21Mark]

Where does this need to be added? In the after_update() event of the combobox, or just the form module?

Thanks

 

[cstlaurent]

you can put it basicaly anywhere on your form, or just on the afterupdate action. it's up to you. As long it's in the form, not as a module. It is a privata sub function.

don't forget to adapt the name of the table and fields.

 

[TR21Mark]

Thanks alot!

 

[TR21Mark]

I keep getting Run Time Error 3061
Too Few Parameters. Expected 2.


Question, where you have txtGC = rstadd("GC") - I put

Text60 = rstadd("GCAddress")

Text60 is the name of the textbox I want the address to be shown

I have followed your example syntax, punctuation, etc to a T.

Thanks

 

[cstlaurent]

could you post the whole code you wrote down.

You could also run debug to run step by step the code.

the "GCAddress" as to be the name of the field in your table.