You cancelled the previous action error on certain combo box selec

[Guest]

Hey,

I have set up a form that uses combo boxes to pull out information from a
linked excel table-

I have in total 14 combo boxes. - The first seven are pulling out text
formatted data. The eight combo simply pulls numeric data. I then have 3
pairs of combo boxes for range selections. The first a simple numeric field.
The second for a single date (european formatted) and the final pair for
another single date field.

Now (deep breath recomended ! ) I can use any selection of the first
10 combo boxes to retrieve data (so thats the seven text combo boxes, the
single numeric and range pair numeric) fine! However when I attempt to
select a value from the second combo box of either of the two date pair combo
boxes I am presented with the " you cancelled the previous action errror".

Now another twist

I can use these combo date range pairs for selection with the numeric range
combo pair and the single numeric combo fine ( basically combos 8 to 14 work
fine)

Not sure why I cant use my combo date selections in conjunction with my text
combos any ideas

Many thanks
eamonn

 

[SteveS]

Hi Eamonn,

It's hard to give you an answer (remember we can't see your form or code)
without more info.

Have you stepped thru the code to see where (what line) the error occurs?

Do you click a button to start the search after selecting the combo boxes?

It would be easier help if you post the code and the combo box names. And maybe
the form record source.

 

[Guest]

Hi SteveS,

Appols dude,

My code is basically a repetition of the following where all that follows is
just repeated for each field with the "Private Sub Combo40_Change()" changed
for each of the combo boxes. So the code is basically the following for my
form.

"Option Compare Database
Option Explicit


Private Sub Combo40_Change()

Dim QuerySTR As String
Dim GridQry As String
Dim db As Database

Set db = CurrentDb
QuerySTR = ""
GridQry = ""


If (Me.Combo40.Value = "_ALL") Then ' Deal type
QuerySTR = "select * from [sheet1] where ([sheet1].[deal type] <> '' or
[sheet1].[deal type] is not null)"
Else
QuerySTR = "select * from [sheet1] where ([sheet1].[deal type] <> '' or
[sheet1].[deal type] is not null) and [sheet1].[deal type] = '" &
Trim(Me.Combo40.Value) & "'"
End If

If (Me.combo38.Value = "_ALL") Then ' Type of Investor
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[type of investor] = '" &
Trim(Me.combo38.Value) & "'"
End If

If (Me.Combo33.Value = "_ALL") Then ' Investor
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[investor] = '" &
Trim(Me.Combo33.Value) & "'"
End If

If (Me.Combo39.Value = "_ALL") Then 'Country
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[country] = '" &
Trim(Me.Combo39.Value) & "'"
End If

If (Me.Combo28.Value = "_ALL") Then 'Layer of funding
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[Layer of funding] = '" &
Trim(Me.Combo28.Value) & "'"
End If

If (Me.Combo50.Value = "_ALL") Then 'counterparty
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[Counterparty] = '" &
Trim(Me.Combo50.Value) & "'"
End If

If (Me.Combo54.Value = "_ALL") Then 'Deal CCY
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[Deal CCY] = '" &
Trim(Me.Combo54.Value) & "'"
End If

If (Me.Combo56.Value = 0) Then 'AMT CCY
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[LCL CCY ORDERED] = '" &
Val(Trim(Me.Combo56.Value)) & "'"
End If

If ((Me.Combo72.Value = 0) And (Me.Combo74.Value = 0)) Then 'LCL CCY Range
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[LCL CCY ALLOCATED] >= " &
Val(Trim(Me.Combo72.Value)) & " AND [sheet1].[LCL CCY ALLOCATED] <= " &
Val(Trim(Me.Combo74.Value)) & ""
End If

