Enter Me.Date into a query triggered in a Form button 1

[MattGreer]

Not sure if this should go here, or the Query forum, or the VBA forum, so... let me know if I'm in the wrong place.

I have a form, put a button on it, and clicking the button runs a query. The query asks me for a date. I want to have Me.Date somehow make it into the named query. Not sure how to do that?

Here's my code:

Private Sub cmdAddToSelected_Click()
    On Error GoTo Err_cmdAddToSelected_Click
    
    'Runs the update query to update a table based on the information
    'shown in the current form
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    DoCmd.SetWarnings False

    DoCmd.OpenQuery "qudEventReSelect", acNormal, acEdit     'This is the query that needs a date from the user, me
    
    DoCmd.OpenForm "frmServiceList"             'This form will display the records that got updated
    DoCmd.Close acForm, "frmEvents", acSaveNo   'This is the name of the form where the button is located
    
    DoCmd.SetWarnings True
    
Exit_cmdAddToSelected_Click:
    Exit Sub

Err_cmdAddToSelected_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddToSelected_Click

End Sub

Thank you for your help!

Thanks!!


Matt

 

[MattGreer]

OK, I think I found what I need but I'm still having trouble figuring out the correct syntax.

I need to use something called PARAMETERS in my SQL statement.

If you know of a good tutorial for setting this up in VBA, please let me know! The Microsoft example is a bit convoluted and, while probably pretty cool, isn't obvious how I should use it quite yet.

I need to put Me.Date into the PARAMETER...

Thanks!!


Matt

 

[dhookom]

There is a Query FAQ faq701-7433 that describes how to change the SQL property of a saved query. I would actually build a SQL statement in code and then used the Execute method. Something like:

Dim strSQL as String
strSQL = "UPDATE myTable SET MyDateField = #" & Me.Date & "# WHERE SomeField = SomeValue"
CurrentDb.Execute strSQL, dbFailOnError

"Date" is a horrible name for a field or control since it is a reserved word (function). Also this line should be replaced by a more up-to-date method. I don't even know what it is supposed to do.

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

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

 

[MattGreer]

You are, of course, absolutely correct about 'Date'. I don't know why Access didn't kick that out to be honest; I would have thought it to be a reserved word. I see what I did tho. More details below.

BTW, I've been following your website since like, 2004, which is when a lot of this code was written and only updated as needed to work with present-day Access. At least, I think I knew of your website back then. >.<

So the backstory here is this database is used to plan song sets for gigs. I try to plan in advance, but sometimes I want to change the set. So I pull up my 'Events' list, which shows the date and the songs, and then I take that list of songs and re-select it in the database so I can change the order, or swap out songs for the event, and so forth. So there's tblSongs which contains all the songs, and tblEvents which has the song list for a given event. I just have a Yes/No checkbox in tblSongs so I can select and narrow down the final list. Then that list goes into tblEvents and the songs are de-selected from tblSongs. Hope this all makes sense!

tblEvents has the following fields
EventID - primary key
Date - date of the event
CategoryID - Reference to tblCategories where I have a list of different kinds/styles of events
Location - The venue
Comment - Anything I want to add about the particular event

I think I managed to resolve the issue the same day I wrote this, so I apologize for not responding sooner. This is what I ended up doing. Any suggestions? What I know of Access VBA is mostly from Access XP... heh.

Private Sub cmdAddToSelected_Click()
On Error GoTo Err_cmdAddToSelected_Click
    
    'This will take the current selected event and re-select those
    'songs for updating/printing/whatever
    
    Dim strSQL As String
    Dim strName As String
    Dim param1 As String

    strSQL = ""
    param1 = Me.Date
    
    strSQL = "UPDATE tblSongs INNER JOIN ((tblCategories INNER JOIN tblEvents ON tblCategories.[CategoryID] = tblEvents.[CategoryID]) " & vbCrLf
    strSQL = strSQL & "INNER JOIN tblSongsPlayed ON tblEvents.EventID = tblSongsPlayed.EventID) ON tblSongs.SongID = tblSongsPlayed.SongID SET tblSongs.Service = True, tblSongs.[Order] = [tblSongsPlayed].[Order] " & vbCrLf
    strSQL = strSQL & "WHERE (((tblEvents.Date)=#" & param1 & "#));"

'    Debug.Print strSQL
    VBA_Query strName, strSQL
    
    DoCmd.OpenForm "frmServiceList"
    DoCmd.Close acForm, "frmEvents"
    
Exit_cmdAddToSelected_Click:
    Exit Sub

Err_cmdAddToSelected_Click:
    MsgBox Err.Description
    Resume Exit_cmdAddToSelected_Click
    

End Sub

Public Sub VBA_Query(strName As String, strSQL As String)

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
        
    Set db = CurrentDb
    
    'Create a QueryDef with the supplied name
    Set qdf = db.CreateQueryDef(strName, strSQL)
    
    If strName <> vbNullString Then Application.RefreshDatabaseWindow
    
    qdf.Execute
    
    Set qdf = Nothing
    Set db = Nothing

End Sub

Thanks!!


Matt

 

[dhookom]

It doesn't look like tblEvents has any songs in it and your description doesn't mention tblSongsPlayed anywhere. Is this code working as you wish or do you need further assistance?

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

 

[Andrzejek]

It looks to me the tblEvents has one record per Event, tblSongs have one record per Song, so to 'marry' any Event with any song you need another table, something like:

[pre]
tblEventSong
EventID SongID[/pre]

Where you can store a Many to Many Relationship

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

I'm good guys; it's working.

The missing part that you both astutely noticed is tblSongsPlayed (definitely could have come up with a better name, heh). There's multiple songs that could be used in multiple events.

The point of all of this was, of course, to get Me.Date into a variable, so that I could pull songs from just that date into a query and work with 'em.

Thanks!

Thanks!!


Matt