Creating a form based on SQL View

[j_gold]

I am linking to a MySQL database and have created various views. I have been
using these views to create reports successfully.

One of the views I wish to display as a datasheet, so I created a form based
on that view. However, the form only displays the first record associated
with an individual. The recordcount shows 7, and 7 "records" are displayed
but they contain the same information.

e.g.

bookID person Title listPrice
1 "Smith, John" , "A Gazillion (+ 2) ways to screw things up in Access", 10
1 "Smith, John" , "A Gazillion (+ 2) ways to screw things up in Access", 10
1 "Smith, John" , "A Gazillion (+ 2) ways to screw things up in Access", 10
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0

Can anyone tell my why this works when done as a report, but not a datasheet?

Thanks,

J

 

[KARL DEWEY]

The report probably hid the duplicates. For a test, look directly at the
data of the linked view (not the form) and see what is displayed.

 

[John W. Vinson]

I am linking to a MySQL database and have created various views. I have been
using these views to create reports successfully.

One of the views I wish to display as a datasheet, so I created a form based
on that view. However, the form only displays the first record associated
with an individual. The recordcount shows 7, and 7 "records" are displayed
but they contain the same information.

e.g.

bookID person Title listPrice
1 "Smith, John" , "A Gazillion (+ 2) ways to screw things up in Access", 10
1 "Smith, John" , "A Gazillion (+ 2) ways to screw things up in Access", 10
1 "Smith, John" , "A Gazillion (+ 2) ways to screw things up in Access", 10
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0
4 "Franklin, Ernie" , "Zen ways to deal with Access issues", 0

Can anyone tell my why this works when done as a report, but not a datasheet?

What do you see when you open the query (linked view?) directly? Do you
perhaps have something in the report's Sorting and Grouping to collapse the
multiple records? Perhaps you could post the SQL view of the query (either the
Access Recordsource or the MySQL view).

 

[j_gold]

Hi Karl,

Sorry, need to clarify. It is not a report that I want to generate. I've
created it as a report and it works. What I am trying to do is to create a
new form based on the same SQL View (not a table) but present it as a
datasheet. Can this be done?

Thanks,

J

 

[John W. Vinson]

What I am trying to do is to create a
new form based on the same SQL View (not a table) but present it as a
datasheet. Can this be done?

Yes.

Whether it's editable depends on the view (and on the MySQL ODBC driver), but
a correctly structured view will not have duplicates.

 

[j_gold]

Hi John,

Thank you for your reply.

To clarify, I've linked to the view which is in a MySQL db (it shows as a
linked table in Access). I am able to generate a report without any
difficulty; however, when I view same linked "table" (the view in mysql)
directly, it shows as noted in my previous post.

Cheers,

J.

 

[KARL DEWEY]

You need to re-read responses to your post.
--- A report can eliminate duplicates so they do not display so that may be
why they are not showing there.
--- Look directly at the link (normally you should not view tables or links
but use a query, form or report) to see if there are duplicates before Access
does any manipulation.

 

[John W. Vinson]

To clarify, I've linked to the view which is in a MySQL db (it shows as a
linked table in Access). I am able to generate a report without any
difficulty; however, when I view same linked "table" (the view in mysql)
directly, it shows as noted in my previous post.

Again:

Please post the Recordsource property of the report. Also indicate what, if
anything, is in the Report's Sorting and Grouping dialog.

Also post the SQL of the MySQL view.

 

[David W. Fenton]

Also post the SQL of the MySQL view.

MySQL has views?

 

[John W. Vinson]

MySQL has views?

So it would seem: from j_gold's original post:

I am linking to a MySQL database and have created various views. I have been
using these views to create reports successfully.

 

[David W. Fenton]

So it would seem: from j_gold's original post:

I am linking to a MySQL database and have created various views. I
have been using these views to create reports successfully.

After posting, I thought to look it up, and it seems the MySQL 5
added them. I haven't had the opportunity to work with MySQL 5, so I
didn't know that.