Checking for Null & Select Case

I have a form called fSearchforCompany with two unbound fields.  After
selecting a field from lstQuickSearch (list box) and tabbing to txtSearchBox
to enter data, the correct form should open with the text from txtSearchBox
entered into the designated field.  But, first the code should check for null,
and if Null, open the correct form.

The problems are the code is not doing anything on Null and when there is
data it is not selecting the data.

The two fields are:

     1.  lstQuickSearch
         ControlSource is empty
         Row Source = 1;"Company ID";2;"Company Name";3;"Date of
Event";4;"Event Type"
         Row Type = Value List

     2.  txtSearchBox
         Control Source is empty
         After Update Event code is listed below:


     Private Sub txtSearchBox_AfterUpdate()

     On Error GoTo ErrorHandler     

        Select Case Me.lstQuickSearch.Value

            Case 1:
                 If IsNull(txtSearchBox) Then
                     DoCmd.OpenForm ("[frmListforCompanyID")
                     DoCmd.GoToControl ("cboSearchBox")
                 Else
                     DoCmd.OpenForm ("frmListforCompanyID")
                     DoCmd.GoToControl ("cboSearchBox")
                     Forms!frmListforCompanyID!cboSearchBox.ControlSource =
txtSearchBox
                 End If
            Case 2:
                 If IsNull(txtSearchBox) Then
                     DoCmd.OpenForm ("frmListforCompanyName")
                     DoCmd.GoToControl ("cboSearchBox")
                 Else
                     DoCmd.OpenForm ("frmListforCompanyName")
                     DoCmd.GoToControl ("cboSearchBox")
                     Forms!frmListforCompanyName!cboSearchBox.ControlSource =
txtSearchBox
                 End If
            Case 2:
                 If IsNull(txtSearchBox) Then
                     DoCmd.OpenForm ("fdlgEventDetail")
                     DoCmd.GoToControl ("txtEventDate")
                 Else
                     DoCmd.OpenForm ("fdlgEventDetail")
                     DoCmd.GoToControl ("txtEventDate")
                     Forms!fdlgEventDetail!txtEventDate.ControlSource =
txtSearchBox
                 End If
            Case Else
                MsgBox "Invalid selection", vbExclamation
                Exit Sub
         End Select

     CleanUpAndExit:
         Exit Sub

     ErrorHandler:
         Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
             "Description:  " & Err.Description & vbCrLf & _
             "Error Number:  " & Err.Number, , "Error")
         Resume CleanUpAndExit

     End Sub

Thanks for any help!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/4/2010 8:54:19 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

14 Replies
1421 Views

Similar Articles

[PageSpeed] 51

The problem is with this line:
Forms!frmListforCompanyID!cboSearchBox.ControlSource =
txtSearchBox


The ControlSource property is used to tell access what field in the table to 
save the data in.

Instead just set the value for the combo like this:
Forms!frmListforCompanyID!cboSearchBox = txtSearchBox

Whatever is the value for txtSearchBox needs to be in the Row Source of 
cboSearchBox.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"lmcc via AccessMonster.com" <u59647@uwe> wrote in message 
news:a90a24f642615@uwe...
>I have a form called fSearchforCompany with two unbound fields.  After
> selecting a field from lstQuickSearch (list box) and tabbing to 
> txtSearchBox
> to enter data, the correct form should open with the text from 
> txtSearchBox
> entered into the designated field.  But, first the code should check for 
> null,
> and if Null, open the correct form.
>
> The problems are the code is not doing anything on Null and when there is
> data it is not selecting the data.
>
> The two fields are:
>
>     1.  lstQuickSearch
>         ControlSource is empty
>         Row Source = 1;"Company ID";2;"Company Name";3;"Date of
> Event";4;"Event Type"
>         Row Type = Value List
>
>     2.  txtSearchBox
>         Control Source is empty
>         After Update Event code is listed below:
>
>
>     Private Sub txtSearchBox_AfterUpdate()
>
>     On Error GoTo ErrorHandler
>
>        Select Case Me.lstQuickSearch.Value
>
>            Case 1:
>                 If IsNull(txtSearchBox) Then
>                     DoCmd.OpenForm ("[frmListforCompanyID")
>                     DoCmd.GoToControl ("cboSearchBox")
>                 Else
>                     DoCmd.OpenForm ("frmListforCompanyID")
>                     DoCmd.GoToControl ("cboSearchBox")
>                     Forms!frmListforCompanyID!cboSearchBox.ControlSource =
> txtSearchBox
>                 End If
>            Case 2:
>                 If IsNull(txtSearchBox) Then
>                     DoCmd.OpenForm ("frmListforCompanyName")
>                     DoCmd.GoToControl ("cboSearchBox")
>                 Else
>                     DoCmd.OpenForm ("frmListforCompanyName")
>                     DoCmd.GoToControl ("cboSearchBox")
>                     Forms!frmListforCompanyName!cboSearchBox.ControlSource 
> =
> txtSearchBox
>                 End If
>            Case 2:
>                 If IsNull(txtSearchBox) Then
>                     DoCmd.OpenForm ("fdlgEventDetail")
>                     DoCmd.GoToControl ("txtEventDate")
>                 Else
>                     DoCmd.OpenForm ("fdlgEventDetail")
>                     DoCmd.GoToControl ("txtEventDate")
>                     Forms!fdlgEventDetail!txtEventDate.ControlSource =
> txtSearchBox
>                 End If
>            Case Else
>                MsgBox "Invalid selection", vbExclamation
>                Exit Sub
>         End Select
>
>     CleanUpAndExit:
>         Exit Sub
>
>     ErrorHandler:
>         Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
>             "Description:  " & Err.Description & vbCrLf & _
>             "Error Number:  " & Err.Number, , "Error")
>         Resume CleanUpAndExit
>
>     End Sub
>
> Thanks for any help!
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1
> 


