Solved

Subform, Tab Control, DoCmd.GOTOPage

Posted on 2007-03-29
18
1,792 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
18 Comments
 
LVL 39

Expert Comment

by:stevbe
ID: 18818878
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
ID: 18819024
I have tabs with subforms on each tab. Is this what you are asking me?
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 18820792
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 39

Expert Comment

by:stevbe
ID: 18823852
<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
ID: 18824012
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
ID: 18824112
Should the fees associated with a specific pet?
0
 

Author Comment

by:JamieBennett
ID: 18824130
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
ID: 18824764
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
ID: 18825391
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
 
LVL 34

Expert Comment

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

Author Comment

by:JamieBennett
ID: 18825659
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
ID: 18825899
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
ID: 18826181
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
ID: 18826201
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
ID: 18826238
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
ID: 18826451
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
ID: 18826478
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
ID: 18826494
LOL

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

Jamie B
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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