If ((Me.Combo75.Value = #1/1/1900#) And (Me.Combo77.Value = #1/1/1900#))
Then 'DATE Range
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[Deal Date] >= #" &
Trim(Me.Combo75.Value) & "# AND [sheet1].[Deal Date] <= #" &
Trim(Me.Combo77.Value) & "#"
End If

If ((Me.Combo102.Value = #1/1/1900#) And (Me.Combo104.Value = #1/1/1900#))
Then 'Mat Range
QuerySTR = QuerySTR
Else
QuerySTR = QuerySTR + " and [sheet1].[Maturity Date] >= #" &
Trim(Me.Combo102.Value) & "# AND [sheet1].[Maturity Date] <= #" &
Trim(Me.Combo104.Value) & "#"
End If


Me.RecordSource = QuerySTR
Me.Refresh
db.Execute ("Delete from Grid_view")
GridQry = "Insert into Grid_view " & QuerySTR
db.Execute (GridQry)
Me.Sheet1_subform3.Requery

End Sub"

WITH -

"Private Sub Form_Load()

Dim db As Database

Set db = CurrentDb

Me.RecordSource = "select * from [sheet1] where ([sheet1].[deal type] <> ''
or [sheet1].[deal type] is not null)"
Me.Refresh
db.Execute ("Delete from Grid_view")
db.Execute ("Insert into Grid_view select * from [sheet1] where
([sheet1].[deal type] <> '' or [sheet1].[deal type] is not null)")
Me.Sheet1_subform3.Requery

Me.Combo28.Value = "_ALL"
Me.Combo33.Value = "_ALL"
Me.combo38.Value = "_ALL"
Me.Combo39.Value = "_ALL"
Me.Combo40.Value = "_ALL"
Me.Combo50.Value = "_ALL"
Me.Combo54.Value = "_All"
Me.Combo56.Value = 0
Me.Combo72.Value = 0
Me.Combo74.Value = 0
Me.Combo75.Value = #1/1/1900#
Me.Combo77.Value = #1/1/1900#
Me.Combo102.Value = #1/1/1900#
Me.Combo104.Value = #1/1/1900#
End Sub"

at the end of all the end if statments


To answer your question on the buttons I dont have any but I have introduced
a simple reset button

thank you,

Eamonn

 

[Guest]

Hi,

Also my code causing the problem is the code behind the single numeric field
combo box or combo 8 as described below.
The following line is highlighted

Me.RecordSource = QuerySTR

 

[Guest]

Hi Eamonn,

I have a couple more questions.

1) are the combo boxes that deal with CCY numeric? (In the access table, are
the data types of the fields text or number?)

2) For the six combo boxes that deal with "'LCL CCY Range", "DATE Range" and
"Maturity Range" (combos 72/74, 75/77 and 102/104), do you *always* have an
entry for both combo box pair? For example, for the "Date Range" (combo
75/77)- would you ever enter a date in just the combo75?

 

[Guest]

Hi Steve,

Apologies for dely in getting back sooner -have been out sick with a nasty bug

1.) None of my actual combo boxes are formatted at all!- Should they be??
I have formatted the fields in excel and as such they are formatted
correctly in access numeric fields are formatted as numeric, text fields as
text, date fields as date.

2.) Firstly I have default values for all my combo boxes (including my range
pairs) that when selected will ensure no records are brought in.

I always have an entry for both as otherwise the combo pair will fail to
bring in records. For Instance for my date combo pairs the default value is
01/01/1900 which in itself is nonsensical. Now it is not necessary to change
this first default value if I wish to search for say maturity dates up to
31/12/06.

So ye both combo boxes will always have a value selected but one of these
may be the default value which is there automatically when I open the form.

Thanks
Eamonn

 

[Guest]

Hey,

Tried formatting the combo boxes and when I select a a value in one of my
range pair boxes I get a completley blank screen

 

[Guest]

I've been fighting a cold myself....

Setting the format of the combo boxes is some initial data validation. If
you set the format to General Number for the "AMT CCY" combo (combo56), you
won't be able to enter letters/dates. Same with setting the date combos to
Short Date - you can't enter invalid dates.
------

Sorry, one more question. In the Form_Open() code (and the combo40) you have:

Me.RecordSource = "select * from [sheet1] where ([sheet1].[deal type] <> ''
or [sheet1].[deal type] is not null)"


This is checking to see if [sheet1].[deal type] is not equal to the
string

" or [sheet1].[deal type] is not null"


Is this what you want???

 

[Guest]

Eamonn,

Never mind about my question in my previous post.

I would recommend having the code in the click event of a button, rather
than the after update event of each combo box.

But, its your project so I'll do it your way

First off, it was hard for me to keep the combo boxes straight, so I renamed
them. See the code.

