Change Str-SQL in module from form

[nancier]

Hi, I'm trying to change the Str-SQL in a module by clicking a button on a form. I need to make this change when the form buttom is clicked.

tblStupload.[NUM-1]
change to
tblAmount.[NUM-1]

I'm trying this code but it's not working. Any idea what's wrong? Thanks

Option Compare Database
Option Explicit
Dim strSQL As String
Private Sub Command0_Click()
DoCmd.OpenModule "mdlDoItAll"
strSQL = Replace(strSQL, "tblStupload.[NUM-1]", "tblAmount.[NUM-1]")
End Sub

And here is the code from the module:

strSQL = strSQL & "UNION ALL (SELECT DISTINCTROW TOP " & lngSampleSize & _
" tblStupload.[EXAM#], tblStupload.[SYS-DATE], tblStupload.[AUD-ID], " & _
"tblStupload.[EXAM-TYPE], Format([DATE],'yyyymmdd') AS [INV-DATE], " & _
"tblAmount.[INVOICE-#], tblAmount.NAME, tblAmount.DESC, tblStupload.[REASON-Q], " & _
"tblStupload.[TAX-CODE], tblStupload.[LOC-CODE], tblAmount.Amt AS [Amt-Q], " & _
"tblAmount.Amt AS [AMT-A], tblStupload.[REASON-A], Format([DATE],'mmddyy') AS " & _
"[I-DATE], tblStupload.[DET-AVG], tblStupload.[T-RATE], tblStupload.COMMENTS, " & _
"tblStupload.[CHAR-1], tblStupload.[CHAR-2], tblStupload.[CHAR-3], " & _
"tblStupload.[CHAR-4], tblStupload.[CHAR-5],tblStupload.[NUM-1], " & _
"tblStupload.[NUM-2], tblStupload.[NUM-3], tblStupload.[NUM-4], " & _
"tblStupload.[NUM-5], Format([DATE],'yy') AS [C-DATE], tblAmount.Amt AS CAAN11 " & _
"FROM tblAmount, tblStupload WHERE Amt between " & ARanges(ctr, 1) & _
" and " & ARanges(ctr, 2) & " ORDER BY GetRan(" & lngSeedNumber & ",[id]))"

 

[ZmrAbdulla]

Do you want to change it back again? If yes then better to use two SQLs and an option/toggle button to choose the SQL1 or SQL2 according to the value of the option button.

Zameer Abdulla
Visit Me

 

[nancier]

Hi,

That would be fine having the toggle switch. Do you know how to do it?

Thanks

 

[ZmrAbdulla]

To test this code, place a comand button(Command1) and a Toggle button(Toggle1) on the form then on the click event of the command button...

Private Sub Command1_Click()
   Select Case Me.Toggle1.Value
    Case -1
       MsgBox "ON"    [b][COLOR=green]'Here you can use SQL1[/color][/b] 
   Case 0
      MsgBox "OFF"   [b][COLOR=green]'Here you can use SQL2[/color][/b]
    End Select
End Sub

Hope this helps


Zameer Abdulla
Visit Me

 

[nancier]

I thought this would be easy but it's not. I want to change part of the code of a string in a module. I need to do this by clicking a command button on a form. I need to make this change.
"tblStupload.[NUM-1]"
Change to this
"tblAmount.[NUM-1]"

Here is the code also for the form's event procedure.

Option Compare Database
Option Explicit

Private Sub Command0_Click()

DoCmd.OpenModule "mdlDoItAll", "Vince"
Dim strSQL As String
strSQL = strSQL & "tblStupload.[NUM-1]"
strSQL = Replace(strSQL, "tblStupload.[NUM-1]", "tblAmount.[NUM-1]")
End Sub

And here is the Module Code that needs to be changed.

Option Compare Database
Option Explicit
Dim strSQL As String
Public Sub Vince()
strSQL = strSQL & "tblStupload.[NUM-1]"
End Sub

 

[ZmrAbdulla]

Can you tell me what is the purpose of the "strSQL" ? Is it recordsource of a form? report?
Also I don't think it is possible to replace a part in module at runtime.

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[SBendBuckeye]

I would suggest a different route. Create a public property on your form and populate it from FormLoad and ButtonClick events. Then have your module incorporate the same:

In your form code
A. Create a module level variable to hold your SQL:

Private mstrSQL As String

B. Populate it during FormLoad event code:

mstrSQL = "tblStupload.[NUM-1]"

C. Change it in ButtonClick event code:

mstrSQL = "tblAmount.[NUM-1]"

D. Somewhere in your form code create a property:

Public Property Get SQLString As String
    SQLString = mstrSQL
End Property

E. Build your SQL string in your module:

Dim strSQL As String
    'Uncomment DoCmd line if form is NOT open here
    'DoCmd.OpenForm "YourFormName", , , , , acHidden
    'Incorporate strSQL into your module code as needed
    strSQL = Forms!YourFormName.SQLString
    'Uncomment DoCmd line if form is NOT open here
    'DoCmd.Close acForm, "YourFormName", acSaveNo

The above allows your code to be dynamic without complication. Good Luck!



Have a great day!

j2consulting@yahoo.com