Using buttons with macro in report

[CIIIHAN]

Hi there,

I'm pretty new to Access so be patient with me please

Here is what I want to achieve:

I have a report where all the records show and behind every one of them I've added a button.
Clicking this button should lead to a form with details about the record where the button is clicked.

The table has 6 columns:
first name, last name, address, city, state and zip

On the report only the first name is visible.
Clicking on the button behind the name leads to the form yet not at te record it should.

How can I achieve this?
Is this even possibel?
Is there a more efficient way to do this?

Hope Ive been clear enough.

Thanks in advanced!
Cihan

[Robeen]

It seems to me that you can just have a Form that is in Datasheet View and shows all the information for all Names on one page.

But, if you must have a Form with only the FirstName on it and another Form that shows the rest of the detail . . .

then what is the most common version of what you are describing is to have a Main Form and a Sub Form.

Your Main Form can be one that shows one record at a time or all records [Datasheet View].

The Main Form will have the First Name - or whatever detail you want on it.

The Sub Form shows the detail for the record selected on the Main Form.

When you change to a new record, the Sub Form displays detail for the new record.

[CIIIHAN]

Thanks for your reply

Well im trying to make something work.
Eventually, this is going to be for a table that has much more columns, like 30
and about 300 rows.

it is going to be a pain in the ass to scroll horizontally so thats why I want to list all the records and show details when clicked on the button.

As I noted im pretty new to this and can use any kind of feedback, maybe a better way to make this work.

I'll be looking into subforms and mainforms.

Replies are still welcome

[Robeen]

I think a Sub Form will work for you.

Look here for a good start:
http://office.microsoft.com/en-us/ac...005187803.aspx

[CIIIHAN]

Thanks again for your reply.

I have been busy with it for a while.
But it isn't really what I want.

Let me rescatch the situation.

The database has 24 rows and 1045 rows(I have the actual file now)
The database holds shipment info.

I want a form to show all the shipments in an excel like overview and possible a button at the end of each record
When the button is clicked further details about the shipment where the button was next to is shown.

the problem here is that when I want the filter to id number in the first form it always picks the first record.

Is it possible that when I click the like 30th button that the 30th record shows up?

Form1 = an overview with ALL shipments
Form2 = detailed view of selected shipment

Hope this clears it better,
English isn't my native language so I suppose this post is full of spelling/grammer errors

Cheers,
Cihan

[Robeen]

The database has 24 rows and 1045 rows(I have the actual file now)

Do you mean 24 Columns and 1045 Rows?

If you are using a Datasheet Form [Datasheet View is kind of like Excel] - then you don't really need a Command Button on each row to do what you need.
Whenever you click in a row, the detail for that record that you clicked in should show in the Sub Form.

I don't know if there is a way to do what you are trying to do with a Report.

I assume you are putting your Command button in the Detail Section of your report. Is that correct?

If that is the case, it will not be possible to get the command button to recognize which row you have clicked.

[CIIIHAN]

Yup, my bad 24 columns 1045 rows.

but where do you want me to put the subform?
the list of rows is too long to put it on the bottom.

here is a screen from the first 3 rows:


clicking that button should open the 2nd form which shows the details

[Robeen]

Can you send me a screenshot of the Design View of your report?

[CIIIHAN]

Sorry for not noting it.
I've changed the report into a form (the screenshot)

I'm still playing around with access.

That WAS the report.

[Robeen]

I have been trying to create a Form-SubForm using the scenario that you have described.

Is all your data in ONE Table which has 24 columns?

[CIIIHAN]

Yup, everything is one table

[Robeen]

You want to start out with a display of all the ROWS in a Excel-like format [Datasheet View] . . .

and when you click on a particular row - you want to see the rest of the detail for that row.

Is that correct?

[CIIIHAN]

DING! DING!

That is exactly what I'm looking for

[Robeen]

Here's one way of doing it. I tried this and it works.

1. Create a Form [Datasheet View] with the Summarized Detail you want to display to start with. Name it 'ShipmentDetail'. For my example - I am assuming you have a field on this Form called 'ShipmentCode' and that it is Text.

2. Create a Second Form that shows All the Detail you want to show once a particular record is selected on the first Form. This Form should also have the 'ShipmentCode' field. Put an Exit button on this Form to close it once you've finished looking at the detail.


3. In the First Form:
Select the Text box that has your Shipment.
Open the Property Sheet for that Text Box.
Click the Event Tab.
Click the Ellipsis button [...].
Choose code Builder.
The Visual Basic Editor will open with the Click Event for the Text box.

4. Paste this into the Code Editor:

 
Private Sub ShipmentCode_Click()
DoCmd.OpenForm "ShipmentDetail", , , "ShipmentCode = '" & Me.ShipmentCode & "'"
End Sub

If your ShipmentCode is numeric - you will have to use this instead:

 
DoCmd.OpenForm "ShipmentDetail", , , "ShipmentCode = " & Me.ShipmentCode

Run the First Form. Click in one ot the ShipmentCode boxes. It should open up the Detail Form with all the detail.

Let me know if this helps.

[TinaCa]

This sounds like something I've done but...i want to clarify a few things.
Do You have a company (or other entity) that has shipments?
and then have information (details) about all the shipments for that entity?
Lets start there first