INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...What a great service! This is the best site I've ever seen!!! It totally restores my faith in humanity when people take time out to help other people..."
Geography
Where in the world do Tek-Tips members come from?
|
open form from other form access 2003
|
|
|
assets (TechnicalUser) |
17 Jun 12 23:21 |
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:
/[code]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 (Programmer) |
18 Jun 12 14:35 |
|
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
CODE --> stLinkCriteria = "[EmployeeID]='" & Me![EmployeeID] & "'"
Linq ;0)> The Missinglinq
Richmond, Virginia
The Devil's in the Details! |
|
|
assets (TechnicalUser) |
4 Jul 12 23:50 |
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 |
|
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 (TechnicalUser) |
26 Jul 12 1:34 |
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 (TechnicalUser) |
30 Jul 12 14:37 |
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 (TechnicalUser) |
20 Aug 12 1:02 |
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 (TechnicalUser) |
12 Oct 12 0:53 |
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 |
|
You're missing a Single Quotation Mark in you ' WHERE' clause in your OpenForm command line, it should be
CODE --> DoCmd.OpenForm "Employees", , , "[EmployeeID]= '" & 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 (Programmer) |
12 Oct 12 8:26 |
Actually assets stated the field was numeric so the code should be:
CODEDoCmd.OpenForm "Employees", , , "[EmployeeID]= " & Me![EmployeeID] Duane
Hook'D on Access
MS Access MVP |
|
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 (Programmer) |
12 Oct 12 11:22 |
As we ignore some things that are clearly lack of reviewing before posting
Quote (Assets)When I clock on the button and run code abpve get
we lose sight of the actual coding errors. Duane
Hook'D on Access
MS Access MVP |
|
|
assets (TechnicalUser) |
14 Oct 12 19:22 |
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 (Programmer) |
14 Oct 12 19:38 |
Place a 2 inch text box on the form "Employees" with a control source of:
CODE --> ControlSource=[Filter]
What do you see in the text box? Duane
Hook'D on Access
MS Access MVP |
|
|
assets (TechnicalUser) |
15 Oct 12 1:24 |
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 (Programmer) |
15 Oct 12 17:07 |
|
|
assets (TechnicalUser) |
15 Oct 12 18:53 |
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 (Programmer) |
15 Oct 12 19:14 |
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 (TechnicalUser) |
15 Oct 12 23:39 |
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 |
|
|
dhookom (Programmer) |
16 Oct 12 0:49 |
You need to get rid of this code since it is negating your OpenForm code.
Employees form
CODE --> vbaPrivate 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 (TechnicalUser) |
16 Oct 12 1:57 |
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 (Programmer) |
16 Oct 12 10:29 |
You can set the WHERE CONDITION to
CODEDoCmd.OpenForm "Employees", , , "[EmployeeID]= " & Me![EmployeeID] & " AND DivisionID=" & DivisionIDfilter Duane
Hook'D on Access
MS Access MVP |
|
|
assets (TechnicalUser) |
16 Oct 12 19:29 |
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 (Programmer) |
17 Oct 12 0:36 |
|
|
assets (TechnicalUser) |
17 Oct 12 1:35 |
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 (Programmer) |
17 Oct 12 3:19 |
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 (TechnicalUser) |
17 Oct 12 18:17 |
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 (TechnicalUser) |
28 Nov 12 0:24 |
One Problem solved anothe came up.
CODEPrivate 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 (Programmer) |
28 Nov 12 0:34 |
|
|
assets (TechnicalUser) |
13 Dec 12 19:28 |
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 |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One