Next, you have waaaay to much code. Instead of having identical code in each
of 14 combo boxes, put the code in a sub and call it in the after update
event.

I tested the sql string but I couldn't test that it reutrned the results you
want - but it should be close.

I named the sub that has the "CreateRS" (create record set).

Watch for line wrap. (remember - this is mostly untested!!)

'****** beg code **************
Option Compare Database
Option Explicit

'Layer of funding - Combo28
Private Sub cboFundingLayer_AfterUpdate()
CreateRS
End Sub

' Investor - Combo33
Private Sub cboInvestor_AfterUpdate()
CreateRS
End Sub

'-----------------------

'The rest of the combo boxes (8) Afterupdate event code goes here

'------------------

'Deal DATE Range - Combo75
Private Sub cboDealDateStart_AfterUpdate()
'set the end date to today
Me.cboDealDateEnd = Date
CreateRS
End Sub

'Deal DATE Range - Combo77
Private Sub cboDealDateEnd_AfterUpdate()
CreateRS
End Sub

' Maturity Range - Combo102
Private Sub cboMaturityRangeStart_AfterUpdate()
'set the end date to today
Me.cboMaturityRangeEnd = Date
CreateRS
End Sub

' Maturity Range - Combo104
Private Sub cboMaturityRangeEnd_AfterUpdate()
CreateRS
End Sub


'------------------- MAIN CODE-------------------
Private Sub CreateRS()

Dim QuerySTR As String
Dim GridQry As String
Dim db As Database

Set db = CurrentDb
GridQry = ""

'this is the default
QuerySTR = "Select * From [sheet1] Where ([sheet1].[deal type] <> '' or
[sheet1].[deal type] Is Not Null"

'------These are text------
If Not (Me.cboFundingLayer = "_ALL") And Not Nz(Me.cboFundingLayer, "")
= "" Then 'Layer of funding - Combo28
QuerySTR = QuerySTR & " and [sheet1].[Layer of funding] = '" &
Trim(Me.cboFundingLayer) & "'"
End If

If Not (Me.cboInvestor = "_ALL") And Not Nz(Me.cboInvestor, "") = ""
Then ' Investor - Combo33
QuerySTR = QuerySTR & " and [sheet1].[investor] = '" &
Trim(Me.cboInvestor) & "'"
End If

If Not (Me.cboInvestorType = "_ALL") And Not Nz(Me.cboInvestorType, "")
= "" Then ' Type of Investor - Combo38
QuerySTR = QuerySTR & " and [sheet1].[type of investor] = '" &
Trim(Me.cboInvestorType) & "'"
End If

If Not (Me.cboCountry = "_ALL") And Not Nz(Me.cboCountry, "") = "" Then
'Country - Combo39
QuerySTR = QuerySTR & " and [sheet1].[country] = '" &
Trim(Me.cboCountry) & "'"
End If

If Not (Me.cboDealType = "_ALL") And Not Nz(Me.cboDealType, "") = ""
Then ' Deal type - combo40
QuerySTR = " and [sheet1].[deal type] = '" & Trim(Me.cboDealType) &
"'"
End If

If Not (Me.cboCounterParty = "_ALL") And Not Nz(Me.cboCounterParty, "")
= "" Then 'counterparty - Combo50
QuerySTR = QuerySTR & " and [sheet1].[Counterparty] = '" &
Trim(Me.cboCounterParty) & "'"
End If

If Not (Me.cboDealCCY = "_ALL") And Not Nz(Me.cboDealCCY, "") = "" Then
'Deal CCY - Combo54
QuerySTR = QuerySTR & " and [sheet1].[Deal CCY] = '" &
Trim(Me.cboDealCCY) & "'"
End If

'------These are numbers------
If Not (Nz(Me.cboAmtCCY, 0) = 0) Then 'AMT CCY - Combo56
QuerySTR = QuerySTR & " and [sheet1].[LCL CCY ORDERED] = " &
Val(Trim(Me.cboAmtCCY))
End If

