Subform controls on a Tab Form not working. 1

[cdgeer]

I placed a subform on a Tab control form that came from an existing form I had already developed named General_Processor. The problem I'm having is that the code behind some command buttons on this form that use textbox data on this form can't find this form. In other words, I'll get "...database can't find the Form General_Processor mentioned in VB Code" or I'll get a Parameter popup asking for a value for... "Forms![General_Processor]![txtTableNew]" when txtTableNew is filled in with data, and it is right on the current form that I'm running the code on. I'm wonderning why isn't my code finding this form any longer. I'm sure it's because it is now a subform on a Tab control form but how do I fix this issue?
Thanks in advance for any help!!

 

[dhookom]

It would help you you actually posted the code with indication of which line is causing the error.

Check Refer to Form and Subform properties and controls to find out how to reference your text boxes.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

it's because it is now a subform
Sure.
Use something like this now:
Forms[!]![name of mainform][/!]![General_Processor][!].Form[/!]![txtTableNew]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cdgeer]

Thanks both of you,
I tried it a few different ways as mentioned above. Still no luck. I still get the Parameter question for what does ...Forms![General_Processor]![txtTableNew] refer to.
Or, Forms![Extract Import Tools]![General_Processor].Form[txtTableNew] Here's the code originally when it wasn't a subform of a Tab control...

Private Sub cmdFindOldTable_Click()
Dim strSQL As String
strSQL = "SELECT TableName INTO y_tmpGeneral_ProcessortxtTableOld FROM y_LatestExtracts WHERE ((Mid((Forms![General_Processor]![txtTableNew]), 10, 65))=([y_LatestExtracts].[TrimmedTableName]));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

Me![txtTableOld] = DLookup("[TableName]", "[y_tmpGeneral_ProcessortxtTableOld]")

Now, the Main Form name is [Extract Import Tools]. I've tried referencing both forms and can't figure out what's going on. The controls are on the subform but I can't seem to get them recognized by the code.



DoCmd.SetWarnings True


End Sub

 

[PHV]

Forms![Extract Import Tools]![General_Processor].Form[!]![/!][txtTableNew]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I would actually try code like:

Private Sub cmdFindOldTable_Click()
  Dim strSQL As String
  strSQL = "SELECT TableName INTO y_tmpGeneral_ProcessortxtTableOld " & _
    "FROM y_LatestExtracts " & _
    "WHERE [TrimmedTableName] = """ & _
       Mid(Me.[txtTableNew], 10, 65) & """ "

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

Thanks PHV, I did catch that right after I sent it. Oops!
Still doesn't work though. Thanks Dhookom, tried your's too but nothin'
This is code from a different form in the same Tab cntrl form. I did the complete structure from Main form (which I named instead of using"Me") to the first subform (which is the Tab cntrl form) to the final subform which contains the cmd buttons and the txtbox controls...

Private Sub cmdNewCPDiffs_Click()
Dim txtOldCP As String
Dim txtNewCP As String
Dim strSQL As String
txtOldCP = Forms![Extract Import Tools].Form![EIT_Tab].Form![CP Import Diffs].Form![txtOldCP]
txtNewCP = Forms![Extract Import Tools].Form![EIT_Tab].Form![CP Import Diffs].Form![txtNewCP]

strSQL = "SELECT [" & txtNewCP & "].[Full Name], [" & txtNewCP & "].[Resource Type], [" & txtNewCP & "].Workgroup, [" & txtNewCP & "].[Primary Function], [" & txtNewCP & "].[Immediate supervisor], [" & txtNewCP & "].Location, [" & txtNewCP & "].[Contract Start Date], [" & txtNewCP & "].[Contract End Date], [" & txtNewCP & "].[Forecast End Date], [" & txtNewCP & "].[Consultant Company] INTO " & txtNewCP & "New FROM " & txtNewCP & " LEFT JOIN " & txtOldCP & " ON [" & txtNewCP & "].[Full Name] = [" & txtOldCP & "].[Full Name] WHERE ((([" & txtOldCP & "].[Full Name]) Is Null));"

DoCmd.RunSQL strSQL
DoCmd.OutputTo acTable, "" & txtNewCP & "New", acFormatXLS, "C:\Documents and Settings\czg\Desktop\ChangepointContractorsNew.xls"

MsgBox "" & txtNewCP & "New table has been created."

End Sub

 

[dhookom]

Did your modified code work or not? If not, can you provide the value of strSQL prior to running?

Your code looks like you have a form->subform->subsubform.

Duane
Hook'D on Access
MS Access MVP

 

[cdgeer]

No, the txtOldCP and txtNewCP return zero length strings. That is the problem I'm having. Yes, I do have a form->subform->subsubform. I can't seem to get to the subsubform txtbox value.
I even threw in:
Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
MsgBox "Current form is " & frmCurrentForm.Name
...in the code and when I run it from a control on the subsubform, it says the current form is the Main form. ??

 

[cdgeer]

YES! I found the structure that works. (Forms![mainform].Form![subsubform]![subsubformControl].

But, now I can't get it to work in this sub:

Private Sub cmdFindOldTable_Click()
Dim strSQL As String

strSQL = "SELECT TableName INTO y_tmpGeneral_ProcessortxtTableOld FROM y_LatestExtracts WHERE ((Mid((Forms![Extract Import Tools].Form![General_Processor]![txtTableNew]), 10, 65))=([y_LatestExtracts].[TrimmedTableName]));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

Me![txtTableOld] = DLookup("[TableName]", "[y_tmpGeneral_ProcessortxtTableOld]")

DoCmd.SetWarnings True

End Sub


I replaced the Me![txtTableOld] with Forms![Extract Import Tools].Form![General_Processor]![txtTableNew] but I get an error: "Object doesn't support this property or method."