VBA to Open A Form Using Query Results 1

[MattGreer]

Not sure if this should be posted here for the VBA forum, but I figured the Form area would be the right place to start.

I'm trying to open a form I've created using a button. The button would contain the code to open the form with records based on the specified query in the VBA. No matter what I try, somehow it just won't work and I don't know why. When I click the button, I get windows popping up asking me for information that shouldn't be required. The query behind the form runs just fine with no errors or required input from me. So I can't figure out why I'm getting asked to give ID numbers.

So I used the Form Wizard and created a form based on the query. Just took all the defaults. Form opens fine and only has the records that are selected by the query. I then went into the form, deleted the entry in 'Record source', which was the query, and then tried to open the form using this code:

DoCmd.OpenForm stDocName, acNormal, stQuery, , , acWindowNormal

(where stDocName is the Form, and stQuery is the Query).

The form opens and every field has "#Name?" in it. If I leave the name of the query as the Record Source and try to open it with the VBA, Access prompts me to to enter the parameter value "tblSongsPlayed.SongID". If I enter a number I get a blank record. If I just hit OK, I get the correct list of records (just discovered this).

So why is it asking me for a value for "tblSongsPlayed.SongID"? And I still go back to my original question, how do I create a form that will allow me to change the source of the records using VBA?

I'm not sure what additional information you all need to debug this. I'll try to describe what's going on here. The database is for saving song information and tracking performances. I'm trying to minimize the number of forms I have as whenever I change a form I have to change it several other times, etc. The query I wrote selects all songs that have never been performed.

Here's the database setup:

Relationships
Query Builder
SQL Statement



Thanks!!


Matt

 

[dhookom]

Are you trying to change the entire record source or simply applying a different filter? I would recommend creating the form without a filter and then using the WHERE argument of the DoCmd.OpenForm method. This passes the where condition to the form's Filter Property.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

I'm not entirely certain how to answer your question; I apologize. I created the form based off the query, and then tried to use VBA to "inject" the right parameters into the Form when it opened to give me the same results. I figured that would be a good starting point to figure out how to use a Form in a modular fashion. But I can't even get that to work from VBA.

So deleted the information in 'Record Source' in the Form and changed the 'Filter On Load' field to 'Yes'. I ran the code as follows:

DoCmd.OpenForm FormName:=stDocName, View:=acNormal, WhereCondition:=stQuery

The Form showed up with "#Name?" in all the fields. I changed 'Filter On Load' back to 'No' and had the same issue.

So then I removed the "WHERE" part of the query and had this, and still got the same results:

stDocName = "frm-qrySongsNeverPlayed"
stQuery = "qrySongsNeverPlayed"
stWhere = "WHERE (((tblSongsPlayed.SongID) Is Null))"

DoCmd.OpenForm FormName:=stDocName, View:=acNormal, FilterName:=stQuery, WhereCondition:=stWhere

What should I try next? Sorry if I misunderstood your post!

Thanks!!


Matt

 

[MattGreer]

Do I have to do something like this?

https://docs.microsoft.com/en-us/office/vba/api/Access.Form.Recordset

Thanks!!


Matt

 

[dhookom]

I would leave the form bound to qrySongsNeverPlayed (or whatever) and then use code like the following. The Where Condition does not have the "WHERE" in it:

stDocName = "frm-qrySongsNeverPlayed"
' [COLOR=#4E9A06]stQuery = "qrySongsNeverPlayed"[/color]
stWhere = "SongID Is Null"

DoCmd.OpenForm FormName:=stDocName, View:=acNormal, WhereCondition:=stWhere

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

So if I've understood you correctly, I need to have 'Record Source' specified in the Form? If I have to do that, then I have to create multiple copies of the same form which is what I'm trying to avoid.

I tried what you suggested and it still gave me an error.

Is there a way to use the same Form with different queries, specified in VBA, so I don't have to have multiple copies of the same Form?

Thanks!!


Matt

 

[MattGreer]

Well, I followed that link above and now everything works in VBA. Problem I have now is that the formats/functions in the form aren't working correctly. Some of the fields are hyperlinks to folders on my computer or links on the internet. They no longer function as hyperlinks, meaning I can't click on them and have them take me to the folder or website. Any thoughts as to why they're not working when the Form is activated via VBA?

Sub TestingNewThing()
Dim stDocName As String
Dim strSQL As String
Dim strName As String
Dim rstSongsNeverPlayed As ADODB.Recordset

strSQL = ""
stDocName = "frmSongs_Testing"

DoCmd.OpenForm stDocName

strSQL = strSQL & "SELECT tblSongs.* " & vbCrLf
strSQL = strSQL & "FROM tblSongs LEFT JOIN tblSongsPlayed ON tblSongs.[SongID] = tblSongsPlayed.[SongID] " & vbCrLf
strSQL = strSQL & "WHERE (((tblSongsPlayed.SongID) Is Null)) " & vbCrLf
strSQL = strSQL & "ORDER BY tblSongs.Title;"

Set rstSongsNeverPlayed = New ADODB.Recordset
rstSongsNeverPlayed.CursorLocation = adUseClient
rstSongsNeverPlayed.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms(stDocName).Recordset = rstSongsNeverPlayed

Set rstSongsNeverPlayed = Nothing

End Sub

I really appreciate you sticking with me through this!

Thanks!!


Matt

 

