SQL/Textbox issues

[poporacer]

I 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?

 

[TheAceMan1]

How are ya poporacer . . .

Your populating the fields wether CDCNum is found or not!. Try the following:

[blue]   Dim rs As DAO.Recordset
   
   Set rs = Me.Recordset.Clone
   
   If Not rs.BOF Then
      rs.FindFirst "[ID] = " & Nz(Me![lstCDCNum], 0)
      
      If Not [purple][b]rs.NoMatch[/b][/purple] 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]
      Else
         MsgBox "CDCNum Not Found!"
      End If
   Else
      MsgBox "No Records!"
   End If
      
   Set rs = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

poporacer . . .

Just noticed something.

[blue]Set rs = Me.Recordset.Clone
    should be
Set rs = Me.[purple][b]RecordsetClone[/b][/purple][/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[poporacer]

Thanks. I tried your change, but still the same....You can only select a CDCNum that exists by the way I have the listbox set up. I will post the DB when I can.

 

[poporacer]

Here is the DB...To duplicate the issue Click on "Add new Incident" then fill in the controls. Then click on Add Incident" Now you can select the new incident and then select the name you put in....The textboxes should be blank to fill in the data but they get populated. I have been pulling out my hair for days on this one...any suggestion?

 

[Pampers]

Hi poporacer,
Looked at your database and made a few modification. I created (from your code) two procedures BindDataOffender and BindDataIncident (I didn't put in any error handling). THen I call them in the afterupdate event of your listboxes.... Works ok.

STEP 1: Creating the procedures

Private Sub BindDataOffender()
'
'Bind DataOffender to Form Controls
'
Me.txtIncidentDate = Me.lstCDCNum.Column(8)
Me.txtCDCNum = Me.lstCDCNum.Column(1)
Me.txtName = Me.lstCDCNum.Column(2)
Me.txtEthnic = Me.lstCDCNum.Column(4)
Me.txtCII = Me.lstCDCNum.Column(6)
Me.txtDOB = Me.lstCDCNum.Column(3)
Me.txtFBI = Me.lstCDCNum.Column(5)
Me.txtCommitment = Me.lstCDCNum.Column(7)

End Sub

Private Sub BindDataIncident()
'
'Bind the IncidentData to Form Controls
'
Dim strSql As String
Dim rst As DAO.Recordset
Dim dbs As DAO.Database


'Create Recordset
strSql = _
"SELECT * FROM tblOffense " & _
"WHERE LogNum = " & "'" & Me.lstLogNum & "'"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSql)

'Bind Data
Me.txtConvictDate = rst![Conviction Date]
Me.txtCourtComments = rst![CourtComments]
Me.txtDAAccept = rst![Date DA Accepted]
Me.txtDACaseNum = rst![DA Case Number]
Me.txtDARefer = rst![Date DA Referred]
Me.txtDAReject = rst![Date DA Rejected]
Me.txtDismissDate = rst![Dismissed date]
Me.txtGuiltyDate = rst![Conviction Date]
Me.txtISUDeny = rst![ISU Denied Date]
Me.cmbMentalHealth = rst![MentalHealth]
Me.txtNextCourt = rst![NextCourtDate]
Me.txtPenalCode = rst![Penal Code]
Me.txtSpecificOffense = rst![Specific Offense]
Me.txtComments = rst![Comments]
Me.chkEscape = rst![Escape]
Me.chkHomicide = rst![Homicide]
Me.chkIndecentExp = rst![Indecent Exposure]
Me.chkInmateAssault = rst![Inmate Assault]
Me.chkOther = rst![Other]
Me.chkPossessionDrugs = rst![Drug]
Me.chkPossessionWeapon = rst![Weapon]
Me.chkSexualAssault = rst![Sexual Assault]
Me.chkStaffAssault = rst![Staff Assault]

'Clean Up
Set rst = Nothing
Set dbs = Nothing

End Sub

Step 2: Call the procedures

Private Sub lstCDCNum_AfterUpdate()
Call BindDataOffender
End Sub

Private Sub lstLogNum_AfterUpdate()
              
    Me.lstCDCNum.Requery
    
    'Call the Incident data
    Call BindDataIncident

PS: Since you work with unbound controls I think you can get rid of the recordsource of the form.

Pampers
Keeping it simple can be complicated

 

[poporacer]

Thanks for the help...It didn't work like it is supposed to. Here is what I did, to get closer. The Add incident problem is resolved, but it created a new problem...I will try to work this out but for your info... When you click on an incident number, all the textboxes need to be cleared and then the lstCDCNum is requeried...then when you click on a cdcnum, the entire form gets populated at that time. I kept the subroutine
Private Sub lstLogNum_AfterUpdate()
The same and modified the

Private Sub lstCDCNum_AfterUpdate()
Call BindDataOffender
'Call the Incident data
Call BindDataIncident
Me.lstCDCNum.Requery

The Add Incident still works but when you select an incident and then select a name, the form populates correctly...the problem is if you select a different name from the same log number, not all of the textboxes update properly (Call BindDataIncident doesn't seem to be requeried)