QBF from Combo box Access 2003

Hi.

Have an access 2003 db of student training.  In this db there is a Trainingmain table which holds all of the training records consisting of  StudentFirst ,  StudentLast,  TrainingTitle,  TrainingDate.

I have a form that I would like to use to search by.  SearchFrm contains a combobox. Problem is, the combobox is populated by a combination of the studentfirst and studentlast :

SELECT [Studentfirst] & " " & [Studentlast] AS Expr1
FROM TRAININGMAIN
GROUP BY [Studentfirst] & " " & [Studentlast];

Now with this as the criteria, how do i then populate the query (based on the Trainingmain table if the combo box is actually two fields in one.
LadyHagoodAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:

select * from Trainingmain where [Studentfirst] & " " & [Studentlast] =" & chr(34) & me.comboName & chr(34)
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Cap's suggestion will work, but how would you determine which record to view based on the user's selection? Is StudentFirst and StudentLast your "unique" composite field? I'd find that be a poor choice (at best) for a unique index/key.

Instead, you might consider including a surrogate AutoNumber key to define a record, and including that in the combo's RowSource. This would allow you to point to a specific record.
0
Helen FeddemaCommented:
You need a unique StudentID for doing the selection.  If the table doesn't have one, add an AutoNumber StudentID field.  Then your row source would be two fields:  StudentID and StudentName (concatenated from the two name fields).  Set the column widths to 0;2, so the name shows in the list, but the ID field is used for selection.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

LadyHagoodAuthor Commented:
OK, so with the unique identifier (ID), how would that change the syntax to create the query or would capricorn' syntax remain?
0
Rey Obrero (Capricorn1)Commented:
you will use the ID instead, the rowsource of the combo will be


SELECT ID, [Studentfirst] & " " & [Studentlast] AS Expr1
FROM TRAININGMAIN

bound column 1
column count 2

column width 0      'this will display the names, hide the ID

and the sql statement will be

select * from Trainingmain where ID =" & me.comboName


0
LadyHagoodAuthor Commented:
ok. so this is what i have now:

Dim strSql

strSql = "SELECT * FROM [TRAININGMAIN] where employeeID = & me.List0"

CurrentDb.Execute strSql

but i am getting the error  below.
error.jpg
0
LadyHagoodAuthor Commented:
oh wait.  says studentID, duh let me try it with the right field
0
LadyHagoodAuthor Commented:
ok. still getting same type of error with studentID (correct field name lol)



strSql = "SELECT * FROM [TRAININGMAIN] where studentID = & me.List0"

CurrentDb.Execute strSql
0
Rey Obrero (Capricorn1)Commented:

look carefully on what i posted at http:#a30230310
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hi Helen:

I'm not sure why you posted a comment that basically echoed what I posted a day earlier, without attributing/acknowledging my comments. It's certainly fine to offer a different take on the same concept, but in general you should acknowledge that your comment bulids off another, earlier comment.

Was there some reason you felt you needed to further explain the concept of surrogate keys and AutoNumbers without acknowledging that comments very similar to that had already been made?

Scott
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.