Code Problems

[Cipher via AccessMonster.com]

First I would like to know..."where do we obtain a descriptions of the
Programming Function in ABA for Access. I have eleven books (11) and they all
dissagree on syntax and functionality of these Functions.

Second:
I have generated some TestCode to see if I can retrieve records from a Table..
..here it is:
Code:--------Start
Record_Number = 1
Flow_Time_Minutes = 0
Volume_Total = 0
Injection_Pressure_AVG = 0
Set rs = New ADODB.Recordset
StrOne = "SELECT InjDateStart FROM Inject_Cycle " & "WHERE ID=" &
Record_Number
rs.Open StrOne, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

Do While Record_Number = 1
Debug.Print rs!CasingAVG
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Code:-------STOP

Here I am forceing the While to be true so that I retrieve records. All of
the spelling is correct and all variables are Dimensioned.

I get a Run-Time error 3265..."Item cannot be found in the collection
corresponding to the requested name or ordinal"

None of my books (11) say anything about Debug or Print. Can somebody advise
on the correct syntax for ADO.

 

[tkelley via AccessMonster.com]

This is your recordset:
StrOne = "SELECT InjDateStart FROM Inject_Cycle " & "WHERE ID=" &
Record_Number

You can't reference rs!CasingAVG. CasingAVG is not in that recordset.

Am I missing something?

 

[Cipher via AccessMonster.com]

It does not matter what you type. I have tried: StrOne = "SELECT CasingAVG
FROM Inject_Cycle " & "WHERE ID=" &
Record_Number
rs.Open StrOne, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

and: Debug.Print rs!CasingAVG

or
StrOne = "SELECT InjDateStart FROM Inject_Cycle " & "WHERE ID=" &
Record_Number
rs.Open StrOne, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

and: Debug.Print rs!InjDateStart

I always get the same error.
Let me ask you this:

1) If I want to read the whole record, is the syntax: SELECT .* FROM
Inject_Cycle etc
or: SELECT * FROM Inject_Cycle etc without the period

2) Is the syntax: StrOne = "SELECT InjDateStart FROM Inject_Cycle " etc
or: StrOne = "SELECT InjDateStart FROM Inject_Cycle; " etc with a semi-colen.
Different books state it all differently.

3) If I read all the record where ID=1, do I point to each field with rs!
Field_Name
or: Inject_Cycle!FieldName
or: Inject_Cycle.Field_Name
or: rs.Field_Name

Every book states it differently and none of them work...thanks for the
assistance

 

[tkelley via AccessMonster.com]

I'll just paste in something that works for me:

=======================
Public Function fnLocalConnectionString() As String

fnLocalConnectionString = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & currentdb().Name & ";" & _
"Uid=Admin;" & _
"Pwd=;"

End Function
=======================
... start code that would be in your sub ...

Dim strSql As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open fnLocalConnectionString

strSql = _
"SELECT JobHistory.CurrentLoc " & _
"FROM JobHistory " & _
"WHERE JobHistory.CO_Number = " & Chr(39) & strOrderNum & Chr(39)
& " " & _
"GROUP BY JobHistory.CurrentLoc " & _
"ORDER BY JobHistory.CurrentLoc;"

rst.Open strSql, cnn, adOpenStatic, adLockOptimistic
=======================
To reference a field in a recordset, I use rst!FieldName just like you had
been.

Play around with how it works for me, and see what happens.

It does not matter what you type. I have tried: StrOne = "SELECT CasingAVG
FROM Inject_Cycle " & "WHERE ID=" &
Record_Number
rs.Open StrOne, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

and: Debug.Print rs!CasingAVG

or
StrOne = "SELECT InjDateStart FROM Inject_Cycle " & "WHERE ID=" &
Record_Number
rs.Open StrOne, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

and: Debug.Print rs!InjDateStart

I always get the same error.
Let me ask you this:

1) If I want to read the whole record, is the syntax: SELECT .* FROM
Inject_Cycle etc
or: SELECT * FROM Inject_Cycle etc without the period

2) Is the syntax: StrOne = "SELECT InjDateStart FROM Inject_Cycle " etc
or: StrOne = "SELECT InjDateStart FROM Inject_Cycle; " etc with a semi-colen.
Different books state it all differently.

3) If I read all the record where ID=1, do I point to each field with rs!
Field_Name
or: Inject_Cycle!FieldName
or: Inject_Cycle.Field_Name
or: rs.Field_Name

Every book states it differently and none of them work...thanks for the
assistance

First I would like to know..."where do we obtain a descriptions of the
Programming Function in ABA for Access. I have eleven books (11) and they all

[quoted text clipped - 30 lines]

None of my books (11) say anything about Debug or Print. Can somebody advise
on the correct syntax for ADO.

 

[tkelley via AccessMonster.com]

Another note ... I'm forced to work in Access 97 still. If I weren't, I'd
use the currentproject.connection like you are.

I'll just paste in something that works for me:

=======================
Public Function fnLocalConnectionString() As String

fnLocalConnectionString = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & currentdb().Name & ";" & _
"Uid=Admin;" & _
"Pwd=;"

End Function
=======================
... start code that would be in your sub ...

Dim strSql As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open fnLocalConnectionString

strSql = _
"SELECT JobHistory.CurrentLoc " & _
"FROM JobHistory " & _
"WHERE JobHistory.CO_Number = " & Chr(39) & strOrderNum & Chr(39)
& " " & _
"GROUP BY JobHistory.CurrentLoc " & _
"ORDER BY JobHistory.CurrentLoc;"

rst.Open strSql, cnn, adOpenStatic, adLockOptimistic
=======================
To reference a field in a recordset, I use rst!FieldName just like you had
been.

