By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,340 Members | 1,673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,340 IT Pros & Developers. It's quick & easy.

change rowsource of a lookup field in each record in a subform

P: 1
I want to create a different rowsource-query for a lookup field (field1) in each record in a subform.
The rowsource changes dependent on the value in another field (field2) in the same record.
How can this be done?
- I tried to change the rowsource-query in an eventmacro when the focus is set to field1, but this ofcourse changes the rowsource for all field1's and makes the allready selected values unvisible.
- i think i have to include the value of record 'field2' in the rowsource query, but i cannot find a way to include that value in the query
something like:
lookup field1 in the subform contains this rowsource
- SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME
FROM CUSTOMERS
WHERE (CUSTOMER.AGE= me![field2]);

me![field2] however does not function

please HELP !!
Jul 6 '14 #1
Share this Question
Share on Google+
1 Reply


twinnyfo
Expert Mod 2.5K+
P: 3,482
Are you setting the lookup field via VBA or in the control's property setting? You should use VBA, and create a small function that updates the combo box associated with the lookup field. You must also add code to call this function when the record is displayed or after the Field@ is updated:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     UpdateLookup
  3. End Sub
  4. Private Sub txtField2_AfterUpdate()
  5.     UpdateLookup
  6. End Sub
  7. Private Sub UpdateLookup()
  8.     Dim strSQL As String
  9.     If Not Me.NewRecord Then
  10.         strSQL = "SELECT CUSTOMER.Id, CUSTOMER.AGE, CUSTOMER.NAME " & _
  11.             "FROM CUSTOMERS " & _
  12.             "WHERE CUSTOMER.AGE = " & Me.Field2 & ";"
  13.         Me.cbofield1.RowSource = strSQL
  14.         Me.cbofield1.Requery
  15.     End If
  16. End Sub
Keep in mind that this is a tricky thing you are trying to do, as others have tried to do similar things (see here and here. As long as you only look at your data using a Form, this may work great, but looking at the data in the table itself, can produce some unexpected results, because the row source in a Table can only have one query as its row source when it is opened. Your methodology is not bad, but you must be prepared for the results that you get.

Hope this gets you going in the right direction.
Jul 6 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.