Full Version: combo box reverse lookup?
UtterAccess Forums > Microsoft® Access > Access Forms
russ25rsa
I have two tables A and B. There is a 1 to many relationship between A and B. Table A's data is controlled by FormA, Table B's data is controlled by FormB which is a subform on FormA. I already have a combo box for navigation on FormA, however I would also like to be able to have a combo box on FormB (FormA's subform) for navigation, so that when I choose a record from the subforms (FormB) combo box, FormA then jumps to the 1 record from FormA that contains the related record choosen in the SubFormB's combo box (at the same time as the subforms choosen record on the subform) .
indalike the combo box on FormA, except it's a 'reverse lookup' if that makes sense. Is this at all possible, does this even make sense? confused.gif Thanks in advance :-)
_______________
HOLY CRAP BATMAN!!!
projecttoday
So what do these forms contain / what is it that you're looking up? Give an example with values.
obert
fkegley
If I understand correctly, it should already be doing this. That is, the records in the subform should be those from the "many" side of the relationship that match up to those on the "one" side of the relationship. If it is not doing this, then check the Link Master and Link Child fields of the subform control. Be sure they are set correctly.
russ25rsa
Thank for the responses :-)
The one to many relationship is working properly. The database is used to track a series of insurance claims... More specifically workers compensation claims... btw I am not an insurance salesman :-) . There are employers (1) and claimants (m). Each Employer can have multiple claimants (employees who claim compensation). The employers form constitutes the one side of the relationship and the claimaints subform constitutes the many side.
Browsing by employers on the employers form with a combo box is easy, however placing a combo box on the claimants subform and then trying to browse by claimants does not work. The combobox in the Claimants subform when running as a form on it's own works, however when trying to use the combo box with the employers form and the Claimants form open as a subform, the subform combo options are visible, but clicking on the options has no effect.
I am hoping to be able to choose a name from the claimaints subform combo and browse to that record (not only on the claimants subform but also have the employers form jump to the associated 1 employer record for the claimant chosen).
Hope his makes a bit more sense. Is this possible... please say yes :-)
datAdrenaline
In the combos after update event you will want to do two things ... navigate to the correct employer, then navigate to the selected claimant .... The sample below may help you with the logic (note: field names may have to change):
irst: Set up your claimant combo box to have the employerID as a column in the combo ... You CAN hide the column by setting its width to 0 if you want to ... I will assume in this sample that a SQL Statement like this will be used.
RowSource = "SELECT ClaimantID, Claiment, EmployerID FROM tblClaimants INNER JOIN tblEmployers ON tblClaimant.EmployerID = tblEmployers.EmployerID
Also, I will assume that the combo is bound to the first column.
With that ... here is a concept of the code I would put in the AfterUpdate event of the claimant navigation combo, which is on the claimant subform.
CODE
Private Sub cmbGotoClaimantID_AfterUpdate()
    
    Dim rstEmployers As DAO.Recordset
    Dim rstClaimants As DAO.Recorset
    'TRY Navigate to the employer IF a claimant has been selected
    If Me.cmbGotoClaimantID.Column(2) & "" <> "" Then  'Note the column property is ZERO based
        
        Set rstEmployers = Forms("NameOfYourParentForm").RecordsetClone
        rstEmployers.FindFirst "EmployerID = " & CLng(Me.cmbGotoClaimantID.Column(2))
        
        If Not rstEmployers.NoMatch Then
  
            'Navigate to the employer
            Forms("NameOfYourParentForm").Bookmark = rstEmployers.Bookmark
            
            'Now that the employer is found, navigate to the appropriate claimant
            Set rstClaiments = Me.RecordsetClone
            rstClaiments.FindFirst "ClaimantID = " & Me.cmbGotoClaimantID
          
            If Not rstClaiments.NoMatch Then
                Me.Bookmark = rst.Bookmark
            End If
      
        End If
  
    End If
    
    Set rstEmployers = Nothing
    Set rstClaimants = Nothing

End Sub

