Recordset Select Error

[StlMacMan]

I have a data entry form that saves data to Access db. I can read and display it via an ADODB control on Form4, but don't want to move one by one through all the data stored. I have attempted this sql statement on Form3 to select the particular data to populate Form4. Can't get it to work. Keep getting error message "Compile error: Syntax error". Any help appreciated. --Ed

Private Sub Enter_Click()
If TxtCtrtNo <> "Contract#" Then
Form4.Show
Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon

ssql = "SELECT Contract_Number FROM Contract" _
"WHERE Contract_Number = 'TxtCtrtNo.text'"
rs.Open ssql, Con

rs.Close
Set rs = Nothing
Con.Close
Set Con = Nothing


End If
End Sub

 

[tsdragon]

You select statement needs to be one continuous string, you're missing the concatenaction at the continuation (and you'll need a space before where), and you're using the name of the variable in the query instead of it's value. Try this:

    ssql = "SELECT Contract_Number FROM Contract " [red]&[/red]_
    "WHERE Contract_Number = '[red]" & [/red]TxtCtrtNo.text[red] & "[/red]'"
    rs.Open ssql, Con

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[StlMacMan]

Thanks for the tip. It solved the error problem, and though Form 4 shows, it does not display the result of the sql query. Is my syntax in error for opening Form 4 from Form 3?

 

[BiggerBrother]

The SQL statement is now correct, but you need to include a section of code to populate the controls on form4. At the moment you are showing form 4 and selecting a recordset, but there is nothing to link the controls of form 4 to the recordset.

What controls do you have on form4? Before rs.close you will need to include some coding such as:

Form4.myTextbox.text = rs.fields("myDBfieldName").value

Where myDBfieldName is the name of a column in your database, and myTextBox is the name of a textbox on form4.

Good luck

BB

 

[StlMacMan]

BB--I've modified my code as below. The "Form4.TxtUnit.Text ..." is one of about 18 fields I am trying to populate. Here is the new error message I am getting:

Run-time error 3265: Item cannot be found in the collection corresponding to the requested name or ordinal.

But I know the db Field is there and notated correctly as well as the Form field name. What do you think? --Ed




Private Sub Form_Load()

Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon
Form4.TxtUnit.Text = rs.Fields("Corps_Institution").Value

ssql = "SELECT (Corps_Institution, Program, Funder, " _
& "Contract_Amount, Contract_Number, Start_Date, End_Date, Contract_Type, " _
& "Application_Submission, Face_Sheet_Completed, Received_in_SS_Dept, " _
& "Presented_to_DFB, Sent_to_THQ, Received_from_THQ, Sent_to_Site_or_Funder, " _
& "Executed_Copy_Sent_to_THQ_or_Unit, Executed_Copy_received_from_Funder, Filed)FROM Contract " _
& "WHERE Contract_Number = '" & Form3.TxtCtrtNo.Text & "')"


rs.Close
Set rs = Nothing
Con.Close
Set Con = Nothing

End Sub

 

[johnwm]

You appear to be trying to use your recordset before you actually open it or populate it

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[BiggerBrother]

At the moment you are openning a connection to the db, setting the sql variable to your select statement, and closing your db connection. The SQL statemetn is never actually performed on the db. Try this

Private Sub Form_Load()

Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon

ssql = "SELECT (Corps_Institution, Program, Funder, " _
& "Contract_Amount, Contract_Number, Start_Date, End_Date, Contract_Type, " _
& "Application_Submission, Face_Sheet_Completed, Received_in_SS_Dept, " _
& "Presented_to_DFB, Sent_to_THQ, Received_from_THQ, Sent_to_Site_or_Funder, " _
& "Executed_Copy_Sent_to_THQ_or_Unit, Executed_Copy_received_from_Funder, Filed)FROM Contract " _
& "WHERE Contract_Number = '" & Form3.TxtCtrtNo.Text & "')"

rs.source = ssql
rs.open

