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 |
#1
|
|||
|
|||
"Go To ID" Button?
This is probably amazingly simple, but I'm missing it.
"ID" is my primary key in the underlying table. I would like to add a button to my form that will go to ID# (whatever the user types in). I don't want to open the "find" screen and I don't want to filter the records. I simply want to go to the record that belongs to that ID number. Thanks for your help. |
#2
|
|||
|
|||
"Go To ID" Button?
Hi H (what is your name?)
make a combobox (I usually put 'find combos' in the form header) on its AfterUpdate event... =FindRecord() this code goes behind the form: '~~~~~~~~~~~~~~~~~~~~ Private Function FindRecord() 'if nothing is picked in the active control, exit If IsNull(Me.ActiveControl) Then Exit Function 'save current record if changes were made If me.dirty then me.dirty = false 'declare a variable to hold the primary key value to look up Dim mRecordID As Long 'set value to look up by what is selected mRecordID = Me.ActiveControl 'clear the choice to find Me.ActiveControl = Null 'find the first value that matches Me.RecordsetClone.FindFirst "SomeID = " & mRecordID 'if a matching record was found, then move to it If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark End If End Function '~~~~~~~~~~~~~~~~~~~~ where - SomeID is the Name of the primary key field, which is in the RecordSource of the form -- assuming your primary key is a Long Integer data type (autonumbers are long integers) Remember that the Rowsource for a combo can come from anywhere -- it can pull from multiple tables or only use one ... just make sure that the first column is the primary key ID of the table you want to search (and that field is part of the RecordSource for the form you are searching). I usually make one or more unbound (no ControlSource) combos on your form (like in the header). Let the first column be invisible (unless you want to search on the ID smile) and be the primary key ID of the RecordSource of your form and then, on its AfterUpdate event... =FindRecord() you can use the same code for every 'find combo' -- and the RowSource can be anything as long as the first field is the primary key ID of your form for information about setting properties for a combobox, read the Combobox Example in: Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace Warm Regards, Crystal * (: have an awesome day * HWhite wrote: This is probably amazingly simple, but I'm missing it. "ID" is my primary key in the underlying table. I would like to add a button to my form that will go to ID# (whatever the user types in). I don't want to open the "find" screen and I don't want to filter the records. I simply want to go to the record that belongs to that ID number. Thanks for your help. |
#3
|
|||
|
|||
"Go To ID" Button?
¿´¿´Äܻظ´²»¡£¡£¡£¡£
|
#4
|
|||
|
|||
"Go To ID" Button?
thank you... it worked very well but the instructions were a little hard to
follow since access 2003 doesn't use "Private Function", but it automatically changed it. Also it wasn't clear what to name the combo box, but I figured it out. Thanks again for your help. "strive4peace" wrote: Hi H (what is your name?) make a combobox (I usually put 'find combos' in the form header) on its AfterUpdate event... =FindRecord() this code goes behind the form: '~~~~~~~~~~~~~~~~~~~~ Private Function FindRecord() 'if nothing is picked in the active control, exit If IsNull(Me.ActiveControl) Then Exit Function 'save current record if changes were made If me.dirty then me.dirty = false 'declare a variable to hold the primary key value to look up Dim mRecordID As Long 'set value to look up by what is selected mRecordID = Me.ActiveControl 'clear the choice to find Me.ActiveControl = Null 'find the first value that matches Me.RecordsetClone.FindFirst "SomeID = " & mRecordID 'if a matching record was found, then move to it If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark End If End Function '~~~~~~~~~~~~~~~~~~~~ where - SomeID is the Name of the primary key field, which is in the RecordSource of the form -- assuming your primary key is a Long Integer data type (autonumbers are long integers) Remember that the Rowsource for a combo can come from anywhere -- it can pull from multiple tables or only use one ... just make sure that the first column is the primary key ID of the table you want to search (and that field is part of the RecordSource for the form you are searching). I usually make one or more unbound (no ControlSource) combos on your form (like in the header). Let the first column be invisible (unless you want to search on the ID smile) and be the primary key ID of the RecordSource of your form and then, on its AfterUpdate event... =FindRecord() you can use the same code for every 'find combo' -- and the RowSource can be anything as long as the first field is the primary key ID of your form for information about setting properties for a combobox, read the Combobox Example in: Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace Warm Regards, Crystal * (: have an awesome day * HWhite wrote: This is probably amazingly simple, but I'm missing it. "ID" is my primary key in the underlying table. I would like to add a button to my form that will go to ID# (whatever the user types in). I don't want to open the "find" screen and I don't want to filter the records. I simply want to go to the record that belongs to that ID number. Thanks for your help. |
#5
|
|||
|
|||
"Go To ID" Button?
Hi H
you're welcome I am glad you figured it out -- but you may have done some things that you didn't need to ... what do you mean by, "access 2003 doesn't use "Private Function"? The reason to put this as a separate function is so that ANY control can use it on its AfterUpdate event...IN THE PROPERTY SHEET DIRECTLY -- =FindRecord() "it wasn't clear what to name the combo box" it doesn't matter what it is called -- that is why the code refers to ActiveControl and not anything specific. This code will work with a number of combos set up to find a record on the form as long as the first column is the numeric primary key of the table that the form is based on Did you read the 'Access Basics' document? It is only 100 pages and has lots of screen shots -- I guarantee it would be well worth your time Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * HWhite wrote: thank you... it worked very well but the instructions were a little hard to follow since access 2003 doesn't use "Private Function", but it automatically changed it. Also it wasn't clear what to name the combo box, but I figured it out. Thanks again for your help. "strive4peace" wrote: Hi H (what is your name?) make a combobox (I usually put 'find combos' in the form header) on its AfterUpdate event... =FindRecord() this code goes behind the form: '~~~~~~~~~~~~~~~~~~~~ Private Function FindRecord() 'if nothing is picked in the active control, exit If IsNull(Me.ActiveControl) Then Exit Function 'save current record if changes were made If me.dirty then me.dirty = false 'declare a variable to hold the primary key value to look up Dim mRecordID As Long 'set value to look up by what is selected mRecordID = Me.ActiveControl 'clear the choice to find Me.ActiveControl = Null 'find the first value that matches Me.RecordsetClone.FindFirst "SomeID = " & mRecordID 'if a matching record was found, then move to it If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark End If End Function '~~~~~~~~~~~~~~~~~~~~ where - SomeID is the Name of the primary key field, which is in the RecordSource of the form -- assuming your primary key is a Long Integer data type (autonumbers are long integers) Remember that the Rowsource for a combo can come from anywhere -- it can pull from multiple tables or only use one ... just make sure that the first column is the primary key ID of the table you want to search (and that field is part of the RecordSource for the form you are searching). I usually make one or more unbound (no ControlSource) combos on your form (like in the header). Let the first column be invisible (unless you want to search on the ID smile) and be the primary key ID of the RecordSource of your form and then, on its AfterUpdate event... =FindRecord() you can use the same code for every 'find combo' -- and the RowSource can be anything as long as the first field is the primary key ID of your form for information about setting properties for a combobox, read the Combobox Example in: Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace Warm Regards, Crystal * (: have an awesome day * HWhite wrote: This is probably amazingly simple, but I'm missing it. "ID" is my primary key in the underlying table. I would like to add a button to my form that will go to ID# (whatever the user types in). I don't want to open the "find" screen and I don't want to filter the records. I simply want to go to the record that belongs to that ID number. Thanks for your help. |
#6
|
|||
|
|||
"Go To ID" Button?
What I meant was when I copy and pasted your code, saved and closed, it did
not work. So I re-opened the code and it had changed the phrase "Private Function" to "Private Sub"... so that caused some confusion for a while. After realizing why the code wasn't working, Private Function/Private Sub was really an irrelevant issue for me, but I thought I would mention it to you in case it meant anything to you. Also, I bookmarked your page and reviewed it briefly. I look forward to spending more time with it. Thanks again. "strive4peace" wrote: Hi H you're welcome I am glad you figured it out -- but you may have done some things that you didn't need to ... what do you mean by, "access 2003 doesn't use "Private Function"? The reason to put this as a separate function is so that ANY control can use it on its AfterUpdate event...IN THE PROPERTY SHEET DIRECTLY -- =FindRecord() "it wasn't clear what to name the combo box" it doesn't matter what it is called -- that is why the code refers to ActiveControl and not anything specific. This code will work with a number of combos set up to find a record on the form as long as the first column is the numeric primary key of the table that the form is based on Did you read the 'Access Basics' document? It is only 100 pages and has lots of screen shots -- I guarantee it would be well worth your time Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * HWhite wrote: thank you... it worked very well but the instructions were a little hard to follow since access 2003 doesn't use "Private Function", but it automatically changed it. Also it wasn't clear what to name the combo box, but I figured it out. Thanks again for your help. "strive4peace" wrote: Hi H (what is your name?) make a combobox (I usually put 'find combos' in the form header) on its AfterUpdate event... =FindRecord() this code goes behind the form: '~~~~~~~~~~~~~~~~~~~~ Private Function FindRecord() 'if nothing is picked in the active control, exit If IsNull(Me.ActiveControl) Then Exit Function 'save current record if changes were made If me.dirty then me.dirty = false 'declare a variable to hold the primary key value to look up Dim mRecordID As Long 'set value to look up by what is selected mRecordID = Me.ActiveControl 'clear the choice to find Me.ActiveControl = Null 'find the first value that matches Me.RecordsetClone.FindFirst "SomeID = " & mRecordID 'if a matching record was found, then move to it If Not Me.RecordsetClone.NoMatch Then Me.Bookmark = Me.RecordsetClone.Bookmark End If End Function '~~~~~~~~~~~~~~~~~~~~ where - SomeID is the Name of the primary key field, which is in the RecordSource of the form -- assuming your primary key is a Long Integer data type (autonumbers are long integers) Remember that the Rowsource for a combo can come from anywhere -- it can pull from multiple tables or only use one ... just make sure that the first column is the primary key ID of the table you want to search (and that field is part of the RecordSource for the form you are searching). I usually make one or more unbound (no ControlSource) combos on your form (like in the header). Let the first column be invisible (unless you want to search on the ID smile) and be the primary key ID of the RecordSource of your form and then, on its AfterUpdate event... =FindRecord() you can use the same code for every 'find combo' -- and the RowSource can be anything as long as the first field is the primary key ID of your form for information about setting properties for a combobox, read the Combobox Example in: Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace Warm Regards, Crystal * (: have an awesome day * HWhite wrote: This is probably amazingly simple, but I'm missing it. "ID" is my primary key in the underlying table. I would like to add a button to my form that will go to ID# (whatever the user types in). I don't want to open the "find" screen and I don't want to filter the records. I simply want to go to the record that belongs to that ID number. Thanks for your help. |
#7
|
|||
|
|||
"Go To ID" Button?
Hi H,
you're welcome a procedure is either a Sub or a Function Its visibility is either Public or Private. If a procedure in code behind a form and is defined to be Private, then only that form can see it. here is some text from Access Basics, p 5-17: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~ To see the code behind a form (anything said about code behind a form also applies to code behind a report): from the menu in the design view of a form, choose -- View, Code This will take you to the module sheet that is stored 'behind' and with the form. When the form is copied, the module sheet tags along without you having to do anything special. This kind of code is a Class Module as opposed to a Standard (General) Module. When you Click on the Modules tab in the Database Window, you see a list of Standard Modules -- they are able to be used by any form, any report, any query ... anything in the database. Therefore, their scope is global. A Procedure is a general term encompassing Subs and Functions. If you look at a procedure declaration you might see something like this: Private Sub eAddress_DblClick(Cancel As Integer) Private means that only this form (and module stored behind the form) can see and use this code -- its scope is limited to the form. Sub means it is a Subroutine that does not return a value as opposed to a Function which does ... or maybe I should say 'can' -- return a value. A Function can do everything a Sub can -- a weird thing -- you cannot assign a Sub directly to a property event, it must be a Function by definition -- and there is no way to use a return value in these cases. Why Microsoft chose to use Functions instead of Subs in Access is a mystery. With this in mind, it seems odd that the [Event Procedure] code is a Sub --not consistent! ... anyway, I digress... eAddress_DblClick means that there is a control with the Name 'eAddress' and this is the DoubleClick event procedure -- so it will be launched (triggered) when the user double-clicks on the control. Cancel As Integer -- Cancel is a parameter to the sub -- although most people don't think of "Cancelling" a DoubleClick event because you can simply exit it! Cancel is better understood on an event such as BeforeUpdate where you Cancel the Update to a control or prevent Access from saving a record if certain conditions are not met. 'As Integer' means that Cancel is a whole number 32K. Some event procedures have Cancel as a parameter but most do not. For instance, out of the 50 events that can be defined at the form level, 13 of them have Cancel as a parameter. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~ anyway, thank you for your comments! I appreciate you mentioning something you thought I needed to know ... actually, I did so I could explain better smile Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day * HWhite wrote: What I meant was when I copy and pasted your code, saved and closed, it did not work. So I re-opened the code and it had changed the phrase "Private Function" to "Private Sub"... so that caused some confusion for a while. After realizing why the code wasn't working, Private Function/Private Sub was really an irrelevant issue for me, but I thought I would mention it to you in case it meant anything to you. Also, I bookmarked your page and reviewed it briefly. I look forward to spending more time with it. Thanks again. |
Thread Tools | |
Display Modes | |
|
|