Archive database

[Mavis]

Hi All,

I have a old database with all the company IT assets. A main form is use to
show user the all the assets and also allow them to edit or add new asset.
As the database growing bigger, i will only like to have the user to view
the Asset with the status does not equal to “disposed†from the main form and
create another form for the user to view all Assets with all status by
clicking a button.

May I know how can i do that?

 

[Keith Wilby]

Hi All,

I have a old database with all the company IT assets. A main form is use
to
show user the all the assets and also allow them to edit or add new asset.
As the database growing bigger, i will only like to have the user to view
the Asset with the status does not equal to “disposed†from the main form
and
create another form for the user to view all Assets with all status by
clicking a button.

May I know how can i do that?

One way to do it would be to have an option group with radio buttons. In
the option group's After Update event, run some code to return the required
records (untested air code):

Private Sub ogrMyOptionGroup_AfterUpdate()

Dim strSQL As String
strSQL = "Select * From tblMyTable Where [Status] "

If ogrMyOptionGroup = 1 Then
strSQL = strSQL & "= 'Disposed'"
Else
strSQL = strSQL & "<> 'Disposed'"
End If

Me.RecordSource = strSQL

End Sub

You'll need to call that subroutine from the form's Open event too.

Keith.
www.keithwilby.co.uk

 

[Mavis]

Hi Keith,

I am very new to access 2007. can you tell me the detail or is there another
easiler way i can do that?

Hi All,

I have a old database with all the company IT assets. A main form is use
to
show user the all the assets and also allow them to edit or add new asset.
As the database growing bigger, i will only like to have the user to view
the Asset with the status does not equal to “disposed†from the main form
and
create another form for the user to view all Assets with all status by
clicking a button.

May I know how can i do that?

One way to do it would be to have an option group with radio buttons. In
the option group's After Update event, run some code to return the required
records (untested air code):

Private Sub ogrMyOptionGroup_AfterUpdate()

Dim strSQL As String
strSQL = "Select * From tblMyTable Where [Status] "

If ogrMyOptionGroup = 1 Then
strSQL = strSQL & "= 'Disposed'"
Else
strSQL = strSQL & "<> 'Disposed'"
End If

Me.RecordSource = strSQL

End Sub

You'll need to call that subroutine from the form's Open event too.

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

Hi Keith,

I am very new to access 2007. can you tell me the detail or is there
another
easiler way i can do that?

The easiest way I can think of without using code is to tell your users how
to use "filter by selection". It's a right-click menu option when the form
is in normal (ie not design) view. Is that worth trying?

Keith.

 

[Mavis]

Hi Keith,

What do u mean by right click on the menu option?

 

[Mavis]

Hi Keith,

Let me explain the whole database clearly so that you will have a picture on
how my database work.
There is a switchboard for me to select to enter to a form "Add/Edit PC
Asset".
Once the user click on "Add/Edit PC Asset", the "Add/Edit PC Asset" form
will appear and shows all the records in the database and allow user to enter
new record or edit existing record.
There is a field "Status" in the form for the user to enter the status of
the asset.
The request is that when the user enter into the "Add/Edit PC Asset" form,
the record with the status = “disposed†will not show. It will only show
those records does not equal to “disposedâ€.
I will need to create something for the user to click to show all the
records (include status equal to “disposedâ€).

How can i do so? can you teach me the steps to do that?

 

[Keith Wilby]

Hi Keith,

Let me explain the whole database clearly so that you will have a picture
on
how my database work.
There is a switchboard for me to select to enter to a form "Add/Edit PC
Asset".
Once the user click on "Add/Edit PC Asset", the "Add/Edit PC Asset" form
will appear and shows all the records in the database and allow user to
enter
new record or edit existing record.
There is a field "Status" in the form for the user to enter the status of
the asset.
The request is that when the user enter into the "Add/Edit PC Asset" form,
the record with the status = “disposed†will not show. It will only show
those records does not equal to “disposedâ€.
I will need to create something for the user to click to show all the
records (include status equal to “disposedâ€).

