Combo Box Question

[Tallbloke]

I have 2 databases. DB1 holds addresses of suppliers and has a memo field with descriptions of their premises (hotels).

DB2 has a form I use to put together info sheets for customers. As part of this I want to import the description of the hotels from DB1. I have tried to do this using a combo box but it appears to cut the text short.

The question I need answering, is it possible to use Me!LocationDescription = Me.Combo129.Column(2) when column(2) is a memo field?

Thanks!

 

[Guus2005]

When Me!LocationDescription is also a Memo field, i believe it is not a problem.

I am not sure why you give all the information about two databases. When all you need to ask is:

is it possible to use Me!LocationDescription = Me.Combo129.Column(2) when column(2) is a memo field?

HTH

 

[Tallbloke]

hehe... I guess the more info the better really.

It's odd though, the combo box seems to truncate the text from the memo field.

 

[Guus2005]

I believe the memo field can be up to 64KB. Which is about 64000 characters. You should be glad the combobox protects you from that

HTH

 

[Tallbloke]

ha!

Doesn't help getting the text from the column into the form in full though!

 

[Guus2005]

A textbox is better suited for this purpose.

Select a record from the combobox and show the result in the textbox.

HTH

 

[Tallbloke]

The source of the combo box is a memo field, because the amount of text is greater than 255 characters..can't then insert that into a text box...

 

[Datagopher]

Hi Tallboke,

I've ran into a similar scenario as you're explaining. As long as your memo fields are not ridiculously long like well over 500 characters, you can use multiple text boxes that will hold up to 255 characters. I've used this method in the past, which has worked well.

 

[Tallbloke]

Hi Tallboke,

I've ran into a similar scenario as you're explaining. As long as your memo fields are not ridiculously long like well over 500 characters, you can use multiple text boxes that will hold up to 255 characters. I've used this method in the past, which has worked well.

Interesting, so replace the memo at source with text boxes and just split the data...is that what you are getting at?

Kinda defies the point of having fields that can hold more than 255 characters though?

 

[Datagopher]

Yes, that's what I'm saying. Text fields are better suited for querying in Access.

 

[wiklendt]

you can display the entire memo length in a text box on a form if you use record sets rather than list/combo boxes. i ran into this problem in the past, and the only way i could get around it was record sets. this is because a list/combo can only hold/transfer/whatever 255 characters, NOT that the memo will only display 255 chars of the field.

edit: what i mean to say is that you use your combo box as a record selector with the following code (and yo umay need to have this as a subform, if the records you are searching aren't the ones the current form is bound to):

Private Sub Combo129_AfterUpdate()
    
    ' Find the record that matches the control.
    
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[UniqueID] = " & Str(Nz(Me![Combo129], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

rather than

Me!LocationDescription = Me.Combo129.Column(2)

by the way, you may want to use logical naming of your controls. e.g., instead of Combo129, call it "cmbLocations" or something relevant. when you then are using VBA, it's much easier to remember the logical name rather than "Combo129"...

 

[Tallbloke]

you can display the entire memo length in a text box on a form if you use record sets rather than list/combo boxes. i ran into this problem in the past, and the only way i could get around it was record sets. this is because a list/combo can only hold/transfer/whatever 255 characters, NOT that the memo will only display 255 chars of the field.

edit: what i mean to say is that you use your combo box as a record selector with the following code (and yo umay need to have this as a subform, if the records you are searching aren't the ones the current form is bound to):

Private Sub Combo129_AfterUpdate()
    
    ' Find the record that matches the control.
    
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[UniqueID] = " & Str(Nz(Me![Combo129], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

rather than

Me!LocationDescription = Me.Combo129.Column(2)

by the way, you may want to use logical naming of your controls. e.g., instead of Combo129, call it "cmbLocations" or something relevant. when you then are using VBA, it's much easier to remember the logical name rather than "Combo129"...

Worked a treat, thanks!

 

[wiklendt]

Happy to help

 

[Tallbloke]

Well.. it does work a treat until I move the entire form into position on a tab.

Then, when i use the drop down I get an error that says the form can't be found.

Private Sub Combo2_AfterUpdate()
    
    ' Find the record that matches the control.
    
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[SupplyRef] = " & Str(Nz(Me![Combo2], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    Forms!Itinerary2.HotelDescription = Description

End Sub

I think the error is in the final line of code which works fine until the whole form / subform is put onto a tab!

 

[Tallbloke]

Not to worry...

Fixed it :

Me.Parent.HotelDescription = Description