VBA into SQL

[tjones]

I started with a split form with a cascading combo box series which is unstable. (don't know why a cascading combo makes a split form unstable????)

I am attempting to make the split form stable by turning the cascading combo boxes into regular combo boxes (have them on other split forms with no problems) and using a query to filter instead.

How would you change this vba code into a query(s) to achieve the same function?

Private Sub cboCourseNameID_AfterUpdate()
' Filter the list of courses based on the selected information.
FilterDescriptionList
End Sub

Private Sub cboCourseNoID_AfterUpdate()
' Set the Course Name combo box to be limited by the selected Course No
Me.cboCourseNameID.RowSource = "SELECT tblCourseName.CourseNameID, tblCourseName.CourseName FROM tblCourseName " & _
" WHERE CourseNoID = " & Nz(Me.cboCourseNoID) & _
" ORDER BY CourseName"
Me.cboCourseNameID = Null

EnableControls
FilterDescriptionList

End Sub

Private Sub cboCourseTypeID_AfterUpdate()
' Set the Department combo box to be limited by the selected CourseType
Me.cboDepartmentID.RowSource = "SELECT tblCourseDept.DepartmentID, tblCourseDept.DepartmentName FROM tblCourseDept " & _
" WHERE CourseTypeID = " & Nz(Me.cboCourseTypeID) & _
" ORDER BY DepartmentName"
Me.cboDepartmentID = Null

EnableControls
FilterDescriptionList

End Sub

Private Sub cboDepartmentID_AfterUpdate()
' Set the Course No combo box to be limited by the selected Department
Me.cboCourseNoID.RowSource = "SELECT tblCourseNo.CourseNoID, tblCourseNo.CourseNo FROM tblCourseNo " & _
" WHERE DepartmentID = " & Nz(Me.cboDepartmentID) & _
" ORDER BY CourseNo"
Me.cboCourseNoID = Null

EnableControls
FilterDescriptionList

End Sub

Private Sub FilterDescriptionList()

Dim strRS As String

' Filter the list box appropriately based on the combo box selection(s)
strRS = "SELECT qryCourseDescList.Units, qryCourseDescList.Offered, qryCourseDescList.Prereqs, qryCourseDescList.Notes FROM qryCourseDescList"

If Not IsNull(Me.cboCourseNameID) Then
strRS = strRS & " WHERE CourseNameID = " & Me.cboCourseNameID
ElseIf Not IsNull(Me.cboCourseNoID) Then
strRS = strRS & " WHERE CourseNoID = " & Me.cboCourseNoID
ElseIf Not IsNull(Me.cboDepartmentID) Then
strRS = strRS & " WHERE DepartmentID = " & Me.cboDepartmentID
ElseIf Not IsNull(Me.cboCourseTypeID) Then
strRS = strRS & " WHERE CourseTypeID = " & Me.cboCourseTypeID
End If

strRS = strRS & " ORDER BY qryCourseDescList.Offered;"

Me.lstDescriptionID.RowSource = strRS

Me.lstDescriptionID.Requery
End Sub


' Enable or disable combo boxes based on whether the combo box preceeding it has a value.
Me.cboDepartmentID.Enabled = (Not IsNull(Me.cboCourseTypeID))
Me.cboCourseNoID.Enabled = (Not IsNull(Me.cboDepartmentID))
Me.cboCourseNameID.Enabled = (Not IsNull(Me.cboCourseNoID))

End Sub

Private Sub Form_Current()
' When the form loads, enable/disable the combo boxes. Combo boxes are only enabled if the preceeding combo box has a value.
EnableControls
' Show all courses in the list until filters are selected from the combo boxes.
FilterDescriptionList
End Sub

Private Sub Form_Load()
' have it start the record in the course type field
DoCmd.GoToControl "cboCourseTypeID"

' When the form loads, enable/disable the combo boxes. Combo boxes are only enabled if the preceeding combo box has a value.
EnableControls
' Show all courses in the list until filters are selected from the combo boxes.
FilterDescriptionList
End Sub

 

[AccessMSSQL]

You need to refer to your form controls in the query as Forms!NameofForm!ControlName. For example in your cboCourseNoID.RowSource your query would look like this:

SELECT tblCourseNo.CourseNoID, tblCourseNo.CourseNo FROM tblCourseNo WHERE DepartmentID = [Forms]![YourFormName]!cboDepartmentID ORDER BY CourseNo

 

[tjones]

I can not get it to work. It lets me select different choices but continues to record the same record instead of letting me choose multiple records for the same person.

I am going to put a shortened version of the database (with only the form giving me trouble) on here and hope someone can help figure out how to make it stop dropping information.

It's very frustrating. the form works great if you remove it from split form. I have tried doing a single form with a subform (datasheet view to minic the split form) but can't get it to move a selection in the split form to the main form for editing.

I have tried using a single form with a list box but can't get the list box to filter to just that persons records or load the selection into the form for editing.

The database I posted is using the subform http://www.access-programmers.co.uk/forums/showthread.php?p=1208588#post1208588

 

[tjones]

I DID IT!!! Thanks to everyone for their help.

Solution:

Create a query based off the fields in the table of the form you want the subform to display. Be sure to include on both the main form and in the query:

1. The autonumber in the table (RecordID)
2. The information to limit what displays in the subform (StudentID)
​

Create the subform off the query and set it to datasheet/allow datasheet view=Yes (This will look like a listbox or the datasheet portion of a split form)

Once you have the subfom displaying the information the way you want.

In the auto RecordID field set format to Display as Hyperlink=Screen only

on the Form format on click enter the attached Macro (change the field name to match your entry) View attachment 45075

Save the form and close the database then reopen - the form should work like a split form. You select the RecordID in the subform to load the record to the main form.