Question Compatability Issue Access 97 and 2007

[mcclunyboy]

Hi,

I have a database in Access97 which works mostly fine. However we have recently upgraded to Access 2007. Most of the database is fine, we had to remove some old VB references and code but for a few months got no reports of any issues.

However we have now discovered 2 things.

Firstly we have a form which has 2 drop down menus and a number of text boxes. The first drop down menu selects a department and the second an individual from within that department, once selected it populates the text box (code below)

    [SIZE=2]Private Sub SelectUser_AfterUpdate()[/SIZE]
  
  [SIZE=2]    '  Find record for user selected in SelectUser combo box.[/SIZE]
  [SIZE=2]    '  Enable controls in detail section and disable UserID text box.[/SIZE]
  [SIZE=2]    '  Go to Type combo box.[/SIZE]
  
  [SIZE=2]    DoCmd.ApplyFilter , "UserID = Forms!frmLimitEditUsers!SelectUser"[/SIZE]
  [SIZE=2]    EnableControls Me, acDetail, True[/SIZE]
  [SIZE=2]    Me!UserID.Enabled = False[/SIZE]
  [SIZE=2]    Me!Type.SetFocus[/SIZE]

For some reason the textboxes are not being populated with the details (this did work in access 97). Anyone know why or suggest a solution?

Secondly, we used to add a button to the toolbar in Access 97 which allowed the user to select "edit" or "add" mode on a form, the code is below, is there any alternative to this in Access 97.

    [SIZE=2]Function EditData() As Integer[/SIZE]
  
  [SIZE=2]    '  Code for Edit Data button on EnterOrEditUsers1 toolbar.[/SIZE]
  
  [SIZE=2]    Dim frm As Form[/SIZE]
  [SIZE=2]    Dim ctlCombo1 As Control[/SIZE]
  [SIZE=2]    Dim ctlCombo2 As Control[/SIZE]
  [SIZE=2]    Dim ctlCombo3 As Control[/SIZE]
  
  [SIZE=2]    On Error GoTo EditData_Err[/SIZE]
  
  [SIZE=2]    Set frm = Forms!frmEditUsers[/SIZE]
  [SIZE=2]    Set ctlCombo1 = Forms!frmEditUsers!SelectBusiness[/SIZE]
  [SIZE=2]    Set ctlCombo2 = Forms!frmEditUsers!SelectSection[/SIZE]
  [SIZE=2]    Set ctlCombo3 = Forms!frmEditUsers!SelectUser[/SIZE]
  
  [SIZE=2]    '  Set AllowEdits property to True to indicate Edit mode.[/SIZE]
  [SIZE=2]    frm.AllowEdits = True[/SIZE]
  
  [SIZE=2]    '  Switch toolbars.[/SIZE]
  [SIZE=2]    DoCmd.ShowToolbar "EnterOrEditUsers1", acToolbarYes[/SIZE]
  [SIZE=2]    DoCmd.ShowToolbar "EnterOrEditUsers2", acToolbarNo[/SIZE]
  
  [SIZE=2]    '  Enable and move to first combo box.[/SIZE]
  [SIZE=2]    ctlCombo1.Enabled = True[/SIZE]
  [SIZE=2]    ctlCombo1.SetFocus[/SIZE]
  
  [SIZE=2]    '  Disable controls in detail section.[/SIZE]
  [SIZE=2]    EnableControls frm, acDetail, False[/SIZE]
  
  [SIZE=2]    Exit Function[/SIZE]
  
  [SIZE=2]EditData_Err:[/SIZE]
  [SIZE=2]    MsgBox Err.Description[/SIZE]
  [SIZE=2]    Exit Function[/SIZE]
  [SIZE=2]
[/SIZE]

 

[vbaInet]

For your first problem, try this:

      DoCmd.ApplyFilter WhereCondition:="UserID = " & Forms("frmLimitEditUsers").SelectUser
      EnableControls Me, acDetail, True
      Me.UserId.enabled = False
      Me.Type.SetFocus

 

