OnClick event not working (1 Viewer)

Tekture · Jul 13, 2009

Code:

Private Sub EmpID_Click()
   Dim SQL As String
   SQL = "SELECT * FROM Employee WHERE (((Employee.EmployeeID)=" & Me.EmpID & "));"
   Me.Parent.sfEmp.Form.RecordSource = SQL
   Me.Parent.tabPublisher = 1
End Sub

This was the code we had before Office 2007. The purpose of this code is to allow a user to click on a search result in tab 3 and have the tab change to tab 1 while also selecting the result "ID". (if that makes sense)

This code did work prior to our Office 2007 switch. Is there something in 2007 that would make this code not work? If Office 2007 is not the problem, does anyone know why this code doesn't work. I've checked all the names and everything is spelled right. The problem is: when you click the result, it DOES take you to Tab1, but it does not select the record you clicked on, it just brings you to a new blank entry. And also, when i try to navigate the form by using the foward and back arrows, it shows the database as being empty, when it really isn't. I assume this is the result of the form being filtered?

I'm sure you'll probably need more detailed information, just let me know what you need to know and i'll repost it.

SOS · Jul 13, 2009

Have you set the database location to be a trusted location? In 2007 you need to do that in order for code to work.

RossWindows · Jul 14, 2009

SOS, would he also need to requery the form?

Tekture · Jul 22, 2009

I went in and added my location as a trusted source and Access no longer prompts me with a security warning when it opens. BUT, I'm still have the problem, which means it's most likely something wrong with the code.

Sigh...

Thanks SOS and Ross

I also requeried the DB and nothing. I did however discover that when clicking on the search results, all of them go back to the main screen (like it should) and all of them show up blank (like a new entry).... EXCEPT the very first one... Could the filter be stuck?

RuralGuy · Jul 22, 2009

I have duplicated your issue but do not have a solution as of yet.

RuralGuy · Jul 24, 2009

OK, I have a work around for Access 2007:

Code:

Private Sub EmpID_Click()
   Dim SQL As String
   SQL = "SELECT * FROM Employee WHERE (((Employee.EmployeeID)=" & Me.EmpID & "));"
   Me.Parent.sfEmp.Form.RecordSource = SQL
   [COLOR="Red"]Me.Parent.sfEmp.LinkMasterFields = ""
   Me.Parent.sfEmp.LinkChildFields = ""[/COLOR]
   Me.Parent.tabPublisher = [B][COLOR="Red"]2[/COLOR][/B]
End Sub

...and you will need to change the hrSearch form as well:

Code:

Private Sub JobID_Click()
   Dim SQL As String
   SQL = "SELECT * FROM qryJobs WHERE [JobID] = " & Me.JobID
   Me.Parent.sfJobs.Form.RecordSource = SQL
   [COLOR="Red"]Me.Parent.sfJobs.LinkMasterFields = ""
   Me.Parent.sfJobs.LinkChildFields = ""[/COLOR]
   Me.Parent.tabPublisher = 1
End Sub

The solution cam with the compliments of Ken Snell, another Access MVP.

RuralGuy · Jul 24, 2009

The key was to refresh the LinkMasterFields and LinkChildFields properties *after* changing the RecordSource of the SubForm. The OP's db was not using these properties so just puting "" in them worked. If they are being used you would probably have to fetch them first and then put them back.

Tekture · Jul 28, 2009

It works perfect! Thanks RG

RuralGuy · Jul 28, 2009

Evidently this issue has existed in Access as far back as ac2000. We both learned something here.

OnClick event not working (1 Viewer)

Tekture

Registered User.

SOS

Registered Lunatic

RossWindows

Que?

Tekture

Registered User.

RuralGuy

AWF VIP

RuralGuy

AWF VIP

RuralGuy

AWF VIP

Tekture

Registered User.

RuralGuy

AWF VIP

Similar threads

Users who are viewing this thread