SQL Statement

[Chris via AccessMonster.com]

Hello all,

I have been using ADO to perform some basic data manipulation in single
tables (Looking for a particular field match and modify another fields value).

I have now moved on to one where I am going to have to match two criteria and
I know that with ADO you can not reference a table in your
rsSomeActiveConnection.Open "tblnamehere",,adOpenKeyset,adLockOptimistic,
AdCmdTable statement, so I will have to use an SQL statement.

I created the SQL statement and then checked it based upon one if I created a
query and everything matches.
I then created a form that will ask for the two criteria, Date and Crew
Number. On this same form I have a button that will run the ADO statement,
fill six(6) variables, open a second form and fill the text boxes on the
second form with the six variable.

Just to make sure I am doing this correctly, after I filled one of the
variable, I have it create a message box and display the variable. When I do
this, instead of it coming up with the correct integer, 17 the value it sends
to the message box is 0. Below you will find my VB, and any help on
something I did wrong or if there is an easier way to do this, please let me
know. If you guys know of a site where I could read up on this that would
also be great to know as well. I appreciate all the help and thank you in
advance!
Thanks, Chris

Here is my VB Form:

Dim rsMyConnection As ADODB.Connection
Dim rsMyLocation As New ADODB.Recordset
Dim mySql As String
Dim SurveyFieldCrewNo As Integer
Dim txtFieldDate As Date
Dim SurveyFieldRequestID01 As Integer

mySql = "SELECT tblSurveyFieldScheduler.*, tblSurveyFieldScheduler.
SurveyFieldCrewNo, tblSurveyFieldScheduler.txtFieldDate " & _
"FROM tblSurveyFieldScheduler " & _
"WHERE tblSurveyFieldScheduler.SurveyFieldCrewNo=" & Me.SurveyFieldCrewNo & "
AND tblSurveyFieldScheduler.txtFieldDate= " & Me.txtFieldDate

Set rsMyConnection = CurrentProject.Connection
rsMyLocation.ActiveConnection = rsMyConnection
rsMyLocation.Open mySql, , adOpenStatic

SurveyFieldRequestID01 = rsMyLocation.Fields(2)

rsMyLocation.Close

MsgBox SurveyFieldRequestID01

End Sub

 

[TomU]

Your SQL statement includes:
"SELECT tblSurveyFieldScheduler.*,
tblSurveyFieldScheduler.SurveyFieldCrewNo,...
So the columns/fields returned in the recordset will be all columns in the
table plus duplicates of some other columns.

Later on you display a column in a message box with:
SurveyFieldRequestID01 = rsMyLocation.Fields(2)
rsMyLocation.Close
MsgBox SurveyFieldRequestID01
This code will display the third of all columns in the table, not
SurveyFieldCrewNo.

So I think the solution is to either correct the SQL statement to eliminate
the tblSurveyFieldScheduler.* phrase or reference the columns you want from
the recordset by name. For example:
SurveyFieldRequestID01 = rsMyLocation!SurveyFieldCrewNo

 

[RoyVidar]

Chris via AccessMonster.com wrote in message

Hello all,

I have been using ADO to perform some basic data manipulation in single
tables (Looking for a particular field match and modify another fields
value).

I have now moved on to one where I am going to have to match two criteria and
I know that with ADO you can not reference a table in your
rsSomeActiveConnection.Open "tblnamehere",,adOpenKeyset,adLockOptimistic,
AdCmdTable statement, so I will have to use an SQL statement.

Sorry? You can open a recordset based on a table with something like
this

rs.open "yourtable", cn, adopenkeyset, adlockoptimistic, adcmdtable

(cn - the connection)

what you cannot do, is use the .find method with more than one column,
but then there's the .filter property to use in stead (also seek, on
native
tables)

I created the SQL statement and then checked it based upon one if I created a
query and everything matches.
I then created a form that will ask for the two criteria, Date and Crew
Number. On this same form I have a button that will run the ADO statement,
fill six(6) variables, open a second form and fill the text boxes on the
second form with the six variable.

Just to make sure I am doing this correctly, after I filled one of the
variable, I have it create a message box and display the variable. When I do
this, instead of it coming up with the correct integer, 17 the value it sends
to the message box is 0. Below you will find my VB, and any help on
something I did wrong or if there is an easier way to do this, please let me
know. If you guys know of a site where I could read up on this that would
also be great to know as well. I appreciate all the help and thank you in
advance!
Thanks, Chris

Here is my VB Form:

Dim rsMyConnection As ADODB.Connection
Dim rsMyLocation As New ADODB.Recordset
Dim mySql As String
Dim SurveyFieldCrewNo As Integer
Dim txtFieldDate As Date
Dim SurveyFieldRequestID01 As Integer

mySql = "SELECT tblSurveyFieldScheduler.*, tblSurveyFieldScheduler.
SurveyFieldCrewNo, tblSurveyFieldScheduler.txtFieldDate " & _
"FROM tblSurveyFieldScheduler " & _
"WHERE tblSurveyFieldScheduler.SurveyFieldCrewNo=" & Me.SurveyFieldCrewNo & "
AND tblSurveyFieldScheduler.txtFieldDate= " & Me.txtFieldDate

