UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Use Variable In Place Of Subform Name In SQL Statement?, Access 2013    
 
   
ScottyBee
post Sep 17 2019, 07:06 PM
Post#1



Posts: 50
Joined: 6-June 05
From: Portland Oregon


I have the following code that works great, thanks to earlier assistance today:

CODE
Sub AppendRatingsData()
    Dim strSQL As String
    
     strSQL = "INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy) " _
        & "SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy " _
        & "FROM Ratings " _
        & "WHERE Ratings.RatingID =  " & [Forms]![EER Form]![frmCat01_OEM_Spares_P1].[Form]![RatingID]

DoCmd.RunSQL strSQL
End Sub


However, I have 11 other subforms that will use this same exact code in a "Before Update" event except the subform reference will change. All subforms contain a RatingID field.

I know I could copy and paste this code into the separate subform events and simply change the name of subform being referenced but would like to "recycle or modularize my code as much as possible.

CODE
Sub AppendRatingsData(NameOfSubform As String)
    Dim strSQL As String
    
     strSQL = "INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy) " _
        & "SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy " _
        & "FROM Ratings " _
        & "WHERE Ratings.RatingID =  " & [Forms]![EER Form]![NameOfSubform].[Form]![RatingID]

DoCmd.RunSQL strSQL
End Sub


In the above code, notice I have replaced the reference to the subform frmCat01_OEM_Spares_P1 with the NameOfSubform variable. When I run the code, I get an error that "Access cannot find the reference to field 'NameOfSubform". However, when I debug and hover my mouse over the NameOfSubform variable, "frmCat01_OEM_Spares_P1" is displayed. Please see attached image.

Attached File  SQL_Error.jpg ( 129.58K )Number of downloads: 3


How do I tell Access that this is a variable, not a field? Maybe it cannot be done? Thanks for any help.

Go to the top of the page
 
June7
post Sep 17 2019, 11:05 PM
Post#2



Posts: 867
Joined: 25-January 16



These 11 subforms are all on [EER Form]?

& "WHERE Ratings.RatingID = [Forms]![EER Form]![" & NameOfSubform & "].[Form]![RatingID]

Advise not to use spaces in naming convention.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
cheekybuddha
post Sep 18 2019, 03:29 AM
Post#3


UtterAccess VIP
Posts: 11,514
Joined: 6-December 03
From: Telegraph Hill


Alternatively, you can pass the whole subform object.

If your sub AppendRatingsData is in a stadard module you can use:
CODE
Sub AppendRatingsData(frm As Form)
    Dim strSQL As String
    
     strSQL = "INSERT INTO RatingsLog (RatingID, fk_SubSystemID, Rating, fk_CategoryID, UpdatedDate, UpdatedBy) " _
        & "SELECT Ratings.RatingID, Ratings.fk_SubSystemID, Ratings.Rating, Ratings.fk_CategoryID, Ratings.UpdatedDate, Ratings.UpdatedBy " _
        & "FROM Ratings " _
        & "WHERE Ratings.RatingID =  " & frm.RatingID

'    DoCmd.RunSQL strSQL
    CurrentDb.Execute strSQL, dbFailOnError    ' <-  Use this if you don't want the 'You are about to insert N records' popup

End Sub


Then in your BeforeUpdate events on your subforms you simply use:
CODE
  AppendRatingsData Me


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
ScottyBee
post Sep 18 2019, 12:23 PM
Post#4



Posts: 50
Joined: 6-June 05
From: Portland Oregon


Both solutions worked perfectly. Thank you very much to both of you! smile.gif
Go to the top of the page
 
cheekybuddha
post Sep 18 2019, 02:08 PM
Post#5


UtterAccess VIP
Posts: 11,514
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Glad we could help! thumbup.gif

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th October 2019 - 06:25 AM