Results 1 to 11 of 11
  1. #1
    melhado
    Guest

    Find subform record and show main form record also (2K SR1)

    This involves a main form with 2 subforms, one of the subforms has a subform linked to it on a field not existant in the main. It all works fine and fits the process it is intended to document.

    I now have a need to be able to find a record by its subform ID, then when it comes up, it needs to pull up its parent record as well. The linking fields are the primary key in the main's table, and foreign key in the sub's table, but the primary key for the sub is completely different ID.

    I have code on the main form that finds the parent record using a 3 part ID, which brings up the related records in the subform. That pretty much works the way I want it to. Now I am trying to figure out how to use the sub's ID to find that record and bring up its related parent -- sort of the reverse of what the existing button does? I tried doing a find on the field that serves as the unique ID to see if it would work, but found that it can only find what is in the recordset related to the main form -- as I expected.

    The button to activate this new code is on the subform. I assume I need to set 2 different recordsets, but since the button is on the subform, is that the Me! object and the Me!Parent can be used to define the main form recordset? Or am I flying in the wrong direction here. I am not sure of how to set this up.

    I have an idea of how I would do the search code for finding the main record and bringing the subs up, but not sure how to get the sub to link back to the main. Can someone give me an idea of how to do this?

    Thanks for the feedback

    'dave

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find subform record and show main form record also (2K SR1)

    The whole relationship among parent and child forms makes it a one-way street ... you have to have a parent record to see the child records and you can ONLY see child records for that parent record. If your subform is linked to the main form on the parent form's primary key, how would you expect to see subform records for a different parent record, or am I misunderstanding you?
    Charlotte

  3. #3
    melhado
    Guest

    Re: Find subform record and show main form record also (2K SR1)

    <P ID="edit" class=small>Edited by melhado on 30-Aug-01 11:21.</P><font color=red>Below is the sordid details of the form/subforms, etc. I have since <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=69464&page=& view=&sb=&o=&vc=1#Post69464> posted attempted code </A> to try and make this work.</font color=red>

    You are not misunderstanding, I knew that it is one way between the parent and child -- that is why when I went to do a find on the subform's primary key, it would not find it, it was in a subset of a different parent. I only did that to try and figure how I could make it work reversed. I need to be able to search the subform's underlying table for an ID and then have it come up with the parent and all related subforms, without changing the subform record I am calling for.

    I am thinking to open a DAO recordset based on the subform's underlying table, take the user input as to the ID, then do a findfirst to grab it. Where I go next -- pick up the parent and other related subforms' data in the same way as if I called up that parent record by using its 3-part ID -- is where I am searching for ideas.

    Let me try and explain the what is going on here, to give an idea of what is going on. It may be a bit lengthy and detailed, but the database, zipped is too large to post. This is a manufacturing process that involves many processes, each of which has different relationships to each other, but are related...sort of.

    We start with the main form -- call it frmL60Tin -- which serves as the "container" which holds the other subforms. At the point I am speaking of, only static data is presented, and the command button to call the parent piece is on here.

    Next, is a subform -- sfCleanTin -- also static, which has a many to one with frmL60Tin (many cleanings can happen to the one piece coming from Line 60). That concludes the easy part. This data is presented at the next station, which is where I want to do the code.

    Next subform, sfTin has a many to one relation with the frmL60Tin parent, linked on the 3-part ID. Not only can there be many pieces created at this station from each, but it takes on a whole new identity, thus the need to be able to find it by using this new ID. The best part is that it is a single field.

    But wait, there is more! sfTin has a subform -- sfCulo -- which has a many to one with sfTin , and is linked on its unique key (each test assigned its own number to assist the data collection application to find the blank record).

    In summary, frmL60Tin has one piece, which can have many cleanings, represented by sfCleanTin, and many pieces are created at the station represented by sfTin from the one piece represented by frmL60Tin. Each of those can have many tests, represented by sfCulo.

    My challenge is to push a button on sfTin, enter that singfle ID, have the record come up, also showing its parent record, and the other subforms' relqted records.

    So after I declare and open the recordset for sfTin's underlying table, so I have access to all the records, and not just the ones in the parent subset, and then find it, how do I re-synch that to the parent, causing the parent to show the other related subform records, but not move the sfTin's pointer?

    Any ideas are welcome.

    Thanks for the feedback

    'dave

  4. #4
    melhado
    Guest

    Re: Find subform record and show main form record also (2K SR1)

    I updated my <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=68938&Search =true&Forum=acc&Words=68938&Match=MessNum&Searchpa ge=0&Limit=25&Old=allposts&Main=68552> other post </A> describing this in detail. In summary, I am attempting to call a subform record, not necessarily in the same subset as the parent, and when retrieved, it will also show the proper parent and subforms.

    Since then I have started working on a few ideas -- none of which is doing anything -- not generating messages, nor moving to the record I am asking it to. It is giving me the input box and confirmation messages, however. The code I have worked out is below. I am calling the code from a button on the subform I am searching for the record.

    My thinking is that if I can get this code idea to go to the subform record I am asking for, I can then just requery the parent form, and all should link back up. It is important for the operator to see the other data to make certain decisions.

    What am I overlooking?

    <font color=blue>
    Option Compare Database
    Option Explicit
    ------------------------------------------------------
    Private Sub cmdFindRun_Click()

    <font color=448800>'asks operator for run number
    'then attempts to find it and
    'call its parent form and suboordinate
    'related records</font color=448800>

    On Error GoTo Err_cmdFindRun_Click

    Dim db As DAO.Database
    Dim rsTin As DAO.Recordset
    Dim lngFirst As Long
    Dim lngLast As Long
    Dim strMessage As String, strConfirm As String
    Dim strSeek As String, strMsg As String
    Dim strSearch As String
    Dim varBookmark As Variant

    Set db = CurrentDb
    Set rsTin = _
    db.OpenRecordset("tblTin", dbOpenDynaset)

    With rsTin

    <font color=448800>' Get the lowest and highest Run Numbers.</font color=448800>
    .MoveLast
    lngLast = !Run
    .MoveFirst
    lngFirst = !Run

    <font color=448800>' Display current record's
    ' Run # and ask user
    ' for desired Run #.</font color=448800>
    strMessage = "You are at Run Number: " & !Run & vbCr & vbCr & _
    "Enter a Run Number between " & lngFirst & _
    " and " & lngLast & "."
    strSeek = Trim(InputBox(strMessage))

    If strSeek = "" Then Exit Sub

    <font color=448800>'store input to variable</font color=448800>
    strSearch = "[Run] =" & strSeek & ""

    <font color=448800>' Store current bookmark to return if search fails.</font color=448800>
    varBookmark = .Bookmark

    <font color=448800>'Feed back message box to confirm entry</font color=448800>
    strConfirm = "The Run Number you entered is " & strSeek & "." & vbCrLf & vbCrLf _
    & " Check your paperwork." & vbCrLf _
    & " If incorrect, Cancel and retry."

    If MsgBox(strConfirm, 289, "CONFIRM REQUEST") = vbYes Then

    <font color=448800>'find the record</font color=448800>
    .FindFirst strSearch

    End If <font color=448800>'end of confirmation message
    ' Return to the current record if the search fails.</font color=448800>
    If Not .NoMatch Then
    .Bookmark = varBookmark
    Else

    <font color=448800>' Give message that record not found,
    'try again or contact Supervisor</font color=448800>
    strMsg = "Please check the RUN NUMBER" & Chr(13)
    strMsg = strMsg & " and Try Again." & vbCrLf & vbCrLf _
    & "If Repeat Effort Fails," & Chr(13) _
    & "Contact Supervisor. Thank You."

    MsgBox strMsg, 16, "!!NO SUCH RECORD EXISTS!!"

    End If

    .Close
    End With

    Set rsTin = Nothing

    Exit_cmdFindRun_Click:
    Exit Sub

    Err_cmdFindRun_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindRun_Click

    End Sub
    </font color=blue>
    Thank you for any guidance.

    'dave

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Find subform record and show main form record also (2K SR1)

    You may be mixing the terms subform and child table, because what you are asking for can't be done, but what I believe you want can be.

    A linked subform only contains a portion of all records in the table (or query) that is it's recordsource. The entire table is filtered by the linking fields. So, if the mainform is based on Customers, and the subform is based on Invoices, the subform recordset only contains invoices for that one customer. So, how can you look for a invoice from another customer?

    However, if you want to search the Invoice table for a particular invoice, you can figure out which customer it belongs to, and then display that customer and move your focus in the subform to that Invoice.

    Let me know what it is you are trying to do.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    melhado
    Guest

    Re: Find subform record and show main form record also (2K SR1)

    <hr><font color=red>You may be mixing the terms subform and child table, because what you are asking for can't be done, but what I believe you want can be.</font color=red><hr>
    OK, in this case, the subform is also a separate table, just linked on common fields. Do I need to create the underlying table of the subform as a child table to make this work?

    Using your analogy of the customer being the mainform and the invoices being the sub, how can I initiate a search from the form for an invoice to see which customer owns it, and at the same time another subform that documents all the sales pitches, tied to the customer, and another subform documenting the items on the invoice. I need to call it up by the invoice ID. How do you suggest I do this if not by opening a recordset based on the table (not filtered, I think) and searching it?

    The recordset I open in code is based on the table, not my form, nor is there any reference to the form -- I need to locate the subform record by its ID, which wouldn then theoretically bring up the related records? If I cannot do this by the traditional find routine, how do you suggest I accomplish this?

    The piece of code that checks for high and low ID # does return the low and high of the entire table, indicating to me, anyway, that it is seeing the unfiltered recordset. Here is the snippet:

    <font color=448800> ' Get the lowest and highest Run Numbers.</font color=448800>
    <font color=blue> .MoveLast
    lngLast = !Run
    .MoveFirst
    lngFirst = !Run

    <font color=448800> ' Display current record information and ask user
    ' for ID number.</font color=448800>
    strMessage = "You are at Run Number: " & !Run & vbCr & vbCr & _
    "Enter a Run Number between " & lngFirst & _
    " and " & lngLast & "."
    strSeek = Trim(InputBox(strMessage))

    If strSeek = "" Then Exit Sub</font color=blue>

    <hr><font color=red>A linked subform only contains a portion of all records...entire table is filtered by the linking fields. So, if the mainform is based on Customers, and the subform is based on Invoices, subform...contains invoices for that one customer. </font color=red><hr>
    I realize that the subform is going to be filtered as part of the form, but is the same true for the entire underlying table? Is there any way to get around it? Would calling the code from a button on the main form make a difference?

    <hr><font color=red>However, if you want to search the Invoice table for a particular invoice, you can figure out which customer it belongs to, and then display that customer and move your focus in the subform to that Invoice.</font color=red><hr>
    That is close to what I want to do -- I want to look up the "invoice" by it's ID and then have the related "customer", "pitch", and "detail" subforms to come up with it. Moving focus to the subform is ideal, but that I have learned from you already. What I am stuck on is doing the search on the subform ID. It needs to be seamless -- do the search on the ID and everything related comes up.

    Any help, assistance, feedback and suggestions would be helpful.

    'dave

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Find subform record and show main form record also (2K SR1)

    >>I realize that the subform is going to be filtered as part of the form, but is the same true for the entire underlying table? Is there any way to get around it? Would calling the code from a button on the main form make a difference?<<

    It is this statement that still leads me to believe you don't have a handle on the relationship between forms and subforms.

    A subform can have a table as its recordsource. However, if it is linked to a mainform, then the only records you "see" are the ones associated with the record on the mainform. In my example from prior message, you will only "see" invoices associated with the customer on the mainform.

    If you want to find an invoice not shown on the subform, you will have to open a recordset in code based on the invoice table. Then use your .FindFirst method to find the invoice you want. This will give you a customer. You can then display that customer on the mainform, which will automatically display all it's invoices in the subform (including the one you want). You can at that time search the subform for the invoice you want, to position yourself at that invoice.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    melhado
    Guest

    Re: Find subform record and show main form record also (2K SR1)

    <hr><font color=red>I realize that the subform is going to be filtered as part of the form, but is the same true for the entire underlying table? Is there any way to get around it? Would calling the code from a button on the main form make a difference?<<

    It is this statement that still leads me to believe you don't have a handle on the relationship between forms and subforms.</font color=red><hr>
    I may not have made myself clear, but I do understand the relationship, I use them frequently when I have one to many relationships that need to be worked on simultaneously, and know that for display, my subform will only show those records related to the subform. What I am trying to do here is search the entire underlying table of the subform to find a record and when that record displays, the main and other subs are synchronized -- analogous to finding the "invoice" only by its number, and then needing to bring up the customer record.

    <hr><font color=red>If you want to find an invoice not shown on the subform, you will have to open a recordset in code based on the invoice table. Then use your .FindFirst method to find the invoice you want.</font color=red><hr>
    I think that is what I did in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=69464&page=& view=&sb=&o=&vc=1#Post69464>my code</A>. I even opened it as a table to try and avoid the filter factor. Are you saying that even though I am opening the table to search, it is still filtered by the mainform, by the mere virtue that it the form is open? Any way around that?

    I did some more experimenting and found that even typing in a non existant number, it did nothing! No error message as coded, nor movement. This led me to thinking that I may have a sequencing problem, so I put the "No Such Record" message between the NoMatchand Bookmark:
    <font color=blue>
    the confirmation message is here, then search begins
    .FindFirst strSearch

    If Not .NoMatch Then

    ' Give message that record not found, try again or contact Supervisor
    ... and so on

    Else
    .Bookmark = varBookmark
    End If
    </font color=blue>
    all that did was keep giving me the message that the record didn't exist, even when it was the record showing. I tried it on both the subform and mainform, making the necessary changes.

    Is there possibly code I am missing to make it search the right form?

    <hr><font color=red>You can then display that customer on the mainform, which will automatically display all it's invoices in the subform (including the one you want). You can at that time search the subform for the invoice you want, to position yourself at that invoice.</font color=red><hr>
    It seems you are thinking datasheet view on subform -- not so -- it is a single record view -- anywhere from 1-24 records per "customer". It needs to be as transparent as if I were looking up the "customer" on the main form, and its related records were displayed, except here I want to "filter" to just the "invoice" and have its "customer" and other related data shown. I would prefer not to use a filter, as they would have to remember to "show all records" after.

    Even so, this is used by machine operators who are not very adept at computers, and I cannot afford to let them make an error searching among 15-20 "invoices" based on the customer. Additionally, if they hit on a new record, a new number and identity is assigned, which would mess up the system.

    Is there any way to do what you mention about finding the "invoice's" "customer" and then using findfirst method to display the record, but in one step?

    Might it be possible to call this search code from another form, where the main/sub would not be active (but be open)? I tried it on the main form, with no luck, would this be a case for QBF?

    Sorry for the length of this message, hopefully, what I am trying to do is clearer.

    Once again, thanks for the feedback and all

    'dave

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Find subform record and show main form record also (2K SR1)

    >>I think that is what I did in my code. I even opened it as a table to try and avoid the filter factor. Are you saying that even though I am opening the table to search, it is still filtered by the mainform, by the mere virtue that it the form is open? Any way around that?<<

    You still don't quite get it. Forms don't filter tables. The filter applied in a form is used to create a recordset from the original table. The original table is still there with all its records.

    When you search for an invoice# in code, you open the table as a recordset. You then do a .FindFirst to find the invoice# you want. Now, this recordset has NOTHING to do with either your mainform or your subform. It is a separate recordset completely.

    If you do have a match, you look at the CustomerNo in the invoice record. You then have to make this CustomerNo the current record on your mainform. How you do this depends on the mainform's recordsource. As soon as you do this, Access immediately recreates the recordsource for the Invoice subform to include only those invoices for the displayed customer. At this time (if you want), you can look at this recordset for the subform to find the invoice# so you can position the cursor and make this the "current" record.

    Whether you do this all from a button on the mainform or the subform is immaterial; the code is the same (provided you fully qualify all form and control names, and don't use "Me", etc.).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    melhado
    Guest

    Re: Find subform record and show main form record also (2K SR1)

    <hr>You still don't quite get it. Forms don't filter tables...The original table is still there with all its records.<hr>
    I get it, Mark, I think that I may not be describing my situation very well. The reason I opened a recordset in code based on the table, was that I felt using Me.RecordsetClone object would reflect the filtered recordset, and I wanted the entire table to search. Am I wrong in that ?

    <hr>When you search for an invoice# in code, you open the table as a recordset. You then do a .FindFirst to find the invoice# you want. <hr>
    I believe that is what I did in <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=69464&page=& view=&sb=&o=&vc=1#Post69464>my post of the code.</A> -- with a few message box choices in between.
    <hr><font color=red>If you do have a match, you look at the CustomerNo in the invoice record. You then have to make this CustomerNo the current record on your mainform. How you do this depends on the mainform's recordsource. </font color=red><hr>
    AHA This is where I am going wrong, I suspect, as I am getting the match, but going nowhere. The mainform is also based on a table, which is linked to this one on a 3 part ID -- billet, end, piece. Would you be so kind as to look at the code I posted and give me a clue or pseudo code, as to how to carry this out?

    <hr><font color=red>..Access immediately recreates the recordsource for the Invoice subform to include only those invoices for the displayed customer. At this time (if you want), you can look at this recordset for the subform to find the invoice# so you can position the cursor and make this the "current" record.</font color=red><hr>
    THAT IS EXACTLY WHAT I WANT TO DO. However, I could use some guidance on how to get there. When you look at my code (linked above) how far off am I? And where do I need to store the customer 3 part ID that is also in the "invoice" table, so that I can link it back up. Would I make the subform record I want current by focusing to the subform and using GoToRecord..WHERE "Run" (the invoice number) is equal to the input box string (strSeek)?

    This is exactly what I want to do; I really would appreciate some ideas on how to accomplish it based on the code I have.

    Thanks so much for sticking with me, Mark.

    'dave

  11. #11
    melhado
    Guest

    Re: Find subform record and show main form record also (2K SR1)

    OK, Mark, I'm cheating; second response to the same message <img src=/S/grin.gif border=0 alt=grin width=15 height=15> but I have been working on this and getting nowhere -- hoping you could provide a clue or two. Modified code is pasted below.
    <hr><font color=d2691e>When you search for an invoice# in code, you open the table as a recordset. You then do a .FindFirst to find the invoice# you want.<hr>
    </font color=d2691e>
    I thought that I had done that, but you will see by the comment in red, that it seems to be dying right before. I am not sure if I have not yet executed it properly or if something else is wrong. Here is the code, followed by one more sticking point after. Note the line in red is where it seems to die. I am getting the input box and confirmation, and then it dies -- no error messages, when running or compiling. BTW, the "Run #" I am searching on is analogous to the invoice number we have been referencing.
    <font color=blue>
    Private Sub cmdFindRun_Click()
    <font color=448800>
    'asks operator for run number
    'then attempts to find it and
    'call its parent form and suboordinate related records
    </font color=448800>
    On Error GoTo Err_cmdFindRun_Click

    Dim db As DAO.Database
    Dim rsTin As DAO.Recordset
    Dim lngFirst As Long
    Dim lngLast As Long
    Dim strMessage As String, strConfirm As String
    Dim strSeek As String, strMsg As String
    Dim strSearch As String
    Dim varBookmark As Variant

    Set db = CurrentDb<font color=448800>
    ' Open a table-type Recordset to avoid
    ' subform filtering by clone recordset method.</font color=448800>
    Set rsTin = _
    db.OpenRecordset("tblTin", dbOpenDynaset)

    With rsTin
    <font color=448800>
    ' Get the lowest and highest Run Numbers.</font color=448800>
    .MoveLast
    lngLast = !Run
    .MoveFirst
    lngFirst = !Run
    <font color=448800>
    ' Display current run # and ask user
    ' for Desired run #.</font color=448800>
    strMessage = "You are at Run Number: " & !Run & vbCr & vbCr & _
    "Enter a Run Number between " & lngFirst & _
    " and " & lngLast & "."
    strSeek = Trim(InputBox(strMessage))

    If strSeek = "" Then Exit Sub
    <font color=448800>
    'store input to variable</font color=448800>
    strSearch = "[Run] =" & strSeek & ""
    <font color=448800>
    ' Store current bookmark in case the Search fails.</font color=448800>
    varBookmark = .Bookmark
    <font color=448800>
    'Feed back message box to confirm entry</font color=448800>
    strConfirm = "The Run Number you entered is " & strSeek & "." & vbCrLf & vbCrLf _
    & " Check your paperwork." & vbCrLf _
    & " If incorrect, Cancel and retry."

    If MsgBox(strConfirm, 289, "CONFIRM REQUEST") = vbYes Then
    <font color=red>
    'HERE IS WHERE IT SEEMS TO BE DYING</font color=red><font color=448800>
    'find the record</font color=448800>
    .FindFirst strSearch
    End If <font color=448800>'end of confirmation message
    ' Return to the current record if the search fails.</font color=448800>
    If Not .NoMatch Then
    .Bookmark = varBookmark
    Else

    <font color=448800>' Give message that record not found, try again or contact Supervisor</font color=448800>
    strMsg = "Please check the RUN NUMBER" & Chr(13)
    strMsg = strMsg & " and Try Again." & vbCrLf & vbCrLf _
    & "If Repeat Effort Fails," & Chr(13) _
    & "Contact Supervisor. Thank You."

    MsgBox strMsg, 16, "!!NO SUCH RECORD EXISTS!!"

    End If
    End With

    Set rsTin = Nothing

    Exit_cmdFindRun_Click:
    Exit Sub

    Err_cmdFindRun_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindRun_Click

    End Sub
    </font color=blue>
    <hr><font color=d2691e>If you do have a match, you look at the CustomerNo in the invoice record. You then have to make this CustomerNo the current record on your mainform. How you do this depends on the mainform's recordsource....you can look at this recordset for the subform to find the invoice# so you can position the cursor and make this the "current" record.<hr>
    </font color=d2691e>
    OK, here is the part that I am not getting! The NoMatch test is what is testing for the match or not? And if no match, then it sits at the same record (bookmark does that?) -- although if there is no match, it should bring up the "NO SUCH RECORD" message box and stop; which, if I input an invalid number, it will do that.

    What I am having a hard time envisioning is how to capture the results of the match and make it the current record, and then put it into a variable? Next, would I then do another recordset based on the parent/main form and find a match where the 3 part ID in the "tblTin" Run (Invoice #) appears and then match it to the same ID in the table underlying the main form??? And then set the focus to the subform again, and do a gotorecord [current] (variable holding the found Run#)?

    Am I even on the right track or making it more complicated than it is? In gour statement I quoted above it captures precisely what I am trying to do. Got a clue to spare as to how I can get there?

    Thanks so much for your feedback

    'dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •