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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
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 Il wrote: 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. 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 |
#22
|
|||
|
|||
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 Conrad wrote: 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. |
#23
|
|||
|
|||
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 On Tue, 23 Nov 2004 12:20:28 +1300, Steve Schapel wrote: 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. :-) |
#24
|
|||
|
|||
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 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. :-) 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 Il wrote: 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. 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 |
#25
|
|||
|
|||
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. -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) 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. |
#26
|
|||
|
|||
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. 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 -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) 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. |
#27
|
|||
|
|||
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" wrote in message ... 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 Conrad wrote: 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. |
#28
|
|||
|
|||
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? -- Steve Schapel, Microsoft Access MVP Jan Il wrote: 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 |
#29
|
|||
|
|||
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? 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 -- Steve Schapel, Microsoft Access MVP Jan Il wrote: 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 |
#30
|
|||
|
|||
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? -- Steve Schapel, Microsoft Access MVP Jan Il wrote: Hi Steve :-) 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 |
Thread Tools | |
Display Modes | |
|
|
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 06: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 |