Combo Box with limited values (1 Viewer)

[Valery]

Hi everyone!

I have a form called – UnitUpd.
It has two subforms – let’s say A and B

I open the form to a specific Unit, let's say Unit 1.

Subform A – lists the tenant living in that Unit with contact info, etc.

Subform B – among other things, has a combo box where the user selects the name of a tenant to whom a refund is being given. The name he will selects will necessarily be a one of the ones listed in Subform A, thus living in that unit.

Right now, my combo box lists every active tenant in the database. Is it possible to limit the combo box to the tenant names that are on the main form?

Thank you!

 

[boerbende]

In the onclick event of the dropdown box do
me.recordsource = " select [tenant] from yourtable where unit = " & me.parent!yourunitfield

 

[Valery]

Thank Boerbende. I added these codes hopefully as per your instructions.

Private Sub cboTransactionTo_Click()
Me.RecordSource = " select [LastName] from tblTenant where UnitNum = " & Me.Parent!UnitNum
End Sub

It didn't do anything - was still seeing the entire list of tenants.

So removed the row source which was a query:

SELECT qlkpMembers.TenantID, qlkpMembers.TenantName
FROM qlkpMembers;

Now I get nothing at all. And previous fields that were filled are now blank.

Any ideas?

Just to ensure I am not missing anything:

Name of form: frmUnitUpd
Name of subform A = frmUnitUpd_Tenant
Name of subform B = frmUnitUpd_Deposit

 

[boerbende]

Sorry, my failure. Answered you too quickly:
1) Put it in the ENTER event.
2) change the code to

Private Sub cboTransactionTo_ENTER()
cboTransactionTo.RowSource = " select [LastName] from tblTenant where UnitNum = " & Me.Parent!UnitNum
End Sub

I don't know what UnitNum is. It sounds like a number, then it is OK like that.
If it is a string, you might need to integrate the character '

Private Sub cboTransactionTo_ENTER()
cboTransactionTo.RowSource = " select [LastName] from tblTenant where UnitNum = '" & Me.Parent!UnitNum & "'"
End Sub

 

[Valery]

Tried the first codes suggested, in OnEnter - getting nothing, still blank with no error message.

The field UnitNum is a number field + a primary key. It is on the Main Form. It comes from tblOccupancy.

Tried the second suggestion - same results.

 

[boerbende]

?!?
The first one should work

I tried it with a very simple construction
Combo42.RowSource = "Select test from tbl_test where test = " & Me.Parent!test

it works fine.
To force a refresh, you have to go out and in the combo. Did you do that?

 

[Valery]

Very sorry. Yes, I did open/close... Guess would be better to provide a sample. Thank you!

 

[Valery]

Here is a reduced sample of the database. You should open the frmUnitUpd first to see...

The combo box is the one in the subform frmUnitUpd_Deposit and is called TransactionTo.