Play around with how it works for me, and see what happens.

It does not matter what you type. I have tried: StrOne = "SELECT CasingAVG
FROM Inject_Cycle " & "WHERE ID=" &

[quoted text clipped - 35 lines]

 

[Steve Sanford]

Don't know if you have solved this yet, but I created a table, pasting in
your field names. I entered a few records and ran the code below.

I modified two lines: I removed the "where " clause from StrOne and I used
"Do While TRUE".

The code ran without errors (except for the EOF error). Somewhere you must
have a spelling error.

Here is the code I used:

'----------------------------------------
Public Sub MyTest()
On Error GoTo MyBad

Dim Record_Number As Integer
Dim Flow_Time_Minutes As Integer
Dim Volume_Total As Integer
Dim Injection_Pressure_AVG As Integer

Dim rs As ADODB.Recordset

Dim StrOne As String

Record_Number = 1
Flow_Time_Minutes = 0
Volume_Total = 0
Injection_Pressure_AVG = 0

Set rs = New ADODB.Recordset
StrOne = "SELECT InjDateStart FROM Inject_Cycle;" ' " & "WHERE ID="
& Record_Number
rs.Open StrOne, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

Do While True
Debug.Print rs!InjDateStart
rs.MoveNext
Loop


Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

MyBad:
'err 3021 is BOF or EOF
If Err.Number <> 3021 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & ") "
End If
Resume Exit_Here

End Sub
'----------------------------------------


HTH

 

[Linq Adams via AccessMonster.com]

In answer to your first question, here;s a link with functions listed and
what they do:

http://www.webcheatsheet.com/SQL/access_functions/

 

[Cipher via AccessMonster.com]

Thank you tKelley...your suggestions worked perfectly...thank you. As you
obviously know VBA could you tell me..is there a Property (or code) that
would help me with:

- I have a form generated by a CUSTOMER file containing many customers. I
would like to generate the form from the data for that customer and then
PRINT the form and then generate the next Form for the next customer and then
print this Form by only pressing a COntrol Button on the Form ONCE.

I can print a single Form from a single Customer, but this is tedious if you
have many customers...thanks again.

I'll just paste in something that works for me:

=======================
Public Function fnLocalConnectionString() As String

fnLocalConnectionString = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & currentdb().Name & ";" & _
"Uid=Admin;" & _
"Pwd=;"

End Function
=======================
... start code that would be in your sub ...

Dim strSql As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open fnLocalConnectionString

strSql = _
"SELECT JobHistory.CurrentLoc " & _
"FROM JobHistory " & _
"WHERE JobHistory.CO_Number = " & Chr(39) & strOrderNum & Chr(39)
& " " & _
"GROUP BY JobHistory.CurrentLoc " & _
"ORDER BY JobHistory.CurrentLoc;"

rst.Open strSql, cnn, adOpenStatic, adLockOptimistic
=======================
To reference a field in a recordset, I use rst!FieldName just like you had
been.

Play around with how it works for me, and see what happens.

It does not matter what you type. I have tried: StrOne = "SELECT CasingAVG
FROM Inject_Cycle " & "WHERE ID=" &

[quoted text clipped - 35 lines]

 

[Cipher via AccessMonster.com]

Great Link...than you

In answer to your first question, here;s a link with functions listed and
what they do:

http://www.webcheatsheet.com/SQL/access_functions/

 

[Cipher via AccessMonster.com]

Thank you Steve, that helped a lot...thank you.

Don't know if you have solved this yet, but I created a table, pasting in
your field names. I entered a few records and ran the code below.

I modified two lines: I removed the "where " clause from StrOne and I used
"Do While TRUE".

The code ran without errors (except for the EOF error). Somewhere you must
have a spelling error.

Here is the code I used:

'----------------------------------------
Public Sub MyTest()
On Error GoTo MyBad

Dim Record_Number As Integer
Dim Flow_Time_Minutes As Integer
Dim Volume_Total As Integer
Dim Injection_Pressure_AVG As Integer

Dim rs As ADODB.Recordset

Dim StrOne As String

Record_Number = 1
Flow_Time_Minutes = 0
Volume_Total = 0
Injection_Pressure_AVG = 0

Set rs = New ADODB.Recordset
StrOne = "SELECT InjDateStart FROM Inject_Cycle;" ' " & "WHERE ID="
& Record_Number
rs.Open StrOne, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly

Do While True
Debug.Print rs!InjDateStart
rs.MoveNext
Loop

Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

MyBad:
'err 3021 is BOF or EOF
If Err.Number <> 3021 Then
MsgBox "Error " & Err.Number & " (" & Err.Description & ") "
End If
Resume Exit_Here

End Sub
'----------------------------------------

HTH

It does not matter what you type. I have tried: StrOne = "SELECT CasingAVG
FROM Inject_Cycle " & "WHERE ID=" &

[quoted text clipped - 64 lines]

 

[tkelley via AccessMonster.com]

I'm glad you got your ado code to work.

I don't print forms. I use reports. You may want to post this question as a
separate, new thread. Maybe with a fresh start on a new topic, you'll get a
good response from someone who likes printing forms.

Thank you tKelley...your suggestions worked perfectly...thank you. As you
obviously know VBA could you tell me..is there a Property (or code) that
would help me with:

- I have a form generated by a CUSTOMER file containing many customers. I
would like to generate the form from the data for that customer and then
PRINT the form and then generate the next Form for the next customer and then
print this Form by only pressing a COntrol Button on the Form ONCE.

I can print a single Form from a single Customer, but this is tedious if you
have many customers...thanks again.

I'll just paste in something that works for me:

[quoted text clipped - 38 lines]