A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Need query to separate 2 entry types in a table field



 
 
Thread Tools Display Modes
  #11  
Old November 22nd, 2004, 05:12 AM
Steve Schapel
external usenet poster
 
Posts: n/a
Default

Jan,

The Case statements will refer to the Option Value of the Option Button
that you select in the Option Group. Enough options for you? Look at
the properties of each of the Option Buttons, and see if the Option
Value property is set to 1, 2, and 3 respectively for the 3 of them.

--
Steve Schapel, Microsoft Access MVP

Jan Il wrote:
Hi Steve :-)


'k..!! Now here's what I now have, and the Debugger's happy with it.

Private Sub fmeOptionGrp_AfterUpdate()
Select Case Me.fmeOptionGrp

Case 1
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not
Like 'DBT*'"
Case 2
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo
Like 'DBT*'"
Case 3
Me.RecordSource = "qryCkRegisterDan"
End Select

End Sub


YES!!!

But.....ahmmm......nothing happens when I click the buttons. (?..?) It
looks really nice though. :-)

What might I have left out? Do I need a command button to go with?

Jan

  #12  
Old November 22nd, 2004, 07:26 AM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default

"Jan Il" wrote:

Hi Jan,

'k..!! Now here's what I now have, and the Debugger's happy with it.

Private Sub fmeOptionGrp_AfterUpdate()
Select Case Me.fmeOptionGrp

Case 1
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not
Like 'DBT*'"
Case 2
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo
Like 'DBT*'"
Case 3
Me.RecordSource = "qryCkRegisterDan"
End Select

End Sub


YES!!!

But.....ahmmm......nothing happens when I click the buttons. (?..?) It
looks really nice though. :-)

What might I have left out? Do I need a command button to go with?


Looks to me like you are missing the all important line of code to tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery

--
Jeff Conrad
Access Junkie
Bend, Oregon


  #13  
Old November 22nd, 2004, 07:48 AM
Steve Schapel
external usenet poster
 
Posts: n/a
Default

Interesting thought, Jeff. But... why?

--
Steve Schapel, Microsoft Access MVP


Jeff Conrad wrote:

Looks to me like you are missing the all important line of code to tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery

  #14  
Old November 22nd, 2004, 01:33 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Steve :-)

Jan,

The Case statements will refer to the Option Value of the Option Button
that you select in the Option Group. Enough options for you? Look at
the properties of each of the Option Buttons, and see if the Option
Value property is set to 1, 2, and 3 respectively for the 3 of them.


Yes, I did give each of the buttons a 1, 2 or 3 Value, respectively, as I
assumed that was the intended setup for the Case statement.

However, I *think* the reason why it is not opening or displaying anything,
is that I understood it was to go on the Filter form, and there is nothing
to be displayed on the Filter form. It's just the form where all the
sorting is done and then the Record form is opened to displayed the various
records as sorted from the Filter form. Should this Option Group frame be
on the Record form in order to display the relative records accordingly?

Jan



Jan Il wrote:
Hi Steve :-)


'k..!! Now here's what I now have, and the Debugger's happy with it.

Private Sub fmeOptionGrp_AfterUpdate()
Select Case Me.fmeOptionGrp

Case 1
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo

Not
Like 'DBT*'"
Case 2
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo
Like 'DBT*'"
Case 3
Me.RecordSource = "qryCkRegisterDan"
End Select

End Sub


YES!!!

But.....ahmmm......nothing happens when I click the buttons. (?..?) It
looks really nice though. :-)

What might I have left out? Do I need a command button to go with?

Jan



  #15  
Old November 22nd, 2004, 02:35 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hey Jeff! :-)


"Jan Il" wrote:

Hi Jan,

'k..!! Now here's what I now have, and the Debugger's happy with it.

Private Sub fmeOptionGrp_AfterUpdate()
Select Case Me.fmeOptionGrp

Case 1
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo

Not
Like 'DBT*'"
Case 2
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo
Like 'DBT*'"
Case 3
Me.RecordSource = "qryCkRegisterDan"
End Select

End Sub


YES!!!

But.....ahmmm......nothing happens when I click the buttons. (?..?) It
looks really nice though. :-)

What might I have left out? Do I need a command button to go with?


Looks to me like you are missing the all important line of code to tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery


'k...I put this in and tried it, but, I'm still not getting any response
from the buttons. I think, as I mentioned to Steve in my reply to him, that
I may have it on the wrong form, and I need to do this on the record form,
or....maybe I need to tell it to open the Record form to display the
relative data for each button. Either that, or I've still done something
correctly.

Thank you for the additional help....I really appreciate it. :-)

Jan





  #16  
Old November 22nd, 2004, 05:27 PM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

Changing the RecordSource of a form always causes a Requery. Doing it
again is just a waste of time.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Sun, 21 Nov 2004 22:26:41 -0800, "Jeff Conrad"
wrote:

"Jan Il" wrote:

Hi Jan,

'k..!! Now here's what I now have, and the Debugger's happy with it.

Private Sub fmeOptionGrp_AfterUpdate()
Select Case Me.fmeOptionGrp

Case 1
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo Not
Like 'DBT*'"
Case 2
Me.RecordSource = "SELECT * FROM qryCkRegisterDan WHERE CheckNo
Like 'DBT*'"
Case 3
Me.RecordSource = "qryCkRegisterDan"
End Select

End Sub


YES!!!

But.....ahmmm......nothing happens when I click the buttons. (?..?) It
looks really nice though. :-)

What might I have left out? Do I need a command button to go with?


Looks to me like you are missing the all important line of code to tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery


  #17  
Old November 22nd, 2004, 06:16 PM
Steve Schapel
external usenet poster
 
Posts: n/a
Default

Jan,

I know you have been using these terms all the way through, and I have
so far not questioned. But I have no idea at all what you mean by a
Record Form and a Filter Form. I have re-read all your posts, including
the last one, and I'm afraid I still can't imagine what you might be
doing. At some point, it might become relevant for me to know, I'm not
sure.

However, the method we have been discussing assumes that the Option
Group is on the form that is displaying the records that you are
manipulating. If, for some reason, you want the buttons to be on one
form, and manipulate the records shown on another form, well... this
would be unusual, but is certainly possible.

--
Steve Schapel, Microsoft Access MVP


Jan Il wrote:
Hi Steve :-)

Yes, I did give each of the buttons a 1, 2 or 3 Value, respectively, as I
assumed that was the intended setup for the Case statement.

However, I *think* the reason why it is not opening or displaying anything,
is that I understood it was to go on the Filter form, and there is nothing
to be displayed on the Filter form. It's just the form where all the
sorting is done and then the Record form is opened to displayed the various
records as sorted from the Filter form. Should this Option Group frame be
on the Record form in order to display the relative records accordingly?

Jan

  #18  
Old November 22nd, 2004, 07:40 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Steve :-)


Jan,

I know you have been using these terms all the way through, and I have
so far not questioned. But I have no idea at all what you mean by a
Record Form and a Filter Form. I have re-read all your posts, including
the last one, and I'm afraid I still can't imagine what you might be
doing. At some point, it might become relevant for me to know, I'm not
sure.


The Record Form is a form on which the selected data is displayed after is
has been sorted/specified on the Filter Form. The Filter Form is the form
where there's a series of combo boxes that have a list of data to select
from, such as, Transactions, Transaction types, etc., and associated command
buttons with code that calls the Record form to open and display only the
specific record information selected from the Filter Form controls. Such
as; if I select Auto Expense from the list of Transaction types in a combo
box on the Filter Form, then click the command button next to it, the Record
Form is then opened only displaying all of the Auto Expenses entered.

The Record Form does nothing but display the data called from the activities
that takes place on the Filter form. Example: here's the code behind one
of the command buttons for one of the combo boxes on the Filter Form:

Private Sub cmdCheckNo_Click()
On Error GoTo Err_cmdCheckNo_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCheckRegister"

stLinkCriteria = "[CheckNo]=" & "'" & Me![cmbCheckNo] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdCheckNo_Click:
Exit Sub

Err_cmdCheckNo_Click:
MsgBox Err.Description
Resume Exit_cmdCheckNo_Click

End Sub

Hopefully, this will help clarify what type of form I mean by 'Filter Form.'
I have seen such filtering/sorting forms in various posts in the ng's called
by many different terms or names (driver form, for one), but, I am not
familiar with any one particular term for this kind of form. However, if
there is a specific name for it, please let me know and I'll be very happy
to use it in order to eliminate confusion for others as well. I just call
it a Filter Form because...... well.... that's what it does. :-))

However, the method we have been discussing assumes that the Option
Group is on the form that is displaying the records that you are
manipulating. If, for some reason, you want the buttons to be on one
form, and manipulate the records shown on another form, well... this
would be unusual, but is certainly possible.


If possible, I would like to keep all the record sorting activities on the
sorting form (Filter Form), so that there will only be the specific data
records displayed on the Record Form, and no other activities required other
than scrolling to review the information. All the users need to do is
review the information, they don't need to do anything with it.

I really do like the look and function of the Option Group, and I'm sure
this will work adequately, if there is a way to code it so that when you
click one of the buttons, the Record Form opens, and just the data for that
button is displayed. As there are only these 3 functions necessary for this
purpose, this would be a very quick and simple way to handle the sorting.
However, is this type of function possible using the Option Group?

Thank you so much for your time and patience, Steve, it's very much
appreciated. :-)

Jan

--
Steve Schapel, Microsoft Access MVP


Jan Il wrote:
Hi Steve :-)

Yes, I did give each of the buttons a 1, 2 or 3 Value, respectively, as

I
assumed that was the intended setup for the Case statement.

However, I *think* the reason why it is not opening or displaying

anything,
is that I understood it was to go on the Filter form, and there is

nothing
to be displayed on the Filter form. It's just the form where all the
sorting is done and then the Record form is opened to displayed the

various
records as sorted from the Filter form. Should this Option Group frame

be
on the Record form in order to display the relative records accordingly?

Jan



  #19  
Old November 22nd, 2004, 09:36 PM
Jeff Conrad
external usenet poster
 
Posts: n/a
Default

My newsreader has lost the beginning messages of this thread so I'm acting on half the information,
but from what I saw it looked like the record source of the form was being changed in code and Jan
said, "Nothing happened."

I have seen on occasion where changing the record source of the form in code does not *always* cause
the form in question to be requeried. I cannot remember the exact circumstances and/or Access
version involved, but you'll just have to trust me. So I thought maybe a Requery line would help.

I'll bow out now.

Only other thing I can think of is that the property sheet does not have [Event Procedure] listed
for the After Update of the control in question so Access does not do anything. Just a random
thought.

--
Jeff Conrad
Access Junkie
Bend, Oregon

"Steve Schapel" wrote in message ...
Interesting thought, Jeff. But... why?

--
Steve Schapel, Microsoft Access MVP


Jeff Conrad wrote:

Looks to me like you are missing the all important line of code to tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery



  #20  
Old November 22nd, 2004, 09:57 PM
Jan Il
external usenet poster
 
Posts: n/a
Default

Hi Jeff :-)

My newsreader has lost the beginning messages of this thread so I'm acting

on half the information,
but from what I saw it looked like the record source of the form was being

changed in code and Jan
said, "Nothing happened."

I have seen on occasion where changing the record source of the form in

code does not *always* cause
the form in question to be requeried. I cannot remember the exact

circumstances and/or Access
version involved, but you'll just have to trust me. So I thought maybe a

Requery line would help.

I'll bow out now.

Only other thing I can think of is that the property sheet does not have

[Event Procedure] listed
for the After Update of the control in question so Access does not do

anything. Just a random
thought.


I think the problem here is, that I was talking about the Filter Form, not
the Record or Data Form, which you two must have been thinking I was talking
about. Thus, the Option Group as presented by Steve may have worked had I
applied it to the Record Form. However, I have explained the situation to
Steve in my other reply, and would rather keep the sorting activities just
on the Filter (?) Form, and not the Record or data form, if possible. This
is in order to keep the Record form merely a data review only source, so
that the can only scroll to review the data and nothing more. They will not
have to do anything with the data other than to review it for data
confirmation.

I really do appreciate your time and input any time. :-)

Jan

BTW - 177 days3 hrs4 mins27 sec...... and counting........ ;o)

"Steve Schapel" wrote in message

...
Interesting thought, Jeff. But... why?

--
Steve Schapel, Microsoft Access MVP


Jeff Conrad wrote:

Looks to me like you are missing the all important line of code to

tell
Access to requery the form.

Add this line right below the End Select:

Me.Form.Requery





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Still Hoping for help with a Query problem Don Sealer Using Forms 15 November 13th, 2004 07:24 AM
Union Query Not Returning A Value Jeff G Running & Setting Up Queries 2 October 19th, 2004 05:47 PM
query a number stored as text Lee Running & Setting Up Queries 19 October 13th, 2004 04:10 AM
Using Start Date and End Date in Query ChuckW Running & Setting Up Queries 5 July 20th, 2004 02:52 AM
I can see the data but... David F-B General Discussion 3 June 24th, 2004 06:15 AM


All times are GMT +1. The time now is 11:25 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.