Ideally, I would like it to show UC ([LastName] &" " & [FirstName] but insert and record the TenantID. I did not ask for all of this in my initial request for help for fear that it would confuse explanation. But if this could be done that way - even better!

THANK YOU

PS: If you have the patience , please let me know where I went wrong i.e. my explanation or use of your codes... Really want to learn.

 

[boerbende]

I see the issue.
Problem is: in table tbltenant you don't have a column with unitID / UnitNum

How should those two tables tblunit and tbltenant related?
Is it tenantID = UnitNum?

 

[Valery]

How should those two tables tblunit and tbltenant related?
Is it tenantID = UnitNum? NO

They are not joined per say. tblOccupancy is joined to tblTenant and the UnitNum should be taken from tblOccupancy.

Make sense? Can you still assist me?

Thank you.

 

[boerbende]

I did not find out why the original combo does not show the record. The query created does actually work, but indeed it is not shown.
What I did: i created a new combo to replace the other one. Important is

Data
control source: TransactionTo
bound column: 1

Format
Format: column count: 2
column widths: 0":4"

Other:
Name: cboTransactionTo_2

Event: On ENTER

Private Sub cboTransactionTo_2_Enter()

cboTransactionTo_2.RowSource = " SELECT tblTenant.tenantid, tblTenant.LastName & "","" & tblTenant.FirstName " & _
" FROM (tblTenant INNER JOIN tblOccupancy ON tblTenant.TenantID = tblOccupancy.TenantID) INNER JOIN tblUnit ON tblOccupancy.UnitNum = tblUnit.UnitNum " & _
" WHERE (tblUnit.UnitNum = " & Me.Parent!UnitNum & ")"
End Sub

 

[Valery]

Wow - very detailed information - thank you so much for that! Totally appreciate the effort. It works and was easy to follow and implement!

I need to know... because I can really use this elsewhere as well. How does this work? Is this like a query that only exists in an event procedure? Like not a saved query? For example, if I wanted to add a criteria, let say only the names of the tenant with the Status that = M or F. Would that be easy to do?

I am sorry to ask all these things... but this is so great - I really should learn from it. If you have the time and the patience, I would appreciate some explanation of the event to possibly be able to adapt it in order to re-use it elsewhere.

I am sure other newbies would also appreciate this.

Thank you!!!

 

[Valery]

I am playing with it for another subform. No success yet. It here what I need to understand:

cboTransactionTo_2.RowSource = " SELECT tblTenant.tenantid, tblTenant.LastName & "","" & tblTenant.FirstName " & _

Ok that part seems easy: It is the name of the control + an underscore and a 2. Then the name of the table where the field is located and the field name (in this case concatenated)

---------------------------------------------------------------

" FROM (tblTenant INNER JOIN tblOccupancy ON tblTenant.TenantID = tblOccupancy.TenantID) INNER JOIN tblUnit ON tblOccupancy.UnitNum = tblUnit.UnitNum " & _

This is the one I can't decipher... as both forms have these tables in them, I can't tell which part of the statement is referring to which subform.

-------------------------------------------------------------------------------
" WHERE (tblUnit.UnitNum = " & Me.Parent!UnitNum & ")"
End Sub

This is the condition (the link). The tblUnit is the link to the main form. The Me.Parent!UnitNum, I guess refers to the form where the combo box is located.
---------------

For example, the one I am trying to adapt needs the same link, the same info (UnitNum and the tenant's name concatenated). The combo box is simply in a different subform than the one I gave you. That subform only has one table: tblParking and contains the field UnitNum (Number field) as it's foreign key (what I call my "link" field).

So taking the same example, I deleted the existing combo box, created a new one and entered the following:

Data
control source: cboTenant
bound column: 1

Format
Format: column count: 2
column widths: 0":4"

Other:
Name: cboTenant_2

Event: On ENTER

Private Sub cboTenant_2_Enter()

-------------------------------------------------------------------------------------
In this part, I just changed the name...

cboTenant_2.RowSource = " SELECT tblTenant.tenantid, tblTenant.LastName & "","" & tblTenant.FirstName " & _

-----------------------------------------------------------------------------------

In this part, I tried all kinds of variation using tblParking.UnitNum... but it returned no records. Of course, I am working blind as I don't understand the coding...

" FROM (tblTenant INNER JOIN tblOccupancy ON tblTenant.TenantID = tblOccupancy.TenantID) INNER JOIN tblUnit ON tblOccupancy.UnitNum = tblUnit.UnitNum " & _


this part below should stay the same (correct me if wrong!):

" WHERE (tblUnit.UnitNum = " & Me.Parent!UnitNum & ")"
End Sub

 

[boerbende]

Cooks secret:
Make a simple query with the query designer based on the tables you need. For the WHERE UnitNum = I just took 1 in the criteria field.
When it gives you the results you want, go to the SQL design of the query
Then copy this SQL to your VBA.
Make sure to add the correct number of ""
replace the Unitnum = 1 to unitnum = " & me.parent!yourfield

I work a lot with dynamical WHERE statements, based on selections I make a where string with VBA, which can be huge.
Usually I change rowsources based on After update events of OTHER fields. So for example, when changing a unitnumber, you might consider changing those rowsources immediately in stead of waiting to ENTER the combo.

Maybe it might look like that, but I don't consider it "rocket science" what I do.

For example did I not understand what was wrong with your combo. Maybe I can find out, but when something does not work, it is often quicker to stop searching for the problem, but just replace it and try again. This worked immediately with your form, so don't ask yourself why, but continue with the next challenge.

So with respect to your SQL query above:
You know indeed that you can change the rowsource (data in the list) of your combo with the ON ENTER event. Try first to create a logical query with the querybuilder
I
control source: cboTenant: This is the datafield in the recordset of the form where you want to store the data


bound column: 1 (the data you store comes from the first column in your SQL, so for you the ID column)

Format: the view of the data
column count: 2
column widths: 0":4"
Here you define that your SQL contains actually two columns, but you don't want to show the first column (columnwidth = 0)

Other:
Name: cboTenant_2
(just the name of the new combo. WHen you delete the old one, you can take away the _2, but you have to change also the event (which is now linked to ... ENTER_2)

Hope that helps

 

[Valery]

Been reading... and testing. Explanation is great.

Got a major problem though: On the procedure you gave me initially, the drop down DOES NOT retain the data. I had not noticed that before. Have no idea how to fix this. Please assist.

 

[boerbende]

Can you upload the database again?

 

[Valery]

Preparing it right not (sorry had deleted it after incorporating in my Master)

 

[Valery]

Here it is. THANk YOU!!!! I also want to do the same thing with the Parking section where you see the label "Owner"

 

[boerbende]

You have a wrong JOIN expression. During debugging, I use the immediate field. TYpe in this debug.Print cboTenant_2.RowSource, then you get the query made by your code. Create a query with the designer, go to SQL and copy this string there, there you get the errormessage that the JOIN is not correct.

It is better you first try to create a query with the builder. When it works, take the SQL to your code and "rebuild" that in the event of VBA

For now:
Change the event to:

cboTenant_2.RowSource = " SELECT tblTenant.TenantID, tblTenant.LastName & "","" & tblTenant.FirstName as PERSON" & _
" FROM tblTenant INNER JOIN tblParking ON tblTenant.TenantID = tblParking.TenantID " & _
" WHERE (tblParking.UnitNum = " & Me.Parent!UnitNum & ")"

Further you now want to show 2 columns in this box? Your column widts shows 2 widths (not 0;4 like beford) So you want to show the tenantID as well, but your combo itself is not wide enough to show the name now

 

[boerbende]

Remember that in your parking table, you have unitnum = 1 without a link to tenantID. So when starting the database, you're mainform starts with Unitnum=1, so your results in this combo are 0 at start.