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]

Check out faq705-7148.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

Is EmployeeID in your current app defined as a Number Datatype or as a Text Datatype? If it's Text, and all-digit Fields can be defined as Text, the correct syntax would have to be changed to

stLinkCriteria = "[EmployeeID]='" & Me![EmployeeID] & "'"

Linq ;0)>

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[assets]

Thank you Missinglin,
Sorry for delay but rroblem with finance at endo f financial year.

No it a number and not a text tryed you idea but still does not work. the EmployeeID in the assets table uses the data in employee table so number should be the same. But it opems the employee form at the wrong employee .

thanks for you idea. Hope you have another solution.

Thanks

Never give up never give in.

There are no short cuts to anything worth doing

 

[missinglinq]

Opening at the first Record suggests that it's not finding a matching EmployeeID. Other than double-checking that everything is spelled correctly, in both Forms, and that matching EmployeeIDs do, in fact exist, don't know what else to tell you.

The only other odd thing I see is that you're checking for a Null in the AssetID Field before opening the Employees Form; normally you'd check that the EmployeeID Field held a Value before trying to link on it.

Linq ;0)>

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[assets]

Thans for you help I been off sick and have not look at send reply to now it is strange because it was working i do not know what the issue is now EmployeeID in the assets table is populated with the dat from the employee table but fpr some reason the on click open the wrong record in the employee's form(not matching fields).

Again I appreciate you input and appoligise in delay in replying.


Never give up never give in.

There are no short cuts to anything worth doing

 

[jkl0]

Check the properties of the employee form and make sure that it does not have a filter saved in it also "Allow Filters" = Yes and "Filter on Load" = No.

 

[assets]

Sorry did not receive notification of reply so sorry for delay in getting back

I think you are on to something whren I look at allow filter it is set to yes.
Do NOT have "filter on Load" setting in properties. But I noticed filter has value saved in it when looking at it in design view. So need to work out how to clear on load like your suggestion.

Big Thank You

Never give up never give in.

There are no short cuts to anything worth doing

 

[assets]

Hi still no joy in getting it working to open form at correct record still only opens first record only in employees table

Private Sub Command11492_Click()
On Error GoTo Err_Command11492_Click
If IsNull(Me![AssetID]) Then
MsgBox "Enter Asset Information before opening Employees form."
Else
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm "Employees", , , "[EmployeeID]= " & Me![EmployeeID] & "'"
End If
Exit_Command11492_Click:
Exit Sub
Err_Command11492_Click:
MsgBox Err.Description
Resume Exit_Command11492_Click
End Sub

Table 1 Assets has EmployeeID field (Number) with row source of:::::::: SELECT [EmployeeID], [LastName] & ", " & [FirstName] FROM Employees ORDER BY [LastName] & ", " & [FirstName];

Table 2 Employee has EmployeeID (autonumber)

When I clock on the button and run code abpve get "Syntax error in string expression "[EmployeeID]=71 Where 71 is the current employee in the assets form, if selct officer with number of 221 then syntax is 221


Any further ideas appreciated




Never give up never give in.

There are no short cuts to anything worth doing

 

[missinglinq]

You're missing a Single Quotation Mark in you 'WHERE' clause in your OpenForm command line, it should be

DoCmd.OpenForm "Employees", , , "[EmployeeID]= [b][COLOR=#CC0000]'[/color][/b]" & Me![EmployeeID] & "'"

Note the Single Quote between the Equal Sign and the first Double Quote. Like parens and brackets, quotes and double quotes must always appear in pairs!

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[dhookom]

Actually assets stated the field was numeric so the code should be:

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

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

You're correct, of course! Didn't bother re-reading this somewhat dated thread (my mistake) and just looked at the last post and its code!

Thanks for the catch!

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[dhookom]

As we ignore some things that are clearly lack of reviewing before posting

When I cl[red]o[/red]ck on the button and run code ab[red]p[/red]ve get

we lose sight of the actual coding errors.

Duane
Hook'D on Access
MS Access MVP

 

[assets]

I sorry But I gave up for a while working on other area thats why it an old thread. I appreciate everyone help.
BUT it still does not work for some reason. I have tried Duane code as it was a number but only opening first records in employees. Thanks Missingling Yes I started with you code but get open form action cancelled. I can not understand why it open the incorrect record in employees.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

Place a 2 inch text box on the form "Employees" with a control source of:

=[Filter]

What do you see in the text box?

Duane
Hook'D on Access
MS Access MVP

 

[assets]

I have done this and the text box gives me Division ID=0. If I look at the form in desibn view filter [employeeID]=227 the current employee in assets. So the filter for employee show the correct employee but does not display it.

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

Is the form already open?
Is there any code that changes the filter?

Duane
Hook'D on Access
MS Access MVP

 

[assets]

There is a login screen that takes the windoe log in and log user for password.

the on open of assets has

Private Sub Form_Open(Cancel As Integer)

If filterOFF = True Then
Exit Sub
Else
Me.Form.Filter = "DivisionID=" & DivisionIDfilter
Me.FilterOn = True
End If
If DivisionIDfilter = 0 Then
Me.FilterOn = False
End If
Me.Refresh

End Sub


the on close has
filterOff = False

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

This to only let people from the section see the records can not see other employee records for other sections.
on Load
Private Sub Form_Load()
If Me.OpenArgs = "GotoNew" And Not IsNull(Me![EmployeeID]) Then
DoCmd.DoMenuItem acFormBar, 3, 0, , acMenuVer70
End If
End Sub


If you require morer information please ask

Never give up never give in.

There are no short cuts to anything worth doing

 

[dhookom]

So, you open the form Employees with a filter/where condition that references the EmployeeID and as the form opens you replace the filter/where condition with "DivisionID=" & DivisionIDfilter.

That's why it doesn't work as expected. It is doing what you (or your code) is telling it to do.

Duane
Hook'D on Access
MS Access MVP

 

[assets]

Duane thankyou

If you mean
DoCmd.OpenForm "Employees", , , "DivisionID=" & DivisionIDfilter

Then it still only open the first record in employees, The text box in each show divisionID=1 or =0 (user admin no division, does not filter records)depending how I login.

So where am I going wrong. The Division filter only blocks record from people seen one should not see. so how do I match employeeID to open correctly.

Never give up never give in.

There are no short cuts to anything worth doing