change listbox to input

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
neylamjAsked:
Who is Participating?
 
bukkoConnect With a Mentor Commented:
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
 
SRigneyCommented:
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
 
neylamjAuthor Commented:
I get a
run-time error 3704

operation is not allowed when object is closed


back to line: objConn.Execute strSQL
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
neylamjAuthor Commented:
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
 
neylamjAuthor Commented:
I can not input any string like the city of Miami or secondary name Miami2
0
 
neylamjAuthor Commented:
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
 
SRigneyCommented:
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
 
neylamjAuthor Commented:
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
 
neylamjAuthor Commented:
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
 
neylamjAuthor Commented:
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
 
SRigneyConnect With a Mentor Commented:
This works, but you can access the controls directly with

Private Sub cmdAdd_Click()
...
txtNew.Text = ""
txtNew2.Text = ""
test2 = ""
test3 = ""
End Sub
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.