Referencing a listbox column value in a query

[intern42]

Hey, I'm getting hung up on this and it's getting a little bit frustrating...

I have a form with a list box (lstTookCourse) that is populated by a query. (the populating query works fine)

The listbox has 3 columns, a personnelID (number field and an auto-number PK for the personnelID table), a last name, and a first name.

I want to be able to run an UPDATE or an INSERT statement on a different table, but this depends upon whether a record already exists (then do update) or if I need a new record (then do INSERT)

Anyway, the table in question has two fields, which act as a joint primary key.

One of the PKs I can get from a combo box somewhere else on the form.

The problem is getting the value of the other PK, which is the first column in the list box.

To loop through the selected items in the listbox I use the code:

Dim item As Variant

For Each item In lstTookCourse.ItemsSelected

Next item

I can reference the value I need with:
lstTookCourse.Column(0, item)

The problem is, I don't know how to reference this number in the WHERE clause of a query that I call within the loop.

So, my question is: how do I reference the number in the WHERE clause of a query that I call from within the for loop?

I tried [Forms]![Form Name]![lstTookCourse].[column(0, item)]

But obviously, that doesn't work.

I also tried the list boxes .value method, but had no luck with that either.

Any suggestiong?

Sorry if I went rambling on a bit, but I wanted to give as complete a picture as possible.

Thank you in advance.

 

[dcx693]

Have you tried something like "SELECT * FROM table WHERE [field]=" & Me.lstTookCourse.Column(0, item) &";"

 

[intern42]

Hmmm... I hadn't thought of directly coding the query in the module.

Could I directly assign the result of the query to a variable if the query only returns one cell?

Here's the way I'm doing it now, which would work if I could reference the selected list box value from the query:

SELECT 1 AS RecNumber, COUNT(*) AS CountOfRecords
FROM PersonnelCourses
WHERE PersonnelID= **list box value** And courseID=[FORMS]![Training Completed]![cboCourse];

PersonnelID and courseID are a joint primary key for the PersonnelCourses table and I need to know if there is already a record in that table to determine if I should UPDATE or INSERT.

So, within the module, I assign the value of "CountOfRecords"
to an integer variable isKey using the statement:

isKey = DLookup("[CountOfRecords]", "PersonnelCourses Key Exists", "[RecNumber] = 1")

If I hard code the query within the module, how do I go about assiging the result of the CountOfRecords column to my variable isKey?

Thanks in advance for any further advice.

 

[intern42]

Looks like everyone's stumped on this one.

I solved the problem by taking the easy/cheezy way out: I put a hidden text box on the form and dumped the desired value into the text box (.visible = false)

Since the saved query CAN reference the textbox's value, problem solved.

Not by any means an elegant solution, but it gets the job done.