0
Jeanette
6/4/2010 10:35:15 PM
Thanks Jeanette,

After it select the text and go to the form and cboSearchBox control, the
cobSearchBox After Update Event does not work.  I have to remove the text and
reenter it and then it searches.

Also, the Null is not working.  I had to enter a space and then it opened the
form and so on.

Your thoughts on what's causing it?

Jeanette Cunningham wrote:
>The problem is with this line:
>Forms!frmListforCompanyID!cboSearchBox.ControlSource =
>txtSearchBox
>
>The ControlSource property is used to tell access what field in the table to 
>save the data in.
>
>Instead just set the value for the combo like this:
>Forms!frmListforCompanyID!cboSearchBox = txtSearchBox
>
>Whatever is the value for txtSearchBox needs to be in the Row Source of 
>cboSearchBox.
>
>Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>>I have a form called fSearchforCompany with two unbound fields.  After
>> selecting a field from lstQuickSearch (list box) and tabbing to 
>[quoted text clipped - 74 lines]
>>
>> Thanks for any help!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/4/2010 10:53:25 PM
The after update won't work because you used code to select the value for
cboSearchBox.
You can use code to make the after update event fire.

On the load event you can go:
Call cboSearchBox_AfterUpdate


Null not working, debug it like this

Debug.Print Me.txtSearchBox
If IsNull(Me.txtSearchBox) Then
                     DoCmd.OpenForm ("[frmListforCompanyID")
                     DoCmd.GoToControl ("cboSearchBox")
                 Else
                     DoCmd.OpenForm ("frmListforCompanyID")
                     DoCmd.GoToControl ("cboSearchBox")
                     Forms!frmListforCompanyID!cboSearchBox.ControlSource =
txtSearchBox
End If

Run the code with the form in normal view, then open the immediate window to 
see what value access gets for Me.txtSearchBox.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia




"lmcc via AccessMonster.com" <u59647@uwe> wrote in message
news:a90b2f2007c05@uwe...
> Thanks Jeanette,
>
> After it select the text and go to the form and cboSearchBox control, the
> cobSearchBox After Update Event does not work.  I have to remove the text
> and
> reenter it and then it searches.
>
> Also, the Null is not working.  I had to enter a space and then it opened
> the
> form and so on.
>
> Your thoughts on what's causing it?
>
> Jeanette Cunningham wrote:
>>The problem is with this line:
>>Forms!frmListforCompanyID!cboSearchBox.ControlSource =
>>txtSearchBox
>>
>>The ControlSource property is used to tell access what field in the table
>>to
>>save the data in.
>>
>>Instead just set the value for the combo like this:
>>Forms!frmListforCompanyID!cboSearchBox = txtSearchBox
>>
>>Whatever is the value for txtSearchBox needs to be in the Row Source of
>>cboSearchBox.
>>
>>Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>>
>>>I have a form called fSearchforCompany with two unbound fields.  After
>>> selecting a field from lstQuickSearch (list box) and tabbing to
>>[quoted text clipped - 74 lines]
>>>
>>> Thanks for any help!
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1
>



