Combo Box and vbcrlf

[angelaw]

Currently have combo box set up to pull from 4 fields in one table and to dump into into one field on another table. Row source is (from sqlview):

SELECT DISTINCTROW [Def].[Defname] & " " & [Def].[Addr1] & " " & [Def].[addr2] & " " & [Def].[addr3] AS expr1 FROM def ORDER BY [Def].[Defname] & " " & [Def].[Addr1] & " " & [Def].[addr2] & " " & [Def].[addr3];

have tried adding vbcrlf after defname, addr1, and addr3 but it doesn't put the format of the combo box correctly. This if for names and addresses of people that will have to merge with a word document. I'm already going to have to replace the mergefields in all the documents to go from 4 fields to one, but I need it to format on the display and table correctly. Any ideas?

 

[cmmrfrds]

Your question is not clear. Why are you adding vbcrlf to the combobox?

 

[angelaw]

Need to have it formatted as an address in order for it to merge properly with the word document form.

 

[angelaw]

either that or have the original 4 fields setup as 4 different combo boxes and have all the information in boxes 2,3,4 fill in automatically depending on what's selected in combo box 1.

 

[cmmrfrds]

Data can be pulled from the combobox in relative postion.

YOUR ROW SOURCE - i.e. 4 columns
SELECT DISTINCTROW [Def].[Defname], [Def].[Addr1], [Def].[addr2], [Def].[addr3] FROM def ORDER BY [Def].[Defname], [Def].[Addr1], [Def].[addr2], [Def].[addr3];


Combobox name = cb1
in the On click event of the combobox.
Dim var as string
var = cb1.column(0) & vbtab & cb1.column(1) & vbtab & cb1.column(2) & vbtab & cb1.column(3)

debug.print var

 

[angelaw]

Tried above with:
var = attyinfom.Column(0) & vbTab & attyinfom.Column(1) & vbTab & attyinfom.Column(2) & vbTab & attyinfom.Column(3)

get compile error, variable not defined ??

 

[angelaw]

All by using the above code you provided, all the other info from the row source doesn't get displayed. Just the names and nothing else.

 

[angelaw]

fixed variable not defined erro, but still only gives name not rest of address from box and if i put the original rowsource back and test with the onclick code doesn't go to the next line. just wraps it at the end of each line

 

[cmmrfrds]

First to display the 4 columns check the properties for the commbobox. Column Count should = 4 and column width should be something like 1.5";2.0";1.5";1.0" In order to see the 4 columns.

Did you dimension the variable?
Dim var as string

 

[cmmrfrds]

Probably need Me in front of the combobox control name. "Me" is an alias for the current Form.

var = Me.attyinfom.Column(0) & vbTab & Me.attyinfom.Column(1) & vbTab & Me.attyinfom.Column(2) & vbTab & Me.attyinfom.Column(3)

 

[angelaw]

The columns are set for:1.5";2";1.5";1", rows 8, width 4"
data row source is:
SELECT DISTINCTROW [Def].[Defname], [Def].[Addr1], [Def].[addr2], [Def].[addr3] FROM def ORDER BY [Def].[Defname], [Def].[Addr1], [Def].[addr2], [Def].[addr3];

on click is:
Private Sub attyinfom_Click()
Dim var As String
var = Me.attyinfom.Column(0) & vbTab & Me.attyinfom.Column (1) & vbTab & Me.attyinfom.Column(2) & vbTab & Me.attyinfom.Column(3)
End Sub

With all this, the information appears in the drop down, when you select from the combo list the name, only the name is in the box, none of the other information is in there, just the name. So I can't see if the vbTab is working or not, no longer have the error now though. That's why I had changed the , to & in the distinctrow list with the columns set to 3,0,0,0 to get everything to appear in the box. I would like to be able to pick this information and have it all in the box if possible. The word doc merges to the table via odbc so I'm not sure if the formatting would even stick to it, might have to try to modify the table instead of the form?

 

[cmmrfrds]

I was showing the example of putting the formatted string in a variable so that the variable could be used whereever you need it. Put in a msgbox to see the results.

Dim var As String
var = Me.attyinfom.Column(0) & vbTab & Me.attyinfom.Column (1) & vbTab & Me.attyinfom.Column(2) & vbTab & Me.attyinfom.Column(3)

msgbox var

I don't know how this relates to the combobox.
"The word doc merges to the table via odbc"

 

[angelaw]

The message box pops up with the info requested, just not as needed with 3-4 lines for address format. Getting ready to make 3 macros for each attorney at this point. My whole goal was based off of having an attorney list that one attorney could either be for personA personB or personC. Needed to populate address information for any of the above people. So now looking at just writing macro, e.g. attorney_a to go to the controls I need for personA, attorney_b with same information just go to control personB. But I was trying to avoid having to duplicate each attorney 3 times. Can't figure out how to just do a macro like in word that basically says, I'm in this field, now do this, but not having it dependent on being in a control, just a macro for text. Beginning to really not like access! uuggh.

Angela