Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Aug 2006
    Posts
    559

    Unanswered: Can't get my "Open" to work...

    Ok, so in the attached DB, there's a form that is used by the user to open a specific record by EP File Number (frmOpenFileNumber). The thing is, I can't get it to open the form when the user enters the EP Number into the text box and then clicks "Open".

    Example that is in the searching table is EP10

    Can anyone figure out what I did wrong?

    Thanks in advance...

    B
    Attached Files Attached Files

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, your comments in the code say that FileNB is an integer field, but it is text, as is your stated value of EP10.

    Edit: Therefore both the recordset code and the open form code need the value surrounded by single quotes. Currently the form being opened is set to Data Entry.
    Last edited by pbaldy; 09-18-08 at 14:45.
    Paul

  3. #3
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pbaldy
    Well, your comments in the code say that FileNB is an integer field, but it is text, as is your stated value of EP10.

    Edit: Therefore both the recordset code and the open form code need the value surrounded by single quotes. Currently the form being opened is set to Data Entry.
    Pbaldy,

    Thanks! I looked at that and switched it around. Now the code reads as below but when I try to search or open using the example I put into the DB "EP10", it says there are no records that match. Is there anything else that I'm overlooking?

    Code:
    Private Sub Command11_Click()
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from tblIndividual where FileNB = ' & Me!Text6 & '" 'Note this is an integer field so use ""
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    rs.Close
    Set rs = Nothing
    MsgBox "No records found matching the EP or File Number you entered!"
    Me.Text6.SetFocus
    Exit Sub
    Else
    rs.Close
    Set rs = Nothing
    DoCmd.OpenForm "frmIndividual", , , "[FileNB] = ' & Me!Text6 & '" 'Note this is an integer field so use ""
    DoCmd.Close acForm, "frmOpenFileNumber"
    If Forms!frmTask!User_Name = "" Then
    'Your option on what you want to do here.
    End If
    End If
    
    End Sub

  4. #4
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Try this...

    This is what you have....
    strSQL = "Select * from tblIndividual where FileNB = ' & Me!Text6 & '" 'Note
    This is what it should be...
    strSQL = "Select * from tblIndividual where FileNB = ' " & Me!Text6 & "'

    Note that the single tick marks are within the double quotes so they will appear in the sql text.
    --If its free, take it for what its worth!

  5. #5
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by stuschmied
    This is what you have....
    strSQL = "Select * from tblIndividual where FileNB = ' & Me!Text6 & '" 'Note
    This is what it should be...
    strSQL = "Select * from tblIndividual where FileNB = ' " & Me!Text6 & "'

    Note that the single tick marks are within the double quotes so they will appear in the sql text.
    Ok, I changed it. Still no luck, says the record is not found.

  6. #6
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Ooops!

    I think I forgot the final quotation mark at the end

    strSQL = "Select * from tblIndividual where FileNB = ' " & Me!Text6 & "'"

    If you put a breakpoint after this line you can see what strSQL actually is.
    --If its free, take it for what its worth!

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    This works for me:

    strSQL = "Select * from tblIndividual where FileNB = '" & Me!Text6 & "'"

    Though other issues arise after that.
    Paul

  8. #8
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by stuschmied
    I think I forgot the final quotation mark at the end

    strSQL = "Select * from tblIndividual where FileNB = ' " & Me!Text6 & "'"

    If you put a breakpoint after this line you can see what strSQL actually is.
    I put it in by myself when I checked and saw one at the front of the SQL SELECT statement. It still returns "No records match" and I know the FileNB is EP10 for a test cause it's record 44.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try mine (there's a space in that one).
    Paul

  10. #10
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pbaldy
    Try mine (there's a space in that one).
    pbaldy,

    Yours worked somewhat... At least it actually opened the form! Although no data from the record is in there, not even the FileNB field is occupied.

    Argh! This is starting to piss me off!

    **EDIT**

    I even tried to make it like I had in another db and put in, but the below doesn't work either...:

    Code:
    Dim strSQL As String
    Dim strFileNB As String
    
    strFileNB = Nz(Me.text6, "")
    
    strSQL = "Select * from tblIndividual WHERE 
    ((tblIndividual.FileNB) Like '*" & strFileNB & "*'); "

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you miss this?

    Quote Originally Posted by pbaldy
    Currently the form being opened is set to Data Entry.
    Paul

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Argh! This is starting to piss me off!
    This is the second time you've said this!! Relax. Either get used to frustrations like this or stop doing development work. Else you will become a very angry person.

    What a cool database to be developing! Maybe it should be called XFilesRegister or something ^_^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by StarTrekker
    This is the second time you've said this!! Relax. Either get used to frustrations like this or stop doing development work. Else you will become a very angry person.

    What a cool database to be developing! Maybe it should be called XFilesRegister or something ^_^
    Thanks Trekk! Its just frustrating and I know that's how Access is because I know it's something stupid-easy and obvious that I'm not doing which is making it now work. At least now I get an error of "No records match", unlike before where it didn't do ANYTHING!

  14. #14
    Join Date
    Aug 2006
    Posts
    559
    Quote Originally Posted by pbaldy
    Did you miss this?
    Yes, I did. So I have to switch it to NO Data Entry or Yes Data Entry? Cause when the user clicks on "New SIA", I want it to start a new record, which is what it does. But is that having an affect on the "Open File Number" aspect?

    **EDIT**

    I switched it to say "No" instead of the "Yes" it was reading but it still says it can't find the two that I've already entered into the system (EP10 and EP-01)...
    Last edited by Grafixx01; 09-19-08 at 10:56.

  15. #15
    Join Date
    May 2008
    Location
    Raleigh, NC
    Posts
    151

    Got it!

    After changing the data entry to No I think the only other thing you missed was in applying the filter to the form. I copied the code out of the module here after making sure it worked for EP10. I think you had one too many commas in where you had the filter in the docmd.openform. Also you didn't have the single tick marks in it as well. This worked on the copy I downloaded off your post. Let us know if it works for you now.

    Private Sub Command11_Click()

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select * from tblIndividual where FileNB = '" & Me!Text6 & "'" 'Note this is an integer field so use ""
    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
    If rs.EOF And rs.BOF Then
    rs.Close
    Set rs = Nothing
    MsgBox "No records found matching the EP or File Number you entered!"
    Me.Text6.SetFocus
    Exit Sub
    Else
    rs.Close
    Set rs = Nothing
    DoCmd.OpenForm "frmIndividual", , "[FileNB] = '" & Me!Text6 & "'" 'Note this is an integer field so use ""
    DoCmd.Close acForm, "frmOpenFileNumber"
    ''If Forms!frmTask!User_Name = "" Then
    '''Your option on what you want to do here.
    ''End If
    End If

    End Sub


    Stu
    --If its free, take it for what its worth!

Posting Permissions

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