Solved

change listbox to input

Posted on 2004-08-24
11
172 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we 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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

785 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