[vbaInet]

I didn't see what your combo1 and combo2 controls did in your code so I removed them, redundant. For your second problem, try this:

Function EditData() As Integer
      '  Code for Edit Data button on EnterOrEditUsers1 toolbar.
  
      Dim frm As Form
  
      On Error GoTo EditData_Err

      Set frm = Forms("frmEditUsers")
  
      '  Set AllowEdits property to True to indicate Edit mode.
      frm.AllowEdits = True
  
      '  Switch toolbars.
      DoCmd.ShowToolbar "EnterOrEditUsers1", acToolbarYes
      DoCmd.ShowToolbar "EnterOrEditUsers2", acToolbarNo
  
      '  Enable and move to first combo box.
      frm.SelectBusiness.enabled = True
      frm.SelectBusiness.SetFocus
  
      '  Disable controls in detail section.
      EnableControls frm, acDetail, False
  
Exit_EditData_Err:
    Exit Function
  
EditData_Err:
      MsgBox Err.Description
      Resume Exit_EditData_Err
    
End Function

I also added an extra line for your error handler. Let us know if that works for you.

 

[mcclunyboy]

hey, thanks for the replies.

Regarding the first problem, it worked a treat on the form I quoted. however a 2nd form not so much.

Existing code:

'DoCmd.ApplyFilter , "EmployeeNumber = Forms!frmLimitEditEmployees!SelectUser"
   'EnableControls Me, acDetail, True
   'Me!EmployeeNumber.Enabled = False
   'Me!Unit.SetFocus

New Code:

DoCmd.ApplyFilter WhereCondition:="EmployeeNumber = " & Forms("frmLimitEditEmployees").SelectUser
      EnableControls Me, acDetail, True
      Me.EmployeeNumber.Enabled = False
      Me.Firstname.SetFocus

Error states- missing operator


EDIT - regarding the 2nd solution, the method you posted is a much clearer solution, however it created the buttons but returned a Syntax error. Whilst playing around I went back to the original and got it working. We needed all 3 controls - each basically allows the user to narrow down their selection, IE - Business Unit, Department and User

Thanks again - I didn't know much about this capability but it is quite nice. i didn't create this database and itis the first time I have seen it used.

EDIT AGAIN - Any way to force the "add in" custom toolbar to the front?!

 

[mcclunyboy]

any thoughts?

 

[vbaInet]

Try hiding the active one before showing the new one.

Edit: Actually, what you should be doing is setting the form's menu/tool bar. Your command is setting the application's menu/tool bar for that current session.

 

[mcclunyboy]

ah that would be much better. considering i didn't know anything about these can you point me in the direction of an example so I can have a look (regarding how to add a button to the forms toolbar)

ill try hiding now.

 

[vbaInet]

Adding/removing menus items from a menu bar is not something you would want to worry your head about now If you still want to get stuck in, here's a good link:

http://www.ozgrid.com/VBA/custom-menus.htm

I was actually referring to changing the form's menu or tool bar. This is how you do it:

Me.Menubar = "NameOfMenuBar"
Me.Toolbar = "NameOfToolbar"

Depending on which one you want to change, and that's it. Try that instead

 

[mcclunyboy]

Ill forget about the menu bars just now - less important.

Regarding the 2 forms that were identical.

The first one (which searches for users with access to the system for admin purposes) worked once I had switched the code to your suggestion.

The second one (which searches for employees within a business section of my organisation) didn't work with the code you supplied(plus a few changes). After a little playing around I found that the original code actually worked but the problem was caused by the form filtering on load and not displaying any details. Instead I have disabled this and I am now requerying when the combo boxes are clicked.

Anyway good news they are working - I think originally the DB was an MS template which has been considerably modified.

 

[vbaInet]

Excellent! Glad you got it sorted.