Set rsMyConnection = CurrentProject.Connection
rsMyLocation.ActiveConnection = rsMyConnection
rsMyLocation.Open mySql, , adOpenStatic

SurveyFieldRequestID01 = rsMyLocation.Fields(2)

rsMyLocation.Close

MsgBox SurveyFieldRequestID01

End Sub

If the txtFieldDate is a Date/Time field, you would need date
delimiters

....AND tblSurveyFieldScheduler.txtFieldDate= #" & Me.txtFieldDate & "#"

but I'm surprised it didn't give you any syntax errors? In case your
date
settings differ from US, you'll probably also need to fomat the date

....txtFieldDate= #" & format$(Me.txtFieldDate,"yyyy-mm-dd") & "#"

Also, you are sure you wan't to display the third field of the
recordset?
I'm usually a bit more inclined to use the field names in stead of
relying on ordinal position

msgbox rs.fields("NameOfField").value

I'd probably also include a test for .eof prior to trying to fetch any
field
values.

 

[RoyVidar]

Chris via AccessMonster.com wrote in message

Hello all,

I have been using ADO to perform some basic data manipulation in single
tables (Looking for a particular field match and modify another fields
value).

I have now moved on to one where I am going to have to match two criteria and
I know that with ADO you can not reference a table in your
rsSomeActiveConnection.Open "tblnamehere",,adOpenKeyset,adLockOptimistic,
AdCmdTable statement, so I will have to use an SQL statement.

Sorry? You can open a recordset based on a table with something like
this

rs.open "yourtable", cn, adopenkeyset, adlockoptimistic, adcmdtable

(cn - the connection)

what you cannot do, is use the .find method with more than one column,
but then there's the .filter property to use in stead (also seek, on
native
tables)

I created the SQL statement and then checked it based upon one if I created a
query and everything matches.
I then created a form that will ask for the two criteria, Date and Crew
Number. On this same form I have a button that will run the ADO statement,
fill six(6) variables, open a second form and fill the text boxes on the
second form with the six variable.

Just to make sure I am doing this correctly, after I filled one of the
variable, I have it create a message box and display the variable. When I do
this, instead of it coming up with the correct integer, 17 the value it sends
to the message box is 0. Below you will find my VB, and any help on
something I did wrong or if there is an easier way to do this, please let me
know. If you guys know of a site where I could read up on this that would
also be great to know as well. I appreciate all the help and thank you in
advance!
Thanks, Chris

Here is my VB Form:

Dim rsMyConnection As ADODB.Connection
Dim rsMyLocation As New ADODB.Recordset
Dim mySql As String
Dim SurveyFieldCrewNo As Integer
Dim txtFieldDate As Date
Dim SurveyFieldRequestID01 As Integer

mySql = "SELECT tblSurveyFieldScheduler.*, tblSurveyFieldScheduler.
SurveyFieldCrewNo, tblSurveyFieldScheduler.txtFieldDate " & _
"FROM tblSurveyFieldScheduler " & _
"WHERE tblSurveyFieldScheduler.SurveyFieldCrewNo=" & Me.SurveyFieldCrewNo & "
AND tblSurveyFieldScheduler.txtFieldDate= " & Me.txtFieldDate

Set rsMyConnection = CurrentProject.Connection
rsMyLocation.ActiveConnection = rsMyConnection
rsMyLocation.Open mySql, , adOpenStatic

SurveyFieldRequestID01 = rsMyLocation.Fields(2)

rsMyLocation.Close

MsgBox SurveyFieldRequestID01

End Sub

If the txtFieldDate is a Date/Time field, you would need date
delimiters

....AND tblSurveyFieldScheduler.txtFieldDate= #" & Me.txtFieldDate & "#"

but I'm surprised it didn't give you any syntax errors? In case your
date
settings differ from US, you'll probably also need to fomat the date

....txtFieldDate= #" & format$(Me.txtFieldDate,"yyyy-mm-dd") & "#"

Also, you are sure you wan't to display the third field of the
recordset?
I'm usually a bit more inclined to use the field names in stead of
relying on ordinal position

msgbox rs.fields("NameOfField").value

I'd probably also include a test for .eof prior to trying to fetch any
field
values.

 

[Chris via AccessMonster.com]

Thank you for all the help and insight. After taking a look at the SQL
Statment again and resolving the multiple instances of the same field, along
with using the date criteria correctly, it works like I wanted it to.

Does anyone know of any good books or online resources that I can find
talking about SQL statments?

Again thank you for all your help.
Chris

 

[Chris via AccessMonster.com]

Thank you for all the help and insight. After taking a look at the SQL
Statment again and resolving the multiple instances of the same field, along
with using the date criteria correctly, it works like I wanted it to.

Does anyone know of any good books or online resources that I can find
talking about SQL statments?

Again thank you for all your help.
Chris