Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unhappy Unanswered: OpenRecordSet......Help!!!!

    I am trying to do the below. This will always return back with the 424 error message - can someone plse tell me on what I am doing wrong?

    Dim dbLetters As DAO.Database
    Dim rstLetters As DAO.Recordset
    Set dbLetters = CurrentDb
    Set rstLetters = dbLetters.OpenRecordset("tblLetter")
    Dim txtletter As String
    Dim txtLocation As String



    If Me![FullApp] = "-1" Then
    txtletter = "Full Applicaton Form"
    MsgBox "String: " & txtletter
    Set rstLetters = db.OpenRecordset("Select * FROM tblLetter; WHERE [Letter] =" & txtletter)
    txtLocation = rstLetters![Location]
    MsgBox "Location: " & txtletter

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    99% there

    I am trying to do the below. This will always return back with the 424 error message - can someone plse tell me on what I am doing wrong?

    Dim dbLetters As DAO.Database
    Dim rstLetters As DAO.Recordset
    Set dbLetters = CurrentDb
    'dont want this:: Set rstLetters = dbLetters.OpenRecordset("tblLetter")
    Dim txtletter As String
    Dim txtLocation As String



    If Me![FullApp] = "-1" Then
    txtletter = "Full Applicaton Form"
    MsgBox "String: " & txtletter

    ' this has problems:: Set rstLetters = db.OpenRecordset("Select * FROM tblLetter; WHERE [Letter] =" & txtletter)

    Set rstLetters = db.OpenRecordset("Select * FROM tblLetter WHERE [Letter] ='" & txtletter & "';")
    'has a better chance IF you have a [Letter] = "Full Application Form"

    txtLocation = rstLetters![Location]
    MsgBox "Location: " & txtletter

    izy

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: OpenRecordSet......Help!!!!

    Originally posted by jarjarbinx1979
    I am trying to do the below. This will always return back with the 424 error message - can someone plse tell me on what I am doing wrong?

    Dim dbLetters As DAO.Database
    Dim rstLetters As DAO.Recordset
    Set dbLetters = CurrentDb
    Set rstLetters = dbLetters.OpenRecordset("tblLetter")
    Dim txtletter As String
    Dim txtLocation As String



    If Me![FullApp] = "-1" Then
    txtletter = "Full Applicaton Form"
    MsgBox "String: " & txtletter
    Set rstLetters = db.OpenRecordset("Select * FROM tblLetter; WHERE [Letter] =" & txtletter)
    txtLocation = rstLetters![Location]
    MsgBox "Location: " & txtletter
    Try

    Dim dbLetters As Database
    Dim rstLetters As Recordset
    Dim txtletter As String
    Dim txtLocation As String
    DIM SQL as String
    Set dbLetters = CurrentDb()



    If Me![FullApp] = "-1" Then
    txtletter = "Full Applicaton Form"
    SQL = "Select * FROM tblLetter; WHERE [Letter] =" & txtletter
    MsgBox "String: " & txtletter
    Set rstLetters = db.OpenRecordset(SQL)
    txtLocation = rstLetters![Location]
    MsgBox "Location: " & txtletter


    If you need to open the table to use as a lookup, then you would need to open two different recordsets. What you are currently doing is trying to open the same recordset twice. You need to close it then reopen it with a second/different SQL statement.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  4. #4
    Join Date
    Sep 2003
    Posts
    4
    Sorry none of the above work....any more ideas?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    jimpen's wont work cos it still contains your two errors in the SQL: unwanted semicolon and missing quotes around the string.

    mine might work, but since you don't say what you are trying to do it's pretty hard to tell. maybe you should explain:

    .does your form have a control FullApp?
    ..what type of control is FullApp?
    ..if it is text (your code says it is) what values can it take?
    ..what is that "-1"? (the string "-1" is not the same thing as True)

    .do you have a table tblLetter?
    ..does it contain a field Letter?
    ..does it contain a field Location?
    ...is there at least one record in tblLetter where Letter = "Full Application Form"?

    you might also want to reconsider your last two lines:
    txtLocation = rstLetters![Location]
    MsgBox "Location: " & txtletter

    izy
    Last edited by izyrider; 09-28-03 at 06:14.

  6. #6
    Join Date
    Sep 2003
    Posts
    4

    Thank You!!!

    Thank you for everyones input I have fixed the problem and have changed the VB to -

    If Me![FullApp] = "-1" Then
    SQL = "Select * FROM tblLetter; WHERE [Letter] =" & txtletter
    MsgBox "String: " & txtletter
    Set rstLetters = dbLetters.OpenRecordset("Select * FROM tblletter WHERE ([Letter] = 'Full Application Form');")
    txtLocation = rstLetters![Location]

    Thanks again JJB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •