Using a list box to choose columns for a query 2

[grantwilliams]

Hi,

I have a form (MainForm), which has a subform (MainFrame) consisting of two list boxes (YearList and Team) and a Command Button.

At the moment, the user selects a Year and the Team list box will show a list of teams that played that year. The user selects a team and presses the Command Button.

I have the Command Button opening a Form in Datasheet View, using the following code to create the RecordSource:

Private Sub Form_Load()

Dim Year, Team, Receipt, DBIDSQL, NameSQL, TeamSQL, PlayerReceiptSQL

Year = Forms!MainForm!MainFrame.Form.YearList
Team = Forms!MainForm!MainFrame.Form.Team
Receipt = Forms!MainForm!MainFrame.Form.YearList & "_Receipt"

DBIDSQL = "SELECT DISTINCT PlayerHistory.RegisterID, " & _
"[PlayerDetails].[Surname], [PlayerDetails].[GivenName] As [GivenName], " &_
"[PlayerHistory].[" & Year & "] As [Team], [PlayerReceipt].[" & _
& Receipt & "] As [Receipt] FROM ([PlayerDetails] INNER JOIN [PlayerHistory]" & _
" ON [PlayerDetails].[RegisterID] = [PlayerHistory].[RegisterID])" & _
" INNER JOIN [PlayerReceipt] ON ([PlayerReceipt].[RegisterID]" & _
" = [PlayerDetails].[RegisterID]) AND ([PlayerHistory].[RegisterID]" & _
" = [PlayerReceipt].[RegisterID]) WHERE [PlayerHistory].[" & Year & "]" & _
" = '" & Team & "';"

Me.RecordSource = DBIDSQL

End Sub

This produces the right data in the datasheet, but I want to be able to edit the fields, for instance, the team field once team grading is completed.

I guess that there's something wrong in the way I'm referencing the three tables, perhaps in using the INNER JOIN statement.

Can anyone help me with this? I've been working on this database for 3 weeks and my brain is now officially fried. I've spent 8 hours on this one problem so far and was able to produce the datasheet 6 hours ago!

Is there any way of doing this using a standard SQL query, given that I'm using the Year list box to choose which column of the PlayerHistory table to query on?

Thanks!

Grant.

 

[Steve101]

Grant,

A couple of things to look out for if you want the recordset created from the above sql to be editable:

(a) Make sure that ALL join fields are indexed.
(b) Joins should involve unique primary keys from one table to a foreign key in another table.
(c) Remove the DISTINCT keyword above. Distinct records do not map one for one on the actual data records, and as such I think, prevent the recordset from being updated.

Try getting this query to work and be editable in the query window before incorporating it into code, or a form. This way you can get it working faster.

Hope this helps,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[grantwilliams]

Hi Steve!

Thanks for the reply...

I'm pretty much learning most of this as I go. I have 3 tables in my database, PlayerDetails, PlayerHistory and PlayerReceipt. All tables have a single common Primary Key: RegisterID. Is this where I am having a problem?

I have it set up so that when I add a new player to the PlayerDetails table, it Inserts rows to the other two tables with the same RegisterID, which is an AutoNumber on PlayerDetails.

As far as getting the query to work in the query window, I'm not sure how to do this as the column from PlayerHistory is chosen in the first form.

Thanks!!!

Grant

 

[Steve101]

Grant,

(a) Make sure all RegisterId's are indexed.
(b) If you've set up a schema in the relationships window, make sure the one to many relationships are formed in the correct direction (drag from the one table to the many table).
(c) "dummy up" a copy of the real query used, but replace the form control reference to a hard coded reference ... or make sure the form is not set to modal (a property which prevents you leaving the form before closing it); then leave the form open (possibly minimised if it gets in the way), and "manually" run the query, and view/change other database objects.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[grantwilliams]

Brilliant!

Thank you Steve!

I ensured that all RegisterID's were indeed Primary Keys, indexed and that the relationships were all up 1 to 1. Then I put a query together using 2003 as the year and a team from 2003... worked a charm. I then transferred the query to the form, replacing the 2003 references to references to the MainForm and it gave me a datasheet that I could edit the records on!!!


THANK YOU!

Grant.