Opening a Record from a report

[TonyHeslop]

Hi Folks

What i have is a report which is filtered from a data table based on a persons login. The report shows individual records with a number of fields (as they do lol). I have a button at the end of each record for an action to be done. The action i want to be done is to have a form open up filtered to the record which the button was pressed for.

How do i do that? I can open up the form, that bit is easy, however, how do i make it so that the form is at the record that the user selects from the report?

Thanks
Tony

 

[Norie]

Tony

Is this really a report?

Why not use forms for the whole thing?

Reports aren't really designed for user interaction via buttons etc.

It would also be easy to have a form filtered to show only the relevant records for the user who's logged in.

 

[TonyHeslop]

Hi Norie

I currently have it set up as a report yes. I think my logic behind this was so that every record would be able to have the "Appeal" button along side it.

If i did that in a form, would i have to use a continuous form? would that be correct?

Thanls for your assistance
Tony

 

[Norie]

Tony

Well if you wanted to display multiple records yes.

It's actually kind of normal to use that type of form to display a bunch of records with enough fields to allow the user to identify each record.

You can also let the user filter and search for particular records.

Once they've done that they can click a button and open a form where the full record can be shown and the user can add/edit/delete.

Is that not the sort of thing you want?

 

[TonyHeslop]

Hi Norie

Not exactly.

I will give you a bit of scoop surrounding what the process is to see if you can think of a better solution.

Currently I have a SQL table with my Fails data in it. I have this linked to a spreadsheet which has all the fails listed for the current month. I have code written in the spreadsheet which enables the user to select a cell in the row of the fail they wish to appeal. They type in a reason for appeal and click submit, my code then writes that to an access table. The problem i am having is with new managers etc coming on board and access rights to our network drives changing, managers are getting errors whilst trying to use the tool as they dont have access to the correct folders. The time to get that access granted is creating lots of manual work for my validations team as they have to manually deal with those appeals.

What i am planning on doing is building a database with the sql tables linked in and locked hidden so that the managers cant access the tables maliciously, however can action their appeals without worry about needing access to network locations, hence reducint the workload my validations team has to manually deal with.

I decided it would be smarter to also filter the fails based on the managers login so they were only looking at their own fails rather than having to filter the records themselves (its all about the customer experience ). I have produced a query which gives me the results, I thought i would have to create a report based on that query so that the Appeal button would appear against each record. I have got that to happen, and I currently have code written for the Appeal button to open the "appeal form" in which they type their reason for appeal etc, however, i want it to update fields in the specific record they select to appeal.

If you would like me to describe anythin further please let me know.

Thanks
Tony

 

[Norie]

Tony

I don't think I'm really following this completely.

The data is actually stored in an SQL database, it's then imported/exported to Excel.

In Excel the user chooses a row/record they wish to appeal, enter a reason and then the data is written to a table in Access.

That's kind of where I get lost.

One thing I'm pretty sure of is you should use a form instead of a report.

Apologies if I'm being thick.

 

[TonyHeslop]

Hi Norie

Your not being thick at all

I have a query written in excel which brings in data between two dates from the SQL server table. This is essentially a linked sheet.

In this table of data that is brought in they select a cell within the row of the record they wish to appeal (basically in any column) press my macro button which uses the active cells row to extract the information i need to write to the datbase table. This is then written to a database table which is imported by my validations team as part of an automated process. The imported detail then gets updated to the original fails table which is housed on the SQL server.

What i am wanting to do is not use excel and present the data to the manager in a similar method to excel (so they feel familiar with it) and have a button which they click which starts the appeal process for them which will update straight away as its going to write straight to the SQL server table.

I thought a report would be the way to present them the info as they can see all of their fails listed one below each other. This is my way of preference. However, getting the record ID automatically is proving to be a problem.

Solutions i have thought about since asking the question are

1. Prompt the user to type in the record ID for the fail they wish to appeal, the form will then be filtered based on that record ID.
2. Create a continuous form which the appeal works on straight away. Less favourite about that as you can only see one record at a time.

Is that a little clearer?

Thanks
Tony

 

[Norie]

Tony

How would you only see 1 record at a time in a continuous form?

The way I see continuous forms is kind of like datasheets but with the added bonus that you can add buttons to them , textboxes/comboboxes for filtering etc.

By the way, if the ID is the only way the user can identify the appeal record then you could have a combobox with all the IDs.

Then the user picks the ID of interest they click a button and a form opens with all the fields from the record.

It could even have another field where they can fill in the reason for the appeal.

 

[TonyHeslop]

Hi Norie

I havent done a lot with continuous forms so thought it was one record and you scrolled down to advance to the next record etc?

Thanks
Tony

 

[Phildaburn]

Tony,
Here's an example of continuous form records: