SQL vs. vba Row Source property

[eww]

I've got a subform with a control in it where I put a condition on the Row source. The Row source statement reads like this:



SELECT tblSegments.SegmentsID, tblSegments.JobNumber, tblSegments.Segments FROM tblSegments WHERE ((tblSegments.JobNumber)=(Forms.frmProjectInfo!Job Number.Value));

I have recently found that I need two different Main Forms with the same subform. The above doesn't work as I need a statement that more reads:

SELECT tblSegments.SegmentsID, tblSegments.JobNumber, tblSegments.Segments FROM tblSegments WHERE ((tblSegments.JobNumber)=(Forms.frmAllProjects!JobNumber.Value));

I know I can change Row source through the vba code based on what Main Form I am in, but I don't know what kind of notation I need. The italicized part is what I need help with.

Private Sub sfrmSubDetStatus_Enter()
Forms!sfrmSubDetStatus.Segment.RowSource = SELECT tblSegments.SegmentsID, tblSegments.JobNumber, tblSegments.Segments FROM tblSegments WHERE ((tblSegments.JobNumber)=(Forms.frmProjectInfo!Job Number.Value))
End Sub

[TheShabz]

I believe it needs to be a string. Either wrap " " around it or save the string as a variable and put the variable there.

[eww]

would I need 2 quotation areas / 2 variables? 1 for what is being selected, 1 where it's being selected from, and 1 for the condition. Or is it all 1 variable?

[TheShabz]

they way you are referencing them does not require concatenation. If you had it as
Me.JobNumber, for example, then yes you would need to concatenate. I believe you should be good to go with just making the whole thing one string. Give it a shot, cant hurt.

[eww]

Okay so I gave it a try and it's not recognizing the code, here's what I have in frmMyProjects:

Private Sub sfrmSubDetStatus_Enter()
Dim MyProjectSegment As Variant
MyProjectSegment = "SELECT tblSegments.SegmentsID, tblSegments.JobNumber, tblSegments.Segments FROM tblSegments WHERE ((tblSegments.JobNumber)=(Forms.frmProjectInfo!Job Number.Value))"
Me!sfrmSubDetStatus.Form.Refresh
Forms!sfrmSubDetStatus.Form.Segment.RowSource = MyProjectSegment
End Sub


and in frmAllProjects:


Private Sub sfrmSubDetStatus_Enter()
Dim AllProjectSegment As Variant
AllProjectSegment = "SELECT tblSegments.SegmentsID, tblSegments.JobNumber, tblSegments.Segments FROM tblSegments WHERE ((tblSegments.JobNumber)=(Forms.frmAllProjects!Job Number.Value))"
Me.sfrmSubDetStatus.Form.Refresh
Forms!sfrmSubDetStatus.Form.Segment.RowSource = AllProjectSegment
End Sub

[weekend00]

How would the subform like if you open another main form while one is opened?

[eww]

Forgive me because I'm a bit confused by your wording Weekend, but if what you're asking is what would happen if this subform was opened in two different forms at the same time...I've set it up where that can't happen. There are only these two Main forms with this subform, and they cannot be opened at the same time.

[weekend00]

can you just link the subform to mainforms with jobnumber ? you don't need any code at all. just leave the record source of the sub form as:
SELECT tblSegments.SegmentsID, tblSegments.JobNumber, tblSegments.Segments FROM tblSegments