Semi Unique Combox Record Source on Continuous Form

[briguy01]

I have a main form which tracks Task Information based on tblTask. There is a subform based on junction table tblTaskDetail. sfrmTaskdetail is a continuous form with a comboBox that looks up AssetID/SerialNumber.




I found a post on another site which was close to my issue:
https://access-programmers.co.uk/for...d.php?t=102479


For my purposes I want the rowsource to not only exclude ANY Serial numbers used in tblTaskDetail instead only the ones used for the current Task.

So, if for the current task you have selected 2 Asset Serial Numbers, when selecting the third all asset serials EXCEPT for the two already selected should show up as rowsource for the combobox.

The Serial numbers should only be available in the combobox rowsource once for each task.


I included a query in the sample with the left outer join and Is Null where criteria (that returns serials for assets not used in any tasks). I am close but bit confused at this point.


Can Anyone help?

(I also asked same question at that site too https://www.access-programmers.co.uk...75#post1607275)

[June7]

The RowSource SQL would be:

SELECT pkAssetID, SerialNumber FROM tblAsset LEFT JOIN tblTaskDetail ON tblAsset.pkAssetID = tblTaskDetail.fkAssetID WHERE fkTaskID Is Null OR fkTaskID <> [txtTaskDetailID];

Problem is this is a filtered query with lookup alias for a BOUND combobox. This means the combobox cannot display the SerialNumber for records where the SerialNumber is excluded from the list.

Set the subform RecordSource to include tblAsset. This will make the SerialNumber available for display in textbox. Do not allow editing this textbox set Locked Yes and TabStop No.

SELECT tblTaskDetail.*, SerialNumber FROM tblAsset RIGHT JOIN tblTaskDetail ON tblAsset.pkAssetID = tblTaskDetail.fkAssetID;

Then UNBIND the combobox and use VBA to save selected pkAssetID value. Also, code to save the record and Requery the combobox.

[briguy01]

Thank you very much for the reply. I really appreciate your help.


The non inner joins are a bit tricky for me and continuous forms have some strange behaviors.

I attached an attempt at your solution, but think I am still missing some small basic thing(s).


When frmTask is open to the first record, going to sfrmTaskDetail, clicking on the unbound combo for the first subform record displays how I would like for a new entry (null TaskID - only serials not used for this task available - 1,2,3 used for this task from 4 onward available).

Clicking on the unbound combo for the second record on the subform, strangely shows all assets except for 4 (which is used on the second task record)

Clicking for the third record on subform shows all except 5 and 6 (which are used on the 3rd task record).


I think each subsequent subform record is somehow checking the subsequent taskID.


When you click on the unbound combobox to enter a new asset for the task, the rowsource shows only assets not used for any task (1-12 used on other tasks, 13 onward available).


Any thoughts?

[June7]

Sorry, I referenced wrong textbox in SQL for combobox RowSource. Should be txtTaskID. Except this doesn't work for a new detail record because there is no value in that textbox yet. The fk will not be populated until record is initiated and since selecting in combobox is the only way to initiate a new detail record, caught in a catch-22. So have to reference main form control: Forms!frmTask![txtTaskID]. And that doesn't quite work when creating a new task record. None of the events requery the combobox after the task ID is saved to table when focus moves to subform. Suggest using the combobox GotFocus event.

[briguy01]

Oh, I see.

I was experimenting with events this morning to assign parent form taskID to the subform control for the foreign key. I was trying on got focus and then on mouse down. Things ended worse (new record showed all assets as available). So I decided to report initial behavior and go from there.


I just tried referencing the txtTaskID on the main form instead of and it appears to work as intended. I will do further testing just in case.

Thanks again.

[briguy01]

MajP came up with a solution too. I am messing around with both approaches right now. Thank you.