Replies

Personally, I’d just set the row source of the second combo in the after update of the first combo rather than looking through the recordset and using AddItem. Either that or just use a reference to the first combo in the rowsource you create at design time.

If you have the second setup with three columns then the following should work (note – as StateText is a string you need to surround it with double quotes. Correction, now that I see you’ve used an ID for the state, you can forget the double quotes, sorry but a more appropriate name for the variable would be lngState as it’s a long integer.

Code:

SQLString = "SELECT tblClient.Surname, tblClient.FirstName, tblClient.State" & _
" FROM tblClient INNER JOIN tblState ON tblClient.State = tblState.StateID" & _
" WHERE tblState.StateID =" & lngState
Me.ctrlClient.RowSource = strSQL

and no, you don’t need the “;” on the end

Marty,

I’ve tested double click in a DS subform with a simple msgbox and it seems to work OK even if there is code in the parent form’s double click.

When you say the events don’t fire, have you put a break point in to test that the event is firing as opposed to the code not doing anything?

Hi Chuck,

I’m not having the same issue dragging the DS onto another form, I guess you’ve set the default view to ‘datasheet’ and that’s the only thing I can see that determines how it displays. There is no subform property that changes its behaviour.

As for the second, you can still create DS forms the old way using Create>From Design. The method you used here where you selected Create>More Forms> Datasheet creates a form where the controls act differently.

The labels are part of the control and determine the column header (no difference there) but you can’t delete them. All the controls are the same width and evenly spaced but here is the ‘useful’ bit – you can drag a control up or down and Access automatically rearranges all the other controls so it fits nicely and they are all equally spaced. This also changest the order of the columns in the DS view.

So you can still use the old way but once you get used to then new DS you will find it quite nice.

One thing that still annoys me is that setting the controls visible property to false doesn’t stop it showing in DS view. You need to set its ColumnHidden property in the open event.

and as for running multiple versions, yes as long as you install them in version order.

The install allows you to elect to keep previous versions.

However when you start a different version there is a bit of reinstalling that goes on.

but apart from that they seem to play nice together.

A2007 loses the developer extensions when you go to a previous version but it’s easy enough to get that back.

But I can’t say I’ve needed to use any of the previous versions since I installed 2010.

I now do all my dev in 2010 but test releases for clients running earlier versions in VMs.

Maybe I misunderstood issue.
I can see you code changes the Button Caption, while ExportSeminarBooking is running,
but it doesn’t actually terminate whatever is running in ExportSeminarBooking or does it?

Code:

  DoEvents
                If ctlCancel.Caption = "Cancelling" Then
                    MsgBox "User Cancelled", vbOKOnly, "Seesion Output"
                    ctlCancel.Caption = "Session Output"
                    GoTo Exit_Procedure
                End If

you can see in the above code that when it sees the user cancelled if sets the caption back to where it was in the start and exits the procedure

Sure Andrew, you can’t call the same procedure to stop it but you can use the same button.
The original code I posted does work.
Setting the caption when clicking on the button like this

Code:

    Set ctl = Screen.ActiveControl
    Select Case ctl.Caption
        Case "Session Output"
            ctl.Caption = "Cancel"
            DoEvents
            ExportSeminarBooking
        Case "Cancel"
            ctl.Caption = "Cancelling"
        Case Else
    End Select

just seems that I had some sort of lockup that prevented me clicking on the button until I restarted.

So if I call a sub then the calling code continues, but if i call a funtion it must wait to get the return value before it can continue.

Does that sound right?

Just shutdown, reopended the app and it now works fine.
Thanks

Thanks John but I think the problem is that the click event gives over control to the procedure it calls and waits for the procedure to finish before the control returns to the calling procedure and therefore form. so you can’t click the button whle the procedure is running.

It’s kinda like when you open a form in code

docmd.openform “myform”
‘ do more stuff …

the ‘ do more stufff runs and you can return to the form

but if you open “myform’ modally then code stops until you close “myform”
and you can’t do anything to the first form until you close “MyForm”

So I guess I want to call the procedure in such a way that the calling procedure doesn’t wait for it to finish before continuing.

I think the theory is that a Null value means that a value has not yet been selected, so if a default has selected a value for you then you should not revert to Null.

Even so I would then think a zero length string should be allowed.

As Access developers we have been using (and in purely theoretical terms, abusing) Nulls for years and Access is very cooperative in what it lets us do. In other languages Nulls are not so forgiving.

John’s answer to change the default in the onchange should work in most situations where you only want the default when the form is first opened.

You really would think that with a change this significant it should have been announced in the State of the Union. But seriously, does MS publish full release notes for Access somewhere apart from the PR blurbs?

Another note on filtering with combos – If you have a two column thing going on with the first column hidden then you can use a union query to append an “all” to the top –

SELECT “%”, “All” FROM tblSelections
UNION
SELECT Selection_ID, Selection_Text FROM tblSelections

so when the user selects “All” the % is passed to the ALike filter.

I’ve given up trying to use Like with the asterisk. Access keeps changing my ‘Like’ to ‘ALike’ but fails to change the * to %

Easier not to fight some things…

What a fabulous new feature! Now all I have to do is figure out a work around…

The value has returned before the before update event occurs but, strangely, the text property is still an empty string.

So if you need to dynamically create an SQL string then you might try doing it here.

I can’t find a way to defeat it. if i try to set the value back to null, in code, I fail.

Depending on what you are using it for, a (combo box filter perhaps?) with something like –

ALike [Forms]![frmSeminarBooking]![cboDay] & “%”

in the following combo’s rowsource

you can use a command button titled “All” to set the value of the recalcitrant control to “%” which will give you the required outcome but you can’t set it to null using the button.

and yes you can manually enter % but try telling a user that.

Nice, thanks John and Andrew. and Luke

an even simpler solution is to replace

Iif(isNull([Nickname]) or ([nickname]=””) …

with

iif([NickName] & “” = “” …

when you concatenate [Nickname] to an empty string the result is an empty string if [Nickname] is either an empty string or a null.
the following are both true

Null & “” = “”
“” & “” = “”

same result, just shorter

Thanks John,

Did they stay fixed if you closed the db and reopened if?

Kent