JS-Q6: Combo Box - mixing tables

[Jack Sheet]

Hi all (Access 97)

I have a table "T_Clients" that contains key field "ClientRef" and various
other fields including "ClientName".
I have a table "T_RepeatingTasks" that contains key field
"ID_RepeatingTasks", and various other fields including "CllientRef" but NOT
including "ClientName".
There is a one-to-many relationship between "T_Clients" and "T_Repeating
Tasks" respectively, based on the "ClientRef" field.

I am trying to create a form for the purpose of reviewing and updating the
table "T_RepeatingTasks".
One of the boxes that I want on the form is a combo box for selecting the
record to be reviewed or updated.
The combobox should refer to ClientRef as the control, but should display
both the ClientRef and Client Name in the dropdown window.

I can almost get there. In the process of creating the combo box I am
prompted to select one of three exclusive options.
One of the options presented is "I want the combo box to look up the values
in a table or query". If I select this option then I can proceed to create
the combo box based on "T_Clients" table, so as to display both ClientRef
and ClientName in the dropdown list. However I cannot get the remaining
fields in the form to update to the selected record.
Another of the options presented is "find a record on my form based on the
value I selected in my combo box". This fits the bill precisely, but the
problem with this option is that it does NOT give me the option to select
fields outside of "T_RepeatingTasks" table and therefore does NOT present me
with the option to include the "ClientName" field in the drop-down box.

Sorry for the verbosity. Any way to use the combo box to look up values
from the T_RepeatingTasks table AND simultaneously include the ClientName
field (from the T_Clients table) in the dropdown box for selecting
ClientRef?

Thanks

 

[Jack Sheet]

Uff! I had what I thought was a great idea to solve this, but of course it
didn't work.
I created two combo boxes: One (box 1) by selecting the option "I want the
combo box to look up the values in a table or query", and the other (box 2)
by selecting the option "find a record on my form based on the value I
selected in my combo box". Then I copied the formula for the "row source"
from properties of box 1 into the row source of the properties of box 2, and
played around with the column widths. Now if I select a record via box 2 I
get "Run time error '3070'. The Microsoft Jet database engine does not
recognise 'AA0821' as a valid field name or expression. The following code
bombs out on the line "Me.RecordsetClone...."

Sub Combo38_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[ID_RepeatingTasks] = " & Me![Combo38]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

I agree that AA0821 is not a valid field name. It is a value that is
contained in a field whose name is ClientRef.

This is not turning out to be very intuitive. I thought that the whole
point of creating one-to-many relationships was so that you could isolate
fields to the appropriate tables and yet still have access to them. Seems I
have missed a trick there somewhere.

 

[Mike Revis]

Not sure that this will do what you want.
Create a query that has the data from both tables and use the query as the
record source
for the form.

Mike

 

[Jack Sheet]

Yes, that worked, thanks Mike.

Just a small cosmetic issue that I need to clear up.
When I open the form, the combo box is clear.
How do I get it so that the combo box is pre-completed with the "ClientRef"
value of the first record, ie the record whose other fields are displayed in
all of the other text boxes on the form?

 

[Mike Revis]

Now you are really challenging my limited skill set.

I will have to think about that. I played with it a little today but can't
come up with a good answer.

Perhaps someone else might care to take a shot at this one.

Mike