Combox box not requerying

[bustersports]

Hi,

I am trying to have 2 combo boxes requery after data is selected (ex: cboContractFeeCode is 1st combobox, cboOP is the 2nd, and cboDP is the 3rd). When an item is selected in the 3rd box, it is dispositioned, so it does not appear again in the 3rd box.

After selecting item in 1st box (cboContractFeeCode), I want to have the 2nd box (cboOP) required to show related list, the same for cboDP after selecting field in cboOP. This works for me the 1st time, but if I go back to cboOP (as the items from 1st combo box may not have all been dispositioned), the boxes do not requery unless I manually hit F9. Below is the code. Any help or suggestions would be greatly appreciated.

Thanks.


Private Sub cboContractFeeCode_Click()

Requery
[cboOP].Visible = False
[cboDP].Visible = False
[subfrmSelectCharge1].Visible = False
[tabSelectCharge].Visible = False
[tabAccessorials].Visible = False
[tabRates].Visible = False
[txtShipFromPort].Visible = False
[txtShipToPort].Visible = False
[txtCarrier].Visible = False
[cboOP] = Null
[cboDP] = Null
[cboOP].Requery
[cboOP].Visible = True

End Sub
Private Sub cboDP_Click()

Requery
[subfrmSelectCharge1].Requery
[subfrmSelectCharge1].Visible = True
[subfrmSelectCharge2].Requery
[tabSelectCharge].Visible = True
[tabRates].Visible = True
[tabAir].Requery
[tabAccessorials].Requery
[tabCapitalAir].Requery
[tabCapitalGround].Requery
[tabCapitalOcean].Requery
[tabGround].Requery
[tabOcean].Requery
[tabRail].Requery
[tabRMA].Requery
[txtRateTable].Requery
[tabAccessorials].Visible = True
[txtShipFromPort].Visible = True
[txtShipToPort].Visible = True
[txtCarrier].Visible = True
[subfrmSelectCharge2].SetFocus
[pgeAccessorials].SetFocus
[tabAccessorials].[Form]![Rate ID#].SetFocus

End Sub

Private Sub cboOP_Click()

Requery
[cboDP].Visible = False
[cboDP] = Null
[subfrmSelectCharge1].Visible = False
[tabSelectCharge].Visible = False
[tabAccessorials].Visible = False
[tabRates].Visible = False
[txtShipFromPort].Visible = False
[txtShipToPort].Visible = False
[txtCarrier].Visible = False
[cboDP].Requery
[cboDP].Visible = True

End Sub

 

[MajP]

I would not think the onclick event makes sense. I would think the onenter or possibly onfocus.

 

[bustersports]

Hi MajP,

I tried

- On Click
- On Enter
- On Dirty
- On Change
- On Got Focus
- On Mouse Down
- On Key Down
- On Key Press
- On Enter

They all seem to give me the same result of not updating properly. Any other suggestions?

Thanks.

 

[TheAceMan1]

How are ya bustersports . . .

For starters its the [blue]After Update[/blue] event of a combobox that truly shows a selection was made.

Say you have a combobox whose height is greater than the amount of records to display. This will leave a blank area at the bottom. If you click this area the [blue]On Click[/blue]event will fire but the [blue]After Update[/blue] event will not, as no selection has been made. This is equivalent to a flase positive. Your working under the premise that the [blue]On Click[/blue] event qualifies a selection has been made. This is not so. I hope you see this!

What do you mean by [blue]dispositioned?[/blue]

I also noticed your not referencing your main form properly. You have a lone [blue]Requery[/blue] in the beginning code of each combobox. [blue]Requery What?[/blue] If this requery was meant for the mainform (where the comboboxes are), you need to append the form reference [blue]Me[/blue] as in [blue]Me.Requery[/blue]. Either way, I'd like to know the point of this requery? The same goes for your other controls save the subform referencing. Like: [blue]Me.[cboDP][/blue], [blue]Me.[txtCarrier][/blue], .... The point is access doesn't know if [blue][cboDP][/blue] is a control or variable and has to go on the hunt to find out.

Also, brackets [blue][][/blue] are not required unless a name has spaces in it!

In your next post it would be a great help if you supplied the [blue]Row Sources[/blue] of the combo's.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[bustersports]

Hi The AceMan1,

Thanks for the response. Let me try and explain better what I am attempting to do.

A user will filter down to the record they want to disposition (approve or disapprove by clicking complete). When they go back to the combobox to select another filtered record, I want the combobox to requery thereby letting them know if there are any records left.

The sequence for filtering is:
1. Select Fee Code (cboContractFeeCode) - SELECT qrySelectCharge.ContractFeeCode, qrySelectCharge.ClaimToBeFiled, qrySelectCharge.Complete FROM qrySelectCharge GROUP BY qrySelectCharge.ContractFeeCode, qrySelectCharge.ClaimToBeFiled, qrySelectCharge.Complete HAVING (((qrySelectCharge.ClaimToBeFiled)=0) AND ((qrySelectCharge.Complete)=0)) ORDER BY qrySelectCharge.ContractFeeCode;

2. Select Origination Point (cboOP) - SELECT tblFreightOverchargeByShipment.ContractFeeCode, tblFreightOverchargeByShipment.ShipFromPort, tblFreightOverchargeByShipment.Complete, tblFreightOverchargeByShipment.Carrier, [City] & ", " & [tblAirportCodes].[Country] AS CityName, tblFreightOverchargeByShipment.ShipFromPort, tblFreightOverchargeByShipment.ShipFromCountryCode, tblCountryAbbreviations.TwoLetterAbbr FROM (tblFreightOverchargeByShipment INNER JOIN tblAirportCodes ON tblFreightOverchargeByShipment.ShipFromPort=tblAirportCodes.AirportCode) INNER JOIN tblCountryAbbreviations ON tblFreightOverchargeByShipment.tblOrigTwoLetterID=tblCountryAbbreviations.tblCountryAbbreviationsID GROUP BY tblFreightOverchargeByShipment.ContractFeeCode, tblFreightOverchargeByShipment.ShipFromPort, tblFreightOverchargeByShipment.Complete, tblFreightOverchargeByShipment.Carrier, [City] & ", " & [tblAirportCodes].[Country], tblFreightOverchargeByShipment.ShipFromPort, tblFreightOverchargeByShipment.ShipFromCountryCode, tblCountryAbbreviations.TwoLetterAbbr HAVING (((tblFreightOverchargeByShipment.ContractFeeCode)=Forms!frmSelectCharge!cboContractFeeCode) And ((tblFreightOverchargeByShipment.Complete)=0)) ORDER BY tblFreightOverchargeByShipment.ContractFeeCode;

3. Select Destination Point (cboDP) - SELECT tblFreightOverchargeByShipment.ContractFeeCode, tblFreightOverchargeByShipment.ShipFromPort, tblFreightOverchargeByShipment.ShipToPort, tblFreightOverchargeByShipment.Complete, [City] & ", " & [tblAirportCodes].[Country] AS CityName, tblFreightOverchargeByShipment.ShipToPort, tblFreightOverchargeByShipment.ShipToCountrycode, tblCountryAbbreviations.TwoLetterAbbr FROM (tblAirportCodes INNER JOIN tblFreightOverchargeByShipment ON tblAirportCodes.AirportCode=tblFreightOverchargeByShipment.ShipToPort) INNER JOIN tblCountryAbbreviations ON tblFreightOverchargeByShipment.tblDestTwoLetterID=tblCountryAbbreviations.tblCountryAbbreviationsID GROUP BY tblFreightOverchargeByShipment.ContractFeeCode, tblFreightOverchargeByShipment.ShipFromPort, tblFreightOverchargeByShipment.ShipToPort, tblFreightOverchargeByShipment.Complete, [City] & ", " & [tblAirportCodes].[Country], tblFreightOverchargeByShipment.ShipToPort, tblFreightOverchargeByShipment.ShipToCountrycode, tblCountryAbbreviations.TwoLetterAbbr HAVING (((tblFreightOverchargeByShipment.ContractFeeCode)=Forms!frmSelectCharge!cboContractFeeCode) And ((tblFreightOverchargeByShipment.ShipFromPort)=Forms!frmSelectCharge!cboOP) And ((tblFreightOverchargeByShipment.Complete)=0)) ORDER BY tblFreightOverchargeByShipment.ContractFeeCode;

I did update the Me statements per your recommendation, thanks.

To summarize by an example.

1. I select Contract Fee Code
2. I select Origination Point (there will be many to select from)
3. I select Destination Point (there could be many to select from)
4. After I have determined if I want to approve or disapprove a record and am finished with it, I click complete field on the record
5. When I go back to the Destination Point combo, I want it to be updated to show only non-completed records. If there are none left for that Origination to Destination pair, then it should show a blank (this happens if I manually select F9 key). The same update should happen on the Origination city in the event all have been completed.

I hope this is more clear, again thanks for the help.

 

[TheAceMan1]

bustersports . . .

When you disposition a record [blue]make sure the record is saved[/blue] before you requery.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]