How can i do so? can you teach me the steps to do that?

OK so I think it's back to the radio button method. Bear in mind that I
don't have A2007 but the A2003 method shouldn't be much different.

Open your form in design mode. Put an option group with two radio buttons
on your form. Make the data values for each radio button 1 and 2
respectively. Set the default value to 1. Label button 1 "Disposed" and
button 2 "All". There's a wizard to help you if needs be.

In the option group's After Update event put this code*:

Call Form_Open(False)

In your *form's* Open event put this code (change the object names to suit
where necessary):

Dim strSQL As String
strSQL = "Select * From tblMyTable"

If ogrMyOptionGroup = 1 Then
strSQL = strSQL & " Where [Status] = 'disposed'"
End If

Me.RecordSource = strSQL

Note the use of the single quotes around the word 'disposed'. The idea is
that, when your form opens, option 1 will be selected by default and so only
'disposed' records will be displayed. When the user makes a selection from
the option group the form's Open event will be triggered again and the
record source of the form updated accordingly.

After you write your code you should compile it to see if there are any
errors in it. Choose "Compile" from the "Debug" menu. Please note that I
haven't tested this code.

*If you don't know how to open a code window here's how: on the properties
palette, click the "event" tab. If you hover your pointer over the
right-hand side of the white box of the required event it will change to an
arrow - click here and it will give you a drop-down list. Choose [Event
Procedure] then click the button with three dots on it. This is where you
paste your code.

It's difficult to explain without pictures but I hope that helps and I hope
that A2007 isn't too far removed from my method.

Keith.
www.keithwilby.co.uk

 

[Mavis]

Hi Keith,

thank you so much for your detail guide!!!!

Hi Keith,

Let me explain the whole database clearly so that you will have a picture
on
how my database work.
There is a switchboard for me to select to enter to a form "Add/Edit PC
Asset".
Once the user click on "Add/Edit PC Asset", the "Add/Edit PC Asset" form
will appear and shows all the records in the database and allow user to
enter
new record or edit existing record.
There is a field "Status" in the form for the user to enter the status of
the asset.
The request is that when the user enter into the "Add/Edit PC Asset" form,
the record with the status = “disposed†will not show. It will only show
those records does not equal to “disposedâ€.
I will need to create something for the user to click to show all the
records (include status equal to “disposedâ€).

How can i do so? can you teach me the steps to do that?

OK so I think it's back to the radio button method. Bear in mind that I
don't have A2007 but the A2003 method shouldn't be much different.

Open your form in design mode. Put an option group with two radio buttons
on your form. Make the data values for each radio button 1 and 2
respectively. Set the default value to 1. Label button 1 "Disposed" and
button 2 "All". There's a wizard to help you if needs be.

In the option group's After Update event put this code*:

Call Form_Open(False)

In your *form's* Open event put this code (change the object names to suit
where necessary):

Dim strSQL As String
strSQL = "Select * From tblMyTable"

If ogrMyOptionGroup = 1 Then
strSQL = strSQL & " Where [Status] = 'disposed'"
End If

Me.RecordSource = strSQL

Note the use of the single quotes around the word 'disposed'. The idea is
that, when your form opens, option 1 will be selected by default and so only
'disposed' records will be displayed. When the user makes a selection from
the option group the form's Open event will be triggered again and the
record source of the form updated accordingly.

After you write your code you should compile it to see if there are any
errors in it. Choose "Compile" from the "Debug" menu. Please note that I
haven't tested this code.

*If you don't know how to open a code window here's how: on the properties
palette, click the "event" tab. If you hover your pointer over the
right-hand side of the white box of the required event it will change to an
arrow - click here and it will give you a drop-down list. Choose [Event
Procedure] then click the button with three dots on it. This is where you
paste your code.

It's difficult to explain without pictures but I hope that helps and I hope
that A2007 isn't too far removed from my method.

Keith.
www.keithwilby.co.uk

 

[Keith Wilby]

Hi Keith,

thank you so much for your detail guide!!!!

You're welcom Mavis, hope it helps.

Keith.