Solved

change listbox to input

Posted on 2004-08-24
11
170 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 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

17 Experts available now in Live!

Get 1:1 Help Now