If Not ((Nz(Me.cboLCL_CCY_Start, 0) = 0) And (Nz(Me.cboLCL_CCY_End, 0) =
0)) Then 'LCL CCY Range - Combo72 & Combo74
'original line - using >= and <=
'QuerySTR = QuerySTR & " and [sheet1].[LCL CCY ALLOCATED] >= " &
Val(Trim(Me.cboLCL_CCY_Start)) & " AND [sheet1].[LCL CCY ALLOCATED] <= " &
Val(Trim(Me.cboLCL_CCY_End))
'same line but using BETWEEN
QuerySTR = QuerySTR & " and [sheet1].[LCL CCY ALLOCATED] between " &
Val(Trim(Me.cboLCL_CCY_Start)) & " AND " & Val(Trim(Me.cboLCL_CCY_End))
End If

' here is where you could check that end date is greater than start
date. If not switch the dates before proceding

'------These are dates------
If Not ((Me.cboDealDateStart = #1/1/1900#) And (Me.cboDealDateEnd =
#1/1/1900#)) Then 'Deal DATE Range - Combo75 & Combo77
'QuerySTR = QuerySTR & " and [sheet1].[Deal Date] >= #" &
Trim(Me.cboDealDateStart) & "# AND [sheet1].[Deal Date] <= #" &
Trim(Me.cboDealDateEnd) & "#"
QuerySTR = QuerySTR & " and [sheet1].[Deal Date] between #" &
Trim(Me.cboDealDateStart) & "# AND #" & Trim(Me.cboDealDateEnd) & "#"
End If

'and here also

If Not ((Me.cboMaturityRangeStart = #1/1/1900#) And
(Me.cboMaturityRangeEnd = #1/1/1900#)) Then 'Maturity Range - Combo102
& Combo104
'QuerySTR = QuerySTR & " and [sheet1].[Maturity Date] >= #" &
Trim(Me.cboMaturityRangeStart) & "# AND [sheet1].[Maturity Date] <= #" &
Trim(Me.cboMaturityRangeEnd) & "#"
QuerySTR = QuerySTR & " and [sheet1].[Maturity Date] between #" &
Trim(Me.cboMaturityRangeStart) & "# AND #" & Trim(Me.cboMaturityRangeEnd) &
"#"
End If

' ----for debugging
'MsgBox QuerySTR
' ----for debugging

Me.RecordSource = QuerySTR
Me.Refresh
db.Execute ("Delete from Grid_view")
GridQry = "Insert into Grid_view " & QuerySTR
db.Execute (GridQry)
Me.Sheet1_subform3.Requery

End Sub


Private Sub Form_Load()

Dim db As Database

Set db = CurrentDb


Me.RecordSource = "select * from [sheet1] where ([sheet1].[deal type] <>
'' or [sheet1].[deal type] is not null"
Me.Refresh
db.Execute ("Delete from Grid_view")
db.Execute ("Insert into Grid_view select * from [sheet1] where
([sheet1].[deal type] <> '' or [sheet1].[deal type] is not null")
Me.Sheet1_subform3.Requery

Me.cboFundingLayer = "_ALL"
Me.cboInvestor = "_ALL"
Me.cboInvestorType = "_ALL"
Me.cboCountry = "_ALL"
Me.cboDealType = "_ALL"
Me.cboCounterParty = "_ALL"
Me.cboDealCCY = "_All"

Me.cboAmtCCY = 0
Me.cboLCL_CCY_Start = 0
Me.cboLCL_CCY_End = 0

'you could also use:
'Me.cboDealDateStart = Null
'Me.cboDealDateEnd = Null
'Me.cboMaturityRangestart = Null
'Me.cboMaturityRangeEnd = Null

Me.cboDealDateStart = #1/1/1900#
Me.cboDealDateEnd = #1/1/1900#
Me.cboMaturityRangeStart = #1/1/1900#
Me.cboMaturityRangeEnd = #1/1/1900#

End Sub
'****** end code **************


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I've been fighting a cold myself....

Setting the format of the combo boxes is some initial data validation. If
you set the format to General Number for the "AMT CCY" combo (combo56), you
won't be able to enter letters/dates. Same with setting the date combos to
Short Date - you can't enter invalid dates.
------

Sorry, one more question. In the Form_Open() code (and the combo40) you have:

Me.RecordSource = "select * from [sheet1] where ([sheet1].[deal type] <> ''
or [sheet1].[deal type] is not null)"


This is checking to see if [sheet1].[deal type] is not equal to the
string

" or [sheet1].[deal type] is not null"


Is this what you want???