Form not updating after event

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a dropdown box that depends of another drop down box, on the after
update event I have the following code:

Dim sMajorAccount As String
sMajorAccount = "SELECT [tblChartofAccounts].[HyperionMajorAccount]" & _
" FROM tblAccountType INNER JOIN tblChartofAccounts ON"
& _
" [tblAccountType].[AccountTypeLabel] =
[tblChartofAccounts].[AccountGroup]" & _
" GROUP BY [tblChartofAccounts].[AccountGroup]," & _
"[tblChartofAccounts].[HyperionMajorAccount],
[tblAccountType].[ID]" & _
" HAVING ((([tblAccountType].[ID])=" &
Me.cboAccountTypeDescription.Value & "));"
Me.cboHyperionMajorAccount.RowSource = sMajorAccount
Me.cboHyperionMajorAccount.Requery

It all appears to be fine, but is not populating the dependent dropdown box.
When I click on the row source of the dependent dropdown, I can see the
proper syntax, further more if I click on the elipse, I get the query on
design mode and if I click on data sheet preview, the right data (what i
want) shows. What am I missing?

Michael Arch.
 
Do you have the RowSourceType of the cboHyperionMajorAccount ComboBox set to
Table/Query?
I have a dropdown box that depends of another drop down box, on the after
update event I have the following code:

Dim sMajorAccount As String
sMajorAccount = "SELECT [tblChartofAccounts].[HyperionMajorAccount]" & _
" FROM tblAccountType INNER JOIN tblChartofAccounts ON"
& _
" [tblAccountType].[AccountTypeLabel] =
[tblChartofAccounts].[AccountGroup]" & _
" GROUP BY [tblChartofAccounts].[AccountGroup]," & _
"[tblChartofAccounts].[HyperionMajorAccount],
[tblAccountType].[ID]" & _
" HAVING ((([tblAccountType].[ID])=" &
Me.cboAccountTypeDescription.Value & "));"
Me.cboHyperionMajorAccount.RowSource = sMajorAccount
Me.cboHyperionMajorAccount.Requery

It all appears to be fine, but is not populating the dependent dropdown box.
When I click on the row source of the dependent dropdown, I can see the
proper syntax, further more if I click on the elipse, I get the query on
design mode and if I click on data sheet preview, the right data (what i
want) shows. What am I missing?

Michael Arch.
Click to expand...
 
If the ID field type is text then add a single quote before and after the value

sMajorAccount = "SELECT [tblChartofAccounts].[HyperionMajorAccount]" & _
" FROM tblAccountType INNER JOIN tblChartofAccounts ON" & _
" [tblAccountType].[AccountTypeLabel] = [tblChartofAccounts].[AccountGroup]"
& _
" GROUP BY [tblChartofAccounts].[AccountGroup]," & _
"[tblChartofAccounts].[HyperionMajorAccount], [tblAccountType].[ID]" & _
" HAVING ((([tblAccountType].[ID])='" & Me.cboAccountTypeDescription & "'));"

If that is not the case, create a code break in this line

Me.cboHyperionMajorAccount.RowSource = sMajorAccount

