Dynamic Row Source for Combo box

[tis9700]

Hi everyone,

I'm using an ADP Access 2002 fronted with SQL Server backend.

I have a Main form that displays Instructor records and a subform where employees add certifications

There are 3 types of certifications..
1 TSP
2 BSP
3 HSP

Instructors can be certified for any of these certifications and the certification status in the table can be either Active(1) or Inactive(2).

If an Instructor is already active(1) in say HSP, I would like to remove it from the combo box so it isn't selected again but if the Instructor is Inactive(2) in that particular certification I want to be able to select HSP.


Also I should tell you that an Instructor can have multiple records of the same certification with the status set to Inactive(2) but only one Active(1) record per certification.

How would I limit the row source of the combo box based on the Instructor having an active certification present?

 

[MajP]

Look at subqueries and "IN" or "EXISTS" expression.

http://msdn2.microsoft.com/en-us/library/ms190963.aspx

In general it would look roughly something like

Select certs from tblCerts where certs IN (Select from tblInstructor_Certs where status = "Active" and instructorID = [forms]![theForm]![instructorID])

 

[bford797]

That's not the answer. The problem is not how to construct the sql statement. The problem is how does one pass a variable to the combo box's record source property from the Access form to sql server? The sql statement in the above answer will work in a .mdb file, but not in a .adb. How do you do this in an access project file (.adb)?

 

[bford797]

Duh! I answered my own question...

Build the sql statement dynamically, in a string, with vba, then set the combo box's RowSource property to the value of the string (sql statement). One can create any rowsource and change it at anytime based on user input in other fields.

 

[tis9700]

Hi bford797,

I never got it to work but ended up doing something different.

Now I have it throw out a message box if the Instructor is already active in that certification and it will undo the record. I put the code in the BeforeUpdate event procedure in my combo box......


Private Sub CertificationType_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("CertificationType", "Instructor_Certifications", "certificationType = " & Me.CertificationType _
& " and instID = " & Me.InstID _
& " and certificationStatus = 1")) Then
MsgBox "Active Certification already exists!!"
Cancel = True
Me.Undo
End If


End Sub

 

[tis9700]

Hi bford797,
Could you post your code to building the SQL statement dynamically? I'd like to give it a try.

Thanks