Solved

change listbox to input

Posted on 2004-08-24
11
174 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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…

680 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