Good Question! Need Solution


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Good Question! Need Solution

  1. #1
    fugazi_8 Guest

    Good Question! Need Solution


    Hello,

    I Built a ADO(all code) app that uses a system DSN for MS-Acces DB. I have
    a logon for the app that stores the usernames & passwords and other user
    info. Once the user is loggedin I want the main form to only show the records
    that user has created and allow to update, addnew, etc.

    I have to tables with the user table relating a one-to-many to the owMain
    table.

    User.UserID = owMain.UserID

    -User table is the User Information i.e. Name, Username, Password, Company,
    Phone, etc
    -owMain table is the User records for my main form. i.e. Createdate, Needdate,
    Problem, Description, etc.

    How do I go about this?
    I can pass stings from the login to the main form.

    Best Regaurds,

    Corey

  2. #2
    Arthur Wood Guest

    Re: Good Question! Need Solution


    What you need to do is to add an additional Where clause to the SQL which
    retrieves records from your main table:


    " AND owMain.UserID = " & loginUser

    where loginUser is the UserID of the Logged in user. This can either be
    a global variable (not a good idea, in general), or passed to the procedure
    which retrieves the data, as a Parameter.

    Since you are alrwady doing this entirely in code, this should require a
    very minor change to your code.

    Arthur Wood

    "fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >
    >Hello,
    >
    >I Built a ADO(all code) app that uses a system DSN for MS-Acces DB. I have
    >a logon for the app that stores the usernames & passwords and other user
    >info. Once the user is loggedin I want the main form to only show the records
    >that user has created and allow to update, addnew, etc.
    >
    >I have to tables with the user table relating a one-to-many to the owMain
    >table.
    >
    >User.UserID = owMain.UserID
    >
    >-User table is the User Information i.e. Name, Username, Password, Company,
    >Phone, etc
    >-owMain table is the User records for my main form. i.e. Createdate, Needdate,
    >Problem, Description, etc.
    >
    >How do I go about this?
    >I can pass stings from the login to the main form.
    >
    >Best Regaurds,
    >
    >Corey



  3. #3
    fugazi_8 Guest

    Re: Good Question! Need Solution


    okay so my sql statment is going to be something like this, I'm not going
    to include all the tables in my statment here:

    Dim sSQL As Sting

    sSQL = "SELECT DISTINCT FULLNAME.Users, USERNAME.Users, PASSWORD.Users PROBLEM.owMain,
    DESCRIPTION.owMain, CREATEDATE.owMain FROM Users, owMain WHERE Users.UserID
    = owMain.UserID AND owMain.UserID = " & LoggedinUser

    Question: Where do I put the sSQL so it will open when the connection is
    made?
    Example:

    Public DBCon As ADODB.Connection
    Public strDBCon As String

    strDBCon = "DSN=WorkOrder DB" 'My System DSN.
    Set DBCon = New ADODB.Connection
    DBCon.Open strDBCon

    'Know where do I put the sSQL so it will open.



    Thanks again,

    Corey





    "Arthur Wood" <wooda@saic-trsc.com> wrote:
    >
    >What you need to do is to add an additional Where clause to the SQL which
    >retrieves records from your main table:
    >
    >
    > " AND owMain.UserID = " & loginUser
    >
    >where loginUser is the UserID of the Logged in user. This can either be
    >a global variable (not a good idea, in general), or passed to the procedure
    >which retrieves the data, as a Parameter.
    >
    >Since you are alrwady doing this entirely in code, this should require a
    >very minor change to your code.
    >
    >Arthur Wood
    >
    >"fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >>
    >>Hello,
    >>
    >>I Built a ADO(all code) app that uses a system DSN for MS-Acces DB. I

    have
    >>a logon for the app that stores the usernames & passwords and other user
    >>info. Once the user is loggedin I want the main form to only show the

    records
    >>that user has created and allow to update, addnew, etc.
    >>
    >>I have to tables with the user table relating a one-to-many to the owMain
    >>table.
    >>
    >>User.UserID = owMain.UserID
    >>
    >>-User table is the User Information i.e. Name, Username, Password, Company,
    >>Phone, etc
    >>-owMain table is the User records for my main form. i.e. Createdate, Needdate,
    >>Problem, Description, etc.
    >>
    >>How do I go about this?
    >>I can pass stings from the login to the main form.
    >>
    >>Best Regaurds,
    >>
    >>Corey

    >



  4. #4
    fugazi_8 Guest

    Re: Good Question! Need Solution


    Okay, this is what I did which I think should work.

    With this I get a Runtime Error:
    [Microsoft][ODBC Micrsoft Access Driver] Too few parameters.
    Expected 26

    When I click the debug it stops on the Form_Load:
    UserowMain.Open sSQL, DBCon, adOpenStatic, adLockOptimistic

    ------Module1
    Public DBCon As ADODB.Connection
    Public strDBCon As String
    Public User As String

    Private Sub Main()
    strDBCon = "DSN=WorkOrder DB"
    Set DBCon = New ADODB.Connection
    DBCon.Open strDBCon

    ------(Form)
    Private UserowMain As ADODB.Recordset
    Private sSQL As String


    ------Form_Load
    DBCon.Close
    sSQL = "SELECT DISTINCT FULLNAME.Users, COMPANY.Users, PHONE.Users, CREATEDATE.owMain,
    NEEDDATE.owMain, MODIFYDATE.owMain, RECIVEDDATE.owMain, STARTDATE.owMain,
    COMPLETIONDATE.owMain, PRODUCTIONDATE.owMain, SLXPROBLEM.owMain, SLXRPROB.owMain,
    SLXSYNC.owMain, SLXREPORT.owMain, SLXNEWDEV.owMain, SLXNEWUSER.owMain, SLXTABRELE.owMain,
    RECIVEDDATECHK.owMain, STARTDATECHK.owMain, COMPLETIONDATECHK.owMain, PRODUCTIONDATECHK.owMain,
    PROBLEM.owMain, DESCRIPTION.owMain, STATUS.owMain, PRIORITY.owMain From Users,
    owMain Where Users.UserID = owMain.UserID And owMain.UserID = " & User


    DBCon.Open
    Set UserowMain = New Recordset
    UserowMain.Open sSQL, DBCon, adOpenStatic, adLockOptimistic
    Set colBind.DataSource = UserowMain
    With colBind
    .Add txtWorkOrderID, "Text", "CTSWOID", , "CTSWoID"
    .Add txtName, "Text", "NAME", , "Name"
    .Add cmbCompany, "Text", "COMPANY", , "Company"
    .Add txtPhone, "Text", "Phone", , "PHONE"
    .Add txtCreateDate, "Text", "CREATEDATE", , "CreateDate"
    .Add txtNeedDate, "Text", "NEEDDATE", , "NeedDate"
    .Add txtProblem, "Text", "PROBLEM", , "Problem"
    .Add txtDescription, "Text", "DESCRIPTION", , "Description"
    .Add cmbStatus, "Text", "STATUS", , "Status"
    .Add txtRecivedDate, "Text", "RECIVEDDATE", , "RecivedDate"
    .Add txtStartDate, "Text", "STARTDATE", , "StartDate"
    .Add txtCompletionDate, "Text", "COMPLETIONDATE", , "CompletionDate"
    .Add txtProductionDate, "Text", "PRODUCTIONDATE", , "ProductioinDate"
    End With


    For Each oCheckDates In Me.chkFieldDates
    Set oCheckDates.DataSource = UserowMain
    Next
    mbDataChanged = False


    For Each oCheck In Me.chkFields
    Set oCheck.DataSource = UserowMain
    Next
    mbDataChanged = False
    End Sub

    Thanks again

    Corey


    "fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >
    >okay so my sql statment is going to be something like this, I'm not going
    >to include all the tables in my statment here:
    >
    >Dim sSQL As Sting
    >
    >sSQL = "SELECT DISTINCT FULLNAME.Users, USERNAME.Users, PASSWORD.Users PROBLEM.owMain,
    >DESCRIPTION.owMain, CREATEDATE.owMain FROM Users, owMain WHERE Users.UserID
    >= owMain.UserID AND owMain.UserID = " & LoggedinUser
    >
    >Question: Where do I put the sSQL so it will open when the connection is
    >made?
    >Example:
    >
    >Public DBCon As ADODB.Connection
    >Public strDBCon As String
    >
    >strDBCon = "DSN=WorkOrder DB" 'My System DSN.
    >Set DBCon = New ADODB.Connection
    >DBCon.Open strDBCon
    >
    >'Know where do I put the sSQL so it will open.
    >
    >
    >
    >Thanks again,
    >
    >Corey
    >
    >
    >
    >
    >
    >"Arthur Wood" <wooda@saic-trsc.com> wrote:
    >>
    >>What you need to do is to add an additional Where clause to the SQL which
    >>retrieves records from your main table:
    >>
    >>
    >> " AND owMain.UserID = " & loginUser
    >>
    >>where loginUser is the UserID of the Logged in user. This can either be
    >>a global variable (not a good idea, in general), or passed to the procedure
    >>which retrieves the data, as a Parameter.
    >>
    >>Since you are alrwady doing this entirely in code, this should require

    a
    >>very minor change to your code.
    >>
    >>Arthur Wood
    >>
    >>"fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >>>
    >>>Hello,
    >>>
    >>>I Built a ADO(all code) app that uses a system DSN for MS-Acces DB. I

    >have
    >>>a logon for the app that stores the usernames & passwords and other user
    >>>info. Once the user is loggedin I want the main form to only show the

    >records
    >>>that user has created and allow to update, addnew, etc.
    >>>
    >>>I have to tables with the user table relating a one-to-many to the owMain
    >>>table.
    >>>
    >>>User.UserID = owMain.UserID
    >>>
    >>>-User table is the User Information i.e. Name, Username, Password, Company,
    >>>Phone, etc
    >>>-owMain table is the User records for my main form. i.e. Createdate, Needdate,
    >>>Problem, Description, etc.
    >>>
    >>>How do I go about this?
    >>>I can pass stings from the login to the main form.
    >>>
    >>>Best Regaurds,
    >>>
    >>>Corey

    >>

    >



  5. #5
    Arthur Wood Guest

    Re: Good Question! Need Solution


    Corey,

    #1) you have the field and table portions reversed in your SQL string:

    sSQL = "SELECT DISTINCT FULLNAME.Users, USERNAME.Users, PASSWORD.Users PROBLEM.owMain,
    DESCRIPTION.owMain, CREATEDATE.owMain FROM Users, owMain WHERE Users.UserID
    = owMain.UserID AND owMain.UserID = " & LoggedinUser


    should be:

    sSQL = "SELECT DISTINCT Users.FULLNAME, Users,USERNAME, Users. PASSWORD,owMain.PROBLEM,owMain.DESCRIPTION,
    owMain.CREATEDATE ROM Users, owMain WHERE Users.UserID
    = owMain.UserID AND owMain.UserID = " & LoggedinUser



    #2) you will need to declare a RecordSet object (to hold the results of
    your query)

    Dim rsResult as ADODB.RecordSet

    then, after opening your Connectio object,


    set rsResult = New ADODB.Recordset

    rsResult.Open sSQL,DBCon

    do while not rsResult.EOF

    (ow you cane use the vcalues in your recordset, for whatever purpose
    you see fit eg

    txtTextBox = rsResult!FULLNAme --- or whatever

    rsResult.MoveNext
    Loop


    Arthur Wood

    "fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >
    >okay so my sql statment is going to be something like this, I'm not going
    >to include all the tables in my statment here:
    >
    >Dim sSQL As Sting
    >
    >sSQL = "SELECT DISTINCT FULLNAME.Users, USERNAME.Users, PASSWORD.Users PROBLEM.owMain,
    >DESCRIPTION.owMain, CREATEDATE.owMain FROM Users, owMain WHERE Users.UserID
    >= owMain.UserID AND owMain.UserID = " & LoggedinUser
    >
    >Question: Where do I put the sSQL so it will open when the connection is
    >made?
    >Example:
    >
    >Public DBCon As ADODB.Connection
    >Public strDBCon As String
    >
    >strDBCon = "DSN=WorkOrder DB" 'My System DSN.
    >Set DBCon = New ADODB.Connection
    >DBCon.Open strDBCon
    >
    >'Know where do I put the sSQL so it will open.
    >
    >
    >
    >Thanks again,
    >
    >Corey
    >
    >
    >
    >
    >
    >"Arthur Wood" <wooda@saic-trsc.com> wrote:
    >>
    >>What you need to do is to add an additional Where clause to the SQL which
    >>retrieves records from your main table:
    >>
    >>
    >> " AND owMain.UserID = " & loginUser
    >>
    >>where loginUser is the UserID of the Logged in user. This can either be
    >>a global variable (not a good idea, in general), or passed to the procedure
    >>which retrieves the data, as a Parameter.
    >>
    >>Since you are alrwady doing this entirely in code, this should require

    a
    >>very minor change to your code.
    >>
    >>Arthur Wood
    >>
    >>"fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >>>
    >>>Hello,
    >>>
    >>>I Built a ADO(all code) app that uses a system DSN for MS-Acces DB. I

    >have
    >>>a logon for the app that stores the usernames & passwords and other user
    >>>info. Once the user is loggedin I want the main form to only show the

    >records
    >>>that user has created and allow to update, addnew, etc.
    >>>
    >>>I have to tables with the user table relating a one-to-many to the owMain
    >>>table.
    >>>
    >>>User.UserID = owMain.UserID
    >>>
    >>>-User table is the User Information i.e. Name, Username, Password, Company,
    >>>Phone, etc
    >>>-owMain table is the User records for my main form. i.e. Createdate, Needdate,
    >>>Problem, Description, etc.
    >>>
    >>>How do I go about this?
    >>>I can pass stings from the login to the main form.
    >>>
    >>>Best Regaurds,
    >>>
    >>>Corey

    >>

    >



  6. #6
    fugazi_8 Guest

    Re: Good Question! Need Solution


    should i be able to add,update,save,delete,refresh that recordset?


    "Arthur Wood" <woodaa@saic-trsc.com> wrote:
    >
    >Corey,
    >
    > #1) you have the field and table portions reversed in your SQL string:
    >
    >sSQL = "SELECT DISTINCT FULLNAME.Users, USERNAME.Users, PASSWORD.Users PROBLEM.owMain,
    >DESCRIPTION.owMain, CREATEDATE.owMain FROM Users, owMain WHERE Users.UserID
    >= owMain.UserID AND owMain.UserID = " & LoggedinUser
    >
    >
    >should be:
    >
    >sSQL = "SELECT DISTINCT Users.FULLNAME, Users,USERNAME, Users. PASSWORD,owMain.PROBLEM,owMain.DESCRIPTION,
    >owMain.CREATEDATE ROM Users, owMain WHERE Users.UserID
    >= owMain.UserID AND owMain.UserID = " & LoggedinUser
    >
    >
    >
    > #2) you will need to declare a RecordSet object (to hold the results of
    >your query)
    >
    > Dim rsResult as ADODB.RecordSet
    >
    >then, after opening your Connectio object,
    >
    >
    > set rsResult = New ADODB.Recordset
    >
    > rsResult.Open sSQL,DBCon
    >
    > do while not rsResult.EOF
    >
    > (ow you cane use the vcalues in your recordset, for whatever purpose
    >you see fit eg
    >
    > txtTextBox = rsResult!FULLNAme --- or whatever
    >
    > rsResult.MoveNext
    > Loop
    >
    >
    >Arthur Wood
    >
    >"fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >>
    >>okay so my sql statment is going to be something like this, I'm not going
    >>to include all the tables in my statment here:
    >>
    >>Dim sSQL As Sting
    >>
    >>sSQL = "SELECT DISTINCT FULLNAME.Users, USERNAME.Users, PASSWORD.Users

    PROBLEM.owMain,
    >>DESCRIPTION.owMain, CREATEDATE.owMain FROM Users, owMain WHERE Users.UserID
    >>= owMain.UserID AND owMain.UserID = " & LoggedinUser
    >>
    >>Question: Where do I put the sSQL so it will open when the connection

    is
    >>made?
    >>Example:
    >>
    >>Public DBCon As ADODB.Connection
    >>Public strDBCon As String
    >>
    >>strDBCon = "DSN=WorkOrder DB" 'My System DSN.
    >>Set DBCon = New ADODB.Connection
    >>DBCon.Open strDBCon
    >>
    >>'Know where do I put the sSQL so it will open.
    >>
    >>
    >>
    >>Thanks again,
    >>
    >>Corey
    >>
    >>
    >>
    >>
    >>
    >>"Arthur Wood" <wooda@saic-trsc.com> wrote:
    >>>
    >>>What you need to do is to add an additional Where clause to the SQL which
    >>>retrieves records from your main table:
    >>>
    >>>
    >>> " AND owMain.UserID = " & loginUser
    >>>
    >>>where loginUser is the UserID of the Logged in user. This can either

    be
    >>>a global variable (not a good idea, in general), or passed to the procedure
    >>>which retrieves the data, as a Parameter.
    >>>
    >>>Since you are alrwady doing this entirely in code, this should require

    >a
    >>>very minor change to your code.
    >>>
    >>>Arthur Wood
    >>>
    >>>"fugazi_8" <charbaugh1000@hotmail.com> wrote:
    >>>>
    >>>>Hello,
    >>>>
    >>>>I Built a ADO(all code) app that uses a system DSN for MS-Acces DB.

    I
    >>have
    >>>>a logon for the app that stores the usernames & passwords and other user
    >>>>info. Once the user is loggedin I want the main form to only show the

    >>records
    >>>>that user has created and allow to update, addnew, etc.
    >>>>
    >>>>I have to tables with the user table relating a one-to-many to the owMain
    >>>>table.
    >>>>
    >>>>User.UserID = owMain.UserID
    >>>>
    >>>>-User table is the User Information i.e. Name, Username, Password, Company,
    >>>>Phone, etc
    >>>>-owMain table is the User records for my main form. i.e. Createdate,

    Needdate,
    >>>>Problem, Description, etc.
    >>>>
    >>>>How do I go about this?
    >>>>I can pass stings from the login to the main form.
    >>>>
    >>>>Best Regaurds,
    >>>>
    >>>>Corey
    >>>

    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center