combo list rowsource problem

[antonyx]

i want my combo list to display addresses (text datatypes) that have been previously used by the person who made the booking..

in my tblJob table, there are records with tblJob.tblJobTo addresses where the tblJob.fkBookerID is 3


****ON MY FORM

the control Me.txtbooker has the value

3

inside it..
i tab to the cboregfroms combo list

i use this on my combo and it is simply blank.. anyone know why?

Private Sub cboregfroms_Enter()
Me.cboregfroms.RowSource = "SELECT DISTINCT [tblJob].[JobTo] FROM [tblJob] WHERE [tblJob].[fkBookerID] = Me.txtbooker.value;"
Me.cboregfroms.Requery
End Sub

 

[boblarson]

get rid of .value (you don't need it) and you have to concatenate in the textbox - see below

Private Sub cboregfroms_Enter()
Me.cboregfroms.RowSource = "SELECT DISTINCT [tblJob].[JobTo] FROM [tblJob] WHERE [tblJob].[fkBookerID] =" &  Me.txtbooker & ";"

 

[antonyx]

still blank..

 

[antonyx]

i know me saying.. "still blank" doesnt really help on my behalf..

off the top of your heads.. can you think of any reason why it wouldnt work?

 

[MarkK]

Check the ColumnCount and ColumnWidths properties of the combo. A visible column may have a width of zero and not appear.

 

[boblarson]

Check the ColumnCount and ColumnWidths properties of the combo. A visible column may have a width of zero and not appear.

What he said

 

[antonyx]

yes thank you.. it is working..the combo is selecting the fields based on the form criteria

at the moment though.. my sql query is selecting 2 fields.. and they are being displayed in 2 separate columns.(as normal)

here is the sql, i want the JobFrom and JobTo data to be in the same column.. in 1 vertical list.. how can i do that?

Me.cbofrom.RowSource = "SELECT DISTINCT [tblJob].[JobFrom], [tblJob].[JobTo] FROM [tblJob] WHERE [tblJob].[fkBookerID] =" & Me.txtbooker & ";"

 

[pbaldy]

SELECT DISTINCT [tblJob].[JobFrom] & [tblJob].[JobTo] AS Both

or

SELECT DISTINCT [tblJob].[JobFrom] & " " & [tblJob].[JobTo] AS Both

 

[antonyx]

thank you very much

 

[antonyx]

ok..

for booker id 3 there is one record..

the JobFrom field has:
London Palledonium

the JobTo field has:
Heathrow Terminal 5

the combo is displaying it like this..
London PalledoniumHeathrow Terminal 5

i want it displayed like this..
London Palledonium
Heathrow Terminal 5

here is the sql..

"SELECT DISTINCT [tblJob].[JobFrom] & [tblJob].[JobTo] AS Both FROM [tblJob] WHERE [tblJob].[fkBookerID] =" & Me.txtbooker & ";"

do i need to edit the properties of the combo itself aswell?

 

[boblarson]

You can't do multiple rows for the same record in a combo box, each row is a record.

 

[antonyx]

so basically what i want to do is impossible.. is that what your sayin?

could it be done with a listbox? or does that choose to follow the same path as its bendy cousin the combo?

 

[boblarson]

Yep, sorry but both of them follow the same path. Now that being said, you POSSIBLY could use a tree view control.

 

[antonyx]

i hate to be the persistent.. nagging.. inpatient.. desperate maniac that i am.. but...

hang on hang on.. let me atleast search for what a tree view control is before i ask you how to do it..

 

[boblarson]

It would have to be someone else to tell you how to use that one. I've not used them.

 

[antonyx]

you know what bob.. all this bother is making me think that i should just store all the addresses in a different table.. and just link the addressid (pk) in the address table to the pickupid (fk) and destinationid (fk) in the job table..

this way the actual textual addresses wont be repeated over and over in the job table.. just the ids will be..

do you think i should do that.. because my user has said already that they need to see a list of common addresses when they are adding jobs.. so they can just search for an address.. and if it exists.. just type the address id in the pickup and destination fields..

in your professional opinion do you think i should do that.. because it is a big design alteration.. and my user has already started putting jobs into the system.. but he has only put a weeks worth.. so it wont be a tragedy if i change it now.. what do you think?

 

[boblarson]

That sounds like a good plan to me. Putting them in by ID will save on maintenance as you only have to modify in one location if the actual text changes.

 

[antonyx]

ok.. i think i will do that.. (huff and puff.. sulk sulk sulk.. )

thanks for the advice.. i guess i better get back to work.