Solved

change listbox to input

Posted on 2004-08-24
11
171 Views
Last Modified: 2010-05-02
I have the following code, VB6.0, that can pop-up in a customer maintenance screen that will list all the City's in my table-CityList, I would like to change the code so I can also input a new City if it’s not in my table/list, does not have to be a listbox, I have played around with a combobox, but I can’t seem to get it to input any new values into my table.
Please help.
current code:
Dim objRec
Dim objConn
Dim objRec1
Dim cmdString
Dim Test
Private Sub UserForm_Initialize()
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=MSDASQL;DSN=Big Test;Initial Catalog=Test;User Id=jo;Password=*"
objConn.Open
cmdString = "Select * from Citylist"
Set objRec = objConn.Execute(cmdString)
While Not objRec.EOF
Test = objRec!City
Citylookup.ListBox1.AddItem Test
objRec.MoveNext
Wend
objConn.Close
End Sub

Private Sub CommandButton1_Click()
If IsNull(ListBox1) Then
ListBox1.Value = ""
Citylookup.Hide
Else
CustomerMaintenance.City = ListBox1
Citylookup.Hide
End If
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
CustomerMaintenance.City = ListBox1
Citylookup.Hide
End Sub

Private Sub UserForm_Terminate()
Citylookup.Hide
End Sub
0
Comment
Question by:neylamj
  • 7
  • 3
11 Comments
 
LVL 15

Expert Comment

by:SRigney
ID: 11881252
A list box is fine for displaying the data.  What I usually do in a situation like this is provide an addnew button, which I open an entry window for.  An input box could be used to get the city, then you would have to add it to your list and to the database so you have it for the next time.

Private Sub AddButton_Click()
   Dim strNewCity As String
   strNewCity = Input("Enter a City to add to the list")
   Citylookup.ListBox1.AddItem strNewCity
   
   ' Now add it back to the database.
   Dim objRec
   Dim objConn
   Dim objRec1
   Dim cmdString
   Set objRec = CreateObject("ADODB.Recordset")
   Set objConn = CreateObject("ADODB.Connection")
   objConn.ConnectionString = "Provider=MSDASQL;DSN=Big Test;Initial Catalog=Test;User Id=jo;Password=*"
   objConn.Open
   ' Note: chr$(34) is the double-quote
   ' I use chr$(34) because it is easier to spot errors than the alternative
   objConn.Execute "Insert into Citylist (city) values (" & chr$(34) & strNewCity & chr$(34) & ")"
   ' Here is the alternative.
   'objConn.Execute "Insert into Citylist (city) values (""" & strNewCity & """)"
   
End Sub
0
 
LVL 8

Accepted Solution

by:
bukko earned 65 total points
ID: 11881264
Add a testbox txtNew with a command button cmdAdd.
The cmdAdd button should call the following query, as you do to load the listbox:

Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=MSDASQL;DSN=Big Test;Initial Catalog=Test;User Id=jo;Password=*"
strSQL = "insert Citylist (City) Values (" & txtNew.Text & ")"
objConn.Execute strSQL

Then refresh your listbox.

bukko
0
 

Author Comment

by:neylamj
ID: 11881698
I get a
run-time error 3704

operation is not allowed when object is closed


back to line: objConn.Execute strSQL
0
 

Author Comment

by:neylamj
ID: 11882104
ok here's the problem now, it only excepts number, ie 1,2,3, 123, ect..  I can input any strings like Miami, or Miami2

Private Sub cmdAdd_Click()
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=MSDASQL;DSN=Big Test;Initial Catalog=Test;User Id=Neyl;Password=trendy1"
objConn.Open
strSQL = "insert Citylist (City) Values (" & txtNew.Text & ")"
objConn.Execute (strSQL)
objConn.Close
End Sub
0
 

Author Comment

by:neylamj
ID: 11882190
I can not input any string like the city of Miami or secondary name Miami2
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:neylamj
ID: 11883455
cleared that problem up, now all need to add the state as well, here is the curerent working code:

need to imput test3 as the state into the tabel simialr to City

Private Sub cmdAdd_Click()
test2 = txtNew
test3 = txtNew2
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=MSDASQL;DSN=Big Test;Initial Catalog=Test;User Id=jo;Password=*"
objConn.Open
objConn.Execute "insert Citylist (City) Values ('" & test2 & "')"
objConn.Close
End Sub
0
 
LVL 15

Expert Comment

by:SRigney
ID: 11883499
If they are in the same table then change

objConn.Execute "insert Citylist (City) Values ('" & test2 & "')"

to

objConn.Execute "insert Citylist (City, State) Values ('" & test2 & "', '" & test 3 & "')"

If it's in a different table then add another Execute method call with the correct table.
0
 

Author Comment

by:neylamj
ID: 11883549
nevermind I got it to work:  here si the code to reference:

Private Sub cmdAdd_Click()
test2 = txtNew
test3 = txtNew2
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=MSDASQL;DSN=Big Test;Initial Catalog=Test;User Id=jo;Password=*"
objConn.Open
objConn.Execute "insert Citylist (City,State) Values ('" & test2 & "','" & test3 & "')"
objConn.Close
End Sub
0
 

Author Comment

by:neylamj
ID: 11891498
one last thing, how can I get the variables, test2 & Test3 on my form to clear away after clicking on teh ADD button????

Private Sub cmdAdd_Click()
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=MSDASQL;DSN=Big Test;Initial Catalog=Test;User Id=jo;Password=*"
objConn.Open
objConn.Execute "insert Citylist (City,State) Values ('" & txtNew & "','" & txtNew2 & "')"
objConn.Close
End Sub
0
 

Author Comment

by:neylamj
ID: 11891692
this is the way I did it, let me know if there is a betwter easier way.

Private Sub cmdAdd_Click()
...
Me("txtNew") = ""
Me("txtNew2") = ""
End Sub
0
 
LVL 15

Assisted Solution

by:SRigney
SRigney earned 60 total points
ID: 11893555
This works, but you can access the controls directly with

Private Sub cmdAdd_Click()
...
txtNew.Text = ""
txtNew2.Text = ""
test2 = ""
test3 = ""
End Sub
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vba find the last empty column 10 88
Validating VB6 Function 19 56
Microsoft Access combo box help 2 38
Prevent user closing word document opened with VB6 6 67
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now