Concatenate Cargo 1

[TimTang]

Hi all!

I'm trying to concatenate the last three cargo's of a vessel. I have tblLastCargo with LastCargo_ID and strCargo which contains the cargo types. This table is linked by a one-to-many relationship in the tblPosition to strLastCargo1,2,and 3.

I also have a frmPositions which has the three last cargo's as cboLastCargo1,2, and 3 as well as one text field strLastCargo. The record source for the form is tblPositionsQuery.

What I'd like to do is select each of the last 3 cargo's using the combo boxes and have the results placed in the text field like this

BIODIESL/PALMOIL/TOLUENE

I've seached this site and all over the internet but I still haven't been able to figure it out. the closest I got was 3 numbers with spaces which I assume was the LastCargo_ID

I'm also woundering if it's better to place the results in the table or just create the results in the form. I guess it's bad carma to duplicate data in a database.

Any assistance would be greatly appreciated.

Cheers!!

 

[Remou]

You will need the column number (starting from zero):

For Each varItem In ctl.ItemsSelected
  strList=strList & ", " & ctl.Column(1,varItem) 
Next
strList=Mid(strList,2)

 

[TimTang]

Thanks Remou

I'll give that a try.

Does it matter whether I use the tblPositions or tblPositionQuery?

Thanks again!

 

[Remou]

Oops. The sample code relates to a listbox, however re-reading I see you have three combos (a multiselect list box might be better). For a combo, you would still need the column:

strList=Me.cboLastCargo1.Column(1) & ", " & Me.cboLastCargo2.Column(1) & ", " & Me.cboLastCargo3.Column(1)
Me.txtList=strList

You will need to run code in the after update event of each combo and the current event of the form. The table is what ever table the combo is based on. The above assumes that by text field you mean a field bound to a textbox. Is this what you were thinking of?

 

[TimTang]

Allright!...now we're cookin'

The code makes perfect sence; it looks like it should work. I'll try it when I get home. The concatenation will eventually land in a basic text box.

I like your "multiselect list box" idea. Does that mean I could eliminate the three combo's and replace it with one list box.

That would be great if I could do that. It would be faster, more efficient, and take up less real-estate on the form.

You guys are great! If there's a computer heaven, you're definitely on your way for helping pathetic newbies like myself.

Cheers!

 

[Remou]

Does that mean I could eliminate the three combo's and replace it with one list box.

Yes.

 

[TimTang]

Hi Remou,

I've done some research on your idea of using a list box. The problem with the list box is that I don't have enough space for one so I'd rather use a drop down list box or a multiselect combo.

I'm currently using Access 2002 and there doesn't seem to be a drop down list and the combo box can only be used for a single selection.

Is there some way to import a drop down list or make a combo multi select?

Cheers!!

 

[Remou]

I do not believe that this is possible in Access. You could create a very short list box, I guess.

 

[MajP]

I think you can fake a drop down list box.
lets say my list box is called "lstOne".
The default height is .25"
and the drop down height is 5.2"

Private Sub lstOne_Enter()
  lstOne.Height = 5.2 * 1440
End Sub

Private Sub lstOne_AfterUpdate()
  Me.someControl.SetFocus
  lstOne.Height = 0.25 * 1440
End Sub

Private Sub lstOne_Exit(Cancel As Integer)
  lstOne.Height = 0.25 * 1440
End Sub


Private Sub Form_Load()
  'Ensure that it is at the small height
  Me.someControl.SetFocus
  lstOne.Height = 0.25 * 1440
End Sub

 

[TimTang]

Hi MajP,

Thanks for that! It works great, but I also had to set the Width and Left to make it look like a real drop box. There's no down arrow but I think I can live without it.

Now I just have to figure out how to place the three selections into the text box.

Cheers

 

[Remou]

Use the ItemsSelected Collection

If Me.lstList.ItemsSelected.Count > 0 Then
   For Each itm In Me.lstList.ItemsSelected
     strSel = strSel & "," & Me.lstList.ItemData(itm)
   Next
End If
<...>

You will need to use the Column property if you wish to return anything other than the bound column.

 

[TimTang]

Hello Remou,

It almost works except the list box returns numbers instead of text.

Also, the list box automaticaly selects item number 1 even though I haven't selected any thing.

Thanks for the assistance.

Cheers!!

 

[Remou]

You will need to use the Column property if you wish to return anything other than the bound column.

 

[TimTang]

Hi Remou,

I've tried fitting the Column property in the code using the example you posted earlier but I seem to get syntax errors. The "SelectedCargos" seems to be populated by column 0 instead of 1.

Is the Column property set in the code or another control?

Here's my code so far:

Private Sub lstLastCargo1_AfterUpdate()

Dim SelectedCargos, strCargo
Dim ctlList As Control

For Each strCargo In lstLastCargo1.ItemsSelected
If SelectedCargos > "" Then
SelectedCargos = SelectedCargos & "/" & lstLastCargo1.ItemData(strCargo)
Else
SelectedCargos = lstLastCargo1.ItemData(strCargo)
End If
Next strCargo

Me.txtLastCargo = SelectedCargos

' Return Control object pointing to combo box.
Set ctlList = Me!lstLastCargo1
' Requery source of data for combo box
ctlList.Requery

lstLastCargo1.Height = 0.1438 * 1440
lstLastCargo1.Width = 0.1667 * 1440
lstLastCargo1.Left = 4.0417 * 1440

End Sub

To MajP:

your resizing list box works fine but I was just wondering what the significance of the * 1440 at the end of each resizing line.

 

[Remou]

Assuming that the cargo 'name' is in the second column (column 1):

For Each strCargo In lstLastCargo1.ItemsSelected
If SelectedCargos > "" Then
SelectedCargos = SelectedCargos & "/" & lstLastCargo1.Column(1, strCargo)
Else
SelectedCargos = lstLastCargo1.Column(1, strCargo)
End If
Next strCargo

Ensure you have the correct column count in the property sheet.

 

[TimTang]

Thanks Remou,

I've been fairly busy, so I couldn't get back sooner.

That last code example worked great. It looks a little funny but it does the job. I had to use a "V" as a down arrow.

I'm still having one little problem. The order of the last three cargoes is important but the list places them in alphbetical order.

Is it possible to have the selections placed in the text field in the same order I select them.

Cheers!!