473,320 Members | 1,939 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
1 1562
twinnyfo
3,653 Expert Mod 2GB
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

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

Similar topics

0
by: Al | last post by:
hi I have a Datagrid where I click on the id to open another aspx page to edit the selected record. I was able to pass the selected row's id and use it in the second page to pull the record into...
2
by: Galina | last post by:
Hello I have an application in MS Access 2000. I have a form, which includes a subform. The subform is based on a table, but locked for any editing. There is Edit button. When clicked, it starts a...
7
by: Marco Simone | last post by:
Hi, What is your opinion about using Lookup field in table. I would like to use lookup field in table 1, so that I can choose data in combo box from table 2. Is this good design of database? ...
1
by: Zachary Turner | last post by:
I want to make a Lookup Field based on another Lookup field. In other words, I have this table A with two fields: ID and Name, where ID is an Autonumber and Name is a friendly name. Then I have a...
1
by: LurfysMa | last post by:
I am working on an electronic flashcard program. Most of the subjects are simple lists of questions and answers. Those seem to be working. Some of the "subjects" have "categories" of questions. ...
4
by: vidiot | last post by:
I am trying to create a subform that will display images related to the parent form. The subform is a continuous form as there will be several images that relate to the parent form. I checked...
4
by: TD | last post by:
I am trying to change the forecolor of a field in a subform that is in datasheet view. I am using Access 2007. I have tried everything I can think of to do this but the text still appears in...
4
by: QntmPg | last post by:
Hi all, I have read through what I could find on previous questions regarding using the OpenArgs property, but I'm still not able to get my form to open correctly. I'm not sure exactly where the...
7
by: samdev | last post by:
I have set up a table with a few fields that are set to required....all work fine except the fields that are Lookup from another table. What am I forgetting to set? Thanks!!
2
by: Yousaf Shah | last post by:
Hello everybody I am struck at point while developing my patient database. I am sure it will be pretty simple thing but I am struck. I come to my question. I have a table that stores the name of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.