Solved: Data input from message box

[Riaaz66]

Hi,

I have recorded a macro where I retreive data from a SQL query.
In the SQl window I have the following command:

Range("A3").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=trade_history;Description=trades;UID=Riaaz Maharban;APP=Microsoft Office XP;WSID=OPAMCL0471;DATABASE=trade_history;Trusted_" _
), Array("Connection=Yes"))
.CommandText = Array( _
"exec dbo.xlsweeklytrades '20080101', '20080131', 123" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "")
.Refresh BackgroundQuery:=False

Now as you can see, where the rule begins with "exec dbo....." I use 2 datefields (startdate and enddate) and an accountnumber (eg. 123)

What I want is when I run de code by clicking a button (where this macro is attached) a messagebox should appear where the startdate, end date and accountnumber can be filled. When entering these data the code should retreive the data. There should also be a errorhandler in case a fiels is not filled.

How do I code that?

Regards and thanx in advance,

Riaaz66

[RonMcK]

Riaaz,

We need a bit more information. What format do you want the users to enter the dates in? yyyymmdd or dd/mm/yyyy or mm/dd/yyyy or ?????? In addition to disallowing blank entries, do you want to validate the dates? Do you want the code to verify that enddate is on or after begin date? Is there a minimum interval between dates (a day, a week, a month, etc) or can the user search on one day by entering the same date into both fields? Does the account code have a fixed length? Is it all numeric or can it be alphanumeric?

This informatiion with let someone here help you get to your solution.

Cheers!

[Bob Phillips]

Dim StartDate As String
Dim EndDate As String

StartDate = InputBox("Supply startdate in format yyyymmdd")
If StartDate <> "" Then

EndDate = InputBox("Supply enddate in format yyyymmdd")
If EndDate <> "" Then

Range("A3").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=trade_history;Description=trades;UID=Riaaz Maharban;APP=Microsoft Office XP;WSID=OPAMCL0471;DATABASE=trade_history;Trusted_" _
), Array("Connection=Yes"))
.CommandText = Array( _
"exec dbo.xlsweeklytrades '" & StartDate & "', '" & EndDate & " ', 123" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "")
.Refresh BackgroundQuery:=False
End With
End If
End If

[RonMcK]

Bob,

I think Riaaz also wants an inputbox for Account Number.

Dim StartDate As String
Dim EndDate As String
Dim AcctNum As String

StartDate = InputBox("Supply startdate in format yyyymmdd")
If StartDate <> "" Then

EndDate = InputBox("Supply enddate in format yyyymmdd")
If EndDate <> "" Then

AcctNum = InputBox("Supply account number")
If AcctNum <> "" Then

