Checkbox to Remove option in Combobox 1

[marcfl]

Hi,

The company i work for copy data off 9-track tapes, when a user has copied the last tapes of contract he will need to click on the "Contract Complete" checkbox to stop the option from appearing in the Contract combobox. Im doing this to limit the number of options that appear to make things easier for the end user. Only im not great with VB so i dont know what code to enter.
Would appreciate any help given.

Thanks,

Marc.

 

[Remou]

How are your tables set up? You should have a CustomerContract table something like this:

CustomerID
ContractID
Completed (a date is best)
<...>

 

[marcfl]

I have a tblContract with ContractID and ClientID in it, connected to the ClientID in that table is the tblClient table. I then have tblTranscriptionByDay table that is connected to the ContractID field in tblContract. The form in which the checkbox and the combobox are in stores all the data in tblTranscriptionByDay and the .

 

[marcfl]

er lol was so focused ongetting the image uploaded i forgot i hadnt finished what i was typing but i guess you see what's what. Apart from that checkbox the Database works perfectly w/o any problems so im pretty sure ive set it up ok.

 

[Remou]

Then it should be simply a matter of setting the row source of the combo appropriately and requering in the AfterUpdate event of the checkbox and the Current Event of the form:

Row Source example:

SELECT c.ContractID <etc>
FROM tblContract c
INNER JOIN tblTranscriptionByDay t
ON c.ContractID=t.ContractID
WHERE c.ClientID=Forms!frmForm!ClientID
AND t.ContractComplete Is False

 

[marcfl]

heh erm.. dont really know much vba or sql. i've been using books to help me do the things i need. Trying to do the work for them and study at the same time isnt easy :s
I dont suppose you could make it a bit easier for me to understand. Thanks for giving me all this help.

 

[Remou]

What is the row source of the combo box?

 

[marcfl]

SELECT [ContractID], [ContractName], [SurveyID] FROM tblContract ORDER BY [SurveyID];

 

[Remou]

Then we have problems. You have a one-to-many relationship tblContract with tblTranscriptionByDay and tblTranscriptionByDay holds the completed data. You can exclude a contract based on the ContractCompleted field of tblTranscriptionByDay but will this be correct? Would it be better to use tblTranscriptionByDay as the row source of the combo box? Or should contracts be excluded if all the entries in tblTranscriptionByDay are marked complete?

 

[marcfl]

Well i think its setup ok, because all I need it to do is exlude the contract after the Tickbox is clicked. I dont need it to delete any data or prevent it from showing that information in past records related to that contract. Does that make sense? Have a look at my form, maybe that will clear things up a bit!

 

[Remou]

If you wish to exclude something, you need the exclusion logic. Have a another look at my last post, it suggest two possibilities:

1. Exclude contract if any ContractComplete field is ticked.
2. Exclude contract if all ContractComplete fields are ticked.

 

[marcfl]

Well i guess i'd need #1 as for example one of our contracts has 68000 tapes in it and the maximum that can be copied per day is usually around 80. #2 Means that every record related to that contract will have to have the ContractComplete tickbox ticked right?
The person who copies the last tape in that contract would tick that box when he entered his numbers for that day in the Total Tapes Copied Field and once the record was saved the contract would no longer appear in the ComboBox.

 

[Remou]

From the above, it seems you need option 2!

Try this for the row source:

[tt]SELECT c.ContractID, c.SurveyID
FROM tblContract c
INNER JOIN
(SELECT ContractID FROM tblTranscriptionByDay
WHERE ContractCompleted Is False
GROUP BY ContractID) t
ON t.ContractID = c.ContractID
ORDER BY c.SurveyID[/tt]

 

[marcfl]

haha told you im a novice! What do the t. and c. mean? do i enter them as they are?

 

[Remou]

Yes. They are aliases for the table names. It makes the whole thing more readable.

 

[marcfl]

ok thanks it works