Full Version: SQL/Listbox/Control problem
UtterAccess Forums > Microsoft® Access > Access Forms
poporacer
have a form that has cascading listboxes. The first one lists incident numbers and the second one is for names that are associated with the selected incident number. The form has textboxes that are populated via code from the query on the second textbox. I have a command button to add incident numbers. When this button is clicked, a form opens up and you input the information and then the form closes. The new incident number is there and the name is there but when you select the name to input the pertinent data, the textboxes on the form get populated with data....(apparently from the first record in the table) The textboxes should be blank. If I close the form and reopen it, the boxes are blank so it appears that the query is working properly. Here is a snippet of the code I use.
Private Sub lstCDCNum_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![lstCDCNum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.txtConvictDate = rs![Conviction Date]
Me.txtCourtComments = rs![CourtComments]
Me.txtDAAccept = rs![Date DA Accepted]
Me.txtDACaseNum = rs![DA Case Number]
when I look in the code window it shows the recordsets are null, but the textboxes get populated with data! Any suggestions?
theDBguy
Welcome to Utter Access!
Have you tried stepping through the code?
poporacer
Yes, When I step through the code it shows the rs as being null, not sure why it puts data in the textboxes.
theDBguy
Are you able to post a small copy of your db with test data?
poporacer
Sure...here it is
theDBguy
Hi,
modified your code by resetting the first listbox:
Private Sub cmdAddIncident_Click()
lstLogNum = Null
Call lstLogNum_AfterUpdate
DoCmd.OpenForm ("frmAddIncident"), acNormal, , , acFormAdd, acDialog
Me.Refresh
End Sub
Hope that helps...
poporacer
That didn't fix the problem....to duplicate problem do this...Click on add incident, fill in the controls on the new form that opens andthen click on"Add Incident" . Then select the new incident you added and then the name in the second listbox....the data gets filled in when it should be blank!
theDBguy
Sorry, I didn't know how to produce the problem earlier.
Short on time right now, but will try again tomorrow.
Meanwhile, try modifying your code from:
rs.FindFirst "[ID] = " & Str(Nz(Me![lstCDCNum], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
to
rs.FindFirst "[ID] = " & Nz(Me![lstCDCNum], 0)
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
Me.txtIncidentDate = Me.lstCDCNum.Column(8)
Me.txtCDCNum = Me.lstCDCNum.Column(1)
Me.txtName = Me.lstCDCNum.Column(2)
... and the rest
End If
Hope that helps...
poporacer
I made the changes, but still the same thing. Hopefully we can get this figured out....It has been stumping me for a while!
theDBguy
I think I figured it out! After you add the incident to the table, it doesn't get added to the form right away. And since you are creating a recordset based on your form, you don't see the new incident. To fix that, add one more line of code to the first one I gave you:
Private Sub cmdAddIncident_Click()
lstLogNum = Null
Call lstLogNum_AfterUpdate
DoCmd.OpenForm ("frmAddIncident"), acNormal, , , acFormAdd, acDialog
Me.Refresh
Me.Requery
End Sub
Hope that helps...
poporacer
Woo Hoo.....You solved it...it appears to work like a champ...I greatly appreciate it! I have worked on it myself for several hours researching and changing things. I figured the SQL was not working...did not think about the form itself not being updated....
Thank you!
theDBguy
You're very welcome. Glad I could assist. If I make one more suggestion...
Since you are using unbound controls on your form, why use a bound form? If you don't need to bind your form, Access won't have to worry about loading the data when the form opens. Instead of creating a copy of the recordset and searching it, I think it would be more efficient to use SQL to load a single record recordset to populate your controls.
Just my 2 cents...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.