Problem updating combobox

[poporacer]

I have a form (frmEdit) with 2 combo boxes. The first combobox (cmbLogNum) is used to filter the contents of the second combobox (cmbCDC2). I have a notinlist event for the cmbLogNum to open e new form (frmAddIncident) to add a new record to the database. The new record is added and frmAddIncident is closed. The problem is when you select the new incident & the CDC#, the data in the fiels should be blank but they are filled in. If you close the form and open it again it works OK. I think the issue is that cmbLogNum is not being updated from the query that populates the combobox. I tried to requery both the form and combo box but got errors both times.
Any suggestions?

 

[ajetrumpet]

The problem is when you select the new incident & the CDC#, the data in the fiels should be blank but they are filled in.

No. The data should be filled in, because that's what you told it to do. Take a look at this event:

Private Sub cmbCDC2_AfterUpdate()

Because there is already a new record added from the popup form (from the NotInList event), the boxes are populated from this block:

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    Me.txtName = Me.cmbCDC2.Column(2)
    Me.txtEthnic = Me.cmbCDC2.Column(4)
    Me.txtCII = Me.cmbCDC2.Column(6)
    Me.txtDOB = Me.cmbCDC2.Column(3)
    Me.txtFBI = Me.cmbCDC2.Column(5)
    Me.txtCommit = Me.cmbCDC2.Column(7)

You may be questioning the EOF line at the beginning of this. If your intention was to check the recordset to see if the FindFirst action found the last record in the database, you can't use the EOF property. The last record does not indicate EOF. If that is the problem, use the absoluteposition +1, combined with "recordcount" to conditionalize when finding the last record of the set. Also, this code:

rs.FindFirst "[ID] = " & Str(Nz(Me![cmbCDC2], 0))

doesn't do anything (I don't think), because you are not using the value that you have looked up. The boxes are populated based on the value in the cmbCDC2 combo box (which, in turn, gets its value from the first combo box). The findfirst method doesn't have anything to do with that process (from the way it is currently written).

And BTW, it's good to see you back. Been awhile since I've talked to you.

 

[poporacer]

Thanks Adam,

I appreciate your help and did some research. I added a public variable (flgNil) to indicate whether a record was added (the isnull DLookup did not work and I figured this was easier) When frmAddInmate opens flgNil is set to false. Then if a record is added it is set to true and the form closes. But when the routine checks the status of flgNil is is always false...does it reset to false when the form closes? I am getting close but not quite.
Thanks for your help!
Rick

P.S. I attached the revised database for your perusal.

 

[ajetrumpet]

I have to go now, but one thought that people never seem to pass when working with variables is:

Variables that have not been destroyed after a procedure runs keep their values! Thus, if a boolean value is set to False when a form closes, but needs to be set to True when that form opens, if you have not written

Boolean Value = True

on one of the form's initializing events, the value is still set to False when that form opens again (I am obviously applying this because you mentioned a public variable that is now included in the process).