Press F9 when the cursor located in it (you'll see red line)
Run the form and select a value from the first combo, when the code stop,
type in the Immidiate window
?sMajorAccount
And press Enter, the SQL should apear, copy it and paste it in a query, run
it and see what is wrong
 
I did what you said, and when I copy the SQL Statement out of the immediate
window, and paste it in a new query I get the proper results:

HyperionMajorAccount (This is the Table's Field Name) and below is the
records.
AccrdDivPay
AccrdEnviron
AccrdIns
AccrdInt
AccrdPension
AccrdPREmpBen
AccrdProfFees
AccrdSalesAllow
AccrdTaxesIncome
AccrdTaxesOther
AccrdUtilities
AccrdWarranty
AcctsPayTrade
AdvancedPayCust
CapSurplus
CollectAsAgents
CommonShares
CurDefdTaxLiab
CurMatLTDebt
CurrTransAdj
DivPaid
EnvironLiab
EquityAdj
InterCoIntPay
InterCoLongTermPay
InterCoPay
InvestmentAccnt
LoansPayable
LTDebt
LTDefdTaxLiab
OtherLTLiab
OtherPayAccr
PensionLiab
PostEmpBen
PostRetBen
PrefdShares
RetndEarnings
SubToFrParent
TreasuryShares

This is why I'm baffled, I do get the right results when I step it through
but the dropdown still empty.
 
Yes, the RowSourceType of the cboHyperionMajorAccount ComboBox is set to
Table/Query and its corresponding rowsource is being updated every time I
click on the cboAccountTypeDescription dropdown with the proper value. Also,
I stepped it through and copied the result of the sMajorAccount string out of
the immediate window into a new query and I obtain the proper data, but the
dropdown refuses to requery the data.

Michael Arch.
 
Can you post the SQL as you copy it from the Immidiate window, before you put
it in a query?
 
This is what I get as an answer in the immediate window to the statement
sMajorAccount?:

SELECT [tblChartofAccounts].[HyperionMajorAccount] FROM tblAccountType INNER
JOIN tblChartofAccounts ON [tblAccountType].[AccountTypeLabel] =
[tblChartofAccounts].[AccountGroup] GROUP BY
[tblChartofAccounts].[AccountGroup],[tblChartofAccounts].[HyperionMajorAccount], [tblAccountType].[ID] HAVING ((([tblAccountType].[ID])=2));

Thanks,


Michael Arch.
 
ruralguy via AccessMonster.com said:
Do you have the RowSourceType of the cboHyperionMajorAccount ComboBox set
to
Table/Query?
Click to expand...

....or,

any chance the combo's ColumnWidths property is set to 0? That would
effectively hide the single-column query results that you say are fine.


HTH,



I have a dropdown box that depends of another drop down box, on the after
update event I have the following code:

Dim sMajorAccount As String
sMajorAccount = "SELECT [tblChartofAccounts].[HyperionMajorAccount]" &
_
" FROM tblAccountType INNER JOIN tblChartofAccounts
ON"
& _
" [tblAccountType].[AccountTypeLabel] =
[tblChartofAccounts].[AccountGroup]" & _
" GROUP BY [tblChartofAccounts].[AccountGroup]," & _
"[tblChartofAccounts].[HyperionMajorAccount],
[tblAccountType].[ID]" & _
" HAVING ((([tblAccountType].[ID])=" &
Me.cboAccountTypeDescription.Value & "));"
Me.cboHyperionMajorAccount.RowSource = sMajorAccount
Me.cboHyperionMajorAccount.Requery

It all appears to be fine, but is not populating the dependent dropdown
box.
When I click on the row source of the dependent dropdown, I can see the
proper syntax, further more if I click on the elipse, I get the query on
design mode and if I click on data sheet preview, the right data (what i
want) shows. What am I missing?

Michael Arch.
Click to expand...

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
Funny that you mention it, it had 3 different numbers: 0",1",2". So I deleted
them all and re-keyed 2" and that did it. I can't believe I did not notice it
until you pointed it out.

Thank you

I feel bad for wasting your guys time, thanks again

George Nicholson said:
ruralguy via AccessMonster.com said:
Do you have the RowSourceType of the cboHyperionMajorAccount ComboBox set
to
Table/Query?
Click to expand...

....or,

any chance the combo's ColumnWidths property is set to 0? That would
effectively hide the single-column query results that you say are fine.


HTH,



I have a dropdown box that depends of another drop down box, on the after
update event I have the following code:

Dim sMajorAccount As String
sMajorAccount = "SELECT [tblChartofAccounts].[HyperionMajorAccount]" &
_
" FROM tblAccountType INNER JOIN tblChartofAccounts
ON"
& _
" [tblAccountType].[AccountTypeLabel] =
[tblChartofAccounts].[AccountGroup]" & _
" GROUP BY [tblChartofAccounts].[AccountGroup]," & _
"[tblChartofAccounts].[HyperionMajorAccount],
[tblAccountType].[ID]" & _
" HAVING ((([tblAccountType].[ID])=" &
Me.cboAccountTypeDescription.Value & "));"
Me.cboHyperionMajorAccount.RowSource = sMajorAccount
Me.cboHyperionMajorAccount.Requery

It all appears to be fine, but is not populating the dependent dropdown
box.
When I click on the row source of the dependent dropdown, I can see the
proper syntax, further more if I click on the elipse, I get the query on
design mode and if I click on data sheet preview, the right data (what i
want) shows. What am I missing?

Michael Arch.
Click to expand...

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
Click to expand...
 
Great catch George!

George said:
Do you have the RowSourceType of the cboHyperionMajorAccount ComboBox set
to
Table/Query?
Click to expand...

...or,

any chance the combo's ColumnWidths property is set to 0? That would
effectively hide the single-column query results that you say are fine.

HTH,
I have a dropdown box that depends of another drop down box, on the after
update event I have the following code: [quoted text clipped - 23 lines]

Michael Arch.
Click to expand...
Click to expand...
Click to expand...
 
Ok.

This is how this happened. I started building the dropdown using the wizard,
and selecting all of the fields on the table; the wizard has an option
pre-checked that reads: "Hide key column(recommended)"; this will place the
0";1";2" on the column width depending on the number of fields being queried.
When I substituted the row source using the code, the formatting options
remained the same.

Live and Learn. . . .

Thanks again to all of you for putting up with a silly question.

Michael Arch.

ruralguy via AccessMonster.com said:
Great catch George!

George said:
Do you have the RowSourceType of the cboHyperionMajorAccount ComboBox set
to
Table/Query?
Click to expand...

...or,

any chance the combo's ColumnWidths property is set to 0? That would
effectively hide the single-column query results that you say are fine.

HTH,
I have a dropdown box that depends of another drop down box, on the after
update event I have the following code:
Click to expand...
[quoted text clipped - 23 lines]
Michael Arch.
Click to expand...
Click to expand...

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
Click to expand...
 
Back
Top