UserForm loading on startup and using user input in SQL query 1

[WalterHeisenberg]

Hello,

First I am a beginner to VBA. I was asked to create an Excel workbook that when opened prompted the user for a date and executed a SQL query using that date as part of the where clause.

My first though was to create a macro to figure out how to get the SQL query linked up to a buttonclick sub. No problem there.

I have two issues, one, I can't get the form to load on startup! I tried adding sheet activate for userform.show() but that didn't do it. I didn't see anything on form properties. This must be simple.

Issue 2, I tried using the following syntax on the query:

 Destination:=Range("$A$1")).QueryTable.CommandText = Array("SELECT * FROM ""SAMINC"".""dbo"".""PAYMENTS" WHERE DATE = " & TEXTBOX1.TEXT & "")

I am getting a SQL syntax error. Thanks so much for help in advance! I appreciate it.

 

[WalterHeisenberg]

Anyone out there who can help with the Syntax? If I remove the TEXTBOX1.TEXT it works like a charm but I can't figure out how to include the value of the control.

Also, with the form.show on activate it pops up when the user changes from sheet2 to sheet1 but does not load on opening which is what I would like. Thanks!

 

[WalterHeisenberg]

I discovered the workbook_open event so I figured out how to get my form to load on startup!

Still working on the SQL syntax. Thanks in advance.

 

[PHV]

Why not using a parametized queryTable ?

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

 

[WalterHeisenberg]

I don't even know what that is! Since I really don't know VBA at all I thought recording a macro and then modifying the SQL statement would be the easiest solution since the query I need will ultimately include multiple UNION commands.

 

[PHV]

No need of VBA to use a Cell as parameter in a QueryTable ....

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

 

[SkipVought]

Hi,

Several problems here.

1. All you need to refresh a QueryTable that you have already added to your sheet is...

With Sheets("YourSheetName").QueryTables(1)
   .CommandText = "SELECT * FROM SAMINC.dbo.PAYMENTS WHERE DATE = #" & TEXTBOX1.TEXT & "#"
   .Refresh False
End With

This ASSUMES the following...

1. your table is SAMINC.dbo.PAYMENTS
2. your TextBox contains a valid date string.

IGenerally, would not recommend using a textbox like this.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

Thank you. I was able to get the first Query working but am still running into errors. I am using a Textbox because this system stores dates as strings I.E. 20090526.

Here is what I have so far:

 .CommandText = Array("SELECT * FROM SAMINC.dbo.PAYMENTS WHERE DATEBATCH = '" & TextBox1.Text & "' UNION SELECT * FROM TSTDB3.dbo.PAYMENTS WHERE DATEBATCH = '" & TextBox2.Text & "'")

I tried using TextBox1.Text for the second where statement but got a "Type Mismatch" error. I tried adding a second TextBox and setting the value of it = TextBox1 when the command button is clicked. I am still getting a type mismatch error. If I remove the second where clause it works like a charm filtering the first query and bringing back all results on the second.

Any ideas? Thanks a lot!

 

[PHV]

What is the data type of TSTDB3.dbo.PAYMENTS.DATEBATCH ?

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

 

[WalterHeisenberg]

Also, I tried removing the first where clause and keeping the second and worked without an issue. I'm now researching runtime error 13 - type mismatch on google.

If anyone has seen this before please post. I'll post if I find out anything as well. Thank you!

 

[SkipVought]

As PHV asked, what is the data type of DATEBATCH?

If it is DATE Type, you have a problem.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

In both databases this field is a decimal 9 type. In both databases fields are populated with dates in YYYYMMDD format.

It is strange that if I keep 1 where clause it works on either database regardless or which one I have it on but when I put both it fails.

 

[SkipVought]

.CommandText = Array("SELECT * FROM SAMINC.dbo.PAYMENTS WHERE DATEBATCH = " & TextBox1.Text & " UNION SELECT * FROM TSTDB3.dbo.PAYMENTS WHERE DATEBATCH = " & TextBox2.Text)

If it DECIMAL, then no TIC delimiters.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

This worked like a charm! I then went on to change select * to specific field names using the same names in each select statement and got the type mismatch error again. I tried aliasing the second table but got the same error.

We're almost there! Thanks so much for the follow-up. I really really appreciate the help.

 

[SkipVought]

Post the SQL.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Just a little help with your UNION SQL.

If you have something like:

[blue]
SELECT A, B, C FROM TABLEA[/blue]
UNION[blue]
SELECT X, Y, Z FROM TABLEB[/blue]

Number of fields in both SELETCs must be the same, and TYPEs of corresponding fields must be the same. You don't have to alias them, but you can if you choose to. Fields in your recordset are going to have names from your first SELECT anyway.


Have fun.

---- Andy

 

[WalterHeisenberg]

Here is the SQL:

.CommandText = Array("SELECT AUDTORG,IDBANK, IDVEND, IDRMIT, DATEBATCH, DATERVRSD,AMTPAYM, TEXTPAYOR FROM SAMINC.dbo.PAYMENTS WHERE DATEBATCH = " & TextBox1.Text & " UNION SELECT AUDTORG,IDBANK, IDVEND, IDRMIT, DATEBATCH, DATERVRSD,AMTPAYM, TEXTPAYOR FROM TSTDB3.dbo.PAYMENTS WHERE DATEBATCH =  " & TextBox2.Text)

 

[SkipVought]

Are you absolutely sure that the corresponding fields in SAMINC.dbo.PAYMENTS and TSTDB3.dbo.PAYMENTS are defined IDENTICALLY as to Data Type?

.CommandText = Array( _
        "SELECT AUDTORG,IDBANK, IDVEND, IDRMIT, DATEBATCH, DATERVRSD,AMTPAYM, TEXTPAYOR " & _
        "FROM SAMINC.dbo.PAYMENTS " & _
        "WHERE DATEBATCH = " & TextBox1.Text & _
        " UNION " & _
        "SELECT AUDTORG,IDBANK, IDVEND, IDRMIT, DATEBATCH, DATERVRSD,AMTPAYM, TEXTPAYOR " & _
        "FROM TSTDB3.dbo.PAYMENTS " & _
        "WHERE DATEBATCH =  " & TextBox2.Text)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[WalterHeisenberg]

Yes. This application literally creates every table/field exactly the same. Each database simply represents a new company.

Just for fun, I am going to try to see if 1 column works and then keep adding a column to see if I can pinpoint what is causing the error. Let me know if anything comes to mind. Thanks.

 

[WalterHeisenberg]

The final column, textpayor which is the payee is the one causing the problem. The macro worked until I added this column.

Now, the values in this field might include ' which I am assuming might be the issue. I tried changing this to:

 replace(TEXTPAYOR,'''',' ')

This did not do the trick. The field type is CHAR(60). Thanks.