[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 611
  • Last Modified:

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.
0
LadyHagood
Asked:
LadyHagood
  • 4
  • 3
  • 2
  • +1
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now