Bound Form unbound Subform master/child relationship

[DJJ]

Access does not allow creating a master child relationship for a Form and
Subform when the Subform is unbound (The Subform contains an ADO recordset
based on MySQL data). Does anybody know hard to get around this?

DJJ

 

[Larry Linson]

Access does not allow creating a
master child relationship for a Form
and Subform when the Subform is
unbound (The Subform contains an
ADO recordset based on MySQL
data). Does anybody know hard to
get around this?

AFAIK, the definiton of synchronizing via Link Master Fields and Link Child
Fields requires both forms be bound.

Perhaps if you could explain or define what you mean by a Master-Child
relationship with an unbound child form, someone could offer suggestions.

Larry Linson
Microsoft Access MVP

P.S. I worked enhancing an Access ADP which had unbound forms (at least
partially because the author did not realize he needed to have primary keys
on the tables in SQL Server). One form appeared similar to a Form/Subform,
but included a great deal of code to try (unsuccessfully) to make it work. I
got the DBA to designate the primary key, bound the forms, got rid of a good
deal of code, and it worked very nicely. Perhaps your easiest solution would
be to consider binding the form in the Subform Control.

 

[DJJ]

I tried to do what you suggested with a pass-through query of the MySQL data
but all I got was a message that Access does not allow a subform to be
related to the parent form (regular Access table) when the subform is bound
to a pass-through query (ODBC MySQL select query).

I was trying to define the relationship between the two forms within the
properties box of the subform control which is sitting on top of the main
form.

There maybe a way to accomplish this with VBA code that's why I created an
ADO recordset consisting of the ODBC MySQL data in code in the Open and
Close form events of the subform but I have not found any good tech-notes to
bind the fields in the subform and create a relationship to the main form.
The relationship would be made based on a 'Style Number' field that exists
both in the 'parent' Access table and the 'child' MySQL table (one to many
relationship).

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection

With cn
.Provider = "MSDASQL.1"
.Properties("Persist Security Info").Value = "False"
'.Properties("Data Source").Value = "BusinessMind"
.Properties("Data Source").Value = "Local Businessmind"
.Open
End With

Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT i.stock_id as Stock_ID, i.vendor_style_number as
StyleNum," & _
"c.name as Name, l.qty as Qty, i.description as 'Desc'," &
_
"i.cost_per_price_unit as Cost, i.price_per_unit as
Retail," & _
"i.price_2 as Wholesale FROM inventory i INNER JOIN
inventory_location l " & _
"ON i.ideaxid = l.inventory INNER JOIN contacts c ON
i.vendor = c.ideaxid " & _
"WHERE c.name = 'MC2'"
.LockType = adLockOptimistic
.CursorType = adOpenForwardOnly
.CursorLocation = adUseClient
.Open
End With