Please Help! Information only half saving

[tjones]

I have a split form with cascading combo boxes and other entry fields. The information in the cascading combo boxes is not saving though the rest of the information on the form is. I have also included a graphic of the cascombobox setup (though not other fields on form as they are working and saving) Also every time I attempt to add a new record, when you change the selection in the cascading combo boxes it changes the information for the first record to match the second record (And it will only allow me to enter 2 records when I need 20+ records)

the save and save & close buttons

"Save"
RunMenuCommand
Command Save Record
RefreshRecord

"Save and Close"

If [Form].[Dirty]
Save Record
end if

Close Window [,,Prompt]
Submacro ErrorHandler
MessageBox (=[MacroError].[Description],Yes,None,)
End Submacro

the code for the cascading combo is:

Option Compare Database
Option Explicit

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.UnitsAssigned, qryCourseDescList.Offered, qryCourseDescList.Prereqs 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

Private Sub EnableControls()

' Clear the combo boxes
If IsNull(Me.cboCourseTypeID) Then
Me.cboDepartmentID = Null
End If

If IsNull(Me.cboDepartmentID) Then
Me.cboCourseNoID = Null
End If

If IsNull(Me.cboCourseNoID) Then
Me.cboCourseNameID = Null
End If

' 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 cboTransfer_AfterUpdate()
'Hide the tranfser from field unles status is transferred
If Me.cboTransfer = "Yes" Then
Me.cboTransferedFrom.Visible = True
Else
Me.cboTransferedFrom.Visible = False
End If
End Sub

Private Sub Form_Current()
'Hide the tranfser from field unles status is transferred
If Me.cboTransfer = "Yes" Then
Me.cboTransferedFrom.Visible = True
Else
Me.cboTransferedFrom.Visible = False
End If
End Sub

Private Sub Form_Load()
' 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]

hmmm...sounds like you need to post your project. Could be a number of issues. You probably can't save but 2 records because of your query joins. It sounds like your form is bound to the query and the query doesn't allow additions. Check the tables in your join - there is probably one table in there that only has two records.

The other issue about your combo boxes not saving - sounds like the combo boxes on your form aren't bound to a field - but this also could be related to your query.

Hard to tell without seeing the project.

 

[tjones]

Here is the database with any extra removed

View attachment GPH Minor Student Database(cascadingreduced).accdb

 

[AccessMSSQL]

It looks like you have a few things going on here. You have a course type table with a CourseTypeID field but in your tblCoursesTaken table you have CourseType (text 25). You need the following fields in your tblCoursesTaken table:

CourseTypeID numeric
CourseNoID numeric
DepartmentID numeric
CourseNameID numeric

Get rid of the text fields you have in there - it's redundant.

Then once you do that I notice your combo boxes in Courses Taken form for those fields are not bound to a field in the table (which is why it's not saving). Make the controlsource of the combo boxes = to the new fields you create in your table.

Try that to start and let me know how it goes.

 

[tjones]

OK, it lets me save more than one record now.

It also saves the CourseTypeID and the CourseNameID fields but not the two middles ones (Department and courseNo) even though they are now bound.

But now it is showing "Enter Parameter Value" message for

tblCourseTaken.CourseType, and doing a real funky thing on the saving. View attachment GPH Minor Student Database(cascadingreduced).accdb

 

[AccessMSSQL]

You have the OrderBy property of your CoursesTaken form set to order by CoureType.. remove that from the form property and you should be good. DepartmentID and CourseNoID are being saved. I tested it but you need to reselect the combo box values because it looks like the department and courseno combo boxes aren't being refreshed automatically when you move to a new record. Might be just left over from making the changes you made.

 

[tjones]

THANKS! Your help has fixed all my problems but two. Everything is now working except that the values (department and courseNo) are saving, but not displaying in the split form. :banghead:

Once you enter records, the cascading fields are no longer grayed out an unavailable until you start a new record. I would imagine this is just a setting in the code (like the code should also be under current??)

View attachment GPH Minor Student Database(cascadingreduced).accdb

 

[tjones]

Yes, adding the code under Current make the cascading fields grayed out an unavailable until you begin the selection. Now to fix the display problem and all is right in the access world, at least for now!

Thanks for all the help.

 

[tjones]

Saving but not displaying on split form

I have been working on the cascading combo boxes. Everything is now working except that the values (department and courseNo) are saving, but not displaying in the split form.

I have followed the advice on fixing the problem (must not be doing it right as I can't get them to work) and searched the forum but have not been able to find anything to help.

Here is the section of the database dealing with the combo. View attachment GPH Minor Student Database(cascadingreduced).accdb

 

[AccessMSSQL]

let me take a look

 

[AccessMSSQL]

You have some funny things going on in your after update event of your combo boxes. I would check the code. You have a sub called EnableControls. Each time you change any combo box you set one of the others to null. I think this is one of the problems you are having. Also, on your form_load event your are calling EnableControls also. Put a breakpoint in your code and step through while you make changes and see how this affects the values in your combos.

 

[tjones]

Way Way over my head there.

 

[tjones]

I did actually manage to get the record to display in the split table now by working with the Row Source code. Now the only problem is that it is displaying "15" instead of "BIOL" and for the department and "12" instead of "525" Course Number.

 

[tjones]

I managed to get it to save the actual data I wanted displayed by changing the row source code from ID to Name

SELECT tblCourseDept.DepartmentID, tblCourseDept.DepartmentName FROM tblCourseDept ORDER BY tblCourseDept.DepartmentID;