Range("A3").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=trade_history;Description=trades;UID=Riaaz Maharban;APP=Microsoft Office XP;WSID=OPAMCL0471;DATABASE=trade_history;Trusted_" _
), Array("Connection=Yes"))
.CommandText = Array( _
"exec dbo.xlsweeklytrades '" & StartDate & "', '" & EndDate & " ', "& AcctNum & """ & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "")
.Refresh BackgroundQuery:=False
End With
End If
End If
End If

[Riaaz66]

Hi RonMcK,

Thanks for your efforts. here are the detailes you requested:

- The date format should be yyyymmdd
- Blank date fields ae NOT allowed, a date must be filled
- Yes, I want the code to verify that enddate is on or after begin date
- I am not sure if the min. interval shpuld be a day. It has to be the possibility to choose as startdate for example 20080101 and as enddate 20080101 so that I retreive data for one day.
- The account code has a fixed lenghth of 3 numeric characters

I hope this will do.
Thanks again for your efforts.

Regards,

Riaaz66

[RonMcK]

Riaaz66,

Here is something that may meet your needs. This is a slight modification of XLDs original suggestion. (Thanks, Bob.)

Option Explicit

Sub GetAcctData()
Dim StartDate As String
Dim EndDate As String
Dim AcctNum As String
Dim resp As Long

Get_StartDate:
StartDate = InputBox("Supply startdate in format yyyymmdd")
If StartDate = "" Or Len(StartDate) <> 8 Then
resp = MsgBox("Please enter 8 character startdate in format yyyymmdd.")
GoTo Get_StartDate
Else
Get_EndDate:
EndDate = InputBox("Supply enddate in format yyyymmdd")
If EndDate = "" Or Len(EndDate) <> 8 Then
resp = MsgBox("Please enter 8 character startdate in format yyyymmdd.")
GoTo Get_EndDate
Else
If EndDate < StartDate Then
resp = MsgBox("Supply an enddate that is on or after the startdate." & vbCrLf & "Please re-enter the enddate.")
GoTo Get_EndDate
End If

Get_AcctNum:
AcctNum = InputBox("Supply the 3 character account number")
If AcctNum <> "" Then
If Len(AcctNum) <> 3 Then
resp = MsgBox("Account Number needs to be 3 characters." & vbCrLf & "Please re-enter the Account Number.")
GoTo Get_AcctNum
End If

Range("A3").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=trade_history;Description=trades;UID=Riaaz Maharban;APP=Microsoft Office XP;WSID=OPAMCL0471;DATABASE=trade_history;Trusted_" _
), Array("Connection=Yes"))
.CommandText = Array( _
"exec dbo.xlsweeklytrades '" & StartDate & "', '" & EndDate & " ', " & AcctNum & """" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "")
.Refresh BackgroundQuery:=False
End With
End If
End If
End If
End Sub

[CreganTur]

Personally, I think that UserForms look more professional that using multiple InputBoxes.

I've attached an example spreadsheet where button click opens a UserForm where User can enter the Start Date, End Date, and Account Number, and then click Run Macro to run the validation code above (and whatever else you want to do.

HTH

[Riaaz66]

Hi RonMcK,
Thank you and also Bob and XLD all for all your codes,ideas,time and efforts.

I used your your codes. It worked good with all the messageboxes, but it ended with an error msgbox.

The error is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark before the charachter string '
Then there is yellow triangle with an exclamation mark end below that it shows " '. " (without the double quotes)

I cannot find the " '. "
Can you help me with this error?

Kind regards and thanks in advance,
Riaaz66

[Bob Phillips]

Can you debug it and tell us what the strig being passed looke like?

[Riaaz66]

Hi CreganTur,

I agree with you that UserForms looks more professional.
However when I click your CommandButton a Compile error appears with the message: Variable not defined.

Besides that, If I click the button, what will happen? will a userform appear where you can enter the dates and accountnumber?

[Riaaz66]

Well xld, that is the problem.
The pop-up window with the error message has only a "OK" button. When click that button, it just stops...no data is being retreived.

Or tell me how to debug...

Riaaz66

[Bob Phillips]

So put a break in the code before it happens and examine the value there.

[CreganTur]

Hi CreganTur,

I agree with you that UserForms looks more professional.
However when I click your CommandButton a Compile error appears with the message: Variable not defined.

Besides that, If I click the button, what will happen? will a userform appear where you can enter the dates and accountnumber?

Mea Culpa... Look at the button in design view, right-click on it and select View Code.

Change the code:DoCmd.OpenForm "frmMain"
To:frmMain.Show

I'm normally an Access coder... so I forgot to use Excel's syntax:whistle:

[Riaaz66]

Hi CreganTur,

Thanx. It's now running but still with errors.
I apologise for my low level of VBA programmimg knowledge.

When I fill in the data in the userform I get an Pop-up window with a compile error message saying "Object required" while the "Set Startdate =" part is selected and the first coderule "Private Sub CommandButton1_Click()" is yellow.

I'm not familiar with the "set" statement. I know that you have to assign an object but I don't know how.

Please help me on this?

Regards,

Riaaz66


Here is the code you wrote:
Private Sub CommandButton1_Click()
Dim StartDate As String
Dim EndDate As String
Dim AcctNum As String
Dim resp As Long

Set StartDate = Me.txtStartDate
Set EndDate = Me.txtEndDate
Set AcctNum = Me.txtAcctNum
Get_StartDate:
If StartDate = "" Or Len(StartDate) <> 8 Then
resp = MsgBox("Please enter 8 character startdate in format yyyymmdd.")
GoTo Get_StartDate
Else
Get_EndDate:
If EndDate = "" Or Len(EndDate) <> 8 Then
resp = MsgBox("Please enter 8 character startdate in format yyyymmdd.")
GoTo Get_EndDate
Else
If EndDate < StartDate Then
resp = MsgBox("Supply an enddate that is on or after the startdate." & vbCrLf & "Please re-enter the enddate.")
GoTo Get_EndDate
End If

Get_AcctNum:
If AcctNum <> "" Then
If Len(AcctNum) <> 3 Then
resp = MsgBox("Account Number needs to be 3 characters." & vbCrLf & "Please re-enter the Account Number.")
GoTo Get_AcctNum
End If

Range("A3").Select
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=trade_history;Description=trades;UID=Riaaz Maharban;APP=Microsoft Office XP;WSID=OPAMCL0471;DATABASE=trade_history;Trusted_" _
), Array("Connection=Yes"))
.CommandText = Array( _
"exec dbo.xlsweeklytrades '" & StartDate & "', '" & EndDate & " ', " & AcctNum & """" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "" & Chr(13) & "" & Chr(10) & "")
.Refresh BackgroundQuery:=False
End With
End If
End If
End If

End Sub