open form from other form access 2003 1

[assets]

A few years a go I developed a database and this was working. Have tried npw and not open correct record.
The form has a command burron with the following:
/

Private Sub Command11492_Click()
On Error GoTo Err_Command11492_Click
If IsNull(Me![AssetID]) Then
    MsgBox "Enter Asset Information before opening Employees form."
    Else
    
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "employees"
    
    stLinkCriteria = "[EmployeeID]=" & Me![EmployeeID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
Exit_Command11492_Click:
    Exit Sub

Err_Command11492_Click:
    MsgBox Err.Description
    Resume Exit_Command11492_Click
End Sub
/[code]

It checks the EmployeeID in the assets table with the employee tabe and open the correct record in employee table . But now in open the first record in employee only.  Can anyone explain why it now not working.

Never give up never give in.

There are no short cuts to anything worth doing   :-)

 

[dhookom]

You need to get rid of this code since it is negating your OpenForm code.
Employees form

Private Sub Form_Open(Cancel As Integer)
    'MsgBox DivisionIDfilter
    Me.Form.Filter = "DivisionID=" & DivisionIDfilter
    Me.FilterOn = True
    If DivisionIDfilter = 0 Then
        Me.FilterOn = False
    End If
    Me.Refresh
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[assets]

Thank you it show the correct person now. The only problem is that you can see all division information that was filtered out. Is there a way of using the filter to restrict this information people can see when they open ( it ised dlookup for the person on login and lets them see there divisionID only or should.

Again thank you for all your assistance

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

You can set the WHERE CONDITION to

DoCmd.OpenForm "Employees", , , "[EmployeeID]= " & Me![EmployeeID] & " AND DivisionID=" & DivisionIDfilter

Duane
Hook'D on Access
MS Access MVP

 

[assets]

Yes duane it only open the one employee record which is good. Maybe I did not explain the problem correctly. The filtering that I had to remove from assets and employee on open form filtered the DivisionID of the person logged in (their division). As I working with my old bata base the information is out of date so ok for testing. But in the case when I started the filtering it contained assets imformation for counter tertorism department that needed to be blocked by other user. But if they logged in as superuser they could see all division's to find equipment when rerquired. Is there a way of still having the filtering on opening and still have your code? Also as a side issue the text box =[filter] give DivisionID=0 the text box for DivisionID shows 1 which is correct it still open the correct record as per your instruction in the last post

Again you help is realy appreciated.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

I don't understand your requirements.

Duane
Hook'D on Access
MS Access MVP

 

[assets]

Sorry Duane.

The data base has a login form with code below:
Dim Pass As Variant, vUser As Variant
If IsNull(Me.Text1) Or IsNull(Me.Text3) Then
MsgBox "Please Enter Your User Id And Password", vbCritical + vbOKOnly, "employees"
Exit Sub
End If
Pass = DLookup("[Password]", "employees", "[login]='" & Me.Text1 & "'")
DivisionIDfilter = DLookup("[DivisionID]", "employees", "[login]='" & Me.Text1 & "'")
If Pass <> Me.Text3 Or IsNull(Pass) Then
MsgBox "Incorrect User Id or Password. Please try again." & Chr$(10) & "Remember, your password is case sensitive.", vbCritical + vbOKOnly, "employees"

Exit Sub

The assets and employee onopen code before hsd to removed
Private Sub Form_Open(Cancel As Integer)
'MsgBox DivisionIDfilter
Me.Form.Filter = "DivisionID=" & DivisionIDfilter
Me.FilterOn = True
If DivisionIDfilter = 0 Then
Me.FilterOn = False
End If
Me.Refresh
End Sub

This filtered the record to only show the ones for the the divison, that the person was from (assets and employees). But to get the employee form to open at the correct record (your code) the filter needed to be removed from both forms as per your post. But is a way of having the filtering back and WHERE condition you have kindly provided that open correctly . Again thank you for your assistance.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

I would use the divisionID to modify the SQL of a saved query. This saved query is then the record source of your form.

Duane
Hook'D on Access
MS Access MVP

 

[assets]

Thanks I give that a go over the next few day

Never give up never give in.

There are no short cuts to anything worth doing

 

[assets]

One Problem solved anothe came up.

 Private Sub TagTest_Click()
On Error GoTo Err_TagTest_Click
    If IsNull(Me![AssetID]) Then
        MsgBox "Enter asset information before opening TagTest form."
    Else
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.OpenForm "TagTest"
    End If
Exit_TagTest_Click:
    Exit Sub
Err_TagTest_Click:
    MsgBox Err.Tagtest
    Resume Exit_TagTest_Click
End Sub
Private Sub Maintenance_Click()
On Error GoTo Err_Maintenance_Click
    If IsNull(Me![AssetID]) Then
        MsgBox "Enter asset information before opening maintenance form."
    Else
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.OpenForm "Maintenance"
    End If
Exit_Maintenance_Click:
    Exit Sub
Err_Maintenance_Click:
    MsgBox Err.Description
    Resume Exit_Maintenance_Click
End Sub

The on click for tagtest has stopped working doed nothing, and the one for maintenance give a "saverecord" isn't available now error. when you click on the button

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

What are the results of you debugging?

Duane
Hook'D on Access
MS Access MVP

 

[assets]

Sorry for not getting back. I have bigger problem I opened the database in 2007 in error and now it does noes not work in either 2003 or 2007 NO BACKUP.

Never give up never give in.

There are no short cuts to anything worth doing