DoCmd.GoToControl

[aziz rasul]

On a form I have two combo boxes (cboReportingMonth, cboReportingYear, cboWorksheets) and a textbox (txtFilename). You can double click the label of the textbox to run some code, which effectively allows the user to select a file and place the name of the file into txtFilename.

The problem I'm having is that if a user carries out the following steps: -

1. enters a value in cboReportingMonth
2. enters a value in cboReportingYear
3. removes value from cboReportingMonth
4. double-clicks the label of txtFilename

In the beginning of the code for the double-click event, I have

DoCmd.GoToControl "cboWorksheets"

This is to check that there is a value in cboReportingMonth and cboReportingYear before allowing a file to be selected. However it doesn't like the code given above?

 

[JamesMcS]

Try setfocus instead of gotocontrol

 

[aziz rasul]

It didn't like that either.

 

[JamesMcS]

If you're just checking there are values in fields before executing your code, why not just use an IF? As in,

If isnull (cboreportingmonth) or isnull(cboreportingyear) then
msgbox "Select a month/year"
Else
[your code here]
Endif

 

[aziz rasul]

File attached.

 

[JamesMcS]

OK, but have you considered my last post?

 

[aziz rasul]

Because if the user removes the value in cboReportingMonth, the original value that was their initially still appears when I do Debug.Print Me.cboReportingMonth, hence the reason to use GoToControl\SetFocus.

 

[JamesMcS]

Your attached DB works fine. If either combo box is null it sets the focus to cboworksheets and tries to open frmtimedmessagebox.

What is it not doing that you want it to do?

 

[aziz rasul]

When I remove the value from cboReportingMonth and go directly to double-clicking the Filename label, then it doesn't work, because it doesn't register that there is no value in cboReportingMonth.

If I remove the value from cboReportingMonth and go to say cboReportingYear, then double-click the Filename label, then it does work as it now knows that there is no value in cboReportingMonth.

Hope that makes sense.

 

[JamesMcS]

It seems to change from null to a zero length string but it doesn't hold the value. That would be crazy!

 

[aziz rasul]

Didn't understand!

 

[JamesMcS]

What I mean is, it doesn't keep the value when you delete it out of the conbo box. Before you enter anything, the value is null. After you enter something, then delete it, it becomes a zero length string. Maybe that's the problem.

 

[JANR]

One way to cover all the bases regarding ZLS and NULLS is to use Nz like this:

Private Sub Text1_AfterUpdate()
If Nz(Me.text1,"") = "" Then
 ' It is empty
End If
End Sub

JR

 

[aziz rasul]

When I debug.Print Nz(Me.text1,"") it simply gives me the original value which happens to be 'January'. I need to capture that cboReportingMonth no longer holds 'January', as I have deleted it, so that my code in the label's double-click can kick in correctly.

 

[JamesMcS]

I don't think you need to worry about NZs here. Which control is text1? We were talking about a combo box earlier on.

I checked it myself - when you first open the form, the combo box values are null (just use "print cbo..." in the immediate window of the VBA editor). Then you select a month. Focus immediately goes to the next combo box, but if you shift tab and delete the value from month, tab away again, and print the value in the immediate window it becomes a zero length string. I think you need to capture that in your if statements.

 

[aziz rasul]

JANR used text1, so I was referring to that as his\her example.

The problem is that I can't prevent the end user, who will be using the db, from carrying the steps in the sequence I wrote earlier. If I tabbed to the next control then it's no problem but Joe Public might not do that.

 

[JANR]

Aziz

My code was ment only as an example and was not ment to be used as written. Text1 was just a controlname I made up.

You have to always retype controlnames as what you are currently using.

JR

 

[aziz rasul]

I understand.

Seems to me that when I delete the contents of cboReportingMonth and then dbl-clk the label I'm effectively leaving unfinished business, hence the issues I'm having. If I have a value in cboReportingMonth then the SetFocus code works, but if I delete the contents of cboReportingMonth, then it doesn't like it as I guess I have not cleaned up after myself before double-clicking the label.

 

[aziz rasul]

A suggestion from another forum led me to the following solution.

    Dim strControlName As String
    Dim ctl As Control

    Set ctl = Screen.ActiveControl
    
    strControlName = ctl.Name

    If strControlName = "cboReportingMonth" Then
        If Me.cboReportingMonth.Text = "" Then
            Me.lblReportingMonth.ForeColor = 255
            Me.lblReportingYear.ForeColor = 0            
            Exit Sub
        End If
    ElseIf strControlName = "cboReportingYear" Then
        If Me.cboReportingYear.Text = "" Then
            Me.lblReportingMonth.ForeColor = 0
            Me.lblReportingYear.ForeColor = 255
            Exit Sub
        End If
    End If

 

[JamesMcS]

Glad it works. Beer time!