X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Dlooup Theory, Access 2010    
post Jan 11 2017, 10:36 AM

Posts: 20
Joined: 10-January 17


You've lost me with the below instructions... how do I go about doing this?

That's about it. On your data entry form for editing a person's "profile" you'd set up the form to be bound to the tblPeople table. For lngAssignedDuty you could use a Combobox control. The query for that combobox would be:

SELECT DutyID, strDutyDescription
FROM tblPeople RIGHT JOIN tblDuties ON tblPeople.lngAssignedDuty = tblDuties.DutyID
WHERE tblPeople.lngAssignedDuty Is Null;

Go to the top of the page
post Jan 11 2017, 08:19 PM

Posts: 20
Joined: 10-January 17


Afraid I'm not quite following.

I'd like to create a dropdown control on a form that you can choose a job, but once that job is assigned, it is not listed as an available option for the next record.

in example; available jobs are Quality Assurance, Team Lead, Team Member. Bob is the Quality Assurance, and when Steve goes to select his job, only Team Lead and Team Member are available.

Is this possible?

-TIA, Mike
Go to the top of the page
post Jan 12 2017, 09:36 AM

UtterAccess Editor
Posts: 16,622
Joined: 29-March 05
From: Wisconsin


> how do I go about doing this?

Are you saying you don't know how to write a query as the rowsource for the combobox? Try adding a combobox to your form, and follow the Wizard instructions where you select the DutyID and strDutyDescription fields from your tblDuties table. Once the wizard is complete, you're 99% there. Then, just edit the combobox's RowSource property. You can either go into SQL view to edit the SQL statement like the one I typed, or you can edit the query in the Design view that is the default. If you do this in the design grid, you will need to add the tblPeople table, then create a right join between the two fields described in my SQL statement, and add "Null" as the criteria for the lngAssignedDuty field from that table.

At this point I would strongly advise that you start learning the basics of working with databases, either with an online course, or a book, or maybe a class at the local technical college.

Hope this helps,


(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
post Jan 12 2017, 03:05 PM

Posts: 20
Joined: 10-January 17


It took a little research, specifically finding how to edit the SQL statement, but after a bit of head scratching, I was able to do the following:

Deselect 'use control wizard' from the control group
Insert a combobox onto the form frmPersonnel
in the property sheet under Rowsource click "..."
show tblPeople and tblDuties
right click on the query tab (frmPersonnel: Query Builder)
Select SQL view
paste the recommended query
save the query

Now only "un assigned" duty ID's are available from the combobox.

Thanks for the lesson and assistance!

Go to the top of the page
2 Pages V < 1 2

Custom Search
RSSSearch   Top   Lo-Fi    23rd January 2017 - 04:20 PM