Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VB Forms & 1 Access Table Search w/ 1 recordset return 2

Status
Not open for further replies.
Joined
May 1, 2006
Messages
35
Location
US
Good Morning-

I am new to VB and VB Forms. I am still in the learning phase. I have searched these forums, connectionstrings.com, even ask.com. I'm getting a little confused with all the information. I will make this quick and simple:

1) I have one access table
2) I am using a VB form to return my name, if my name is
entered into a text box correctly
3) Once the 'OK' button is pressed, then the search and
return will begin
4) I just want to return my name after the search into
another text box on another form

If you have any suggestions, please let me know. Thanks. Here is the code I have so far:
Code:
Private Sub Command1_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connStr As String
Dim connStr2 As String

connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\db1.mdb;"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.Open connStr
conn.CommandTimeout = 0

With rs
    .CursorLocation = adUseClient
    .LockType = adLockReadOnly
    .CursorType = adOpenStatic
    .Source = SQLString
    Set .ActiveConnection = Nothing
End With
    
If Text1.Text = "Joe Gibbs" Then
    sSQL = "SELECT Table1.Name FROM Table1"
    sSQL = sSQL & " where " & sWhereclause
    sWhereclause = " Table1.Name =  ' " & connStr
    connStr2 = sSQL
    Results.Text2.Text = connStr2
    Results.Show
    Unload Me
Else
    Error.Show
    Search.Hide
End If
  
Set ReturnRecordset = rs

ExitHandler:
    Set rs = Nothing
    Exit Sub
End Sub
 
OK...there's a lot of errors here. I'll run through a quick select with you.

Dim cn as adodb.connection
Dim rs as adodb.recordset
Dim str as string

If Text1.Text = "Joe Gibbs" Then
Set cn = new adodb.connection
cn.connection = (use same as above)
cn.open

Set rs = new adodb.recordset
str = "Select Name from Table1 Where Name = '" & Text1.Text & "'"
rs.open str,cn,(there's two connection settings that you need to determine based on your needs),adcmdtext
if rs.eof = false then
rs.movefirst
do until rs.eof = true
Results.Text2.text = rs("Name")
rs.movenext
loop
end if
Set rs = nothing

cn.close

 
if rs.eof = false then
rs.movefirst
do until rs.eof = true
Results.Text2.text = rs("Name")
rs.movenext
loop
end if
Set rs = nothing

There is no point in having a loop, or a movefirst or (pet bugbear) checking = True or False

if NOT (rs.eof and rs.bof) then
Results.Text2.text = rs("Name")
end if
rs.close
Set rs = nothing

Also Name is a terrible field name as it is a keyword. You may well need to put it in square brackets.
 
For the simple task that he mentioned, no. However I assumed he would be looking at looping through data at some point so I added it in to show how to do it. Just forgot to mention it in the first post :).

Peter does bring up a very important point. When you're naming your columns, make sure you don't use keywords. It WILL cause errors in your code when you hit it. There's a lot of websites with suggested naming conventions. There's not real right way to do it as long as you avoid certain pitfalls (keywords, spaces, ambiguous names). Other than that you can develop your own naming convention. I'd recommend documenting it and sticking with it through out your entire code. You never know when you're going to pass your project on to others and you may not want to cause them a ton of grief (or you might) :)
 
Hey folks-

Thanks for all the help! I am still working out the kinks on my part. If you could explain more, I would greatly appreciate it. I am still having a little bit of trouble with the connection portion. I will work on it some more!

Thanks again!
 
To All-

Here is the final code that made it all happen. Thanks folks for the help. If any new guys need help with this same problem. Here is a quick fix answer. Have a great VB day!

Code:
Private Sub Command1_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connStr As String
  
If Text1.Text = "Jimmie Johnson" Then
    Set conn = New ADODB.Connection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\db1.mdb;"
    conn.Open
        
    Set rs = New ADODB.Recordset
    connStr = "SELECT Name, Address, City, State, Zip, Birthdate FROM Table1 WHERE Name LIKE '" & Text1.Text & "'"
    rs.Open connStr, conn, adOpenDynamic, adCmdText
    If rs.EOF = False Then
        rs.MoveFirst
        Do Until rs.EOF = True
            Results.Text2.Text = rs("Name")
            Results.txtAddress.Text = rs("Address")
            Results.txtCity.Text = rs("City")
            Results.txtState.Text = rs("State")
            Results.txtZip.Text = rs("Zip")
            Results.txtBDay.Text = rs("Birthdate")
            rs.MoveNext
        Loop
    End If
    Set rs = Nothing
    conn.Close
    Close

    Results.Show
    Unload Me
Else
    Error.Show
    Search.Hide
End If
  
End Sub
 
Well you still have a not required loop (and are checking = True).
Also you will likely have problems if any of the fields are Null. I always use a function round the right hand side of the equations that will return a blank string if the field is Null, or I think adding & "" to the field value will work.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top