1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

access database question

Discussion in 'Software Development' started by charliekim, Jan 6, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    i have fields in my database, and the first field is autonumber which is set as the primary key...

    the next field starts with Lname, Fname, Add, state, and so on...

    how do i sort the Lname field which stands for last name to be in alphabetical order everytime a user inputs that information through a visual basic program.....

    thanks..
     
  2. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    The data won't be input into the dbase in alpha order, but you can control how the lists and data are viewed once the info is already in your dbase by how you write your sql stmts... example: SELECT * FROM Directory ORDER BY lname

    hope this helps,

    MBN

    PS: If your previous thread is complete, could you mark it solved, thank you.
     
  3. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    i see, but how i'm viewing all the recordsets inside the database is not by a sql statement.

    i'm connected through adodc and populating and used adcmdtable... but i've changed it to adcmdtext and put in that sql statement and it's not working....

    any ideas....
     
  4. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    this is my view button coding...

    -------------------

    Private Sub cmdView_Click()

    Me.Hide
    frmFiles.Show
    frmFiles.Adodc1.Refresh
    frmFiles.DataGrid1.Refresh

    End Sub

    -----------------------

    i guess there is a conflict when i use adcmdText and insert the sql statement...

    the highlighted error when i debug is

    frmFiles.Adodc1.Refresh

    and when i take that line out... my datagrid doesn't view any of my recordsets...
     
  5. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    The answer depends on when/where you are first pulling set of records to populate your fields.

    I just don't have enough information to help you....maybe if you post a screen shot of what you have, it might help....

    MBN
     
  6. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    Option Explicit
    Dim conn As ADODB.Connection

    Private Sub cmdClear_Click()
    Dim ctr As Control
    For Each ctr In Me.Controls
    If TypeOf ctr Is TextBox And Not (ctr Is txtInstructions) Then
    ctr.Text = vbNullString
    End If
    Next
    txtLast.SetFocus
    End Sub

    Private Sub cmdDelete_Click()
    Dim strSql As String

    strSql = "DELETE * FROM Directory WHERE Lname= '" & txtLast.Text & "' and Fname = '" & txtFirst.Text & "'"

    conn.Execute strSql

    MsgBox "File has been Deleted...", vbOKOnly, "Successful"

    Dim ctr As Control
    For Each ctr In Me.Controls
    If TypeOf ctr Is TextBox And Not (ctr Is txtInstructions) Then
    ctr.Text = vbNullString
    End If
    Next

    End Sub

    Private Sub cmdExit_Click()
    End
    End Sub
    Private Sub cmdInput_Click()
    Dim strSql As String

    strSql = "INSERT INTO Directory (Lname, Fname, MI, Hadd1, Hadd2, Hcity, Hstate, Hzip," & _
    "Hphone, Hemail, School, Major, [Year], Sadd1, Sadd2, Scity, Sstate, Szip, Sphone, Semail," & _
    "Company, Title, Wadd1, Wadd2, Wcity, Wstate, Wzip, Wphone, Wfax, Wemail, Mobile, Dob," & _
    "Aim, Msn, Yahoo, Xanga) VALUES ('" & txtLast & "','" & txtFirst & "','" & _
    txtMiddle & "','" & txtHadd1 & "','" & txtHadd2 & "','" & txtHcity & "','" & _
    txtHstate & "','" & txtHzip & "','" & txtHphone & "','" & txtHemail & "','" & _
    txtSchool & "','" & txtMajor & "','" & txtYear & "','" & txtSadd1 & "','" & _
    txtSadd2 & "','" & txtScity & "','" & txtSstate & "','" & txtSzip & "','" & _
    txtSphone & "','" & txtSemail & "','" & txtCompany & "','" & txtTitle & "','" & _
    txtWadd1 & "','" & txtWadd2 & "','" & txtWcity & "','" & txtWstate & "','" & _
    txtWzip & "','" & txtWphone & "','" & txtWfax & "','" & txtWemail & "','" & _
    txtMobile & "','" & txtDob & "','" & txtAim & "','" & txtMsn & "','" & _
    txtYahoo & "','" & txtXanga & "')"
    txtLast.SetFocus

    conn.Execute strSql
    MsgBox "File has been Entered...", vbOKOnly, "Successful"

    Dim ctr As Control
    For Each ctr In Me.Controls
    If TypeOf ctr Is TextBox And Not (ctr Is txtInstructions) Then
    ctr.Text = vbNullString
    End If
    Next

    End Sub

    Private Sub cmdSearch_Click()
    Unload Me
    frmSearch.Show
    End Sub

    Private Sub cmdUpdate_Click()
    Dim strSql As String

    strSql = "UPDATE Directory" & " " & _
    "SET Lname = '" & txtLast & "', Fname = '" & txtFirst & "', MI = '" & txtMiddle & "', Hadd1 = '" & txtHadd1 & "', Hadd2 = '" & txtHadd2 & "', Hcity = '" & txtHcity & "'" & "," & " " & _
    "Hstate = '" & txtHstate & "', Hzip = '" & txtHzip & "', Hphone = '" & txtHphone & "'" & "," & " " & _
    "Hemail = '" & txtHemail & "', School = '" & txtSchool & "', Major = '" & txtMajor & "', [Year] = '" & txtYear & "'" & "," & " " & _
    "Sadd1 = '" & txtSadd1 & "', Sadd2 = '" & txtSadd2 & "', Scity = '" & txtScity & "'" & "," & " " & _
    "Sstate = '" & txtSstate & "', Szip = '" & txtSzip & "', Sphone = '" & txtSphone & "'" & "," & " " & _
    "Semail = '" & txtSemail & "', Company = '" & txtCompany & "', Title = '" & txtTitle & "'" & "," & " " & _
    "Wadd1 = '" & txtWadd1 & "', Wadd2 = '" & txtWadd2 & "', Wcity = '" & txtWcity & "'" & "," & " " & _
    "Wstate = '" & txtWstate & "', Wzip = '" & txtWzip & "', Wphone = '" & txtWphone & "'" & "," & " " & _
    "Wfax = '" & txtWfax & "', Wemail = '" & txtWemail & "', Mobile = '" & txtMobile & "'" & "," & " " & _
    "Dob = '" & txtDob & "', Aim = '" & txtAim & "', Msn = '" & txtMsn & "'" & "," & " " & _
    "Yahoo = '" & txtYahoo & "', Xanga = '" & txtXanga & "'" & " " & _
    "WHERE Lname = '" & txtLast.Text & "' and Fname = '" & txtFirst.Text & "'"

    MsgBox "File has been updated", vbOKOnly, "Update Complete"
    conn.Execute strSql

    End Sub
    Private Sub cmdView_Click()

    Me.Hide
    frmFiles.Show
    frmFiles.Adodc1.Refresh
    frmFiles.DataGrid1.Refresh

    End Sub
    Private Sub Form_Load()

    Set conn = New ADODB.Connection
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    conn.Open "c:\VB\Directory\Directory.mdb"

    End Sub


    this is the code for frm directory.......

    originally the datagrid populated by connectecting through an adodc1
     
  7. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    so as you can see in the cmdview_event

    it refreshs the datagrid and adodc..
     
  8. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    Private Sub cmdBack_Click()
    Me.Hide
    frmDirectory.Show
    End Sub

    this is all the code i have for frmfiles... where the datagrid is located...
     
  9. MustBNuts

    MustBNuts

    Joined:
    Aug 21, 2003
    Messages:
    2,016
    I wish I could help you, but I haven't worked with datagrids and adodc in years and I wouldn't know how to help you straighten it out...

    Any VB guru's out there who need a challenge? CODERITR, YOU OUT THERE????
     
  10. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    you have been more of a help then you know.. you are the man. i guess i'm still a rookie trying to find my way...

    thank you.
     
  11. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    i've set my adodc1 to adcmdtext and this is what my sql statement looks like..

    "SELECT * FROM Directory ORDER BY Lname ASC"

    and when i run the program, this is the error i get

    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    anyone know why?
     
  12. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    i figured it out.. there is not supposed to be any quotes in that statement....

    thanks.
     
  13. charliekim

    charliekim Thread Starter

    Joined:
    Jun 1, 2002
    Messages:
    140
    man am i bothering you guys too much..?

    here is my final question.. in the access database, how do i hide the primary key field from showing in the datagrid...

    my primary key field is just an id field in which it is autonumbering.. i don't want that field to show in the datagrid...
    thanks.
     
  14. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Don't make it part of your initial SELECT statement, only select the fields you want to show up in the datagrid. It will also make your code more efficient if you don't do the SELECT *
     
  15. coderitr

    coderitr

    Joined:
    Oct 12, 2003
    Messages:
    3,015
    What Rockn said is right. Sorry I haven't been around for this one but I've been busy around the house for the last few days.

    Modify the SQL statement to read "select fname, lname, add, state, zip from Directory order by lname asc". The adCmdText setting on the ado data control will work for any sql statement you want to put in the control's recordsource property.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/192849

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice