Continuous form combo filter

I have a continuous form that has two comboboxes where data in one is dependent on data in the other.  The code I'm using is below - it occurs when focus is lost from the dependent combo.  I can enter 2 records with no problem but when I enter the third, the form deletes all records in the form.  When I debugged, it looks like when the code looks at the recordcount and goes into the if statement, on requery it's resetting the form to 0 records.  I don't know why this would be happening.

Private Sub Error_ID_LostFocus()

Dim CurrentRecord As Variant

Dim strSQL As String
Dim dbThis As DAO.Database
Dim rstErrors As DAO.Recordset

If Me.Error_ID.Value = 0 Then
    MsgBox "An Error Description is required.", , "Error Desc"
    Me.Error_ID.SetFocus
    Exit Sub
End If


strSQL = "SELECT QM_Error_Definitions.Error_ID, QM_Error_Definitions.Error_Description AS [Error desc], " & _
        "QM_Error_Definitions.Error_Capture_Vehicle_ID, QM_Error_Definitions.Error_Capture_Location_ID, " & _
        "QM_Error_Definitions.Error_Capture_Building_ID, QM_Error_Definitions.[Error_Capture_Class/Cov], " & _
        "QM_Error_Definitions.Error_Capture_Other_Source FROM QM_Error_Definitions INNER JOIN [QM_Error-Trans] ON " & _
        "QM_Error_Definitions.Error_ID=[QM_Error-Trans].Error_ID " & _
        "WHERE ((QM_Error_Definitions.Error_Exp_Date) Is Null) ORDER BY QM_Error_Definitions.Error_Description;"

Form_frmQM_MainReviewForm.frmQM_ErrorEntry.Form.Error_ID.RowSource = strSQL

DoCmd.RunCommand acCmdSaveRecord
Set rstErrors = Form_frmQM_MainReviewForm.frmQM_ErrorEntry.Form.Recordset
If rstErrors.RecordCount > 1 Then
    CurrentRecord = Form_frmQM_MainReviewForm.frmQM_ErrorEntry.Form.Bookmark
    Form_frmQM_MainReviewForm.frmQM_ErrorEntry.Form.Bookmark = CurrentRecord
    Form_frmQM_MainReviewForm.frmQM_ErrorEntry.Form.Requery
End If


End Sub
msanzenbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

msanzenbAuthor Commented:
I just ran another test and it looks like it's actually saving the records but if I remove the requery line and replace it with DoCmd.RunCommand acCmdSaveRecord then when I go back into the form it's not showing the saved records
Leigh PurvisDatabase DeveloperCommented:
Why do you want to run the Save line?

It's also entriely possible that rstErrors.RecordCount won't return a number greater than one if it hasn't moved further in the recordset yet.
rstErrors.MoveLast
rstErrors.MoveFirst
should help with that.

As for using CurrentRecord against a bookmark - what are you trying to achieve?
Moving to a particular record?  Which record?

And then again - why requery?
Surely nothing has changed data wise?
msanzenbAuthor Commented:
I actually got the bookmark code from a post on this website.  It was supposed to make these combos work together for each record
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Leigh PurvisDatabase DeveloperCommented:
When you say "the form deletes all records in the form" does it actually delete them - or just not show them?

Where are these combos - in a header used to filter the list - or in the detail - as part of data entry?
msanzenbAuthor Commented:
It just doesn't show them....all the records are actually saved.  The combos are in the detail as part of data entry.  Even when I requery it still doesn't show the saved records.
Leigh PurvisDatabase DeveloperCommented:
So what exactly are you wanting to see as a result?
As you leave the Error_ID combo - you want it's rowsource to change?
Why?

And what is the rest of the code supposed to be doing exactly?

What do you want to see happen on screen (step by step).
msanzenbAuthor Commented:
Error_ID is dependent on Error_Subcategory_ID.  Once the user chooses an Error_Subcategory_ID, I want the Error_ID combo to populate with the related values.  Once the user chooses an Error_ID for the current record, I want that value to stay for that record so that when the user goes to the next record on the continuous form, they can choose a separate subcategory/error combination.  The Error_Subcategory_ID/Error_ID combo set for each record should reflect what the user has chosen for that specific record.

From what I understand, as you leave the error_id combo, the entire form is supposed to requery itself to pick up the new value, and then bookmark the current record to distinguish it from the others.

Hope that makes sense!
Leigh PurvisDatabase DeveloperCommented:
Is the list of records in a subform or just directly a main continuous form?
(And if subform does it have a child/master field set when it shouldn't?)

One problem you have is the rowsource changing from row to row.
It may appear that an entry in your Error_ID field has dissappeared - when the Error_Subcategory_ID combo is changed (which presumably changes the rowource of your Error_ID combo).
(This is becuase all rows of continuous forms are the same control repeated - with the same properties.)

You might want to create an outer join query which includes data from the Error table and have that error field included as a textbox in your continuous form (so that it is displayed no matter what the rowsource of the combo happens to be).

Regardless - if your data is all native to Access then there should be no need to requery the entire form.
Only the combo's themselves should need requerying - indeed probably only the Error_ID one based on the SubCategory entry.
If you don't requery the whole form - then there's no need to try bookmarking records and getting back to where you started.
msanzenbAuthor Commented:
The list of records is in a continuous subform.  The subform does have a child/master set because it is directly related to the main form.

When you say have the field included as a textbox, are you saying have both the combo where they can select and have the textbox that shows them what they selected?  If so, then the combo should not be bound to the column in the table...is that correct?

Why do you think the records don't show up when there are 3 or more records on the table?
Leigh PurvisDatabase DeveloperCommented:
Why the records dissappear isn't certain at this stage.
Perhaps they just hide from view - something to do with the requery and attempting to get back to where you were?

What happens if you don't do all the requerying and bookmarking navigation?
What doesn't happen that you want to happen?

As far as the textbox/combo scenario - I'd suggest that you have the combo there still present - bound to the Error_ID foreign key in your main table - and an Error description field (or whatever) from your related Error table.
That way the description gets shown if one is selected - regardless of the rowsource of the combo.
In time - you could make the combo narrow so that it doesn't show any text - and just let the textbox do that bit for you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
msanzenbAuthor Commented:
I just decided to open another form and work it that way...it's just less complicated.  Thanks for all of your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.