How to Append two fields content?? 1

[qajussi]

Hi!
I want to merge two contents of fields from the table and show it in the combo box and when one is selected I want to get the Primary key of that value.

Do you remember that Access's form wizard let you create combo box and in doing so it let you do what i am trying to do.

Ex)
TblName
ID
Name
TitleID

TblTitle
TitleID
TitleName

When I create a form for the Name table(data entry form) I used the combo box wizard to show TitleName and TitleID(has to option to show the TitleID or not) and When I select the titleName from the combo box, it enters the TitleID in the TbleName.

What I would to do is similar.
I have a table

TblItem
ID
TypeID
TypeDescription

I want to do:
1) Merge TypeID and TypeDescription
2) Insert them in the combo box
3) when select from the combo box, I want to get the ID of TbleType

ID TypeID TypeDescription
1 1.a abc
2 1.b.3 dfdfd
4 3.4.d fdfdfd
from this to combo box =>
1.a abc
1.b.3 dfdfd
3.4.d fdfdfd

when I select on from the combo box
I want to get the
ID.

Could I need to do this in several step using the vbs or can this be done simpler way like first example??
Thank you for your help.

 

[SQLSister]

Create query that has two columns, the id field and the concatenated column. USe that as the row source for your combo box. Make the bouind column be col1 (the id field) and bind it to the id field in your form datasource. If you don;t want the id to display, simply give it a 0" length in the column widt while at the same time setting a width for the other column (something like 0";1.5")

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[qajussi]

That's exactly what I would like to.
When you use a combo box and do that using the wizard.
How do I see the exact vb code??
I look everywhere but didn't see it.

For concatenating, how do I do that in queries(using the sql) or do I need to use vbs to merge and insert into the column??

Thanks so much for your time and reply.

Is there a tutorial or example I can see how this is done(see actual vb codes)??

 

[PHV]

For concatenating, how do I do that in queries
SELECT TypeID & " : " & TypeDescription AS Type FROM tblType

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLSister]

Other than the SQL to write the query, everything else is done using the combo box properties. I suppose you could do it in code as well, but why bother?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[RoyVidar]

Coding - depends on the desired functionality - if it is to be bound to the ID field and change this, then no code. If the desired functionality is to go to the correct record, then unbind it (remove controlsource, but keep rowsource), then a smallish part of code is needed. This is an approximate of what the wizard drops into the after update event of such combo:

[tt]dim rs as dao.recordset
set rs=me.recordsetclone
rs.findfirst "ID = " & me!cboMyCombo
if not rs.nomatch then me.boomark = rs.bookmark
set rs=nothing[/tt]

Should it be ADO recordset, declare as ADO and use the .Find method. Test for EOF in stead of .nomatch.

Roy-Vidar

 

[qajussi]

SELECT TypeID & " : " & TypeDescription AS Type FROM tblType

Wow PHV!
I didn't know SQL can this kind of things!!!!
I tested it and it really does work.
I thought "select, insert, drop, and update" were everything about the SQL.

Gee, Where can I read of these things??
Don't tell me these are in any database books.
Thanks very much.

 

[qajussi]

SQLSister!
Thanks for your input.
I was wondering if I can use the code outside of the Access form.
So I guess this is done through combo box properties and no code to look at.

Anyway I appreciate for all your help

 

[qajussi]

Hi RoyVidar!

Thank you very much for your codes.
I am testing your code to understand what you did.
I let you know how it turns out.
Thanks millions.