0
Jeanette
6/5/2010 3:51:48 AM
I changed the code to:

  Select Case Me.lstQuickSearch.Value   
       Case 1:
            If Trim([txtSearchBox] & " ") = "" Then
                DoCmd.OpenForm ("[frmListforCompanyID")
                DoCmd.GoToControl ("cboSearchBox")
            Else
                DoCmd.OpenForm ("frmListforCompanyID")
                DoCmd.GoToControl ("cboSearchBox")
                Forms!frmListforCompanyID!cboSearchBox = txtSearchBox
            End If
       Case 2:
            If Trim([txtSearchBox] & " ") = "" Then
                DoCmd.OpenForm ("frmListforCompanyName")
                DoCmd.GoToControl ("cboSearchBox")
            Else
                DoCmd.OpenForm ("frmListforCompanyName")
                DoCmd.GoToControl ("cboSearchBox")
                Forms!frmListforCompanyName!cboSearchBox = txtSearchBox
            End If
       Case 3:
            If Trim([txtSearchBox] & " ") = "" Then
                DoCmd.OpenForm ("fdlgEventDetail")
                DoCmd.GoToControl ("txtEventDate")
            Else
                DoCmd.OpenForm ("fdlgEventDetail")
                DoCmd.GoToControl ("txtEventDate")
                Forms!fdlgEventDetail!txtEventDate = txtSearchBox
            End If
       Case Else
           MsgBox "Invalid selection", vbExclamation
           Exit Sub
    End Select

Now the Null situation is taken care of.

The only problem is the selected text in txtSearchBox when put into
cboSearchBox won't search on the data; I have to delete it and type it back
in and then the search works.  That's what I was trying to use the Else
statement for, so I would not have to retype the entry.

Jeanette Cunningham wrote:
>The after update won't work because you used code to select the value for
>cboSearchBox.
>You can use code to make the after update event fire.
>
>On the load event you can go:
>Call cboSearchBox_AfterUpdate
>
>Null not working, debug it like this
>
>Debug.Print Me.txtSearchBox
>If IsNull(Me.txtSearchBox) Then
>                     DoCmd.OpenForm ("[frmListforCompanyID")
>                     DoCmd.GoToControl ("cboSearchBox")
>                 Else
>                     DoCmd.OpenForm ("frmListforCompanyID")
>                     DoCmd.GoToControl ("cboSearchBox")
>                     Forms!frmListforCompanyID!cboSearchBox.ControlSource =
>txtSearchBox
>End If
>
>Run the code with the form in normal view, then open the immediate window to 
>see what value access gets for Me.txtSearchBox.
>
>Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>> Thanks Jeanette,
>>
>[quoted text clipped - 30 lines]
>>>>
>>>> Thanks for any help!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/5/2010 4:30:41 AM
Would you post the code that you have for the after update event for 
cboSearchBox.
Did you put this code on the load event for frmListForCompanyID?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"lmcc via AccessMonster.com" <u59647@uwe> wrote in message 
news:a90e20cfe3c18@uwe...
>I changed the code to:
>
>  Select Case Me.lstQuickSearch.Value
>       Case 1:
>            If Trim([txtSearchBox] & " ") = "" Then
>                DoCmd.OpenForm ("[frmListforCompanyID")
>                DoCmd.GoToControl ("cboSearchBox")
>            Else
>                DoCmd.OpenForm ("frmListforCompanyID")
>                DoCmd.GoToControl ("cboSearchBox")
>                Forms!frmListforCompanyID!cboSearchBox = txtSearchBox
>            End If
>       Case 2:
>            If Trim([txtSearchBox] & " ") = "" Then
>                DoCmd.OpenForm ("frmListforCompanyName")
>                DoCmd.GoToControl ("cboSearchBox")
>            Else
>                DoCmd.OpenForm ("frmListforCompanyName")
>                DoCmd.GoToControl ("cboSearchBox")
>                Forms!frmListforCompanyName!cboSearchBox = txtSearchBox
>            End If
>       Case 3:
>            If Trim([txtSearchBox] & " ") = "" Then
>                DoCmd.OpenForm ("fdlgEventDetail")
>                DoCmd.GoToControl ("txtEventDate")
>            Else
>                DoCmd.OpenForm ("fdlgEventDetail")
>                DoCmd.GoToControl ("txtEventDate")
>                Forms!fdlgEventDetail!txtEventDate = txtSearchBox
>            End If
>       Case Else
>           MsgBox "Invalid selection", vbExclamation
>           Exit Sub
>    End Select
>
> Now the Null situation is taken care of.
>
> The only problem is the selected text in txtSearchBox when put into
> cboSearchBox won't search on the data; I have to delete it and type it 
> back
> in and then the search works.  That's what I was trying to use the Else
> statement for, so I would not have to retype the entry.
>
> Jeanette Cunningham wrote:
>>The after update won't work because you used code to select the value for
>>cboSearchBox.
>>You can use code to make the after update event fire.
>>
>>On the load event you can go:
>>Call cboSearchBox_AfterUpdate
>>
>>Null not working, debug it like this
>>
>>Debug.Print Me.txtSearchBox
>>If IsNull(Me.txtSearchBox) Then
>>                     DoCmd.OpenForm ("[frmListforCompanyID")
>>                     DoCmd.GoToControl ("cboSearchBox")
>>                 Else
>>                     DoCmd.OpenForm ("frmListforCompanyID")
>>                     DoCmd.GoToControl ("cboSearchBox")
>>                     Forms!frmListforCompanyID!cboSearchBox.ControlSource 
>> =
>>txtSearchBox
>>End If
>>
>>Run the code with the form in normal view, then open the immediate window 
>>to
>>see what value access gets for Me.txtSearchBox.
>>
>>Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>>
>>> Thanks Jeanette,
>>>
>>[quoted text clipped - 30 lines]
>>>>>
>>>>> Thanks for any help!
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1
> 