if rs.eof = false then 'check you have a recordset.
[red]Form4.TxtUnit.Text = rs.Fields("Corps_Institution").Value[/red]
' place all control populations here

end if

rs.Close
Set rs = Nothing
Con.Close
Set Con = Nothing

End Sub

Good luck

BB

 

[StlMacMan]

BB--I believe I have modified my code as you suggested and I now get "Operation is not allowed on an object referencing a closed or invalid connection." DeBug highlights "rs.Open" in the code below. I appreciate your help.

Option Explicit

Dim rs As ADODB.Recordset
Dim Con As ADODB.Connection
Dim ssql As String

Const strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\EdM.SALVARMY\My Documents\Contracts.mdb"

Private Sub Form_Load()

Set Con = New ADODB.Connection
Set rs = New ADODB.Recordset
Con.Open strCon

ssql = "SELECT (Corps_Institution, Program, Funder, " _
& "Contract_Amount, Contract_Number, Start_Date, End_Date, Contract_Type, " _
& "Application_Submission, Face_Sheet_Completed, Received_in_SS_Dept, " _
& "Presented_to_DFB, Sent_to_THQ, Received_from_THQ, Sent_to_Site_or_Funder, " _
& "Executed_Copy_received_from_Funder, Executed_Copy_Sent_to_THQ_and_Unit, Filed)FROM Contract " _
& "WHERE Contract_Number = '" & Form3.TxtCtrtNo.Text & "')"

rs.Source = ssql
rs.Open

If rs.EOF = False Then
Form4.TxtUnit.Text = rs.Fields("Corps_Institution").Value
Form4.TxtProgram.Text = rs.Fields("Program").Value
Form4.TxtFunder.Text = rs.Fields("Funder").Value
Form4.TxtCtrtDollarAmount.Text = rs.Fields("Contract_Amount").Value
Form4.TxtCtrtNo(0).Text = rs.Fields("Contract_Number").Value
Form4.TxtCtrtStartDate.Text = rs.Fields("Start_Date").Value
Form4.TxtCtrtEndDate.Text = rs.Fields("End_Date").Value
Form4.TxtCtrtType.Text = rs.Fields("Contract_Type").Value
Form4.TxtAppDate.Text = rs.Fields("Application_Submission").Value
Form4.TxtFaceSheetCompleted.Text = rs.Fields("Face_Sheet_Completed").Value
Form4.TxtReceivedBySS.Text = rs.Fields("Received_in_SS_Dept").Value
Form4.TxtSubmittedToDFB.Text = rs.Fields("Presented_to_DFB").Value
Form4.TxtSentToTHQ.Text = rs.Fields("Sent_to_THQ").Value
Form4.TxtReceivedFromTHQ.Text = rs.Fields("Received_from_THQ").Value
Form4.TxtSentToFunder.Text = rs.Fields("Sent_to_Site_or_Funder").Value
Form4.TxtExecutedCopyReceivedFromFunder.Text = rs.Fields("Executed_Copy_received_from_Funder").Value
Form4.TxtExecutedCopySentToTHQ.Text = rs.Fields("Executed_Copy_sent_to_THQ_and_Unit").Value
Form4.TxtFiled.Text = rs.Fields("Filed").Value

End If

rs.Close
Set rs = Nothing
Con.Close
Set Con = Nothing

End Sub

 

[BiggerBrother]

Sorry, before rs.open, place the line rs.ActiveConnection = Con

Good Luck

BB

 

[StlMacMan]

BB--I'm discouraged. I added the ActiveConnection line and the new error message is Run-time error: Syntax error (comma) in query expression (the SELECT statement). But there isn't a comma error or spelling error or any other error I can find in that statement. Debug highlights rs.Open

I have been over the SELECT statement 20 times, compared it w/ the db table names, etc. Everything looks fine. Just can't get past this error. Any hunch? --Ed

 

[StlMacMan]

Yes! Got it! A parentheses problem, not comma. Thanks for all your help. --Ed

 

[BiggerBrother]

Glad you finally sorted it.

BB