Unbound combobox doesn't display data

[Joallyn]

Hi listers,

Help, please. My brain is a puny thing.

I have a "Projects" database that includes these tables and relationships, among others:

tblProjects - pkeyProjectID
fkeyClientID

tblClients - pkeyClientID
txtClient
fkeyGroupID

tblGroup - pkeyGroupID
txtGroup

In prose: each Project (i.e., "Map of penguin sites") is completed at the request of a client ("Mike Smith") who belongs to a more generalized group ("Science", "Logistics"). I currently have ~100 individual clients aggregated into 4 main groups.

On the main form "frmProjectEntry" I have combo boxes cboGroup (unbound) and cboClient (bound). After the user chooses the Group, the cboClient box shows only those clients that belong to that particular Group.

That part works wonderfully. What *doesn't* work is that since cboGroup is unbound, no group is displayed when I go back through the data I've entered previously. That makes sense, since the box is unbound, but it's annoying.

Making cboGroup bound to the underlying table (by inserting an "fkeyGroupID" into tblProjects) seems to be poor database design, however.

Any ideas?

Thanks very much in advance...

J

 

[Remou]

Can you not retrieve the group by a query joining tblProjects to tblClients? This could then be displayed with DlookUp or a subform.

 

[Joallyn]

Hi Remou,

thanks for replying to my post! Unfortunately, here's where the puny brain issue comes into play: I'm stumped by the circularity of using cboGroup to filter cboClient, and then (trying to) use cboClient to fill in cboGroup. It seems like there must be an easier, more logical, more efficient way to set everything up.

No?

thanks!

J

 

[Remou]

If your form shows a ClientID, you can set the combo or a textbox to:
=DlookUp("fkeyGroupID","tblClients","pkeyClientID=" & Me.txtClientID)
In an appropriate event, On Current for example.

 

[Joallyn]

Hi Remou,

thanks for your suggestion, and sorry for the delayed response. I've been fiddling with the concept for most of the last day or so, unsuccessfully, so I think I'm going to revert back to a bound combobox for the group and struggle with the consequences of that move later on.

Super depressing.

cheers

Joallyn

 

[Remou]

I am failing to communicate these days. What I mean is set the displayed value for the combobox:
[tt]Me.cboCombo =DlookUp("fkeyGroupID","tblClients","pkeyClientID=" & Me.txtClientID)[/tt]
Or set a new textbox on your form to:
[tt]=DlookUp("fkeyGroupID","tblClients","pkeyClientID=" & Me.txtClientID)[/tt]
Where txtClientID is the name of a textbox that contains a numeric Client ID.
In your post you say:
[tt]tblClients - pkeyClientID
txtClient
fkeyGroupID[/tt]
It seems to me that you can DlookUp this table to get the group to which a client belongs. I hope that is a bit clearer, or have I missed some important point you have made?

 

[Joallyn]

HI Remou!

I think you're expressing yourself extremely clearly, actually. I'm going to stick with my kludgy workaround for the time being since I'm time-limited on this project but I'll try your suggestion once things free up a bit.

thanks again for checking this out!

J