Continued Problems on Subforms

[thayes5150]

OK, next question :) Once the form sub sub form is open I am trying to update a table based on a double click selection. - employee may have multiple claims, they need to select one to attach charges to by double clicking one of the records in the opened sub form. I have been trying to get the following to work. (forum is wrapping the lines)

Private Sub Form_DblClick(Cancel As Integer)

Dim stSQL As String
stSQL = "UPDATE UCT_Data SET UCT_Data.pkUnemploymentDataID = " & Me.pkUnemploymentDataID & ", UCT_Data.CreateFile = 'Yes' WHERE UCT_Data.UCT_Data_ID = " & Forms.UCT_Data_sub.UCT_DATA_ID & ";"
DoCmd.SetWarnings False
MsgBox stSQL
'DoCmd.RunSQL stSQL
DoCmd.SetWarnings True
  
End Sub

but the WHERE clause is blowing it up. Any ideas?

[thayes5150]

I believe the problem lay in the fact that I was referenceing the parent form in the where clause. I modified the Query that feeds the subform to carry the necessary key along to the subform and it seems to be working now.

[NeoPa]

OK. It seems there are a number of things to say here if we are to proceed in a positive manner.

1. I've moved this new question from an earlier thread (Problem opening subform when double click record from main form). Multiple questions in the same thread can make sense sometimes. Not ideal but less complicated sometimes than the alternatives. This is not one of those times. The preferred way to do it is to keep each thread for its own question (as per the site rules Posting Guidelines).
2. I have edited three of your posts just today because they have included code without the CODE tags. I commented each one, yet you seem to have failed to notice this when looking for our help. Please note this for all future posts.
3. Your latest question posts a line of code that creates some SQL, but it neither displays the resultant SQL (something I just suggested would be a first step to help understand what's going wrong), nor is it formatted in such a way as to be readable without scrolling the page.
   
   Your own coding style is a matter for you of course, but when posting to request help it would be less inconsiderate if you formatted your code at least legibly for the medium. This assumes of course that you are aware of continuation characters in VBA and/or the ability to create a string over multiple lines using concatenation. If not then say so, as that would be a different position. We could help with that of course.

[NeoPa]

Having said all that, it seems your problem is to do with referring to the item (control) on your form.

I would expect the following to work better for you :

stSQL = "UPDATE UCT_Data " & _
        "SET [pkUnemploymentDataID]=" & Me.pkUnemploymentDataID & ", " & _
            "[CreateFile]='Yes' " & _
        "WHERE [UCT_Data_ID]=" & Me.UCT_Data_sub.UCT_DATA_ID & ";"

This is without your actually explaining where this control fits in overall though, so if this doesn't work, describe that and we can look at a solution that will work.

[thayes5150]

Sorry for the bad form, I did not know about using the Coding tags, I will do so in the future. Based on your example, I also now know how to break and concatenate lines in vba, it is frustrating in that the syntax is close to the .net version of vb I am used to, but different enough that nothing seems to work (you use an underscore _ to indicate the end of line, but do not need to include the ampersand in .Net, the system just knows) I apologize again for my elementary mistakes, This is my first real advent into programming a GUI in ACCESS and vba.

[NeoPa]

In that case there's nothing to worry about.

Some posters are a lot more trouble of course ;)

Welcome to Bytes!