Connect to SQL SERVER with a excel 2010 userform

**drobinson782001** · 09-10-2012, 11:06 AM

I created a userform with a text box that says Status and below it is a command button that says check status. There is also a list box that provides the result of the query.
When the user enters transaction id in the text box and clicks submit. It should connect to a SQL database and search a table called TBL_TRANSACTION. when it finds the Transaction id. it shows the resultus in the list box (Lisbox1) example if pending, cancelled, updating. It should show it in the list box. Here is what i did so far but its not working

Please Login or Register  to view this content.

**JosephP** · 09-10-2012, 11:31 AM

you don't have any code that actually queries the database or populates any controls so it's not unexpected that it doesn't work ;-) you need to open the recordset using the query string and then assign the recordset data to the listbox using

Please Login or Register  to view this content.

**drobinson782001** · 09-10-2012, 11:53 AM

Please Login or Register  to view this content.

get error " operation is not allowed when object is closed"

Please advise

**JosephP** · 09-10-2012, 11:55 AM

I did say you had to open the recordset first :-)

Please Login or Register  to view this content.

**drobinson782001** · 09-11-2012, 12:19 PM

I am currently using my uname and Pwd within the connection string how do i change it so that the user enters their info. I currently have

Please Login or Register  to view this content.

Please let me know what I need to do

**JosephP** · 09-11-2012, 03:18 PM

what security does the sql server use? windows logins or database user ids and passwords? also which provider are you using?

**drobinson782001** · 09-11-2012, 04:17 PM

Database logins.
Provider is SqlOledb

**JosephP** · 09-11-2012, 04:23 PM

I reckon you need to set the prompt property of the connection to adpromptalways before you set the connection (leave the userid and password parts out)

Please Login or Register  to view this content.

**drobinson782001** · 09-11-2012, 05:30 PM

I received error 3220 when I the uid and Pwd and replace with your code

Please advise

**JosephP** · 09-11-2012, 05:34 PM

what is your complete code now? that error is about trying to change providers whilst one is in use

**drobinson782001** · 09-12-2012, 09:23 AM

Here is my current code
Provider = sqloledb
Data source = some source
Initial catalog = test
Conn.properties("prompt")= adpromptalways

**JosephP** · 09-12-2012, 09:41 AM

that's not code

**drobinson782001** · 09-12-2012, 10:44 AM

Private Sub cmdStatus_Click()
Dim conn As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset
Dim SQL As String

conn.Open _
"Provider=SQLOLEDB; " & _
"Data Source=SQLEXPRESS; " & _
"Initial Catalog=test;" & _
"User ID=test;" & _
"Password= test;"

If txtTransaction = "" Then
MsgBox ("You have not entered a Transaction_ID"), vbExclamation

Else
SQL = "select TR_Status from TBL_TEST WHERE TR_Id = '" & frmTransaction.txtTransaction.Text & "'"

myRecSet.Open SQL, conn
If Not myRecSet.EOF Then
Me.ListBox1.Column = myRecSet.GetRows

End If
End If
End Sub

**drobinson782001** · 09-12-2012, 10:46 AM

Here is the one with the new line

Private Sub cmdStatus_Click()
Dim conn As New ADODB.Connection
Dim myRecSet As New ADODB.Recordset
Dim SQL As String

conn.Open _
"Provider=SQLOLEDB; " & _
"Data Source=MORSQLEXPRESS; " & _
"Initial Catalog=test;" & _

conn.Properties("Prompt") = adPromptAlways

If txtTransaction = "" Then
MsgBox ("You have not entered a Transaction_ID"), vbExclamation

Else
SQL = "select TR_Status from TBL_TEST WHERE TR_Id = '" & frmTransaction.txtTransaction.Text & "'"

myRecSet.Open SQL, conn
If Not myRecSet.EOF Then
Me.ListBox1.Column = myRecSet.GetRows

End If
End If
End Sub

**JosephP** · 09-12-2012, 10:54 AM

move this line

Please Login or Register  to view this content.

before the line that opens the connection

also note that a moderator has been adding code tags to your posts for you but if you don't start doing it yourself you will probably get reprimanded pretty soon

**drobinson782001** · 09-12-2012, 11:12 AM

Error 3220 again

**JosephP** · 09-12-2012, 11:17 AM

my bad-was testing with the wrong provider

Please Login or Register  to view this content.

**drobinson782001** · 09-12-2012, 11:34 AM

You are a Rick star thanks a lot

**drobinson782001** · 09-13-2012, 08:37 AM

Last question when the SQL login box pops up I notice that when I click cancel it returns to my code on line .conn. how do I prevent that from happening . Maybe a message box Can pop up

**JosephP** · 09-13-2012, 08:40 AM

there is no line .conn
I reckon you'll have to add an error handler to trap the opening of the connection failing

**drobinson782001** · 09-13-2012, 09:22 AM

It's on .open not .con

Run time error invalid authorization

**JosephP** · 09-13-2012, 09:25 AM

like I said you need an error handler

Please Login or Register  to view this content.

**drobinson782001** · 01-02-2013, 04:21 PM

quick question instead of using the SQL statement
Else
SQL = "select TR_Status from TBL_TEST WHERE TR_Id = '" & frmTransaction.txtTransaction.Text & "'"

i would like to replace that with a store procedure 'sp_transmon_get_transactions_by_status'

and also how can a export my listbox entries into a new spreadheet. my list box has 3 colums name, address, date i would like to export all 3 colums into a excel spresheet with the click of a button the spresheet must have all there colums

any help would be appreciated

Connect to SQL SERVER with a excel 2010 userform

LinkBack

Thread Tools

Rate This Thread

Display

Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Re: Connect to SQL SERVER with a excel 2010 userform

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions