Query All Datasheet Records?

[knawtn via AccessMonster.com]

I have a table with two fields: [AssignedTo] and [Number]. The [Number] field
is full, [AssignedTo] is primarily empty. (I query where [AssignedTo] IsNull
to find available [Number].)

I have an unbound form with a combobox that will fill the [AssignedTo] field
with a name, and a query-based subform (datasheet view) that selects the next
top 10 available [number]. When I select "Joe Shmoe" from the unbound form,
the subform results shows 10 records: [AssignedTo]=Joe Shmoe for all 10
records, [number]= 2334, 2335, 2336...2343. Perfect! Exactly the desired
results. However...

How do I now query the subform? (previous threads say it's impossible...and I
believe it.)

I need these 10 [AssignedTo] records to be appended to the original table to
remove the 10 numbers from the next assignment (I query where [AssignedTo]
IsNull to find available [Number].) I hope that made sense. My brain hurts.

Thanks in advance.

 

[Steve Schapel]

Knawtn,

I can't figure out the purpose of your subform. Could you do it like
this?...

1. Make a query to get the next 10 available numbers.
SELECT TOP 10 [AssignedTo],[Number]
FROM YourTable
WHERE [AssignedTo] Is Null
ORDER BY [Number]

2. Put one unbound combobox only on the form, for the entry of the person.

3. Use code like this to make it happen...
CurrentDb.Execute "UPDATE YourQuery SET AssignedTo='" &
Me.YourCombobox & "'"

 

[knawtn via AccessMonster.com]

Steve,
Thanks for your response.
Where would I place that code?

The idea is to select a name from the combo and assign that name to the next
10 available registration numbers, then to remove those 10 numbers from the
master list (table).

 

[Steve Schapel]

Knawtn,

You would place that code on whatever event you wanted to use to assign
this name to the next 10 numbers. Could be the Click event of a command
button on the form, maybe?

I don't know what the "master list" is, but my understanding was that
you had a list of registration numbers, and they are "available" based
on whether the AssignedTo field is blank. No? So you are not removing
the numbers from the table, are you? You are just assigning the name to
the AssignedTo field, and then these numbers will no longer be available?

 

[knawtn via AccessMonster.com]

Thanks, Steve.

Your assumptions are correct....and exact. I was unfamiliar with the
CurrentDb.Execute code, and VB in general, as I rely mostly on wizards and
entry-level VB. I did what you suggested, adding the code to the click event
of a command button, and it worked perfectly.

Seems I should educate myself more on code and such.

I'm grateful for your assistance. My boss thanks you as well (vicariously).