If textbox visible and blank setfocus, msgbox enter date not working 2

[Triacona]

Dear All,

I have a frustrating problem.
I want to check if the text box is visible
if it is not I just want it to output to excel.
If it is visible then I want it to check if it is blank.
If it is blank I want it to output a msgbox
The code is below:

Dim stDocName As String
    stDocName = ListBcReports
        
    If txtStartDate.Visible And txtStartDate.Enabled = True Then
        txtStartDate.SetFocus
        
        If txtStartDate.Text = "" Then
                 
            DoCmd.RunMacro "MsgBoxNoDate"
                
        Else:  DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, ListBcReports & ".xls", True
        End If
       
    Else: DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, ListBcReports & ".xls", True
               
    End If

the error message I get when the text box is disabled and not visible is:

You can't reference a property or method for a control unless the control has the focus

Any help would really be much appreciated.

Kind regards
Triacona

 

[randy700]

How about this (not tested)

Dim stDocName As String
stDocName = ListBcReports
       
If txtStartDate.Visible And txtStartDate.Enabled Then
    If txtStartDate.Text = "" Or IsNull(txtStartDate) Then
        DoCmd.RunMacro "MsgBoxNoDate"
    Else
        DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, ListBcReports & ".xls", True
    End If
Else 
    DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, ListBcReports & ".xls", True
End If

Randy

 

[dhookom]

You don't have to set the focus to find the value of a text box. Typically you should always be able to use the default property of the text box which is the Value property.

        txtStartDate.SetFocus
        
        If txtStartDate.Text = "" Then

        [green]'txtStartDate.SetFocus[/green]
        
        If IsNull(txtStartDate) Then

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya Triacona . . .

My take is as follows:

[blue]   Dim SD As Control
   
   Set SD = Me.Controls("txtStartDate")
   
   If SD.Visible Then
      If Trim(SD & "") = "" Then
         MsgBox "Your Mrssah"
      End If
   Else
      [green]'output to Excel[/green]
   End If[/blue]

See Ya! . . . . . .

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

 

[Triacona]

Dear Duane and Randy,

Thank you very much!!

The IsNull(txtStartDate) works perfectly!

I have created an output query button with the same code except the command:

DoCmd.OutputTo acOutputQuery, stDocName, acFormatXLS, ListBcReports & ".xls", True

That works in all instances.

I have a new problem though, the button that outputs the report to excel with the code:

 Dim stDocName As String
    stDocName = ListBcReports
        
    If txtStartDate.Visible And txtStartDate.Enabled = True Then
               
        If IsNull(txtStartDate) Then
                 
            DoCmd.RunMacro "MsgBoxNoDate"
                
        Else:  DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, ListBcReports & ".xls", True
        End If
       
    Else: DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS, ListBcReports & ".xls", True
               
    End If

It works for the items in the list (I use a case statement),
that enable and make visible the txtStartDate textbox, BUT it does not work for the item in the list that disables and make hides txtStartDate.

I have tested it in two ways:
1. I opened Access and the form and then clicked on the list item that disables and hides txtStartDate and clicked on the command button that outputs the report to excel.

I get the following error:

Application-defined or object-defined error

It is very strange as it works with the output query to excel command??

If I have run any of the other output query/report to excel, those other query/reports output to excel fine.(txtStartDate = visible and enabled)

Then after running these queries/reports, I decided to try running the ouput report to excel command for the list item that hides and disables the txtStartDate textbox and it gives me another error:

The file '|' already exists.

??
There is no file and that is not the file name it is meant to be?
(The list item choosen is AllWorksInProgress)

Any further help would be greatly appreciated.

Kind regards

Triacona

 

[Triacona]

Dear All,

I have sorted the problem out...
It is the query.
I have a function -> OneLineReplace.

It replaces all the carriage returns with ", "(comma and space)

This function was causing the problem, as soon as I took it out it worked.

Due to one record not having an address, it had an error in the report, which translated into the output command not working.

Thanks for all your help!

 

[PHV]

This function was causing the problem
Why not posting the code of this function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Here are the functions:

Option Compare Binary
         ' Otherwise, the function will replace spaces with percent signs.
Option Explicit
                    
 '============================================================
      'The following function will:
      ' - Find the tabs in a Text or Memo field.
      ' - Call another function to replace the tabs.
      '============================================================

      Function FindTabs(WhichField As String) As String
         Dim x As Integer, strText As String
         Dim start As Integer
            start = 1
            x = 1
            strText = WhichField

            Do Until x = 0
               ' Chr(13) is the carriage return character.
               ' Replace Chr(9) with the ANSI code for the character
               ' you are searching for.
               x = InStr(start, strText, Chr(13))
               start = x + 1
               If x > 0 And Not IsNull(x) Then
                  strText = ReplaceTabs(x, strText)
               End If
            Loop

            FindTabs = strText
      End Function

      '==================================================================
      ' The following function is called from the FindTabs() function. It
      ' accepts two arguments, strText and start. The function replaces
      ' tabs with %. It returns the updated text.
      '==================================================================

      Function ReplaceTabs(start As Integer, strText As String) As String
         ' Replace % with the character you want to substitute.
         Mid(strText, start, 1) = ", "
         ReplaceTabs = strText
      End Function
                    
     Function OneLineReplace(strText As String) As String
         ' Replace % with the character you want to substitute.
         OneLineReplace = Replace(strText, Chr(13), ", ")
         
      End Function

It may be that because one of the address records is blank it doesn't know what to do with it...

What do you think?

Kind regards
Triacona

 

[PHV]

...
Function FindTabs(WhichField As [!]Variant[/!]) As String
[!]If Trim(WhichField & "") = "" Then Exit Function[/!]
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

I have changed String to variant in the procedure.

I tried to put the If statement after or before every line in the FindTabs function - no joy...

Where should I insert the If statement within these functions??

Any help would be greatly appreciated

Kind regards
Triacona

 

[PHV]

no joy...
Any chance you could elaborate ?
What happens ?
Error message ? Unexpected behaviour ? Computer crash ? ... ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Thanks for the response
The error message I get when running the report by double clicking on the report is:

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

I know why this happens...
The report's sorting/grouping has the ADDRESS field (which I have renamed site) as a sorting group with a header (with the field in it), and the settings keep together.

The reason it can't sort by the address is because of the function, which replaces the tabs and carriage returns with a comma space ( ,).

One of the records does not have an address and therefore in the query (which runs) it just displayes:

error

If I remove the function from the query the report works.

So my dilema is this: the report will not run if the function is existant in the query; so if I remove the function it looks messy; if I keep it and the user has made a mistake with the address then the report does not generate.

Any help would really be appreciated.

Kind regards

Triacona

 

[dhookom]

What is your current function? Did you implement the changes suggested by PH?

Have you tried running your function(s) in the immediate window with various values?

Duane
Hook'D on Access
MS Access MVP

 

[Triacona]

Hi Duane,

Thanks for your response
I am running the function as above...
I have implemented PHV's changes.
I changed

Function FindTabs(WhichField As String)

TO

Function FindTabs(WhichField As Variant)

I then tried to implement the if statement that PHV suggested...
This is where I get stuck...
I have implemented it in several places, then tried after every line of code and it ignored it...
I don't know how to modify the function to fit in this IF statement.

Thanks for all the help, it is greatly appreciated

Kind regards

Triacona

 

[dhookom]

I was hoping you would provide your modified code similar to what you posted on 26 May 10 5:18.

If your issue occurs when you run the function as part of a query, what is the SQL of the query? Did you try limit the records in the query so you can identify which values run through your function ok and which ones don't?

Duane
Hook'D on Access
MS Access MVP