| View previous topic :: View next topic |
| Author |
Message |
drtjmpr17
Newbie
Joined: 30 Nov 2005
Posts: 4
Location: Washington
|
 Posted: Dec 22nd, 2005 01:41 AM Post subject: setting up a search box |
 |
|
Ok i did some searching on the web and found that SQL is the code i needed.
I am pretty much stuck in the same spot as i was before.
I have 2 tables that display information
partnumbers: is just a single field that displays all the part numbers ex.101-102, 101-103 -- this is the info just for the combo1 (combo box)
machine: has all the tools needed for the partnumbers. and for every tool number it yes / no for the partnumber field.
example.
mastertool......... size.........diameter.....101-102......101-103....etc
101....................2.................1.0...........yes............no
102....................3.................2.0...........no..............yes
here is my code.
[vb]
Private Sub Command1_Click()
Dim myconn As ADODB.Connection
Dim myrecset As ADODB.Recordset
Dim strmastertools As String
Dim strdiameter As String
Dim mycombo1
Set myconn = New ADODB.Connection
myconn.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\Owner\Desktop\databaseinfo\2000db.mdb;"
myconn.Open
mycombo1 = combo1.Text
Set myrecset = myconn.Execute("SELECT mastertools, diameter FROM machine WHERE mastertools = '" & mycombo1 & "'")
Do Until myrecset.EOF
strmastertools = myrecset.Fields.Item("mastertools").Value
strdiameter = myrecset.Fields.Item("diameter").Value
lstdata.AddItem strmastertools & " _ " & strdiameter
myrecset.MoveNext
Loop
myconn.Close
End Sub
[/vb]
now i need to get the combo1 to be the number used to search. and have the yes on all tools needed.
[vb]Set myrecset = myconn.Execute("SELECT mastertools, diameter FROM machine WHERE mastertools = '" & mycombo1 & "'")[/vb]
i know is where i am wrong at.
Last edited by drtjmpr17 on Jan 14th, 2006 01:44 PM; edited 3 times in total |
|
| Back to top |
|
dougthomas
Moderator
Joined: 27 Jul 2005
Posts: 271
Location: Essex, UK
|
| Posted: Dec 26th, 2005 01:59 AM Post subject: |
|
|
Hi,
Not too sure what you mean by "to search table 2 fields not rows".
What is the relationship between the Product Number in the Combo and the Rows of Table 2 ? If the Product Number is common you can use the selected value to build a SELECT statement for Table 2.
Regards
Doug
_________________
If you can see the light at the end of the tunnel, it probably means there's a Train coming. |
|
| Back to top |
|
drtjmpr17
Newbie
Joined: 30 Nov 2005
Posts: 4
Location: Washington
|
| Posted: Jan 17th, 2006 10:41 PM Post subject: |
|
|
i am getting an error
compile error
data or method not found
color red is where it highlights
DataCombo1.AddItem rs("partnumbers")
| Code: | Option Explicit
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Private Sub Command1_Click()
If DataCombo1 <> "" Then
If rs.State = 1 Then rs.Close
rs.Open "Select mastertools, diameter, size From tblMachine Where " & DataCombo1 & " = 1"
DataGrid1.ReBind 'If this doesn't work try Set datagrid1.Datasource = rs
End If
End Sub
Private Sub Form_Load()
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
Set DataGrid1.DataSource = rs
cn.Open "provider=microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\Owner\Desktop\databaseinfo\2000db.mdb;"
rs.ActiveConnection = cn
rs.Open "Select PartNumbers From partnumber"
Do Until rs.EOF = True
[color=red] DataCombo1.AddItem rs("partnumbers")[/color]
rs.MoveNext
Loop
'Now close and open again with other data
rs.Close
'rs.Open "Select * from machine"
'now set the property of the datagrid object to the rs.
'DataGrid1.DataSource = rs
End Sub
|
|
|
| Back to top |
|
VB_Developer
Newbie
Joined: 25 Feb 2006
Posts: 18
Location: Hyderabad, India
|
| Posted: Feb 26th, 2006 12:32 PM Post subject: |
|
|
Hi,
You are getting this problem, the reason is there is no Property or Event, so called AddItem for DataCombo1.
Thanks
Deepak |
|
| Back to top |
|
|
