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
|
|||
|
|||
"Open Form" Button Function
Hello,
I'm very new to Access, so I apologize if this question has been answered before, but I am having trouble with the button function wizard. I am creating an inventory database that creates a history of who has checked out what equipment in the past, plus what equipment is currently checked out. I have one Checkout/Checkin form based on an inventory history table that creates a new record whenever someone checks out an item (the table includes Equipment Number, Employee ID, Date Out and Date Returned). The form has Equip Number, Employee ID and Date Out fields. I then have a query to display equipment that is currently checked out (Date Returned = Is Null). On my Checkout/Checkin form I have two buttons: Checkout and Checkin. The Checkout button creates a new record and works fine. According to the button wizard, I set the Checkin button to open the Checkin form and search for a record. I set the Equip Number fields in both forms to be related, so I was under the impression that upon entering an Equip Number into the Checkout/Checkin form and pressing "Checkin," the Checkin form should open and display the associated record from the query. After this the user should be able to enter the date returned, and hit "Return Equip" which saves the record and closes the form. Instead, the form simply displays the first record in the query. Not very helpful. So am I misunderstanding the purpose of this button, or am I simply doing something wrong? Any help would be greatly appreciated. Thanks, BenD |
#2
|
|||
|
|||
"Open Form" Button Function
I'm confused. In one part of your post you say you have "one Checkout/Checkin
form", implying that the same form is used for both. Then you say you are trying to open a separate "Checkin" form. At any rate, why not just code the "Checkin" button to automatically enter the current date in the DateReturned field, then just requery your form? Then your users wouldn't need to do anything additional. -- _________ Sean Bailey "BenD" wrote: Hello, I'm very new to Access, so I apologize if this question has been answered before, but I am having trouble with the button function wizard. I am creating an inventory database that creates a history of who has checked out what equipment in the past, plus what equipment is currently checked out. I have one Checkout/Checkin form based on an inventory history table that creates a new record whenever someone checks out an item (the table includes Equipment Number, Employee ID, Date Out and Date Returned). The form has Equip Number, Employee ID and Date Out fields. I then have a query to display equipment that is currently checked out (Date Returned = Is Null). On my Checkout/Checkin form I have two buttons: Checkout and Checkin. The Checkout button creates a new record and works fine. According to the button wizard, I set the Checkin button to open the Checkin form and search for a record. I set the Equip Number fields in both forms to be related, so I was under the impression that upon entering an Equip Number into the Checkout/Checkin form and pressing "Checkin," the Checkin form should open and display the associated record from the query. After this the user should be able to enter the date returned, and hit "Return Equip" which saves the record and closes the form. Instead, the form simply displays the first record in the query. Not very helpful. So am I misunderstanding the purpose of this button, or am I simply doing something wrong? Any help would be greatly appreciated. Thanks, BenD |
#3
|
|||
|
|||
"Open Form" Button Function
Thanks for the reply Sean.
In answer to your first question: Yes, there are two separate forms, one called "Checkout/Checkin" and one called "Checkin." The latter of these is hidden, with the idea that new users will only ever have to open/know about the first form. In retrospect, it's pretty confusing. In answer to your second question: Because I did not know this was a possibility (I've used Access for all of a week). It sounds like a much better method, however. So my next question is how do I do this? Do I need to use VBA (which I don't know) or is there a macro for it? Thanks, Ben "Beetle" wrote: I'm confused. In one part of your post you say you have "one Checkout/Checkin form", implying that the same form is used for both. Then you say you are trying to open a separate "Checkin" form. At any rate, why not just code the "Checkin" button to automatically enter the current date in the DateReturned field, then just requery your form? Then your users wouldn't need to do anything additional. -- _________ Sean Bailey |
#4
|
|||
|
|||
"Open Form" Button Function
It's rather simple to do. You already have the command button, so you just
need some simple code in it's Click event. Open your form in Design View, select the "Checkin" command button, open the properties sheet and go to the Event tab. If you used the wizard to create the button, there is likely already some code there. When you look at the line for On Click it should have [Event Procedure] listed. Click the elipse at the right side to open the code window. You should see something like; ************************************************** Private Sub Command15_Click() On Error GoTo Err_Command15_Click blah blah blah some code here delete whatever code is in some more code here this area Exit_Command15_Click: Exit Sub Err_Command15_Click: MsgBox Err.Description Resume Exit_Command15_Click End Sub ************************************************** * Delete whatever code is between the On Error GoTo etc. and the Exit_Command15_Click: (or whatever it says) Now, assuming that the field in your forms record source that stores the return date is called DateReturned, then you would put the following in the same area where you deleted the previous code; Me![DateReturned] = Date() Me.Requery This will insert today's date in the field, and requery the form so that record will no longer be displayed. Now, if your users do not always process returns on the same day that they are actually returned, then you may want to do it slightly different. For example, you could add an unbound text box on your form next to the comman button (i.e. txtReturnDate) where your users could enter the actual date the item was returned. Then you would do everyting the same as before, except the code you enter would be; Me![DateReturned] = Me![txtReturnDate] Me.Requery This would insert whatever date value is in the unbound text box into the field. -- _________ Sean Bailey "BenD" wrote: Thanks for the reply Sean. In answer to your first question: Yes, there are two separate forms, one called "Checkout/Checkin" and one called "Checkin." The latter of these is hidden, with the idea that new users will only ever have to open/know about the first form. In retrospect, it's pretty confusing. In answer to your second question: Because I did not know this was a possibility (I've used Access for all of a week). It sounds like a much better method, however. So my next question is how do I do this? Do I need to use VBA (which I don't know) or is there a macro for it? Thanks, Ben "Beetle" wrote: I'm confused. In one part of your post you say you have "one Checkout/Checkin form", implying that the same form is used for both. Then you say you are trying to open a separate "Checkin" form. At any rate, why not just code the "Checkin" button to automatically enter the current date in the DateReturned field, then just requery your form? Then your users wouldn't need to do anything additional. -- _________ Sean Bailey |
#5
|
|||
|
|||
"Open Form" Button Function
That was immensely helpful, thank you very much. I have one more question
however: how do I get the button to update an existing record rather than add a new record? This event needs to be able to find a record with matching Equip Number and Employee ID with the criteria that DateReturned Is Null, then update that record. Any thoughts? "Beetle" wrote: It's rather simple to do. You already have the command button, so you just need some simple code in it's Click event. Open your form in Design View, select the "Checkin" command button, open the properties sheet and go to the Event tab. If you used the wizard to create the button, there is likely already some code there. When you look at the line for On Click it should have [Event Procedure] listed. Click the elipse at the right side to open the code window. You should see something like; ************************************************** Private Sub Command15_Click() On Error GoTo Err_Command15_Click blah blah blah some code here delete whatever code is in some more code here this area Exit_Command15_Click: Exit Sub Err_Command15_Click: MsgBox Err.Description Resume Exit_Command15_Click End Sub ************************************************** * Delete whatever code is between the On Error GoTo etc. and the Exit_Command15_Click: (or whatever it says) Now, assuming that the field in your forms record source that stores the return date is called DateReturned, then you would put the following in the same area where you deleted the previous code; Me![DateReturned] = Date() Me.Requery This will insert today's date in the field, and requery the form so that record will no longer be displayed. Now, if your users do not always process returns on the same day that they are actually returned, then you may want to do it slightly different. For example, you could add an unbound text box on your form next to the comman button (i.e. txtReturnDate) where your users could enter the actual date the item was returned. Then you would do everyting the same as before, except the code you enter would be; Me![DateReturned] = Me![txtReturnDate] Me.Requery This would insert whatever date value is in the unbound text box into the field. -- _________ Sean Bailey "BenD" wrote: Thanks for the reply Sean. In answer to your first question: Yes, there are two separate forms, one called "Checkout/Checkin" and one called "Checkin." The latter of these is hidden, with the idea that new users will only ever have to open/know about the first form. In retrospect, it's pretty confusing. In answer to your second question: Because I did not know this was a possibility (I've used Access for all of a week). It sounds like a much better method, however. So my next question is how do I do this? Do I need to use VBA (which I don't know) or is there a macro for it? Thanks, Ben "Beetle" wrote: I'm confused. In one part of your post you say you have "one Checkout/Checkin form", implying that the same form is used for both. Then you say you are trying to open a separate "Checkin" form. At any rate, why not just code the "Checkin" button to automatically enter the current date in the DateReturned field, then just requery your form? Then your users wouldn't need to do anything additional. -- _________ Sean Bailey |
#6
|
|||
|
|||
"Open Form" Button Function
The only way the code I suggested would create a new record is if your
form was already on a new record (and you hadn't entered anything) when you clicked the button. Then it would insert a date value into the DateReturned field, thereby creating a new record. I was assuming that your users would already be at the appropriate record when they clicked the button. You said that you had a query that only displayed items without a return date. I thought you meant that your form was based on this query. Is that not the case? -- _________ Sean Bailey "BenD" wrote: That was immensely helpful, thank you very much. I have one more question however: how do I get the button to update an existing record rather than add a new record? This event needs to be able to find a record with matching Equip Number and Employee ID with the criteria that DateReturned Is Null, then update that record. Any thoughts? "Beetle" wrote: It's rather simple to do. You already have the command button, so you just need some simple code in it's Click event. Open your form in Design View, select the "Checkin" command button, open the properties sheet and go to the Event tab. If you used the wizard to create the button, there is likely already some code there. When you look at the line for On Click it should have [Event Procedure] listed. Click the elipse at the right side to open the code window. You should see something like; ************************************************** Private Sub Command15_Click() On Error GoTo Err_Command15_Click blah blah blah some code here delete whatever code is in some more code here this area Exit_Command15_Click: Exit Sub Err_Command15_Click: MsgBox Err.Description Resume Exit_Command15_Click End Sub ************************************************** * Delete whatever code is between the On Error GoTo etc. and the Exit_Command15_Click: (or whatever it says) Now, assuming that the field in your forms record source that stores the return date is called DateReturned, then you would put the following in the same area where you deleted the previous code; Me![DateReturned] = Date() Me.Requery This will insert today's date in the field, and requery the form so that record will no longer be displayed. Now, if your users do not always process returns on the same day that they are actually returned, then you may want to do it slightly different. For example, you could add an unbound text box on your form next to the comman button (i.e. txtReturnDate) where your users could enter the actual date the item was returned. Then you would do everyting the same as before, except the code you enter would be; Me![DateReturned] = Me![txtReturnDate] Me.Requery This would insert whatever date value is in the unbound text box into the field. -- _________ Sean Bailey "BenD" wrote: Thanks for the reply Sean. In answer to your first question: Yes, there are two separate forms, one called "Checkout/Checkin" and one called "Checkin." The latter of these is hidden, with the idea that new users will only ever have to open/know about the first form. In retrospect, it's pretty confusing. In answer to your second question: Because I did not know this was a possibility (I've used Access for all of a week). It sounds like a much better method, however. So my next question is how do I do this? Do I need to use VBA (which I don't know) or is there a macro for it? Thanks, Ben "Beetle" wrote: I'm confused. In one part of your post you say you have "one Checkout/Checkin form", implying that the same form is used for both. Then you say you are trying to open a separate "Checkin" form. At any rate, why not just code the "Checkin" button to automatically enter the current date in the DateReturned field, then just requery your form? Then your users wouldn't need to do anything additional. -- _________ Sean Bailey |
#7
|
|||
|
|||
"Open Form" Button Function
The form is indeed based on the query. I think the problem lies in the fact
that I have the form set for data entry so that the Checkout button creates a new record and then clears the form (which I assume puts the form on a new record). I think I can easily get around this by turning off data entry, then adding a couple more buttons (one to put the form on a new record for checkout and one to search for and display the old record for checkin). I shouldn't need any more help (unless you know a quick way to get around this...I don't want to waste your time). Thank you very much! Ben "Beetle" wrote: The only way the code I suggested would create a new record is if your form was already on a new record (and you hadn't entered anything) when you clicked the button. Then it would insert a date value into the DateReturned field, thereby creating a new record. I was assuming that your users would already be at the appropriate record when they clicked the button. You said that you had a query that only displayed items without a return date. I thought you meant that your form was based on this query. Is that not the case? -- _________ Sean Bailey "BenD" wrote: That was immensely helpful, thank you very much. I have one more question however: how do I get the button to update an existing record rather than add a new record? This event needs to be able to find a record with matching Equip Number and Employee ID with the criteria that DateReturned Is Null, then update that record. Any thoughts? "Beetle" wrote: It's rather simple to do. You already have the command button, so you just need some simple code in it's Click event. Open your form in Design View, select the "Checkin" command button, open the properties sheet and go to the Event tab. If you used the wizard to create the button, there is likely already some code there. When you look at the line for On Click it should have [Event Procedure] listed. Click the elipse at the right side to open the code window. You should see something like; ************************************************** Private Sub Command15_Click() On Error GoTo Err_Command15_Click blah blah blah some code here delete whatever code is in some more code here this area Exit_Command15_Click: Exit Sub Err_Command15_Click: MsgBox Err.Description Resume Exit_Command15_Click End Sub ************************************************** * Delete whatever code is between the On Error GoTo etc. and the Exit_Command15_Click: (or whatever it says) Now, assuming that the field in your forms record source that stores the return date is called DateReturned, then you would put the following in the same area where you deleted the previous code; Me![DateReturned] = Date() Me.Requery This will insert today's date in the field, and requery the form so that record will no longer be displayed. Now, if your users do not always process returns on the same day that they are actually returned, then you may want to do it slightly different. For example, you could add an unbound text box on your form next to the comman button (i.e. txtReturnDate) where your users could enter the actual date the item was returned. Then you would do everyting the same as before, except the code you enter would be; Me![DateReturned] = Me![txtReturnDate] Me.Requery This would insert whatever date value is in the unbound text box into the field. -- _________ Sean Bailey "BenD" wrote: Thanks for the reply Sean. In answer to your first question: Yes, there are two separate forms, one called "Checkout/Checkin" and one called "Checkin." The latter of these is hidden, with the idea that new users will only ever have to open/know about the first form. In retrospect, it's pretty confusing. In answer to your second question: Because I did not know this was a possibility (I've used Access for all of a week). It sounds like a much better method, however. So my next question is how do I do this? Do I need to use VBA (which I don't know) or is there a macro for it? Thanks, Ben "Beetle" wrote: I'm confused. In one part of your post you say you have "one Checkout/Checkin form", implying that the same form is used for both. Then you say you are trying to open a separate "Checkin" form. At any rate, why not just code the "Checkin" button to automatically enter the current date in the DateReturned field, then just requery your form? Then your users wouldn't need to do anything additional. -- _________ Sean Bailey |
#8
|
|||
|
|||
"Open Form" Button Function
You're welcome
Good Luck -- _________ Sean Bailey "BenD" wrote: The form is indeed based on the query. I think the problem lies in the fact that I have the form set for data entry so that the Checkout button creates a new record and then clears the form (which I assume puts the form on a new record). I think I can easily get around this by turning off data entry, then adding a couple more buttons (one to put the form on a new record for checkout and one to search for and display the old record for checkin). I shouldn't need any more help (unless you know a quick way to get around this...I don't want to waste your time). Thank you very much! Ben "Beetle" wrote: The only way the code I suggested would create a new record is if your form was already on a new record (and you hadn't entered anything) when you clicked the button. Then it would insert a date value into the DateReturned field, thereby creating a new record. I was assuming that your users would already be at the appropriate record when they clicked the button. You said that you had a query that only displayed items without a return date. I thought you meant that your form was based on this query. Is that not the case? -- _________ Sean Bailey "BenD" wrote: That was immensely helpful, thank you very much. I have one more question however: how do I get the button to update an existing record rather than add a new record? This event needs to be able to find a record with matching Equip Number and Employee ID with the criteria that DateReturned Is Null, then update that record. Any thoughts? "Beetle" wrote: It's rather simple to do. You already have the command button, so you just need some simple code in it's Click event. Open your form in Design View, select the "Checkin" command button, open the properties sheet and go to the Event tab. If you used the wizard to create the button, there is likely already some code there. When you look at the line for On Click it should have [Event Procedure] listed. Click the elipse at the right side to open the code window. You should see something like; ************************************************** Private Sub Command15_Click() On Error GoTo Err_Command15_Click blah blah blah some code here delete whatever code is in some more code here this area Exit_Command15_Click: Exit Sub Err_Command15_Click: MsgBox Err.Description Resume Exit_Command15_Click End Sub ************************************************** * Delete whatever code is between the On Error GoTo etc. and the Exit_Command15_Click: (or whatever it says) Now, assuming that the field in your forms record source that stores the return date is called DateReturned, then you would put the following in the same area where you deleted the previous code; Me![DateReturned] = Date() Me.Requery This will insert today's date in the field, and requery the form so that record will no longer be displayed. Now, if your users do not always process returns on the same day that they are actually returned, then you may want to do it slightly different. For example, you could add an unbound text box on your form next to the comman button (i.e. txtReturnDate) where your users could enter the actual date the item was returned. Then you would do everyting the same as before, except the code you enter would be; Me![DateReturned] = Me![txtReturnDate] Me.Requery This would insert whatever date value is in the unbound text box into the field. -- _________ Sean Bailey "BenD" wrote: Thanks for the reply Sean. In answer to your first question: Yes, there are two separate forms, one called "Checkout/Checkin" and one called "Checkin." The latter of these is hidden, with the idea that new users will only ever have to open/know about the first form. In retrospect, it's pretty confusing. In answer to your second question: Because I did not know this was a possibility (I've used Access for all of a week). It sounds like a much better method, however. So my next question is how do I do this? Do I need to use VBA (which I don't know) or is there a macro for it? Thanks, Ben "Beetle" wrote: I'm confused. In one part of your post you say you have "one Checkout/Checkin form", implying that the same form is used for both. Then you say you are trying to open a separate "Checkin" form. At any rate, why not just code the "Checkin" button to automatically enter the current date in the DateReturned field, then just requery your form? Then your users wouldn't need to do anything additional. -- _________ Sean Bailey |
Thread Tools | |
Display Modes | |
|
|