Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40

    Open Recordset

    Hi All,


    Can someone tell me what I'm doing wrong here? Because I don't see it.....I'm trying to open a recordset to look at a textbox input and compare it to my database to make sure it's not already in there so my code is as follows:




    Option Compare Database
    Dim db As Database
    Dim rs As Recordset



    Public Sub Command5_Click()

    Dim str_CGBox As String
    Dim str_SQL As Variant
    str_CGBox = [Forms]![frm_CGBoxStatusIn]![CGBox]

    '
    ' Open a recordset based on the box numbers entered into the text box
    ' to check if the a record exists using a .BOF or .EOF check.
    '
    '
    ' SQL String to open the recordset
    '


    If Len([Forms]![frm_CGBoxStatusIn]![CGBox]) > 4 Then


    str_SQL = "SELECT CGBoxStatusIn.CGBox " & _
    "FROM CGBoxStatusIn " & _
    "WHERE (((CGBoxStatusIn.CGBox)='" & [Forms]![frm_CGBoxStatusIn]![txtCGBox] & "'));"


    Else

    str_SQL = "SELECT CGBoxStatusIn.CGBox " & _
    "FROM CGBoxStatusIn " & _
    "WHERE (((CGBoxStatusIn.CGBox)='" & [Forms]![frm_CGBoxStatusIn]![txtCGBox] & "'));"

    Set db = CurrentDb
    Set rs = db.OpenRecordset("str_SQL", dbOpenDynaset, dbSeeChanges)

    If rs.BOF And rs.EOF Then

    MsgBox "Box is already In-Process"


    End If
    End If



    rs.Close








    What I get is a "Run-time error '91'
    Object variable or With block variable not set





    Please advise.....


    Thanks

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you do not nee the quotes around str_SQL

    Set rs = db.OpenRecordset(str_SQL, dbOpenDynaset, dbSeeChanges)

    however I don't see the benefit of the iif statement - it is the same whether the len>4 or no

    also recommend when posting code you use the code tags to surround the code - highlight the code and hit the # key option

    Not doing so makes the code difficult to read and many responders won't bother trying to help - help others to help you

  3. #3
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    OK, Thanks....I took the quotes out and also the if statement, it is iterating through the recordset now however it is not popping up the MsgBox "Box is already In-Process" when I come to a duplicate in the database. do you see what I'm missing. I'll put in the code using the code tags you spoke of.

    Code:
    Option Compare Database
    Dim db As Database
    Dim rs As Recordset
    
    Public Sub Command5_Click()
    
         Dim str_CGBox As String
         Dim str_SQL As Variant
         str_CGBox = [Forms]![frm_CGBoxStatusIn]![CGBox]
    
         str_SQL = "SELECT CGBoxStatusIn.CGBox " & _                  
                        "FROM CGBoxStatusIn " & _
                        "WHERE (((CGBoxStatusIn.CGBox)='" & [Forms]![frm_CGBoxStatusIn]![txtCGBox] & "'));"
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset(str_SQL, dbOpenDynaset, dbSeeChanges)
    
      Do Until rs.EOF
        
        rs.MoveNext
        
        Loop
        
        
        If rs.BOF And rs.EOF Then
        
            MsgBox "Box is already In-Process"
    
    
    
       End If
    
      rs.Close
    And following that is code to add the box if it is not already in the database.


    Thanks Again,
    Lenny

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IF the sql returns any records, you are simply moving from the first to last record via a loop. If eos is true because there are no records returned, then the next line
    If rsbBof and rs.eof will never be true.

    If there are records, you will loop through to the end. Then that line will still never be true.
    Your problem is that you aren't working with your rs properly. Suggest you review http://allenbrowne.com/ser-29.html

    Additionally, you are making this far more complicated than it needs to be. Suggest you review DLookup. If it returns null, (or not, depending on your need) then take the appropriate action. Just ensure that the form control value you pass isn't Null (the control has no data when the function evaluates)
    Look here under domain functions https://www.techonthenet.com/access/functions/
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't understand your logic...

    I reworked your code a little. Why the Do... While loop? It doesn't do anything.

    Code:
    Option Compare Database
    Option Explicit   '<<--Should ALWAYS be added to EVERY code module
    
    Public Sub Command5_Click()
        Dim db As DAO.Database   'moved to inside sub
        Dim rs As DAO.Recordset   'moved to inside sub
        Dim str_CGBox As String
        Dim str_SQL As Variant
    
        Set db = CurrentDb
    
        str_CGBox = [Forms]![frm_CGBoxStatusIn]![CGBox]
    
        str_SQL = "SELECT CGBoxStatusIn.CGBox"
        str_SQL = str_SQL & " FROM CGBoxStatusIn"
        str_SQL = str_SQL & " WHERE CGBox)= '" & [Forms]![frm_CGBoxStatusIn]![txtCGBox] & "';"
        ' Debug.Print str_SQL
        Set rs = db.OpenRecordset(str_SQL, dbOpenDynaset, dbSeeChanges)
    
        'What????? This loop does nothing!!
    '    Do Until rs.EOF
    '        rs.MoveNext
    '    Loop
    
        'check to see if any records in record set.
        If rs.BOF And rs.EOF Then    'if rs.BOF = true And rs.EOF = true, then no records in record set
            MsgBox "Box is already In-Process"
        Else
            MsgBox "Box is not in database"
        End If
    
    '.
    '. other code??
    '.
    
        'clean up
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End Sub

  6. #6
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Hi Steve,
    I really appreciate your help, so I updated the code and used what you wrote. I do get the message boxes to pop up however, the first data I put in was a number that was already in the database, the first popup says input is not in the database, when you click ok it opens the next popup which is the first one in the code that says Box is already in the system(The one it should of popped up). So I don't know where do go from here. Problem is when you're a fairly newbie to this you can read books, watch Youtube, but if you have a specific question it's hard to get an answer....that's why this forum and others like it are such a great thing.....I really do appreciate your help! Thanks

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was curious because it looked like the message boxes were backwards in the code in Post #3.

    If you open a record set in VBA and "BOF = TRUE AND EOF = TRUE", then this means NO records match the criteria.


    So swap the message boxes and test again:
    Code:
        'check to see if any records in record set.
        If rs.BOF And rs.EOF Then    'if rs.BOF = true And rs.EOF = true, then no records in record set
             MsgBox "Box is not in database"
        Else
             MsgBox "Box is already In-Process"  ' record matching criteria exists 
        End If

  8. #8
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Continuing with my project with your help the code takes the user input on CGBox # and looks to see if it's already in process or not, if not it goes ahead and adds it. Problem now is when it finds a CGBox # already in the database it adds a new record with only the CGBox # only and no other data, so it's like a record with one field entered and the rest is blank. So what I need it to do is when a CGBox # is entered that is already in Process to just give the message "Box is already In-Process" and move on to the next input, without entering the CGBox # that was already there. I tried to add the following code:
    Code:
    [Forms]![frm_CGBoxStatusIn]![txtCGBox] = ""        If Len(Me.txtCGBox) = "" Then
            rs.AddNew = False
    which didn't work.....so I'm stuck....I will add the whole set of code so you can see what it's doing.

    Code:
    Public Sub cmd_Update_Scan_In_Click()'On Error GoTo Err_Command5_Click
    
    
    'Stop
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim str_CGBox As String
        Dim str_SQL As Variant
        Dim stDocName As String
        Dim stLinkCriteria As String
        Dim str_ScanIn
        Dim Message As String
        Dim Title As String
        Dim MyValue
        Dim Default
        Dim Answer As Integer
        Dim MyNote As String
        Dim int_MsgBox As Integer
        Dim str_Msg As String
        
        Set db = CurrentDb
    
    
    
    
        str_CGBox = [Forms]![frm_CGBoxStatusIn]![CGBox]
        
                   
    
    
        'Stop
        
        
            str_SQL = "SELECT CGBoxStatusIn.CGBox " & _
                      "FROM CGBoxStatusIn " & _
                      "WHERE (((CGBoxStatusIn.CGBox)='" & [Forms]![frm_CGBoxStatusIn]![txtCGBox] & "'));"
    
    
        Set rs = db.OpenRecordset(str_SQL, dbOpenDynaset, dbSeeChanges)
    
    
    
    
         'check to see if any records in record set.
        If rs.BOF And rs.EOF Then    'if rs.BOF = true And rs.EOF = true, then no records in record set
            'MsgBox "Box is not in database"
                
              
                
        Else
            MsgBox "Box is already In-Process"
            [Forms]![frm_CGBoxStatusIn]![txtCGBox] = ""
            If Len(Me.txtCGBox) = "" Then
            rs.AddNew = False
            
              'Call cleanupblankScans
              
              stDocName = "frm_CGBoxStatusIn"
              stLinkCriteria = "(((CGBoxStatusIn.CGBox)='" & str_CGBox & "'))"
              DoCmd.OpenForm stDocName, , , stLinkCriteria
                    
                    
                    Call Form_Load
                     Exit Sub
                        
                End If
              
        
        'Clean up
        rs.Close
        Set rs = Nothing
        Set db = Nothing
           
         
       'Text for Yes/No Message Box
       MyNote = "Is the Box Full?"
       
       'Display Message Box
       Answer = MsgBox(MyNote, vbYesNo, "???")
       
       If Answer = vbYes Then
       'Code for Yes button Press
        str_CGBox = [Forms]![frm_CGBoxStatusIn]![CGBox]
        [Forms]![frm_CGBoxStatusIn]![ScanIn] = Now()
            
        
        Else
        
         Message = "How many are missing?"
         Title = "CG Box"
         Default = ""
         MyValue = InputBox(Message, Title, Default)
         [Forms]![frm_CGBoxStatusIn]![missing] = MyValue
         [Forms]![frm_CGBoxStatusIn]![ScanIn] = Now()
           
        End If
        
          
    
    
        DoCmd.Close
        
        
        
        
        stDocName = "frm_CGBoxStatusIn"
        stLinkCriteria = "(((CGBoxStatusIn.CGBox)='" & str_CGBox & "'))"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    
    Exit_cmd_Done_Click:
    
    
    Exit Sub
    
    
    Err_Command5_Click:
        MsgBox Err.Description
        Resume Exit_cmd_Done_Click
        
    End Sub
    
    
    Private Sub Command8_Click()
     DoCmd.Close acForm, "frm_CGBoxStatusIn"
     DoCmd.OpenForm "frm_Welcome"
    End Sub
    
    
    
    
    
    
    Private Sub Form_Load()
    'On Error GoTo Err_Form_Load
    
    
        DoCmd.GoToRecord , , acNewRec
        Me!txtCGBox = ""
        Me!txtCGBox.SetFocus
        
        
    Exit_Form_Load:
        Exit Sub
    
    
    Err_Form_Load:
        MsgBox Err.Description
        Resume Exit_Form_Load
    End Sub
    Thanks in advance,
    Lenny
    Last edited by lamore48; 02-14-2018 at 11:24 AM.

  9. #9
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Can someone help me with this please, I'm stuck

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My comment in post 4 about this apparently being made more difficult than it needs to be didn't interest you?
    Regardless, I'd say your main problem is that you're using the wrong event. Use the BeforeUpdate event - either on the form, or the cgbox control. A click event often will cause a record save - especially when dealing with main/subform setup. Anything that causes your record to save/append (such as leaving the record part of a form to click a button) means you're too late to prevent it.

  11. #11
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    How would I incorporate an BeforeUpdate event in my code???

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I found several syntax and logic errors that need to be fixed.
    And there is a lot of "extra" code I'm not sure


    This is what I see:
    The form "frm_CGBoxStatusIn" opens.
    The form open event code executes which creates a new record in the form record source (table "CGBoxStatusIn"??) and the focus is set to the control "txtCGBox".
    Something is entered into that control. (when is something entered into the control "CGBox"?)
    Button "Update_Scan_In" is clicked.
    The code checks if the entry in control "txtCGBox" is in the table.

    (now I'm guessing)
    If the entry IS in the table, display a message, clear the control "txtCGBox" and wait for another entry.
    If the entry IS NOT in the table, enter it into the table.
    You said
    Problem now is when it finds a CGBox # already in the database it adds a new record with only the CGBox # only and no other data,
    What other data is/should be added?? Where does it come from?


    Code syntax and logic errors:
    There is a line " Dim Default"....."Default" is a reserved word in Access

    These variables are not used in the procedure: ( I commented them out)
    ' Dim str_ScanIn
    ' Dim int_MsgBox As Integer
    ' Dim str_Msg As String

    By closing the form and reopening it , you are saving the data entered after the form was opened. This works, but is expensive in terms of time and processor usage.

    You have the lines
    Code:
    MsgBox "Box is already In-Process"
            [Forms]![frm_CGBoxStatusIn]![txtCGBox] = ""    'you set the control to an empty string
           If Len(Me.txtCGBox) = "" Then                  ' the Len()  function returns a number. You cannot compare the number 3 to an empty  string. It will NEVER be TRUE
            rs.AddNew = False       ' The ADDNEW command does NOT have arguments. The syntax is "expression.AddNew"
    Enough for now.


    I would not used this method to check/add data.
    I would have an unbound form with unbound (obviously) controls. Code would do the validation and add the records.
    See the attached example.... is this close to what you want?
    Attached Files Attached Files

  13. #13
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Steve,
    Yes that is exactly what I need it to do minus adding the date, however just a couple questions....so I know what the code is doing.....
    Code:
    If Len(Trim(Nz(Me.CGBox, ""))) = 0 Or Len(Trim(Nz(Me.txtCGBox, ""))) = 0 Then
    I get that this line is looking for data in the textbox, but what is "Nz", I'm guessing a reserved word???. I appreciate your help and I also appreciate that you explain exactly what's going on....so I'm actually learning.


    Thanks,
    Lenny

  14. #14
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Steve,
    I cleaned up the code and it works(for the most part) It adds box numbers, it adds missing tools, and if it finds a duplicate in the table it doesn't add another and gives a message saying it's already there. However, I've been screwing around with this all day, and I tried different things but when a box isn't in the table it will add it however it will still give me a message that the "Box is already In-Process"

    I have zipped it up and attached so you can see exactly what I'm talking about.
    Again what I think would be an easy fix is not for me(easy)


    Thanks,
    Lenny
    Attached Files Attached Files

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    but what is "Nz",
    Nz (seems I read somewhere that it stands for "non zero" - not that it makes sense). It's used to return the value you want the function to have if it would otherwise return Null. Many operations/fields/expressions etc fail (and maybe error out) if a variable contains null. Nz handles it.
    A lot of useful functions here.
    https://www.techonthenet.com/access/functions/

    Will check back later to see if anyone else has reported on your attachment. I'm out of time for now.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to open pass through query with recordset
    By adnancanada in forum Queries
    Replies: 7
    Last Post: 01-13-2016, 11:25 AM
  2. Open recordset
    By Praveenevg in forum Access
    Replies: 2
    Last Post: 08-18-2014, 12:21 PM
  3. Recordset Open Error
    By RayMilhon in forum Programming
    Replies: 4
    Last Post: 08-27-2013, 05:54 PM
  4. open recordset with variable SQL
    By rivereridanus in forum Queries
    Replies: 4
    Last Post: 07-27-2011, 12:58 PM
  5. ADO Recordset.Open (SQL) does nothing
    By workindan in forum Programming
    Replies: 3
    Last Post: 06-23-2010, 02:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums