Need query to separate 2 entry types in a table field

S

Steve Schapel

Jan

Ok, thanks for the further information. I think I understand now. So
you need to be relating to the Record Source of the Record Form, and not
the form that the code is running on, somehting like this...

With Forms!frmCheckRegister
Select Case Me.YourOptionGroup
Case 1
.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not
Like 'DBT*'"
Case 2
.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Like
'DBT*'"
Case 3
.RecordSource = "YourQuery"
End Select
End With

I don't remember ever seeing a system like you are using. If it was
mine, I am pretty sure I would take all the stuff you have on the Filter
Form, and put it instead in the Form Header of the Record Form. :)

--
Steve Schapel, Microsoft Access MVP


Jan said:
Hi Steve :)
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.
Click to expand...


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 :)
Click to expand...
 
S

Steve Schapel

Jeff,

On looking at my response to your earlier suggestion, it is a bit
obscure. I was in a hurry at the time. I hope you weren't offended, as
none was intended. :)
 
T

Tom Ellison

As one who knows Steve, I can assure you he is a most inoffensive
person. I find it virtually impossible that he could have meant any
ill. I recommend giving him the benefit of any doubt.

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

Jan Il

Hi Steve :)
Jan

Ok, thanks for the further information. I think I understand now. So
you need to be relating to the Record Source of the Record Form, and not
the form that the code is running on, somehting like this...

With Forms!frmCheckRegister
Select Case Me.YourOptionGroup
Case 1
.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Not
Like 'DBT*'"
Case 2
.RecordSource = "SELECT * FROM YourQuery WHERE txtCheckNo Like
'DBT*'"
Case 3
.RecordSource = "YourQuery"
End Select
End With
Click to expand...

Now...here is what I have in the code, which I copy/pasted to the Event
Procedure of the Option Group, and I hope that I'm close after making the
necessary name changes where applicable:

rivate Sub fmeOptionGrp_AfterUpdate()
Select Case Me.fmeOptionGrp

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

End With

End Sub

While the debugger did not bark on complie, when I clicked the first button,
which is the CheckNo (Case 1) it opened the debugger and it highlighted the
With in that line. It did the same for the rest, of course. Also, when I
pasted the copy into the code, the " between the Like and 'DBT was created
automatically, guess that is how it got there the first time, but, I did
notice it this time when I went back over the code carefully after the name
changes.
I don't remember ever seeing a system like you are using. If it was
mine, I am pretty sure I would take all the stuff you have on the Filter
Form, and put it instead in the Form Header of the Record Form. :)
Click to expand...

Oh...well...ahmm....I see.....I guess I may be a bit unique (aka...not by
the book) in some of the things I do. :)

But.....see....the Filter form provides a quick, simple method of sorting
and data processing for the inexperienced Users, and it also leaves me room
on my Record Forms for including those functions that are more important to
the needs of the Users there, and allows the forms to be more efficient, as
they are not crammed with various functions, and with less things for the
Users to have to make decisions about. Thus, they are less complicated for
the Users. Some of the forms I have to create can get really quite complex
in the information and details they need to provide for Freight and Hard
Rail Railroads and Light Rail Transit operations, especially with regards to
equipment maintenance, which is the primary area of my database
applications. Thus, I found it much easier to create the Filter Forms to do
the laundry, 'cause, ya see....everything to a Wayside Maintainer is a
"Gizmo". ;o))

Jan :)
--

Jan said:
Hi Steve :)
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.
Click to expand...


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 :)
Click to expand...
Click to expand...
 
S

Steve Schapel

Jan,

I reckon that in copy/pasting the code, you have copy/pasted the line
break gizmo that was put into the newsgroup post by you newsreader.
Each of the lines of code that start off with .RecordSource should all
be on one line.
 
J

Jan Il

Jan,
I reckon that in copy/pasting the code, you have copy/pasted the line
break gizmo that was put into the newsgroup post by you newsreader.
Each of the lines of code that start off with .RecordSource should all
be on one line.
Click to expand...

No no.....they are actually all on one line, I know to watch the for line
break and put everything on one line. It is just the With
Forms!frmCheckRegister line in the code that the debugger is now fussing
about.

Plus...I noticed here that when I copied the code form the Event Procedure I
left off the P of Private...xxx that I pasted here, but, it actually is in
the code in the form, so don't be confused by that.

Jan :)
 
J

Jeff Conrad

No offense even crossed my mind Steve, serious.
Just thought I'd give a suggestion on the partial info I had at the time.
:)

--
Jeff Conrad
Access Junkie
Bend, Oregon

Steve Schapel said:
Jeff,

On looking at my response to your earlier suggestion, it is a bit
obscure. I was in a hurry at the time. I hope you weren't offended, as
none was intended. :)

--
Steve Schapel, Microsoft Access MVP


Jeff said:
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.
Click to expand...
Click to expand...
 
S

Steve Schapel

Jan,

Yes, I saw the missing "P", and assumed a copy/paste slip. How about
the replication of the line...
Select Case Me.fmeOptionGrp
Is that a type too?
 
J

Jan Il

Hi Steve :)
Jan,

Yes, I saw the missing "P", and assumed a copy/paste slip. How about
the replication of the line...
Select Case Me.fmeOptionGrp
Is that a type too?
Click to expand...

No..sorry, that did get in there twice, a carry over I missed I guess. I
have removed the one in the first line, and now have this code. I got the
whole wazoolie this time. :)

Private Sub fmeOptionGrp_AfterUpdate()

With Forms!frmCheckRegister
Select Case Me.fmeOptionGrp

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

End Sub


But, the debugger is still barking at the line:
With Forms!frmCheckRegister

Clicking on the buttons is now firing a Runtime error 2450, and the message
"Microsoft Access can't find the form 'frmCheckRegister' referred to in a
Macro expression or Visual Basic code.

But, frmCheckRegister *is* the name of the Record Form to be opened and the
data is to be displayed. I'm not sure I understand what is causing this
error message.

Thank you.

Jan :)
 
S

Steve Schapel

Jan,

Is frmCheckRegister open at the time? You might need a line like this
at the beginning of the procedure...
DoCmd.OpenForm "frmCheckRegister"

And, what's the difference between a gizmo and a wazoolie?
 
J

Jan Il

Hi Steve :)
Jan,

Is frmCheckRegister open at the time? You might need a line like this
at the beginning of the procedure...
DoCmd.OpenForm "frmCheckRegister"
Click to expand...

Blue Eagle 1, this is Blue Eagle 2, we have now neutralized the 'Bugger...
YES!! ;o))
And, what's the difference between a gizmo and a wazoolie?
Click to expand...

Oh.....w'll....a gizmo is a thing....and.... a wazoolie is....eh...
everything. See...?


Thank you very much for all your time and help, and patience. I really do
appreciate it. :)


Jan :)
Smiles are meant to be shared,
that's why they're so contagious.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need a code for a command button to sort records 5
Query criteria - exclude data and return all records if null 3
Need to add date sorting to form 5
Need help getting query to reference a field in a subform 2
I want a blank field in User Form to pull ALL records in a Query 1
IIF Stament in a Query 4
IIf Criteria Question in a Query 4
Update query to update new qty and cost 1

Top