[MattGreer]

I fixed it! Use DAO instead of ADO and all the hyperlink functions are restored. Woo!

Sub VBA_Test()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stDocName As String
Dim strSQL As String
Dim strName As String

    strSQL = ""
    stDocName = "frmSongs_Testing"
    
    DoCmd.OpenForm stDocName

    strSQL = strSQL & "SELECT tblSongs.* " & vbCrLf
    strSQL = strSQL & "FROM tblSongs LEFT JOIN tblSongsPlayed ON tblSongs.[SongID] = tblSongsPlayed.[SongID] " & vbCrLf
    strSQL = strSQL & "WHERE (((tblSongsPlayed.SongID) Is Null)) " & vbCrLf
    strSQL = strSQL & "ORDER BY tblSongs.Title;"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    Set Forms(stDocName).Recordset = rs
    
    Set db = Nothing
    
    
End Sub

Thanks!!


Matt

 

[dhookom]

I find it a bit unusual to need many different record sources for the same form. As I mentioned earlier it is almost always a matter of simply changing the filter. However you have more knowledge of your tables and seem to have resolved the issue.

I think I would have simply used [faq701-7433] that was used as the record source.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

I think I understand what you're saying. Perhaps a simple (simple?) filter could be applied to the Form and I could get the desired results. It certainly would simplify things quite a bit.

So I have two forms I use, for the most part. One is a list of songs, and the other is the full detail for a given song. For either of these forms, I will change the selected records as follows (off the top of my head):

[ul]
[li]Songs we haven't played in the past [user entered] months[/li]
[li]Songs we *have* played in the last [user entered] months[/li]
[li]Songs I've selected for next week's set[/li]
[li]Songs I've selected for the next rehearsal (not the same as the list of songs for the next performance)[/li]
[li]Songs that have never been played[/li]
[li]Songs that have been played the most, in descending order (this is more of a report really)[/li]
[li]Songs that belong to a certain group (Adults, Kids, New Songs)[/li]
[li]Songs that have a certain theme[/li]
[/ul]

Granted, it's super easy to do many of these things from the list of songs (Continuous Forms view). Just right click and you can filter it like a spreadsheet. I'm obviously not able to do that from the Single Form view. I think I can sort them, but not filter them from that Form.

I can see where a Filter could isolate songs that have a yes/no checkbox, but filtering songs that have never been played? I need to pull in another table to get that list from tblSongs.

I know I have a lot to learn, and I appreciate you following up and I thank you for the link to the FAQ. So beyond everything that's been said, would your suggestion be that I need to read up on/study how to use the Filter capabilities of a Form? Anything else you'd suggest?

Thanks!!


Matt

 

[dhookom]

I think it is just a matter of understanding queries. A "Filter" is just a where condition applied to a form or report. There is a bit of a difference if you want your form editable or not. If not you could use a table of all songs joined with a left join to songs that have been played and then apply "Is Null" against a column from the songs that have been played table. It looks like your recordset already uses the left join.

SELECT tblSongs.* 
FROM tblSongs LEFT JOIN tblSongsPlayed ON tblSongs.[SongID] = tblSongsPlayed.[SongID]
WHERE tblSongsPlayed.SongID is Null

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

Perhaps I should change the default Record Source for the frmSongs to a query (which is probably what you've been telling me this entire time, lol). Right now the source for frmSongs (the detailed form) is tblSongs. /scratches head...

Thanks!!


Matt

 

[dhookom]

You have never stated if the form can be read only.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

Definitely not read only. Need to be able to make edits/changes/etc.

Thanks!!


Matt

 

[dhookom]

You would need to make sure your query is updatable. This would mean to group bys.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MattGreer]

OK, this is something new I need to learn. After your last post I started going through queries and in some of them I could update the records, and some I could not. I wasn't quite sure why, but when I had a query created that was used in another query that would prevent me from updating records. Is there a way around that?

Here's one that won't allow me to update the results:

SELECT tblSongs.SongID, tblSongs.Title, tblSongs.Service, tblSongs.Order, tblThemes.Theme
FROM tblThemes INNER JOIN (tblTempos RIGHT JOIN (tblStyles RIGHT JOIN ((tblRanking RIGHT JOIN (tblGrouping RIGHT JOIN (tblSongs LEFT JOIN qryVariableHistory ON tblSongs.SongID = qryVariableHistory.tblSongs.SongID) ON tblGrouping.GroupID = tblSongs.Group) ON tblRanking.RankID = tblSongs.Rank) INNER JOIN tblLinkTheme ON tblSongs.SongID = tblLinkTheme.SongID) ON tblStyles.StyleID = tblSongs.Style) ON tblTempos.TempoID = tblSongs.Tempo) ON tblThemes.ThemeID = tblLinkTheme.ThemeID
WHERE (((tblThemes.Theme)="Christmas") AND ((qryVariableHistory.tblSongsPlayed.SongID) Is Null))
ORDER BY tblSongs.Title;

So the first query (qryVariableHistory) asks how far back to eliminate songs that we've performed. That query then feeds into the overall query that searches for songs that have the theme 'Christmas'.

I can't read SQL myself so if the design view window is easier to decipher let me know and I'll post a screenshot.

Thanks!!


Matt

 

[dhookom]

I rarely bind a form to more than one table. It is much more functional to use forms with one or more subforms for editing child data.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016