listbox won't refresh data, causing queries to have incorrect value

[Richard Hollenbeck]

Thanks to everyone for your help. Doug Steele, this is the next stage in
the problem you were helping me with yesterday. By the way, I got the table
to update alright, but it's no longer in the error handler, as you will see.
But the problem is that the courseCode is the previous selection from the
previous form.

Why won't the value in the list box change when the value in the list box
changes?

me!requery in the form "frmGroups" doesn't seem to do it. To be sure, just
before I run a query, I run a msgbox to see what the value is and it shows
the current selection. No problem! But when I run the append query, it
applies the query to the previous value of the list box (in the previous
form (which is still open)). Here's my subroutine in "frmGroups":

Private Sub PopulateListBox()
On Error GoTo Err_PopulateListBox
reload_PopulateListBox:

Dim db As DAO.Database, rs As DAO.Recordset, StrSQL As String

'If current course has groups in it. . .
If Nz(DLookup("[groupID]", "[groups]", "[groups].[coursecode] = '" &
Forms!frmSelectCourse!cboSelectCourse & "'"), "") <> "" Then
StrSQL = "SELECT [groups].[groupID] AS [ID],
[groups].[groupDescription] AS [Group], [groups].[groupWeight] AS [Weight]
FROM [groups] WHERE [groups].[courseCode] = '" & Me.courseCode & "'ORDER BY
[groups.groupOrder]"

' I just now tried changing Me.courseCode to
' "Forms!frmSelectCourse!cboSelectCourse"
' but that didn't change anything.

Set db = CurrentDb()
Set rs = db.OpenRecordset(StrSQL, dbOpenDynaset)
Set lstGroups.Recordset = rs ' This all works in an established
course with groups.

Else ' add a group to the course
DoCmd.OpenForm "frmNewGroup", , , , , acDialog 'frmNewGroup is a
modal popup form.
StrSQL = "INSERT INTO groups (courseCode, groupDescription,
groupWeight) VALUES ( '" & Forms!frmSelectCourse!courseCode & "', '" &
Forms!frmNewGroup!txtGD & "', " & Forms!frmNewGroup!txtGW & " )"
CurrentDb.Execute StrSQL, dbFailOnError
DoCmd.Close , "frmNewGroup"
MsgBox Forms!frmSelectCourse!cboSelectCourse

' Maybe I should call Form_Load from here to start over,
' or re-call PopulateListBox (risking an infinite loop)

End If

' If I don't do something like this the form will appear
' totally blank because it couldn't find the courseID which
' is an auto number created when a new group is created.

' the problem is that it is reading the wrong CourseCode
' (a five digit numerical String) from the previous form
' because, for some reason, the course code sticks from
' the previous selection in the previous form.

' If necessary, I'll post the code for the
' cboSelectCourse_OnChange, etc. from
' the previous form.

Exit_PopulateListBox:
Exit Sub

Err_PopulateListBox:
MsgBox "Error " & Err.Number & ", " & vbCrLf & Err.Description,
vbCritical, "case else from Sub PopulateListBox"
Resume Exit_PopulateListBox

End Sub


Many thanks.

Rich Hollenbeck

 

[Jeff Boyce]

Rich

I'm sure you know what you meant, but when I read

"Why won't the value in the list box change when the value in the list box
changes?"

I can honestly say "I don't understand".

 

[Richard Hollenbeck]

Thank you, Jeff, for your rapid reply and your kind way of telling me I
didn't explain my situation very well. Here's the deal; I have a gradebook
program. The opening form has a listbox of existing classes (primarily
community college classes) and a row of buttons for things to do. Classes
are made of groups of activities and students. For example, groups may be
"Participation", "Essays", "Exams", etc. These are the groups. In Exams,
for example, there may be "Midterm", "Final", etc.

So I'm in the main form and I click on a course. Then I click on the
"Groups/Activities" button (which is actually a label, but that isn't the
point), and I expect to go to a form that displays all the groups and
activities within that course.

So far, so good.

But if I click on a course that doesn't yet have any groups of activities,
and then click the Grouops/Activities button (modified label) the entire
form appears blank.

So I added some code to first determine whether or not the course has any
groups associated with it. If so, it should display normally. Otherwise it
should open a pop-up form to enter a group.

Here's where the trouble begins. The newly entered group does get entered
into the groups table but the courseCode is wrong; therefore, the form still
appears blank. After a little investigation, I learned that the course code
assigned to the new group is the previously selected course in the previous
form rather than the currently selected course.

I know this sounds strange, but I understand what I mean. Sorry. I'll be
happy to explain finer details about anything I didn't explain clearly. You
will probably want the code from the Sub "cboSelectCourse_AfterUpdate"
from the previous form. I'll send that next, and other code, as needed.

Thanks for your offer of help.

Rich Hollenbeck

 

[Richard Hollenbeck]

Thinking this might be relevent, I'm posting this subroutine from the
previous form:

Private Sub lblGroupsAndActivities_Click()
On Error GoTo Err_lblGroupsAndActivities_Click
If cboSelectCourse <> "" Then
Dim stLinkCriteria As String
stLinkCriteria = "[courseCode]=" & "'" & Me!cboSelectCourse & "'"
DoCmd.OpenForm "frmGroups", , , stLinkCriteria
Else: MsgBox "First, in the combo box to the right, select a course."
End If

Exit_lblGroupsAndActivities_Click:
Exit Sub

Err_lblGroupsAndActivities_Click:
MsgBox "Error number: " & Err.Number & vbCrLf & "Description: " _
& Err.Description & vbCrLf & "Source: " & Err.Source
Resume Exit_lblGroupsAndActivities_Click

End Sub

 

[Richard Hollenbeck]

Doh! I'd better take a nap. I think I just saw code making my stuff reset
itself inappropriately. Now that I look at it again I can't find it. Please
reply anyway, as I'm pretty confused. ? Thanks.

rh

 

[Jeff Boyce]

Richard

Have you set a breakpoint in your procedure and stepped through the code,
inspecting values at each step? Is there a chance that what you want your
code to do is not how Access is interpreting it?