Access 2003 to 2007 Code not working

[pgsibson]

Hi Everyone
I have a form that displays the results of a query. I have a combo box to select and filter information on this form. All worked well in 2003 but 2007 goes awry. Initially I have 957 results displayed from the query and after the initial selection from the combo code is

Private Sub combooptDocumentCode_AfterUpdate()
DoCmd.ApplyFilter , "[DocumentCode]=Forms!frmPatientInformation!combooptDocumentCode"
EnableControls Me, acDetail, True
End Sub

the form redisplays the selected matching record and the filter marker on the ribbon shows filtered. If I reset the form to display all records I go back to my 957 starting position but if I make another combo selection the form just blanks and just the background is displayed.

I have attached some images to help

Any help would be greatly appreciated.

 

[vbaInet]

Show the Reset code. Also when you reset you should clear the combo box by setting its value to the empty string.

 

[Khalid_Afridi]

Try to compact and repair database and also check for any missing references if you have in any VBA module.


Tools>>>>>References

Also ....

Compile database
Debug>>>>>Compile... from code menu

Khalid

 

[pgsibson]

Hi
The reset code is

Private Sub btnReset_Click()
Forms!frmPatientInformation!combooptselOwner = Null
Forms!frmPatientInformation!combooptDocumentCode = Null
Forms!frmPatientInformation!combooptDepartment = Null
Me.optSelDirectorate = Null
DoCmd.Requery
DoCmd.ShowAllRecords
On Error GoTo Err_btnReset_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_btnReset_Click:
Exit Sub
Err_btnReset_Click:
MsgBox Err.Description
Resume Exit_btnReset_Click
End Sub

I have also debuged the code with no problems and compacted and repaired the database with no apparent problems. The references I am not sure about. The list was partially ticked and I don't think I am using anything that was not in the list.

Could it have anything to do with trying to filter a query rather than a table?

Still does not work?

 

[vbaInet]

Let's amend both procedures. Use Filter and FilterOn properties of the form instead.

Try these:

Private Sub combooptDocumentCode_AfterUpdate()
    Me.Filter = "[DocumentCode]=Forms!frmPatientInformation!combooptDocumentCode"
    Me.FilterOn = True
    EnableControls Me, acDetail, True
End Sub

Private Sub btnReset_Click()
On Error GoTo Err_btnReset_Click

    With Forms!frmPatientInformation!
        !combooptselOwner = Null
        !combooptDocumentCode = Null
        !combooptDepartment = Null
    End With
    Me.FilterOn = False
    Me.Filter = ""
    Me.optSelDirectorate = Null
'    DoCmd.Requery
'    DoCmd.ShowAllRecords
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    
Exit_btnReset_Click:
    Exit Sub
Err_btnReset_Click:
    MsgBox Err.Description
    Resume Exit_btnReset_Click

End Sub

Could also show what your EnableControls routine does? Just curious how you're using Me and the acDetail constant.

 

[pgsibson]

Hi VbaInet

That didn't work. I had to reset the form after opening it before selecting a document code. The initial state was as image 3 and the selection process did not work

 

[vbaInet]

So what does the EnableControls routine do? Also what is the menuItem method selecting?

Can you post your db?

 

[pgsibson]

The db is rather large and I cannot see a way to depersonalise it.
The enable controls function has crept in form some other person advising me
Function EnableControls(frm As Form, intSection As Integer, intState As Boolean) As Boolean

' Enable or disable controls in specified section of form.
' Use the Form object, section constant and state arguments
' passed to the EnableControls procedure.

Dim ctl As Control

' Set intState for all controls in specified section.
For Each ctl In frm.Controls
If ctl.Section = intSection Then
On Error Resume Next
ctl.Enabled = intState
Err = 0
End If
Next ctl

EnableControls = True

End Function

Don't think it is doing anything in this situation and the emnu item is microsoft standard
It is as if the filter is working and there is nothing to display, which is not true and previously it always worked the first time to a second always fails.

Thanks for your help

 

[vbaInet]

Actually, let me get some things clarified.

1. The combo box is on the main form right?
2. Is the form being filtered a subform?

 

[pgsibson]

The combo box is in the header of the main form and filters the main form. There is a subform displaying the detail and dates linked to the ID of the main form.

 

[vbaInet]

Replace this line:

Me.Filter = "[DocumentCode]=" & combooptDocumentCode

 

[pgsibson]

Syntax error expected end of statement with cursor on the last " Any idea what has changed in 2007. My code worked perfectly in 2003

 

[vbaInet]

There was a quote at the end, that was a typo. Remove the last quote and see the result.

In relation to your code, nothing has changed. It may be the way 2007 handles the code in the background.

 

[pgsibson]

That produced a strange result. When I select a document code a parameter query message box is opened with the document code as the text question. Typing in a code and selecting dispalys that document correctly. Resetting and trying another document code the form displays the previously selected code's document?
I have noticed that on opening the from the filtered box at the bottom of the form next to the record selectors shows unfilterd. After resetting it is greyed out and shows no filter?

 

[vbaInet]

It would be quicker to diagnose if we could see the db? Are able to post it?

Here's advice on how:
http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Khalid_Afridi]

Hi
The reset code is

Private Sub btnReset_Click()
Forms!frmPatientInformation!combooptselOwner = Null
Forms!frmPatientInformation!combooptDocumentCode = Null
Forms!frmPatientInformation!combooptDepartment = Null
Me.optSelDirectorate = Null
DoCmd.Requery
DoCmd.ShowAllRecords
On Error GoTo Err_btnReset_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_btnReset_Click:
Exit Sub
Err_btnReset_Click:
MsgBox Err.Description
Resume Exit_btnReset_Click
End Sub

I have also debuged the code with no problems and compacted and repaired the database with no apparent problems. The references I am not sure about. The list was partially ticked and I don't think I am using anything that was not in the list.

Could it have anything to do with trying to filter a query rather than a table?

Still does not work?

change this line of code
'DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
with:

[COLOR="Magenta"]Me.Refresh[/COLOR]

and it should solve your problem.

 

[SOS]

There's already a requery, so no refresh needed.

 

[Khalid_Afridi]

Yes!
you are right SOS, I just noticed that now...
but OP wants to refresh the form itself I think and DoCmd.DoMenuItem acFormBar.... produce problem here.