cbo rowsource help needed

[ppoindexter]

I have the following code in the second combo box on a form

Me.cboReportType.RowSource = "select fldReportTypeID,fldReportType, fldFrom, fldTo, fldDue from tblReportType Where fldReportTypeID Not in (select fldReportTypeID from tblReport) order by fldReportType"

right now this code is filtering so that once a specific reporttype is used that specific reporttype does not appear again as a choice HOWEVER i need it to do this for a specfic record chosen in the first combo box...at this point once a reporttype is used for one record it does not appear again for any record -

the first combo box rowsource:

Me.cboProject.RowSource = "select fldProjectID,fldProjectNo, fldTitle, fldPurpose from tblProject order by fldTitle"

any help is appreciated

 

[ScottGem]

Requery the second comob after a selection is made in the first one.

 

[ppoindexter]

I got it! Solution below -

Me.cboReportType.RowSource = "select fldReportTypeID, fldReportType from tblReportType where fldReportTypeID Not in (select fldReportTypeID from tblReport Where fldProjectID=" & Me.cboProject & ")"

 

[ppoindexter]

Now i have another issue when tblReport!fldReportTypeID is null i get no records...i dont know what is causing fldReportTypeID to have null values
is there a way to prevent that from happening? or code i can add so that all records are visible if tblReport!fldReportTypeID is null?
thanks in advance

 

[ShaneMan]

Hello Poindexter,

You could do a DLookup on the the fldReportTypeID using the same criteria in your WHERE clause part of your SQL statement.

If Nz(DLookup("fldReportTypeID","TableNameHere","fldProjectID =" & Me.cboProject),"") = "" Then
Msgbox "No Records"
Me.cboReportType.RowSource = "select fldReportTypeID, fldReportType from tblReportType where fldReportTypeID Not in (select fldReportTypeID from tblReport"
Else
Me.cboReportType.RowSource = "select fldReportTypeID, fldReportType from tblReportType where fldReportTypeID Not in (select fldReportTypeID from tblReport Where fldProjectID=" & Me.cboProject & ")"
End If

Air code so I hope I got it all right.

HTH,
Shane