Can I make a button do this?

[Sancti0n]

I have a form which has 7 fields, listed top-to-bottom. When I open this form it retrieves the first record from a single table and displays that record in those 7 fields.

I want to place 1 button on this form (several in fact, but 1 to start out with since this is so new to me). The sole purpose of this button would be to change the Record Source of the form. It would change the source from showing all info for all entries in the table (as it does when I first open up the form), to showing all info for a single item. Also, it would display the results in those same 7 fields on the form.

I know I can just change the view to design view and change the source there, but I don't want the end user to have to do that. As new as I am to Access 2007, they are even newer to using PC's in general, so I want/NEED to keep things as simple as possible. I only want 1 button to start out, but eventually there will be several, and if I can limit their actions to just clicking buttons it'll make things much easier for them AND me.

OR...

A different approach would be to have a button that runs a query. I've actually done this, but the results of pressing the button show up in a new tab, and I want the results to show up in those 7 fields I mentioned above.

If it helps, this is purely for viewing on-screen. It won't be printed. Also, the records will not be added/deleted/edited. Just looked at.

So a button to either A: change the record source and display the results in those 7 fields on the form, or B: place query results in those 7 fields on the form. Can somebody tell/show me how to do this (assuming it even can be done)? Know of any tutorials that can walk me through it?

Thank you in advance for your time and help

Joe

 

[ColinEssex]

How is the user going to select which record to show the info of?

Col

 

[highandwild]

Hi.

Just add an On Click Event on the button.

For example:

Private Sub cmdYourButton_Click()

Me.Filter = "[TransactionId] = 167"
Me.FilterOn = True

End Sub

where cmdYourButton is the button name.

Substitute "[TransactionId] = 167" for your own criteria, the filter.

Just Google "ms access add command button" as there is lots of help on the interwebby.

HighandWild

 

[Sancti0n]

@ColinEssex:

They'll see all records for item-x (item-x = Desktops, Laptops, Thin Clients, etc...).

EX: There's a button for Desktops, a button for Laptops, one for Thin Clients, etc. They push the button and all Desktops are selected, then they use the record nav buttons in the lower left of the form to view each of them (and their related info), one at a time.

@highandwild:

I'll try your example, and do some more looking.

Thank you both for your time

 

[highandwild]

Hi

Instead of having buttons for each dfferent set of records:

Add a combo box on the form with a row source such as

SELECT tbl_ItemList.Item FROM tbl_ItemList
GROUP BY Item ORDER BY Item

where tbl_ItemList is the same table that the form is based on

and then use the value of the item selected in the form filter.

If different items are added to the table they will always be able to be viewed by the user.

There are more complicated ways but this will work fine for now.

HighandWild

 

[Sess]

I was going to suggect same as this except to put a union query for the query source and have " All Orders" as the default value of the combobox. I do this for things like looking for order details so here is my sample for the union query.

SELECT " All Orders" as TempOrder FROM tblYOURTABLE
UNION
SELECT FIELD1.tblYOURTABLE as TempOrder FROM tblYOURTABLE
order by TempOrder;

Then just make a command button to open up the display form
Code under the button with an IF statement to test the combo value so that if it equals " All Orders" it opens the form to all records the else clause can have the criteria equal to the combo value

Just make sure that there is a space in front of the " All Orders" so it is sorted ahead of alpha or numeric values and at the top of the list allways that way.

 

[vbaInet]

I would go for:

1. Using a check box or a multiselect list box
2. The user ticks or selects which fields he/she wants to view
3. Based on the selections/ticks you simply Hide that control (using the Visible property)

Keep the query the same, keep the row source the same, just hide the relevant controls.