Full Version: Cascading Combo box
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
mhearnsww
I've got my first combo box on the form set up to display the account holderID, name and property. The second combo box displays only the read date. When the user clicks on the account holder combo box, the second combo box filters out the reading dates for the property that is assigned to that account holder.
The problem i'm getting is that when the user selects the read date, it displays only the first record for each filtered read date.
For example the read date has three readings.
12/12/2005
05/12/2005
08/08/2005
If you select the 08/08/2005 from the combo box, i would then expect the 08/08/2005 to display in the combo box, but for some reason it will display 12/12/2005.
This is the same for every property, for some reason it displays the first record even if u've clicked on the nineth.
Can anyone suggest what i might be doing wrong?
jmcwk
Are you saying that when the user selects the account holder ID you only want the Dates populated in your DateID Combo that apply to that Account holder? In other words if account holderID 1 had records for 01/01/06,01/02/06,01/03/06 those are the only dates available. Then after the user selects 01/02/06 what do you want to occur? Open a report? Open a form, Preview A report? Below is a simular situation I use to preview a report Baded On an Agents Name, and a Deposit date.
ame/s of my combo boxes are:cboAgent and Cbodtproduced
CODE
Private Sub cboAgent_AfterUpdate()
    Me.Cbodtprod = Null
    Me.Cbodtprod.RowSource = "SELECT DISTINCT tblDepositSheets.dtprod FROM tblDepositSheets WHERE tblDepositSheets.AgentID = " & Me.cboAgent.Column(1)
