msanzenb
asked on
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
"QM_Error_Definitions.Erro
"QM_Error_Definitions.Erro
"QM_Error_Definitions.Erro
"QM_Error_Definitions.Erro
"WHERE ((QM_Error_Definitions.Err
Form_frmQM_MainReviewForm.
DoCmd.RunCommand acCmdSaveRecord
Set rstErrors = Form_frmQM_MainReviewForm.
If rstErrors.RecordCount > 1 Then
CurrentRecord = Form_frmQM_MainReviewForm.
Form_frmQM_MainReviewForm.
Form_frmQM_MainReviewForm.
End If
End Sub
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
"QM_Error_Definitions.Erro
"QM_Error_Definitions.Erro
"QM_Error_Definitions.Erro
"QM_Error_Definitions.Erro
"WHERE ((QM_Error_Definitions.Err
Form_frmQM_MainReviewForm.
DoCmd.RunCommand acCmdSaveRecord
Set rstErrors = Form_frmQM_MainReviewForm.
If rstErrors.RecordCount > 1 Then
CurrentRecord = Form_frmQM_MainReviewForm.
Form_frmQM_MainReviewForm.
Form_frmQM_MainReviewForm.
End If
End Sub
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?
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?
ASKER
I actually got the bookmark code from a post on this website. It was supposed to make these combos work together for each record
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?
Where are these combos - in a header used to filter the list - or in the detail - as part of data entry?
ASKER
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.
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).
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).
ASKER
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
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!
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!
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.
(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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just decided to open another form and work it that way...it's just less complicated. Thanks for all of your help!
ASKER