MS Access Tab switching (2 Viewers)

[Tekture]

Thanks RG, that gets me very close to what i'm looking for, but there is still something that Access doesn't like.

I had to modify some names (and may have done so incorrectly) so here is what i have now:

Private Sub JobID_Click()
Dim SQL As String
SQL = "SELECT * FROM tblJobs WHERE JobID = '" & Me.JobID & "'"
Me.Parent.sfJobs.Form.RecordSource = SQL
Me.Parent.tabPublisher = 1
End Sub

When clicking on JobID, it prompts me for the JobID which makes me think that Access can not read the individual control. The JobID that i am clicking is in the details section and therefor is repeated several times down the subform, one JobID for each search result.

It DOES move me to the correct tab, but it's not filtering or bringing up that specific "JobID" that i clicked on (or typed in).

Edit: After reading back through the posts for some answers, i seen that you suggest i try Link Master/Link Child fields. Do these need to be enabled for this to work?

Edit#2: Now i don't even get the prompt, not sure what i did now. When i click "JobID" i am given error 2001 and the debugger highlights this code: Me.Parent.sfJobs.Form.RecordSource = SQL

 

[RuralGuy]

OK, try this one.

Private Sub JobID_Click()
   Dim SQL As String
   SQL = "SELECT * FROM tblJobs WHERE JobID = '" & [JobID] & "'"
   Me.Parent.sfJobs.Form.RecordSource = SQL
   Me.Parent.tabPublisher = 1
End Sub

 

[Tekture]

same error code and same line is highlighted.

 

[RuralGuy]

What is the name of the MainForm on which these SubForms are displayed?

 

[Tekture]

the name of the main form is called "hrMain"

 

[RuralGuy]

Try:

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

 

[Tekture]

Nope, The debugger is still highlighting this line:
Forms.hrMain.sfJobs.Form.RecordSource = SQL
and I still get error 2001.

 

[RuralGuy]

Something else is going on. Error 2001 usually indicates that two forms are trying to process the same table at the same time. It may be time to post your db so someone here can assist.

 

[Tekture]

Well, that does seem to be an accurate statement. Both the "hrJobs" subform and the "hrSearch" subform are connected to the same table "tblJobs".

I will attach my DBM when i get back home. Thanks for the help RG.

 

[RuralGuy]

It is certainly possible to alter the same table from two different forms but each form needs to be based on a query and not the table directly.

 

[Simon_MT]

Tab Controls I find that if you put on the Form something like [Page_No] and on the Tab Control On Change

    With CodeContextObject
        .[Page_No] = .[TabCtl0]
    End With

Now on the form you can stay:

  With CodeContextObject
      If .[Page_No] = 0 Then
         Docmd
      elseif .[Page_No] = 1 Then
         Docmd  etc
      End if
   End With

Simon

 

[Tekture]

My zipped DB size is beyond limit, so i've got a temporary account at a file storage website. The DB is located at:

http://www.mediafire.com/?sharekey=730aa18480ec1f2a111096d429abd360e04e75f6e8ebb871

The basic problem is this:
The tab labeled *Search Jobs* is a search page for the [Jobs] table.
I want to be able to click the search result (the Job ID - left side, blue font)
And be switched to the *Job Announcement* tab with that job open.

Also, can a scroll bar be added to the search results?

 

[RuralGuy]

You were almost there! In the hrSearch form JobID click event you had:
SQL = "SELECT * FROM Jobs WHERE JobID = '" & Me.JobID & "'"
...when JobID is a LongInteger numerical field so the line should read:
SQL = "SELECT * FROM Jobs WHERE JobID = " & Me.JobID

That's it!

 

[Tekture]

Wow, are you serious!? That is just crazy....

Thanks a TON for your help RG and everyone else, next time instead of wasting 30 posts, ill just upload the DB at first. Ha!

Well, the tab switch works. When i click the ID, it does not put it on the right job. It does for the first ID (311) but not for any others. Any insight?

 

[RuralGuy]

You are absolutely correct and it makes no sense to me yet. I'll keep playing.

 

[RuralGuy]

Finally!!!! I created a query for the Jobs table that simply returned *all* of the fields. I then replaced all of the Form RecordSources that were using the table directly with the query instead. Problem resolved.

 

[RuralGuy]

Your db is bloated because Access does not store pictures very well. I deleted the beautiful seal in the hrHelp form and you can see it shrank considerably.

 

[Tekture]

Ohh wow, that's good to know about access picture storing. I've seen sample databases that appear to use pictures, is there a work around? Also, that image was huge, yet I scaled it down inside access, maybe I just need to use a smaller image file.

I see that you had to alter the JobID OnClick event to point to that new location as well. I'm just trying to think through the changes I need to make on the more advanced version i have at work.

Thanks a lot for your time, help and understanding!

 

[RuralGuy]

I see that you had to alter the JobID OnClick event to point to that new location as well. I'm just trying to think through the changes I need to make on the more advanced version i have at work.

I only changed the SQL string in that event just to see if that was the problem. I believe your SQL string would work as well.

As for displaying images without bloating the db, MVP Larry Linson has a sample db with three approaches to the issue that do not bloat the db. They all store the image outside of the db.

 

[Simon_MT]

With images, I use a paid for ActiveX application called dbpix. The cost was irrelevant as I had to handle lots of images, last count about 15,000. I have used dbpix from Access 1997.

Access 2007 has a picture and not longer OLE. It is a vast improvement being able to handle images in their native format.

Simon