Solved

Subform, Tab Control, DoCmd.GOTOPage

Posted on 2007-03-29
18
1,742 Views
Last Modified: 2013-11-28
I have a database the keeps track of animal licenses, people, microchip info, bite, impound, adoption, redemption, receiving, etc for Animal Control. Using Access 2003. Main form with tab controls linking all tables to the people table. Each subform has PeoID, PetID. When clicking from subform1 to subform2 I am trying to accomplish the PetID to fill in on the next subform along with PeoID, which is pretty much working. On this click I am also trying to do a "DoCmd.GOTOPage"

' Go To fMiscFees Form with PetID number
    stLinkCriteria = "[PetID]=" & Me![PetID]
    DoCmd.GoToPage 5, , , stLinkCriteria
It dies on this part and I can not figure out how to make it happen as I don't care what the left and right position it goes to on that tab (page).

Private Sub cmdFees_Click()
 On Error GoTo Err_cmdFees_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = tMiscFees
    Dim strInsSQL As String
   
' Make sure there is a city tag number in record
' If no record has been created, pop a message box and end sub
    If IsNull(Me.PetID.Value) = True Then
        MsgBox "Missing PetID Information!", vbCritical, "Missing Information!"
        Exit Sub
    Else
' Insert new record into tFees Table with value of PetID Number
        DoCmd.SetWarnings False
        strInsSQL = "INSERT INTO [tFees Table] (PetID) " & _
                    "VALUES (" & Me.PetID & ");"
        DoCmd.RunSQL strInsSQL
        DoCmd.SetWarnings True
    End If
   
' Go To fMiscFees Form with PetID number
    stLinkCriteria = "[PetID]=" & Me![PetID]
    DoCmd.GoToPage 5, , , stLinkCriteria

Exit_cmdFees_Click:
    Exit Sub

Err_cmdFees_Click:
    MsgBox Err.description
    Resume Exit_cmdFees_Click
   
End Sub
0
Comment
Question by:JamieBennett
  • 8
  • 7
  • 3
18 Comments
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
besides page number, right and down there are no other arguments for GoToPage.
so DoCmd.GoToPage 5  would be what you need to do.

Are you actually using page breaks on your form or are you just trying to move to another subform? If not GoToPage is not what you need for code, please let us know.

Steve
0
 

Author Comment

by:JamieBennett
Comment Utility
I have tabs with subforms on each tab. Is this what you are asking me?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
IF all of your forms are linked to the main, then the ID you're on with one form, would be the ID that you're on for the others correct?

So can't you refer to the control itself?

Me.TabCtl0.Value = 6   )starting index at 0, so page 5 is 6
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
<Is this what you are asking me?> Yes, do as Jeff suggests and refer to the control directly rather than GoToPage wich is used for something entirely different.

<DoCmd.GoToPage 5, , , stLinkCriteria>
What are you trying to accomplish with this code?

Steve
0
 

Author Comment

by:JamieBennett
Comment Utility
What I am trying to attempt to accomplish is: I have the following Tables: People, License, Adoption, Redemption, Receiving, Bite/VicisousAnimal, Legal, Comments/Info, MiscFees
I have a main form that has the People information at the top and tabs attached to this form, which I have subforms of each of the tables on the individual tabs.
My People table has a PeoID field, the rest of the tables also has the PeoId in their tables with their own unique ID like License has PetId, PeoId, etc., Adoption table has AdopId, PeoID, PetID, etc. What I am thinking in doing it this way is their can be many licenses, adoptions, redemptions, bite records belonging to one individual.  I hope I am explaining myself correctly.
In my form, my first tab is the License subform (continious form on each of these subforms) which I have a button for them to click on to go to the Misc Fees Tab (subform) and put the PetID in the new record for that person associated with their PeoID. If i do not have the subform directly in my main form the PetID will go into a new record, but I want or would like to have the subform directly in the main form just incase they want to browse someones records to make sure they are upto date on their licesnses, or if they have an animal that has been impounded. My button is dying when click on. I am not understanding how to make that part work.

Thanks in advance


0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
Should the fees associated with a specific pet?
0
 

Author Comment

by:JamieBennett
Comment Utility
Yes, Once they click it should have the PetID filled in on that tab with the FeeID, so there will be no typo on the record ID's.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Jamie, this is still a bit confusing. An I do apologise...I can kind of picture what you have going on if I close my eyes and cock my head to the left...but the terminology you're using is still confusing to me.

>>People information at the top and tabs attached to this form, which I have subforms of each of the tables on the individual tabs.

This sounds "normal" except where you use the word "attached". But I picture a main form, with people data above...and a tab control with multiple tabs below that. Each tab on your tab control has a subform representing your other tables.

>>My People table has a PeoID field, the rest of the tables also has the PeoId in their tables with their own unique ID like License has PetId, PeoId, etc.,

This also sounds "normal" and you're using the PeoIP as the foreigh key inside your other tables...so you can relate specific records back to the people table. This is all good so far.

