Find value in one or more tables

[PADFOR]

How do you search more than one table, using a value selected in a combo box? I have two tables called "Parent" and "child". I have created a union query, which contains both primary keys for the table. When a user selects a value from the combo box, I would like to them to go to the appropriate form i.e. If a "Parent" primary key is selected, it would go the "Parent" data form.

Regards

PADFOR

 

[MichaelRed]

Some info is not included. Assume that the Union includes a field which describes the source TABLE (refer to as ['Parent' | 'Child']. When the combobox selection is made, you could include the paret/chile field as a hidden column. Use that hidden field value as the name of the form to open.

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JerryDennison]

What happens when a person who is a child gets married and has children of their own? You'll then have to add them as a record to the parent table, in essence duplicating information already held in the child table. Wouldn't it be better to use one table recursively? By adding a mother and father field to one table. With each of these fields lookups to the primary key of this one table. It becomes recursive to itself, allowing unlimited "nesting" of relationships between parents and children, and grandchildren, and greatgrandchildren, and .......

 

[PADFOR]

I found some code in a book, and adapted it to my needs. I have used a recordset to search more than one table. See below:

Private Sub cmdNext_Click()

Dim db As Database
Dim rec As Recordset
Dim rec1 As Recordset
Dim strSQL As String
Dim strSQL1 As String
Dim strMatches As String
Dim stDocName As String
Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stLinkCriteria As String
Dim stLinkCriteria1 As String
Dim var As Variant

var = Forms![frmItemTagSelection]![cmboItemTag]

strSQL = "MaintainableItemChild"
strSQL1 = "MaintainableItemParent"

stDocName = "frmNewFieldMaintenData"
stDocName1 = "frmEditFieldMaintenData"
stDocName2 = "frmNewFieldMaintenParentData"
stDocName3 = "frmEditFieldMaintenParentData"
stLinkCriteria = "[MaintainableItemChildTag]=" & Me![cmboItemTag]
stLinkCriteria1 = "[MaintainableItemParentTag]=" & Me![cmboItemTag]


Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)
Set rec1 = db.OpenRecordset(strSQL1, dbOpenSnapshot)

If IsNull(var) Then

MsgBox "No Item Tag has been selected, please select a Item Tag", , "Item Tag Warning"

Else

rec.FindFirst "MaintainableItemChildTag = " & cmboItemTag
Do While rec.NoMatch = False
strMatches = strMatches & rec("MaintainableItemChildTag&quot
rec.FindNext "MaintainableItemChildTag = " & cmboItemTag
Loop

If strMatches = "" Then
rec1.FindFirst "MaintainableItemParentTag = " & cmboItemTag
Do While rec1.NoMatch = False
strMatches = strMatches & rec1("MaintainableItemParentTag&quot
rec1.FindNext "MaintainableItemParentTag = " & cmboItemTag
Loop
Select Case fraMode
Case 1
DoCmd.OpenForm stDocName2, , , , acFormAdd
Case 2
DoCmd.OpenForm stDocName3, , , stLinkCriteria1, acFormEdit
End Select

Else
Select Case fraMode
Case 1
DoCmd.OpenForm stDocName, , , , acFormAdd
Case 2
DoCmd.OpenForm stDocName1, , , stLinkCriteria, acFormEdit
End Select

End If

rec.Close

End If
End Sub