0
Jeanette
6/5/2010 8:49:49 PM
Okay Jeanette,

Here’s the code:

     Private Sub cboSearchBox_AfterUpdate()

     On Error GoTo ErrorHandler

         Me.txtCompanyName.Enabled = True
         Me.txtCompanyName.SetFocus
    
         If Not IsNull(cboSearchBox) Then
             DoCmd.FindRecord cboSearchBox
             Me.txtBlank.SetFocus
             Me.txtCompanyName.Enabled = False
             Me.cboSearchBox.Value = Null
        
         End If


     CleanUpAndExit:
         Exit Sub

     ErrorHandler:
         Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
             "Description:  " & Err.Description & vbCrLf & _
             "Error Number:  " & Err.Number, , "Error")
         Resume CleanUpAndExit

     End Sub


No, I didn’t put code on the load event for frmListForCompanyID.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/5/2010 10:39:14 PM
You need to make the after update event using code after the form opens.
So on the form's load event, below any other code you have on the load event 
put this line of code:

Call cboSearchBox_AfterUpdate



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"lmcc via AccessMonster.com" <u59647@uwe> wrote in message 
news:a917a20a61257@uwe...
> Okay Jeanette,
>
> Here's the code:
>
>     Private Sub cboSearchBox_AfterUpdate()
>
>     On Error GoTo ErrorHandler
>
>         Me.txtCompanyName.Enabled = True
>         Me.txtCompanyName.SetFocus
>
>         If Not IsNull(cboSearchBox) Then
>             DoCmd.FindRecord cboSearchBox
>             Me.txtBlank.SetFocus
>             Me.txtCompanyName.Enabled = False
>             Me.cboSearchBox.Value = Null
>
>         End If
>
>
>     CleanUpAndExit:
>         Exit Sub
>
>     ErrorHandler:
>         Call MsgBox("An error was encountered" & vbCrLf & vbCrLf & _
>             "Description:  " & Err.Description & vbCrLf & _
>             "Error Number:  " & Err.Number, , "Error")
>         Resume CleanUpAndExit
>
>     End Sub
>
>
> No, I didn't put code on the load event for frmListForCompanyID.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1
> 


0
Jeanette
6/5/2010 10:58:08 PM
Jeanette,

I entered the "Call cboSearchBox_AfterUpdate" but still get the same results.

I am going to leave it for now while I rethink it and read more about
filtering because this should be pretty simple.  

Sometimes it helps when you stop and restart over.  Because what I am trying
to say is search for this data; if found, open this detail form filtered on
the searched data.  If null, open this list form without any filters.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/6/2010 12:30:06 AM
oops I hit Post to soon.

Thanks for your help!

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/6/2010 12:30:56 AM
Hello again Jeanette,

Okay, here I go, I started over.  

1.  The lstSelectionBox will have 9 values to choose from.
2.  The txtSearchBox is where I enter the data (criteria).
3.  If txtSearchBox is empty or the data is not found, then open
frmSearchCompanyID.
4.  If there is data in txtSearchBox, then open the fmainCompany and filter
on the CompanyID in 
     txtSearchBox.

