Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 9th, 2006, 11:15 PM
Registered User
Join Date: Jun 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Subform combo box 'reverse lookup?'

I have two tables A and B. There is a 1 to many relationship between A and B. Table A's data is controlled by FormA, Table B's data is controlled by FormB which is a subform on FormA. I already have a combo box for navigation on FormA, however I would also like to be able to have a combo box on FormB (FormA's subform) for navigation, so that when I choose an record from the subforms (FormB) combo box, FormA then jumps to the 1 record from FormA that contains the related record choosen in the SubFormB's combo box (at the same time as the subforms choosen record on the subform) .

Kindalike the combo box on FormA, except it's a 'reverse lookup' if that makes sense. Is this at all possible, does this even make sense?
Old June 11th, 2006, 12:13 AM
Authorized User
Join Date: May 2006
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to Ashfaque

Basically it is not a good idea.

If you need this way then there is no meaning of one-to-many relationship. I would suggest one way only.


Old June 12th, 2006, 08:26 AM
Friend of Wrox
Join Date: Dec 2005
Posts: 142
Thanks: 0
Thanked 0 Times in 0 Posts

So basically you want an internal Search function on the subform that will lookup a record from Table B and display it, plus its 'parent' record in Form A? If so, is it truly a one-many relationship (each Table B entry corresponds to one and only one Table A record?) or is it a many to many (Table B record may be related to multiple Table A records).

I mean really, it's programmable if it's the functionality wanted (I know that 'what is wanted' seldom matches up with 'what is good design'). The code would go on the 'On Click' event for a button such as 'Go' or whatever next to the combo box. The gist of it would be something like:

'Store the search parameter in a variable (VarB)
'Create query on Table B to bring up the record searched for
'Make query into a recordset
'If recordset not empty then
  'Store the value in the recordset's field referring to Table A in a variable (VarA)
  Me.Parent.RecordSource = "SELECT * FROM TableA WHERE Id = '" & VarA & "'"
  Me.RecordSource = "SELECT * FROM TableB WHERE Id = '" & VarB & "'"
'End If

You might have to remove any master/child links and manually program it in, I'm not sure how well they play along with code that's basically backdooring the database into doing what you want.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Combo box to display items from parent combo box Gini Visual Studio 2008 0 June 18th, 2008 12:30 AM
Count in combo box(display results in text box) mboyisis Access 4 April 4th, 2008 07:08 AM
Combo box choice creating filtered combo box stevensj5 Access 11 September 13th, 2007 11:33 AM
Subform combo box insanity Freddyfred Access VBA 5 June 9th, 2004 07:37 PM
Combo Box Lookup mikericc Access 1 May 21st, 2004 04:08 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.