Combo Box with limited values

[Valery]

"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."

I am confused here - in tblParking, there is a field TenantID. I don't see where I have unitnum = 1... In the Relationship diagram, the Parking table is linked to the Tenant table. Very sorry - I don't understand.

 

[Valery]

You mentioned the Parking table but what about fixing the first combo box that you assisted me with - the TransactionTo combo box? It is not retaining the data. Again, could be something I have not copied properly...

 

[boerbende]

The first combo was working at my computer so I did not focus on that Can you try to replace the comma between firstname last name by a minus sign, just to test
I have no time till Sunday unfortunately. Have to travel a whole day

 

[Valery]

Thank you Ben. Replacing comma with minus sign did nothing. No rush - Happy Easter!

 

[boerbende]

Also for this second combo I see that the event does not change the list.
I have replaced the combo again with a new one and this new one does work.

With respect to the first combo box: I tried it again, it works fine here.
What are your regional settings?
What Access version do you use?

 

[Valery]

Glad it works somewhere, lol !
English (UnitedStates) - English language
MS Access 2010

Does this help? Can you send me a working copy of the dtb sample I provided?

THANK YOU!

 

[boerbende]

What I found until now
1) Cbotenant_2, properties, data, row source type was empty. This property should always be “table/query” when you change the rowsource. I don’t know why they are blank in your database, but you have to check that for all the comboboxes which are not functioning.

2) When changing rowsources with VBA, this can affect if information is visual or not. For example when you have selected a name and change then from UnitNumber 1 to 2, the default rowsource in the combo is still with selection on 1, but with records related with 2. Effect is that the names in the combo (which only belongs to two), are “invisible” because the name was not part of the previous query with unitnum 1. I guess by the way that is what you mean when you say it is not working. The Unitnum is there, but the combo has no name to show. So what you should do is:
a. Put back the general rowsource under properties. So the whole query without the WHERE statement. When the rowsource is blank and you start the database, you don’t see any names, despite they are there… (look into the tables to verify)
b. Create an AFTERUPDATE event for when you change the UnitNumber (I don’t you’re your whole database, so I don’t know where you change this) This afterupdate should set the rowsources back to general first. Use the same queries as the on enter, but without the “WHERE … “ part
3) Parking fees: you use not a subform but you open a new form with the FILTER stLinkCriteria = "[PkgID]=" & Me![PkgID]. With that it opens the form with only records related to the selected pkgid. But because it is not a subform, the new record is created without this pkgID. What you can do is: Take a field you always update with a new record, for example pkgfeestartdate. Go to properties and create an AFTER Update Event (Code)
Private Sub PkgFeeStartDate_AfterUpdate()
[PkgID] = Forms![frmunitipd]![ frmUnitUpd_Parking]![pkgID]
End Sub.
What this does is: after updating the date, it always copies the information in the unitnum field to the pkgid column in this recordset. This has to be tested by the way

 

[sneuberg]

I'm finding that with the row source of the cboTransactionTo_2 combo box being set in the subform's enter that it doesn't have a row source until you enter it. So to see the value that is really there you need to click on the combo box. To update the row source when you change records you can update the row source in the main form's (frmUnitUpd) on current event. Try putting this in the frmUnitUpd On Current event

Edit: Don't do this see next post.

Me!frmUnitUpd_Deposit.Form!cboTransactionTo_2.RowSource = " SELECT tblTenant.tenantid, tblTenant.LastName & "", "" & tblTenant.FirstName " & _
" FROM tblTenant INNER JOIN tblOccupancy ON tblTenant.TenantID = tblOccupancy.TenantID " & _
" WHERE (tblOccupancy.UnitNum = " & Me.UnitNum & ")"
Me!frmUnitUpd_Deposit.Form!cboTransactionTo_2.Requery

 

[sneuberg]

A better solution than in my previous post is to create a permanent row source for the combo box that gets its criteria from the form with a reference. To do this:

1. If you added the on current event procedure I suggested in post 28 delete it
2. Open the frmUnitUpd in design view
3. Select the cboTransactionTo_2 combo box, right click on it, in the properties Event tab select On Enter and delete the code
4. Right click on the cboTransactionTo_2 combo box, in the properties Data tab, click on the far right of Row Source to invoke the code builder
5. With the Show Table dialog add tables tblOccupancy and tblTenant then close the dialog. The tables should be already joined
6. Double click on TenantID to add the field to the query
7. Add the follow expression for the next field or you could double click on Salut, SalutStat or SalutStatShort if you prefer one of them
   DispName: [tblTenant].[LastName] & ", " & [tblTenant].[FirstName]
8. Double click on UnitNum in the tblOccupancy to add it as a field
9. Uncheck the UnitNum as we are only using this as criteria
10. In the criteria of the UnitNum enter the reference [forms]![frmUnitUpd]![UnitNum]
11. Save and close the query

Note that if you type form! in the criteria you should get a drop down with the possible forms so you can use the intelliSense to help you get these references right.

Sometimes I find that when I type forms! nothing happens. The only thing I found that fixes this is to restart Access. Also the intelliSense seems to work better if the target forms are open especially when the target control is on a subform.