Below is what I have so far:

     Private Sub txtSearchBox_AfterUpdate()
         Select Case Me.lstSelectionBox.Value
             Case 1:
                 If Trim([txtSearchBox] & " ") = "" Then
                     DoCmd.OpenForm ("frmSearchCompanyID")
                     DoCmd.GoToControl ("cboSearchBox")
                 Else
                     DoCmd.OpenForm ("fmainCompany")
                     Me.Filter = "CompanyID = " & Me.txtSearchBox
                     Me.FilterOn = True
                 End If            
             End Select
     End Sub

Problems:
1.  How to handle when the data entered into txtSearchBox is not found.
2.  Filter is not working when it opens fmainCompany form.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/6/2010 5:08:42 AM
You can pass the filter to fmainCompany using OpenArgs and then use it
to filter the form.

---------------------
Dim strFilter As String

strFilter =  "CompanyID = " & Me.txtSearchBox
DoCmd.OpenForm ("fmainCompany"), , , , , , strFilter
----------------
In the load event for fmainCompany go:

Private Sub Form_Load
If Not IsNull(Me.OpenArgs) Then
    Me.Filter = Me.OpenArgs
    Me.FilterOn = True
End If
End Sub

You can also use a filter in the after update of cboSearchBox if you wish.

 If Not IsNull(cboSearchBox) Then
             Me.Filter =  "CompanyID = " & Me.cboSearchBox
             Me.FilterOn = True
             Me.txtBlank.SetFocus
             Me.txtCompanyName.Enabled = False
 End If


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"lmcc via AccessMonster.com" <u59647@uwe> wrote in message
news:a91b08bd0d63f@uwe...
> Hello again Jeanette,
>
> Okay, here I go, I started over.
>
> 1.  The lstSelectionBox will have 9 values to choose from.
> 2.  The txtSearchBox is where I enter the data (criteria).
> 3.  If txtSearchBox is empty or the data is not found, then open
> frmSearchCompanyID.
> 4.  If there is data in txtSearchBox, then open the fmainCompany and
> filter
> on the CompanyID in
>     txtSearchBox.
>
> Below is what I have so far:
>
>     Private Sub txtSearchBox_AfterUpdate()
>         Select Case Me.lstSelectionBox.Value
>             Case 1:
>                 If Trim([txtSearchBox] & " ") = "" Then
>                     DoCmd.OpenForm ("frmSearchCompanyID")
>                     DoCmd.GoToControl ("cboSearchBox")
>                 Else
>                     DoCmd.OpenForm ("fmainCompany")
>                     Me.Filter = "CompanyID = " & Me.txtSearchBox
>                     Me.FilterOn = True
>                 End If
>             End Select
>     End Sub
>
> Problems:
> 1.  How to handle when the data entered into txtSearchBox is not found.
> 2.  Filter is not working when it opens fmainCompany form.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1
>



0
Jeanette
6/6/2010 7:15:19 AM
I'm confused.  Are you saying to use the code instead of the Select Case code?


Where does "Dim strFilter As String, strFilter =  "CompanyID = " & Me.
txtSearchBox, DoCmd.OpenForm ("fmainCompany"), , , , , , strFilter" code go?


Jeanette Cunningham wrote:
>You can pass the filter to fmainCompany using OpenArgs and then use it
>to filter the form.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1

0
lmcc
6/6/2010 5:33:27 PM
In the code below is where it goes.

Private Sub txtSearchBox_AfterUpdate()
    Dim strFilter As String
         Select Case Me.lstSelectionBox.Value
             Case 1:
                 If Trim([txtSearchBox] & " ") = "" Then
                     DoCmd.OpenForm ("frmSearchCompanyID")
                     DoCmd.GoToControl ("cboSearchBox")
                 Else
                     DoCmd.OpenForm ("fmainCompany")
                     strFilter =  "CompanyID = " & Me.txtSearchBox
                     DoCmd.OpenForm ("fmainCompany"), , , , , , strFilter
                 End If
             End Select
     End Sub

Then when fmainComapy opens, its load event will put the value of CompanyID 
in the combo and call the code that makes the after update event of the code 
run.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


"lmcc via AccessMonster.com" <u59647@uwe> wrote in message 
news:a9218917e2715@uwe...
> I'm confused.  Are you saying to use the code instead of the Select Case 
> code?
>
>
> Where does "Dim strFilter As String, strFilter =  "CompanyID = " & Me.
> txtSearchBox, DoCmd.OpenForm ("fmainCompany"), , , , , , strFilter" code 
> go?
>
>
> Jeanette Cunningham wrote:
>>You can pass the filter to fmainCompany using OpenArgs and then use it
>>to filter the form.
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201006/1
> 