Notes: THIS IS AIR CODE ... IT IS JUST MEANT TO GIVE YOU A STARTING CONCEPT ... Also, the above code ASSUMES that the Fomr(Employers) is OPEN ... it does NOT have to be the parent to claimants, but I did write the above with the assumption that claimants form is a sub-form to the employers.
HTH ...
russ25rsa
Thanks Brent,
Just a quick one, what does the references rstEmployers and rstClaimants refer to? Have modified the code where applicable but get the following Compile Error: User Defined type not defined on the Dim rstEmployers As DAO.Recordset line (line 2)... I really appreciate the effort :-) and your help.
datAdrenaline
DAO.Recordset is an object variable TYPE, just like when you declare a variable as a string (ie: strMyText As String).
DAO.Recordset (as well as an ADODB.Recordset) is a "virtual" table of records.... Kind of like running a query or opening a table through the User Interface ... but in memory only, so the user does not see the records. So with that ... rstEmployers is a variable that points to a set of records inside the Employers (tblEmployers in my code) table. In my code, I get that set of employer records from the records that are feeding ("bound to" - via the RecordSource property) your main form (.RecordsetClone) and rstClaimants is a variable that points to a set of records in the Claimants (tblClaimants in my code) table. I use the .RecorsetClone property of the sub-form object to get those records.
DAO Refers to the an object hierarchy. DAO stands for "Data Access Objects". MS Access is highly optimized to use the DAO object libraries, but ... with the compile error you recieved, you apparently do not have a "reference" to the DAO object library. To set the reference to the DAO object library, while in the Visual Basic Editor .. use the menu path Tools > References > {A dialog box of references pops up} ... select the "Microsoft DAO 3.6 Object Library".
You may notice in the list of references a "Microsoft ActiveX 2.x Data Objects" ... the ADO library is similar to the DAO library, but is more flexible with respect to its usage on things (data sources) outside of MSAccess.
Once you have the reference set to the DAO object library, you code SHOULD compile ... if not, then we will work from there.
russ25rsa
Brent, you are an absolute legend :-) works a treat !!!! Had an error on this... removed it and it works a charm.
If Not rstClaimants.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Thanks for all your effort and assistance ! I am assuming that the Microsoft DAO 3.6 Object Library needs to be set on each PC that will be using the DB or is that now part of the DB?
Thanks again!!!
datAdrenaline
What error are you getting, you should not be getting an error?!? ... any chance of posting your app?
>>> I am assuming that the Microsoft DAO 3.6 Object Library needs to be set on each PC that will be using the DB or is that now part of the DB? <<<
... It SHOULD be part of the DB .. but .. if you get an error on a "simple" function that should NOT have a problem (ie: Left("my text",1)) ... or ... you get the Undefined User Type ..or something like that, you will know what to do.... but I am fairly certain that the reference will "travel" with the app.
Gotta go put the kids in bed!! ... See ya!
Brent
russ25rsa
Thanks Brent, I will have to check with the big boss as to whether or not I can post the app (just covering my bacon), I appreciate your assistance no end.
Cheers,
and thanks again !!
datAdrenaline
You are welcome! ... I understand about posting your app! ... I am just curious as to why that portion of code errored out ... if nothing else, I can put together a small sample and post to the thread. Does the code that is working right now go to the exact claimant? ... or just the employer of the claimant.
russ25rsa
Works perfectly in all regards, goes to the claimant and to the Employer... here is the modifed code...
!--c1-->
CODE
Private Sub Combo119_AfterUpdate()
Dim rstEmployers As DAO.Recordset
Dim rstClaimants As DAO.Recordset
    'TRY Navigate to the employer IF a claimant has been selected
    If Me.Combo119.Column(2) & "" <> "" Then  'Note the column property is ZERO based
        
        Set rstEmployers = Forms("Frm IMA Claims Review").RecordsetClone
        rstEmployers.FindFirst "[Client ID] = " & CLng(Me.Combo119.Column(2))
        
        If Not rstEmployers.NoMatch Then
  
            'Navigate to the employer
            Forms("Frm IMA Claims Review").Bookmark = rstEmployers.Bookmark
            
            'Now that the employer is found, navigate to the appropriate claimant
            Set rstClaimants = Me.RecordsetClone
            rstClaimants.FindFirst "ID = " & Me.Combo119
          
      
        End If
  
    End If
    
    Set rstEmployers = Nothing
    Set rstClaimants = Nothing
End Sub

Works well :-) Thanks again Brent
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.