>>In my form, my first tab is the License (subform) which I have a button for them to click on to go to the Misc Fees Tab (subform) and put the PetID in the new record for that person associated with their PeoID.

This part sounds ok...you're grabbing the ID and navigating to a new tab and opening a new record there and inserting the PetID...////IS this the code you initially pasted in the question above??////

>>If i do not have the subform directly in my main form the PetID will go into a new record, but I want or would like to have the subform directly in the main form just incase they want to browse someones records

it gets a little confusing here. Right now, you have the subform on the tab control, BUT, it's not working the way you want and you're thinking you need to put the subform on your main form instead?
Having a subform on a tab control is no different than having it on the main form. So either I'm reading it wrong, or there is something about main form/subform relations that you aren't able to get to work for you. But that's not clear.

>>My button is dying when click on. I am not understanding how to make that part work.

Buttons don't die. They may not perform, or appear to perform the action you want, but if they aren't popping an error, then something is happening that you would normally step through to troubleshoot. is this button code the code you pasted into the Q initially?

IS there any way you can upload this DB?
www.ee-stuff.com


0
 

Author Comment

by:JamieBennett
Comment Utility
jefftwilley:

I am in the process of trying to upload my file now. I do appoligize for not being able to explain what I am trying to do.  The name of the DB is "Expert Copy of DailyRevenue.zip" I stripped alot of the data and uneccessary data out of it to make the file size smaller as it was 81 mb.
Thank you for look at this, my main page is the fPeople. I do not really have it linked to my switchboard yet the way I want it to be.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
ok, let us know when you're able to get it uploaded. Will be fun to look at.
J
0
 

Author Comment

by:JamieBennett
Comment Utility
I just got through uploading now.
Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/3041-Expert-Copy-of-DailyRevenue.zip
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
here's a mo to your button's code. I had to change the table name in the insert statement also.

Private Sub cmdFees_Click()
 On Error GoTo Err_cmdFees_Click

    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = tMiscFees
    Dim strInsSQL As String
   
' Make sure there is a city tag number in record
' If no record has been created, pop a message box and end sub
    If IsNull(Me.PetID.Value) = True Then
        MsgBox "Missing PetID Information!", vbCritical, "Missing Information!"
        Exit Sub
    Else
' Insert new record into tFees Table with value of PetID Number
        DoCmd.SetWarnings False
        strInsSQL = "INSERT INTO [tFees] (PetID) " & _
                    "VALUES (" & Me.PetID & ");"
        DoCmd.RunSQL strInsSQL
        DoCmd.SetWarnings True
    End If
   
' Go To fMiscFees Form with PetID number
'    stLinkCriteria = "[PetID]=" & Me![PetID]
'    DoCmd.GoToPage 5, , , stLinkCriteria


'Add mod to move to proper tab control and insert the PetID
'===============================================================================================
Me.Parent.TabCtl34.Value = 4
Forms!fPeople!fMiscFees.Form.PetID = Me.PetID
'===============================================================================================

Exit_cmdFees_Click:
    Exit Sub

Err_cmdFees_Click:
    MsgBox Err.description
    Resume Exit_cmdFees_Click
   
End Sub


See if that's the behavior you're looking for
J
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
I've played with this some more..and the insert statement can be bypassed all together so that your code would look like so

Private Sub cmdFees_Click()
 On Error GoTo Err_cmdFees_Click
'Add mod to move to proper tab control and insert the PetID
'===============================================================================================
Me.Parent.txtPetID = Me.PetID
Me.Parent.TabCtl34.Value = 4
Forms!fPeople!fMiscFees.Form.PetID = Me.PetID
'===============================================================================================
Exit_cmdFees_Click:
    Exit Sub

Err_cmdFees_Click:
    MsgBox Err.description
    Resume Exit_cmdFees_Click
End Sub


This moves you to the tab, and inserts the PetID into the form automatically. Check it out.
J
0
 

Author Comment

by:JamieBennett
Comment Utility
Thank you, I was just looking at your other suggestion when this came in. I will play(work) with this, this weekend and get back to you on Monday.
Again, thank you for looking at this. I'm not all that good as you can tell with the programming part.
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 total points
Comment Utility
Just as a note....I was trying a couple of different things...an the first line here

Me.Parent.txtPetID = Me.PetID
Me.Parent.TabCtl34.Value = 4
Forms!fPeople!fMiscFees.Form.PetID = Me.PetID

was me setting a hidden field on the main form...and is NOT needed to do the job.

so the code is even smaller

Me.Parent.TabCtl34.Value = 4
Forms!fPeople!fMiscFees.Form.PetID = Me.PetID

Enjoy your weekend!
J
0
 

Author Comment

by:JamieBennett
Comment Utility
OK, I couldn't wait for the weekend. This is exactly what I was trying to accomplish. Thank you, Thank you.

Jamie B
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
you're very welcome.
Sorry to take you to all the trouble of posting and all...but it helped.
J
0
 

Author Comment

by:JamieBennett
Comment Utility
LOL

Well sometimes I don't explain myself with what I am trying to say.

Jamie B
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now