Limking a field witin a form

[erich13us]

I have a form that contaians two tables A and B. These tables are presented such that Table is is in justified format and is the primary table. Table B is presented in tabular format as a subform. the tables are liked so no problem there. I want to add a "list box" that displays all the values from Table A field 1 to the left. Furthermore, when I click on a value in the lis box, I want it to display that record in the primary and secondary tables. How do I cunstruct the link betwwen the list box ansd the two tables?

 

[Steve101]

If the main form and subform are already linked (I presume that you using the LinkMasterFields and LinkChildFields properties to do this), then all you need to do is force the main form to the record you want; the subform will follow.

Add the following code to the AfterUpdate event of your combo box:

Private Sub cmbYourCombo_AfterUpdate()
Dim F As Form: Set F = Me
F.RecordsetClone.findfirst "SearchField = " & cmbYourCombo.Column(0)
F.Bookmark = F.RecordsetClone.Bookmark
End Sub

Notes:

(a) Modify the cmbYourCombo and YourSearchField references as appropriate.

(b) YourSearchField should refer to the unique field in the table behind the main form; this of course should be used as the row source behind your combo.

(c) The .Column(0) bit assumes that you are interested in synching the forms based on the first column of the combo box. Modify this as appropriate.

(d) The code at present does'nt incorporate error handling; eg. if the combo entry doesnt exist. To protect against this, set the LimitToList property of the combo to Yes, and ensure that the combo recordsource matches the main forms.

(e) You could enhance this logic to make it bullet proof using error handling, or to make it work based on a compound primary key (say).

Let us know if this satisfies your immediate requirement,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[erich13us]

This is the code I wrote in. CLTCODE is the field in the Primary key primary table. It is linked to the secondary table as you say. When I celect the new value in the list box, the value in the primary key field changes but it does not select a new record. The secondary table does follow. The Name of the primary table is "Client Demogarphics". Combo65 is the name of the form's field.
Private Sub cmbCombo65_AfterUpdate()
Dim F As Form: Set F = Me
F.RecordsetClone.FindFirst "CLTCODE =" & cmdCombo65.Column(0)
F.Bookmark = F.RecordsetClone.Bookmark
End Sub

 

[Steve101]

hmmmmm. Cant understand why its not working. Here's some code which does sync a bound form with an unbound combo on the form:

Private Sub cmbYourCombo_AfterUpdate()
Dim F As Form: Set F = Me
F.RecordsetClone.findfirst "f1 = " & F!cmbYourCombo.Column(0)
F.Bookmark = F.RecordsetClone.Bookmark
end sub

The only thing that comes to mind is if you are not using the same underlying table for your main form recordsource and combo box rowsource, or the search key value from the combo box was not the same field to the find field in the underlying table. Check these things out.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)