2000 code not recognized in 2007

[mlai08]

I have a combo box with employee names on a main form, which is used to search employee records on the sub form. It works well in Access 2000 but some Methods used in 2000 are no longer valid in Access 2007-2010 after I migrated the database to 2007. For example, the "If Not .NoMatch" method is not found in the 2007 version on the following sub routine. Can anyone suggest how can I change the codes to make it work again?

Private Sub cboFind_AfterUpdate()
'Purpose: Find employee indicated in drop-down box
On Error GoTo Error_cboFind_AfterUpdate
Dim rst As Recordset

If IsNull(Me.cboFind) Then Exit Sub
Set rst = Me.fsubEmployeeInput.Form.RecordsetClone
With rst
.MoveFirst
.Find "EmpID = " & gcstrQuote & Val(Me.cboFind) & gcstrQuote
If Not .NoMatch Then
Me.fsubEmployeeInput.Form.Bookmark = .Bookmark
End If
End With
Exit_cboFind_AfterUpdate:
Exit Sub
Error_cboFind_AfterUpdate:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_cboFind_AfterUpdate
End Sub

Thanks

 

[RainLover]

A 2000 has a Wizard in the Tool box that will write the code to do this.

I don't use 2007 but I would assume it also has a wizard to do the same thing.

Suggest that you give it a try.

 

[mlai08]

Thanks for the response but I don't see there is a feature in the form wizard to add codes to filter records on the sub form by selecting a respective ID on the main form. The subform wizard only helps will adding the sub form and identifying the Master/Child link field. Can you be more specific on your suggestion?

 

[RainLover]

Just to be sure I am reading you correctly.

You have a Main Form that displays ONE employee record at a time.
The sub form shows in more detail Information about each Employee. There is usually multiple recors in the sub.
The Sub is Joined to the Main Master/Child via the Employee ID

If this is correct you need to find a particular Employee from the Main Form which in turn will display different records in the sub due to the Master/Child links.

Therefore if you use a Combo box on the Main to find a new employee you change the sub automatically.

In the Tool Box Menu you can add Text Boxes, Sub Forms, Command Buttons etc.

If you select a Combo Box the wizard will prompt you to select EmployeeID in the main.

The following is what the wizard created for me. (You would have different Names)

Private Sub Combo13_AfterUpdate()
 
    'Find the record that matches the control.
    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ClientPK] = " & Str(Nz(Me![Combo13], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
 
End Sub

 

[boblarson]

If the code you had used to work but doesn't now, the simple fix is to just change this small bit of code:

Dim rst As Recordset

to this

Dim rst As DAO.Recordset

 

[mlai08]

It works like a chime. Thank you so much for your help.

Now I have another problem. After migrating to 2007, the customer menu bar on the Start Up Form does not show up. I can't find it under Options/Current Database/Shortcut Menu Bar dropdown but I see it under the Add-Ins top menu though. The purpose is for users to only see the customer menu bar and not the default menu bar. I have tried to set up custom Ribbon but it does not work for me.

Do you have any good idea how to make this work? I know this is a differenent topic but just hope you have an easy solution before I post another thread on this subject.

Thanks again.

 

[boblarson]

No easy solution I'm afraid. You can either create your own Ribbon, live with the location under Add-Ins, or create a form that stays open but that might be a little difficult to make work.

 

[mlai08]

No easy solution I'm afraid. You can either create your own Ribbon, live with the location under Add-Ins, or create a form that stays open but that might be a little difficult to make work.

I tried to create custom Ribbon but can only make it shows up on the Ribbon dropdown list but the Ribbon itself does not show up. I could live with as is under Add-Ins but the Home tab still shows up on user's PC when they open the database. Is there a way to hide the default Home tab?

 

[boblarson]

I tried to create custom Ribbon but can only make it shows up on the Ribbon dropdown list but the Ribbon itself does not show up. I could live with as is under Add-Ins but the Home tab still shows up on user's PC when they open the database. Is there a way to hide the default Home tab?

I do not believe so. I have tried too and haven't been able to. But as for your custom ribbon not showing up, it would indicate that you have not created it properly - the XML is not correct. It is a bit of a challenge to learn that but it isn't impossible.

A good book I recommend on the subject is:
RibbonX: Customizing the Office 2007 Ribbon

I own a copy personally and it helped immensely.

 

[mlai08]

boblarson, thanks again for your guidance. I have done some reading on the web and been able to make the XML work to display custom ribbon on forms and reports. The only weird thing I encountered is that some buttons only display when I open the database by holding down the Shift key. If the database is opened normally, not all buttons are displayed.

Below is the XML for a custom tab associated with a report. In this case, only Exit button is displayed but not the Export to Excel and Print Menu button when the database is opened normally.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="true">
<tabs>
<tab id="dbCustomTab" label="Report Tab" visible="true">
<group id="dbCustomGroup" label="Report Group">
<control idMso="ExportExcel" label="Export to Excel" enabled="true"/>
<control idMso="FilePrintMenu" label="Print Menu" enabled="true"/>
<control idMso="FileExit" label="Exit" enabled="true"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>

Just wonder if you have any clue on this?

Thanks