Good Question! Need Solution

[fugazi_8]

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

[Arthur Wood]

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

[fugazi_8]

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
>

[fugazi_8]

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
>>
>

[Arthur Wood]

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
>>
>

[fugazi_8]

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
>>>
>>
>