0
Jeanette
6/7/2010 2:21:13 AM
Okay!  Thank you!


Jeanette Cunningham wrote:
>In the code below is where it goes.
>
>Private Sub txtSearchBox_AfterUpdate()
>    Dim strFilter As String
>         Select Case Me.lstSelectionBox.Value
>             Case 1:
>                 If Trim([txtSearchBox] & " ") = "" Then
>                     DoCmd.OpenForm ("frmSearchCompanyID")
>                     DoCmd.GoToControl ("cboSearchBox")
>                 Else
>                     DoCmd.OpenForm ("fmainCompany")
>                     strFilter =  "CompanyID = " & Me.txtSearchBox
>                     DoCmd.OpenForm ("fmainCompany"), , , , , , strFilter
>                 End If
>             End Select
>     End Sub
>
>Then when fmainComapy opens, its load event will put the value of CompanyID 
>in the combo and call the code that makes the after update event of the code 
>run.
>
>Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
>> I'm confused.  Are you saying to use the code instead of the Select Case 
>> code?
>[quoted text clipped - 5 lines]
>>>You can pass the filter to fmainCompany using OpenArgs and then use it
>>>to filter the form.

-- 
Message posted via http://www.accessmonster.com

0
lmcc
6/7/2010 4:41:38 AM
Reply:

Similar Artilces:

Automatic spell checking lost
Using Outlook 2003 on many systems. All but one automatically checks the spelling and corrects it. I have looked at all settings and they are correct. I have done a repair and still have the problem. Any thoughts - should I remove and reinstall? Any ideals on this? "John Leonard - Sage" <sagegrp@adelphia.net> wrote in message news:O%23NkunCkEHA.2764@TK2MSFTNGP11.phx.gbl... > Using Outlook 2003 on many systems. All but one automatically checks the > spelling and corrects it. > > I have looked at all settings and they are correct. I have done a repair > ...

Subquery select field from previous record
I have the following table setup Table Name - tblInventoryCounts Field Names - Store Number, Count Date, Item name, Count Amount Sample Data Store Number Count date Item Name Count Amount 9 1/4/2010 Gear 10 9 1/11/2010 Gear 18 9 1/18/2010 Gear 18 I have a parameter query where the user is asked for the store number and Count Date. I need for the query to return the information for the date entered and the Count Amount from 7 ...

Default printing selection
Is there a way to set the "Entire Workbook" selection in the print dialogue box as the default, instead of having to choose manually? Thanks. ...

Check for more than 2 decimal places
I'm look for a macro that will check the number of decimal places in a range of values. I would like to have a pop up box alert the user when a cell is found in that range that contains more than 3 decimal places. For example if the numbers in a range are 50.35, 25.2, 35.235, and 35, the macro would stop on that cell and deliver a pop up box showing me that it contains more than 3 decimal places. I can create an excel formula to check the information: =if(B2<>trunc(b2,2,"error",B2) however I don't know enough VBA to get this fomula into a macro to check a range....

Check Box #4
Does anyone know of a way to convert a cell to a check box? We would like to convert the cell rather than using the active X controls on top of it. Thanks. You could double-click the cell and "convert" it to a=20 checkbox: Private Sub Worksheet_BeforeDoubleClick(ByVal Target _ As Range, Cancel As Boolean) With ActiveCell .Value =3D "=FC" .Font.Name =3D "Wingdings" .Borders(xlEdgeLeft).LineStyle =3D xlContinuous .Borders(xlEdgeTop).LineStyle =3D xlContinuous .Borders(xlEdgeBottom).LineStyle =3D xlContinuous .Borders(xlEdgeRight).LineSt...

Checking a forms fields for null
Hi All, I can think of a few ways to accomplish this, but would like to know of a better way. just like when you fill out forms on the web if the field is mandatory it pops up a msg say somthing like "This field isn't optional" if it is left blank. What is the best way to do this in access on a form? I'm using now: if ISNull(myFeild1) then msgbox"Please fill in myFeild1" end if If ISNull(myFeild2) then msgbox"Please fill in myFeild2" end if if ISNull(myFeild3) then msgbox"Please fill in myFeild3" end if I use s...

Check the security update from MS Corp.
--wadrggrtzyh Content-Type: multipart/related; boundary="ipwfliboy"; type="multipart/alternative" --ipwfliboy Content-Type: multipart/alternative; boundary="adodlupkrh" --adodlupkrh Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to help maintai...

Using a Check Box in a Subform to select records
I have a Form with a subform and two combo field are used to filter for records in the subform. The records in the subform have a bound check box. When I check one record on the subform and then move my forcus off the subform, every record becomes checked. Any suggestions? Thanks Are you sure the check box is a bound control? The behaviour you are getting suggests that its more likely to be unbound. What does it say in the control's ControlSource property in its properties sheet? If it is bound to a column in the underlying recordset then the behaviour could be explained by ...

Case select question
Hi I have a case select code on my listbox. The listbox is called ReportID, I have an event procedure on dbl click. But when I dbl click the selection nothing happens. I have 2 columes of information that is shown in the list box, column 1 has report name, column 2 has report description. I put the report name in the case selection; from the table were reportID pulls information from did I write down the correct field information? Can anyone help me out? Private Sub ReportID_DblClick(Cancel As Integer) On Error GoTo handleErr Select Case Me!ReportID Case "Supply Plant Flour&...

Deposit vs Check
Can anyone tell me if it is possible to make money put a deposit before a check number on the same date. My payroll goes in on Friday and I write checks that day. Money makes it look like I am negative because money puts checks before deposits. Any help would be great. ewingerter | 2009-05-23 | 7:56:01 PM wrote: >Can anyone tell me if it is possible to make money put a deposit >before a check number on the same date. My payroll goes in on Friday >and I write checks that day. Money makes it look like I am negative >because money puts checks before deposits. Any help would be gr...

Anyone ever seen a case where FormatConditions.Count = -1 ?
I have a case where I have a selected range (Application.Selection) that I want to do some conditional formatting on and I am seeing a case where Application.Selection.FormatConditions.Count is -1 instead of a value between 0 and 3. Anyone ever seen this before? I am sure that some sort of error condition exists, but don't know the significance of Count = -1 except that I cannot add a condition. I believe the selection is valid. Any help would be appreciated. TIA. Roy You'll see that if the selection contains cells with different conditional formatting. roybrew@att.net wrote:...

how do I assign a variable to a check box or radio control?
I need to assign the value of a radio control or check box when clicked on to a variable, preferably using the SetTemVar macro. Use the AfterUpdate of the control (if it's a stand-alone control), or of the option group (if it's part of an option group) to SetValue (in a macro) or assign the value (in code.) Not sure if it's really a good idea though. Considerations such as: - You can quite easily read the value from the form. - Doesn't work in versions of Access before 2007. - If it's a bound form, the tempvar would not be up to date when you move to a different recor...

A unique document number could not be found. Please check setup
has anyone come across this message Is this when creating a new transaction? It's possible that you've 'run out' of numbers....what does the module setup say? If you have something like ABC99999, it may not be able to go to ABC100000 without manual intervention. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html "rcr" <rcr@discussions.microsoft.com> wrote in message news:642E314C-DFEF-48C9-BD37-EB047270430E@microsoft.com... > has anyone come across this message Victoria has one ...

Possible to hide a sub-report if NULL values?
Hi, I have created a report (column) that list open invoices and I have also attached a sub-report (different table) that displayes comments. Problem: sub-report is showing for all invoices, but I would like it to show ONLY for invoices that has a comment. That is, invoices that has no comments only list their usual values, and invoices with comments I have a sub-report displaying the comment. Is that possible? (otherwise my invoice-list with contain a lot of unecessary space - used by the sub-report). Kindly, Mikael Mikael Lindqvist wrote: >I have created a report (column) that l...

Check this corrective patch from the MS
--jvpplluf Content-Type: multipart/related; boundary="fjivqayfwxnprlkf"; type="multipart/alternative" --fjivqayfwxnprlkf Content-Type: multipart/alternative; boundary="cigzxgkmd" --cigzxgkmd Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "September 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to continue...

Assigning and Accepting Cases
We have a workflow rule that emails a user when a Case assigned to them. The email is sent when the case is ASSIGNED. However, the same email is also sent when the user ACCEPTS the case. We do not want the email to be sent when the user Accepts the case. Since there is no Accept event in Workflow, has anyone found a work around for this problem? Carroll ...

