"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

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

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

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

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

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

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

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