End Sub
Private Sub CmdPreview_Click()
DoCmd.OpenReport "rptdepositsheets", acViewPreview, , "[dtprod] = #" & Me.Cbodtprod & "# And [Agent Name] = '" & Me.cboAgent & "'"
End Sub
ScottGem
Can we see the SQL for the second combo and the bound column. What I suspect is happening is the PK is not the bound field.
mhearnsww
I've already got the combo boxes to display the relevant data.
AccountHolderID 1 has dates 01/01/2006,01/02/2006,01/03/2006.
AccountHolderID 2 has dates 02/02/2006, 02/03/2006, 02/04/2006.
If i select AccountHolderID 1 in the Account combo box, the 2nd combo box will display 01/01/2006, 01/02/2006, 01/03/2006. That is working fine.
The problem i'm getting is that when i click on the second combo box for AccountHolderID 1, and select the 01/03/2006 record, it wont display that record, it will only display the 1st record 01/01/2006. This is the same for every AccountHolder.
Once i've clicked on the relevant record, i will then have three text fields that will become visible with relevant data linking it to the read date.
I just dont know why it is displaying the 1st record when i've clicked on the 2nd,3rd,4th etc.. record.
Do you have any sugestions?
mhearnsww
Below is the SQL for the second combo box.
SELECT tblProperty.PropRef, tblMeter.MeterRef, tblReading.ReadingID, tblReading.ReadDate, tblReading.Reading, tblReading.Consumption, tblReading.ReadType
FROM tblProperty INNER JOIN (tblMeter INNER JOIN tblReading ON tblMeter.MeterRef = tblReading.MeterRef) ON tblProperty.PropRef = tblMeter.PropRef
GROUP BY tblProperty.PropRef, tblMeter.MeterRef, tblReading.ReadingID, tblReading.ReadDate, tblReading.Reading, tblReading.Consumption, tblReading.ReadType
HAVING (((tblProperty.PropRef)=[forms]![Form12]![cboAccount]));
The bound column is tblProperty.PropRef.
jmcwk
As Scott Suggests:<
ScottGem
Looks to me like PropRef is not unique in tblProperty. So that the PropRef chosen yields only the first record.
mhearnsww
PropRef is the PK in the Property table with an auto num data type.
ScottGem
Hmmm, then I don't understand why it would return any date but the one associated with that PropRef.
You may want to post a stripped down copy of your database so we can check it out.
mhearnsww
I've attached the database as requested.
mhearnsww
here it is.
mhearnsww
I've managed to sort it out now.
do have another problem with the same form though.
Once the user selects the read date, three fields appear, 'Current Reading', 'Consumption' and 'Read Type'. If the user wants to create an invoice for this reading, they will click on the 'create invoice' button. This will make seven more fields appear.
I'm trying to get a calculation to work that will take the consumption and multiply it against the 'volrate'. The code below comes back with an error message saying
Run Time Error '2465'
'Microsoft Access Can't Find The Field 'l' referred to in your expression.
If Me!cboReadDate >= [tblMeteredCharge]![StartOfChargePeriod] And Me!cboReadDate <= [tblMeteredCharge]![EndOfChargePeriod] Then
Me!txtVolCharge.Value = Me!txtConsumption * [tblMeteredCharge]![MeteredVolRate]
End If
I'm guessing i cant use [tblMeteredCharge]![StartOfChargePeriod]. What code would i need to look into the table?
ScottGem
It looks like you are comparing the value of cboReadDate with the fields in the table. You can't do that. Access has no idea what record you are trying to read from. You need to use a DLookup to pull the values from the table. Or use controls on the form.
mhearnsww
i've never done a dlookup before. When it says (Expr as String) would that be the table name followed by the field name? I'm not sure what the (domain as string) and (criteria) is either.
ScottGem
The syntax for the DLookup is:
Lookup("[fieldname]","table or query", criteria.
So if you want to grab the value of StartofChargeperiod from the table tblMeteredcharge you would use:
=DLookup("[StartofChargePeriod]","tblMeteredCharge")
If you want to specifiy what recrod from tblMeteredCharge to sue you need to add criteria like:
"[fieldname] = " & variable
mhearnsww
I've had a go at it with the following code.
If Me!cboStartChargeDate.Value >= DLookup("[StartOfChargePeriod]", "tblMeteredCharge", "[StartOfChargePeriod] = " & Me!cboStartChargeDate) And Me!txtEndChargeDate <= DLookup("[EndOfChargePeriod]", "tblMeteredCharge", "[EndOfChargePeriod] = " & Me!txtEndChargeDate) Then
Me!txtVolCharge.Value = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeteredVolRate = " & Me!txtVolCharge) * Me!txtConsumption
End If
When i tried this, it didnt populate the consumption field with anything.
Am i doing this right? or should there only be one dlookup for it?
ScottGem
A couple of points. First, if your criteria are dates then you need to surround them with an octothorpe like this:
[StartOfChargePeriod] = #" & Me!cboStartChargeDate & "#"
Second, You are assigning txtVolCharge to MeteredVolRate where its = to what's there. Basically you are saying:
a=a if a=a.
Osuspect that you are getting 0 time txtConsumption which = 0.
mhearnsww
i've added in the '#' below, but it didnt put an amount into txtConsumption field.
If Me!cboStartChargeDate.Value >= DLookup("[StartOfChargePeriod]", "tblMeteredCharge", "[StartOfChargePeriod] = #" & Me!cboStartChargeDate & "#") And Me!txtEndChargeDate <= DLookup("[EndOfChargePeriod]", "tblMeteredCharge", "[EndOfChargePeriod] = #" & Me!txtEndChargeDate & "#") Then
Me!txtVolCharge.Value = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeteredVolRate = " & Me!txtVolCharge) * Me!txtConsumption
End If
Is there anything else i'm missing out or doing wrong?
ScottGem
You didn't read the second part.
This line:
Me!txtVolCharge.Value = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeteredVolRate = " & Me!txtVolCharge) * Me!txtConsumption
Makes no sense.
mhearnsww
Yeah i did read it properly after i sent my previous post. I have now simplified it to the following.
e!txtVolCharge = DLookup("[MeteredVolRate]", "tblMeteredCharge") * Me!txtConsumption
This is bringing back an amount now. But its bringing back the first record in the table.
Otried the following code.
Me!txtVolCharge = DLookup("[MeteredVolRate]", "tblMeteredCharge", "StartOfChargePeriod = #" & Me!cboStartChargeDate & "#") * Me!txtConsumption
But this did nothing. So i'm kind of stuck in between what i need.
ScottGem
I don't know what you need because I'm not clear how you determine which record in tblMeteredCharge that you need to pull the value from. That's what you need to determine, then setup the criteria accordingly.
mhearnsww
The table tblMeteredCharge has 5 columns as stated below
eterChargeRef (PK) - autonum
ServiceRef - Number
StartOfChargePeriod - Date/Time
EndOfChargePeriod - Date/Time
MeteredVolRate - Number
In the StartOfChargePeriod and EndOfChargePeriod the dates are:
SOCP EOCP
01/04/2003 - 31/03/2004
01/04/2004 - 31/03/2005
01/04/2005 - 31/03/2006
01/04/2006 - 31/03/2007
On my form i have a combo box 'cboStartChargeDate'. This combo box will have 6 monthly dates e.g. 01/04/2003, 30/09/2003, 01/04/2004, 30/09/2004 etc.
When the user selects '01/04/2003' from the combo box, it will then take the MeteredVolRate from the '01/04/2003 - 31/03/2004' record in tblMeteredCharge. This meteredVolRate will then multiply against the txtconsumption field to make the txtVolCharge amount.
My code at the moment brings back the first record in the table, so if the user selects the date '01/04/2005' it will bring the record for '01/04/2003 - 31/03/2004'.
Me!txtVolCharge = DLookup("[MeteredVolRate]", "tblMeteredCharge", "StartOfChargePeriod = #" & Me!cboStartChargeDate & "#") * Me!txtConsumption
ScottGem
Ok, Are the periods unique? Does the combobox stored the MeterChargeRef?
mhearnsww
No, the combo box is a 'value list' and i've typed in the dates. The meterChargeRef is not stored in the combo box. The StartOfChargePeriod and EndOfChargePeriod is a 12 month period for the whole year.
The dates in the combo box are for 6 months. So theres no way to query it with a meterchargeref.
ScottGem
Well there's your problem. You need to be able to uniquely identify the record in tblMeteredCharge. I'm just not clear how you can do it gioven your structure.
mhearnsww
Can i not code it to say "if this date in the combo box is between the date in the tblMeteredCharge table, then take the MetereVolRate amount"?
ScottGem
Yes, but only if the dates will result in ONE RECORD being returned. You could use criteria of:
[StartOfChargePeriod] <= #" & Me!cboStartChargeDate & "# AND [EndofChargePeriod] >= #" & Me!cboEndChargeDate & "#"
mhearnsww
The result will only return one record when the user selects a date in the combo box. I tried that code before but it dosent seem to return anything.
I thought it would be quite simple just adding that in, but i guess i was wrong.
ScottGem
can you post a stripped down copy of your database
mhearnsww
I've attached the database.
ScottGem
Where is the code causing the problem. Can you just give a recap of what you are trying to do.
mhearnsww
I've left work now and i'm back home, so i'm trying to remember off the top of my head, sorry if its a bit vague.
The user will select an account, then they will select a read date. They will then click on create invoice, and then select the start date from the combo box. Once they select a start date, the end date will automatically fill in, and so should the unit cost, vol charge, standing charge and total.
The unit cost, is the MeteredVolRate, and that is the value i'm trying to get from the tblMeteredCharge. Once the unit cost is found, the txtconsumption field will multiply against the unit cost, to get the volcharge.
To calculate the standing charge, the date range of cboStartChargeDate and txtEndChargeDate is used to give the date difference in terms of how many days it is. Then that will multiply against the standingcharge in the tblMeterSize.
The total is the vol charge and standing charge added together.
The code that is causing the problem is on the afterupdate on the cboStartChargeDate combo box. Its the IF txtReadType = "A" then
IF cboStartChargeDate = "01/04/2003" then
txtunitcost = "This is where it looks for the record in the tblMeteredCharge"
HTH
mhearnsww
Hi, were you able to look at my database last week. Since last week, i've changed the form slightly. With the below code, i'm able to pull the correct value from the tblMeteredCharge table.
I've got the following if statement below.
If Me!txtStartOfChargeDate = "01/04/2003" Then
Me!txtUnitCost = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeterChargeRef = 1")
ElseIf Me!txtStartOfChargeDate = "01/04/2004" Then
Me!txtUnitCost = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeterChargeRef = 2")
ElseIf Me!txtStartOfChargeDate = "01/04/2005" Then
Me!txtUnitCost = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeterChargeRef = 3")
ElseIf Me!txtStartOfChargeDate = "01/04/2006" Then
Me!txtUnitCost = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeterChargeRef = 4")
End If
This pulls the correct value from the tblMeteredCharge table. The problem i'm getting is that the user may not always type in the value of "01/04/2003", "01/04/2004", "01/04/2005" etc. for the start date. They may type in "10/06/2004", so i need the if statement to scan for the whole charge year e.g. "01/04/2003 - 31/03/2004".
Otried the code below.
If Me!txtStartOfChargeDate >= "01/04/2003" and Me!txtStartOfChargeDate <= "31/03/2004"
This didnt work. Am i doing this criteria wrong?
ScottGem
Try this way:

Dim intMeterChargeRef as Integer

SELECT CASE Me!txtStartofChargeDate

CASE 1/4/2003 To 31/3/2004
intMeterChargeRef = 1

CASE 1/4/2004 To 31/3/2005
intMeterChargeRef = 2

CASE 1/4/2004 To 31/3/2006
intMeterChargeRef = 3

CASE 1/4/2006 To 31/3/2007
intMeterChargeRef = 4

END SELECT

Me!txtUnitCost = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeterChargeRef = " & intMeterChargeRef)

However, I have a problem with this since, you will have to go back and change this code each new year. Instead, create a table

tblMeterChargeRef
MeterChargeRef (Integer)
StartDate
EndDate

Then you could use:

intMeterChargeRef = DLookup("[MeterChargeRef]","tblMeterChargeRef","[StartDate] < #" & yourdate & "# AND [EndDate] > #" & yourdate & "#")

Me!txtUnitCost = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeterChargeRef = " & intMeterChargeRef)







Edited by: ScottGem on Tue May 2 12:08:57 EDT 2006.
mhearnsww
I've already got a Meteredchargetable with the dates in for the whole year, so i dont know why i would need a second table for it.
Otried the code without the 'intMeterChargeRef = DLookup("[MeterChargeRef]","tblMeterChargeRef","[StartDate] < #" & yourdate & "# AND [EndDate] > #" & yourdate & "#") ' and it would display the first record in the table, regardless of what dates the user typed in.
When i did create the table and added the above code in, it came back with an error message saying invalid use of null and pointed to that line.
ScottGem
Does your table follow the structure I suggested?
The reason for the error is that the DLookup is not finding a matching record.
mhearnsww
I've created a table called tblMeteredChargeRef with the following fields.
eterChargeRef - num
StartDate - date/time
EndDate - date/time
The data i've typed in is:
1 - 01/04/2003 - 31/03/2004
2 - 01/04/2004 - 31/03/2005
3 - 01/04/2005 - 31/03/2006
4 - 01/04/2006 - 31/03/2007
Is this correct?
ScottGem
looks correct. So your code should be:
ntMeterChargeRef = DLookup("[MeterChargeRef]","tblMeteredChargeRef","[StartDate] < #" & yourdate & "# AND [EndDate] > #" & yourdate & "#")
Me!txtUnitCost = DLookup("[MeteredVolRate]", "tblMeteredCharge", "MeterChargeRef = " & intMeterChargeRef)
mhearnsww
Where you've got 'yourdate' is that my Me!txtStartOfChargeDate and Me!txtEndOfChargeDate?
ScottGem
Wel now I've been going under the assumption that you had a single date and where testing to see what range it fell into. If you are trying to see if 2 dates fall inside a range, that gets much more complex.
mhearnsww
The one date is fine, i think i was just assuming that both dates had to be used. I tried your code with just using the startdate field and it does bring back the MeteredVolRate for each year.
The only problem is that it brings back the MeteredVolRate for the year ahead.
The data below is the MeteredVolRate for each annual year.
eteredChargeRef StartDate EndDate MeteredVolRate
1 01/04/2003 31/03/2004 0.4838
2 01/04/2004 31/03/2005 0.5933
3 01/04/2005 31/03/2006 0.7129
4 01/04/2006 31/03/2007 0.8392
For example i type in '01/04/2005 and 30/09/2005' and that should bring back 0.7129, but it will return 0.8392 instead and if i type a start date in between '01/04/2003 - 31/03/2004' it gives an error message of 'invalid use of null'
I've done a bit more testing for the year '01/04/2003 - 31/03/2004', and it brings back an error message if i type in the start date of either '01/04/2003' or '02/04/2003' or '03/04/2003' but anything from the '04/04/2003' till the '31/03/2004' it will bring the MeteredVolRate for that year, which is 0.4838.
The same applies for each charge year.
Do you have any suggestions to why it might be doing this?
ScottGem
Yep, Change < to <=.
The Startdate in the table is not less than the start date you entered on the form. Therefore, it picked up the next record.
mhearnsww
Yeah i tried that earlier but it still displayed the year ahead, and for the '01/04/2003 - 31/04/2004' year, it gave an error saying invalid use of null.
Oalso tried <= and >=, < and >=, <= and >.
But none of these worked, they either gave me a syntax error or gave me the wrong MeteredVolRate.
ScottGem
Hmm, not sure what that's not working. Can you post a stripped down version of the app?
mhearnsww
I've attached the database for you.
lick on form 'CopyOfForm12' and then select the account '1111 - Mr Paul Smith - The Cottage', then select the read date of '01/08/2005' in the list box, then click onto create invoice.
Then type in '01/04/2004' for the start date and '30/09/2004' for the end date. If you look into the tblMeteredCharge, you will see for that year the UnitCost should be 0.8733, but on the form it will display 0.7581.
The code that calculates the Unitcost value is on the end of charge date afterupdate event.
ScottGem
You have to test the one date (start date) against both dates in the table. Otherwise its going to show the first record where the stored start date is less that the entered date.
ntMeterChargeRef = DLookup("[MeterChargeRef]", "tblMeteredCharge", "[StartOfChargePeriod] <= #" & Me!txtStartOfChargeDate & "# AND [EndofChargeperiod] >= #" & Me.txtStartOfChargeDate & "#")
mhearnsww
I've done some more testing today and it only just occured to me that maybe the date format is in US dates and not UK dates, so i typed in '01/04/2003 - 30/09/2003' and it came back with an error message saying invalid use of null.
So i therefore changed the dates to '04/01/2003 - 09/30/2003' and it brought back the MeteredVolRate for that year. I then typed in the same dates for years 2004,2005, and 2006 and it brought back the right amount each time.
Is there a way i can change the date format to UK dates and not US?
ScottGem
Your input mask and regional settings should handle that.
mhearnsww
I've already got the input masks on.
mhearnsww
The strange thing about it is that when i type in '04/01/2003 - 09/30/2003' for the start and end date, the correct unit cost is displayed, but the standing charge, which calculates the datediff between those two dates, comes out at 269 days which is wrong.
If i change the date range to '01/04/2003 - 30/09/2003' the unit cost will give an error message saying invalid use of null, but the standing charge will have a value of 180 days, which is correct.
Its as if the standing charge text field is calculating in the UK date format but the unit cost is calculating in US date format.
Ofind this a bit strange.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.