Pivot Tables: New dimension members show up checked in dropdown
I've noticed that when distinct new members are added to dimension dat they show up as checked in a dropdown list when the pivot table i refreshed. Is there anyway to disable this so that they don't show up (pre checked? Basically, I have a model that depends on a subset of the members which I have checked in a dropdown, and the rest are unchecked - an when a distinct new member is added it automatically shows up a checked in the dropdown list, which gives me wrong answers. E.g. I have Stock Tickers as a dimension and avg. price as the measure I have 10,000 Distinct Tickers of whic...

selective forward
Hi all, One of managers is going for hollydays, how do I setup forward in exchange 2003 so other manager will recive forwarded mail only from selected customers? Is there a way to do that? Thank you. You will have to do this through Outlook Rules within Outlook on the Manager that is leaving for a holiday. -- John Oliver, Jr MCSE, MCT, CCNA Exchange MVP 2006 Microsoft Certified Partner "Peter" <Peter@discussions.microsoft.com> wrote in message news:9FCE6BFA-B5C7-4774-94A9-29D5BA5ED8D1@microsoft.com... > Hi all, > One of managers is going for hollydays, how do I setu...

Listbox Selection
I have a form with a List Box(lst_entry), upon clicking on an entry a new form (frm_edit) should load up showing the details of the entry selected in listbox. In the "On Dbl Click" event I've used the following code; Private Sub Lst_entry_DblClick(cancel As Integer) DoCmd.OpenForm "frm_edit", , , "BINPROCESSID = " & List23.Column(0) & " and BINPROCESSDate = " & List23.Column(1) End Sub However I'm getting the following error message every time I dbl click on an item in the list; Run-time error '3075': Syntax error (missing...

Deselect one of many non-adjacent rows selected
If you are going through a worksheet and selecting several rows to, for example, delete them and you accidentally select a row you didn't mean to, is there a way to deselect just that one row? I noticed that CTRL clicking on the row again does not do the trick. Thanks!! Nicolle Very annoying feature. You can add a couple of Chip Pearson macros to your Personal.xls that will allow the unchecking of a mistake. Assign to a button or to the right-click menu. See this thread for the macros and instructions for adding an item to the right-click menu. http://snipurl.com/bxoj Gord D...

URGENT
One of my users is having problems resolving cases. The error message dialog appears with the following message: "An error has occurred. For more information, contact your system administrator." I've checked the event viewer on the server but can't find any further information. The user is in the same Business Unit with the same Role as other users who can resolve Cases without a problem. The user owns the case that he's trying to resolve. We've run a full Publish through deployment manager and IISRESET to ensure all customizations were published. The user ...

How to sychronize in this case:
Hi there! Is it possible to synchronize my contacts in the following scenario. The boss and I have two similar (but not *exactly*, hence the problem) contacts list on two separate computers that are *not* networked (and won't be, in case that's one of your suggestions! :-) Is there an easy way for me to make sure they are identical, short of printing them both out and comparing them one name at a time since we're talking about *1300* contacts here... We're both using Outlook 2000. Thanks in advance! This WILL be appreciated, believe me! Signed, Carmen (the poor peon w...

How do I distinguish between upper and lower case with pivot tabl.
How do I distinguish between upper and lower case items when creating a pivot table, e.g. a list of warehosue names which have combinations of both upper and lower case, lower case being one type of warehouse and the upper case ones representing a different type What you could do is add a column to the original table that attaches a "1" in front of every warehouse name whose first character is upper case. =IF(CODE(LEFT(A2))<91,1&A2,A2) And fill down. Then use this new column in your pivot table. HTH Jason Atlanta, GA >-----Original Message----- >How do I dist...

check spelling on greek
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Hi, <br><br>I write in greek in Word 2008 and even if I changed the language from the tools menu the spelling check doesn't work. <br><br>I have seen at the website that there no available proofing tools for greek. Is there any solution on that issue? <br><br>Thanks On 12/10/09 4:21 PM, in article 59baebaf.-1@webcrossing.JaKIaxP2ac0, "Maria_Konsta@officeformac.com" <Maria_Konsta@officeformac.com> wrote: > I have seen at the website that there no availa...

how do we check the nullity of a string
hello friends, i am using ms access 2003, i have a couple of doubts 1. how do we check the nullity of string with vb6 code 2. when we use the code MsgBox(" hello world"), then hello world will be displayed. now i want to embedded double quotation marks in the output messsge i.e for example i want the out put as hello "the" world. i mean the output should include quotation marks too. how can i accomplish this? thanq Too check the nullity of a variable simply use isnull() if isnull(varname) then .... As for quotations, if you absoltely want them you'll n...