Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    12

    Unanswered: Using a query to Enable/Disable

    I'm sure this is an easy one for you Access experts, but I need help. I have a command button on a form. The command will bring up another form with information from another table if there is matching data. I have a query linking the 2 tables. If there is no matching data I want to disable the button. If there's matching data, I want to enable the button. I know how to enable/disable based on data in a field or control, just not from the results of a query. This has to be easy.


    I'm using Access 2000.

    I'd appreciate your help!

    Cheryl

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    There are a few different ways to do this although it's not a simple one line command. You're going to need to open a recordset via code and test to see if the join type query returns matching records or not.

    If you want to enable/disable a button on a form depending on records matching between 2 tables, I might opt to put the code in the form's OnCurrent event. This way, as you navigate from record to record on the form, the button will become enabled or disabled depending on the results of the query. Here's some code I would write (in ADO) for the form's OnCurrent event but again, there's different ways to write this code (ie. in DAO).

    Private Sub Form_Current()
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    Dim strSQL As String
    strSQL = "Select IDField FROM MyTable2 where FieldToMatch = '" & Forms!MyFirstForm!FieldToMatchOn1stForm & "'" (for string field matching)
    or
    strSQL = "Select AnyField FROM MyTable2 where FieldToMatch like """ & Forms!MyFirstForm!FieldToMatchOn1stForm & """" (for string field matching - note: using 3 " takes into account ' for string matching should an apostrophe be an issue in the data.)
    or
    strSQL = "Select IDField FROM MyTable2 where FieldToMatch = " & Forms!MyFirstForm!FieldToMatchOn1stForm & "" (for integer field matching)

    (Note: you would get better performance using criteria such as in the strSQL above to match the record you're currently on in the form against the table2 versus using an INNER JOIN on 2 tables in the strSQL or in any type of query which links the 2 tables together. I'm assuming that you want to see if there are matching records in table2 that match the current record you're on in the first form. You could also utilize the Count(*) in the strSQL to give you a little bit better performance if speed is an issue but the above strSQL will work fairly fast.)

    rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockReadOnly

    msgbox "There are: " & rs.recordcount & " matching records." (for testing purposes.)

    If rs.EOF And rs.BOF Then (ie. if the recordset is at the End Of File or Beginning Of File....)
    rs.Close
    Set rs = Nothing
    Me.CmdButton.Enabled = False
    Else
    rs.Close
    Set rs = Nothing
    Me.CmdButton.Enabled = True
    End If

    End Sub

    Again, I use ADO but corresponding code could be written in DAO. Either way though, to accomplish what you want to do requires a few lines of code and entails opening a recordset (or executing the results of the query) to see if matching records exist between the 2 tables. The above would be the best way I would do it to see the results of the matching query.

    I hope the above code doesn't seem overwhelming.
    Last edited by pkstormy; 07-21-08 at 19:45.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Hmmm... nicely comprehensive answer PK

    Here's a simpler alternative.

    Code:
    If DCount("FieldInQuery", "QueryName") > 0 Then
       Me.ButtonName.Enabled = True
    Else
       Me.ButtonName.Enabled = False
    End If
    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

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    A nicer solution StarTrekker. I had a feeling I was missing something a tad easier and I bow to your simplier solution. I think I'm too used to opening recordsets.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, I'm not entirely certain why, but many people have a strong tendency to dodge Dfunctions. I like them though... used in the right places they certainly have the ability to simplify things
    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

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I think it's due to them being a bit slow against large recordsets. On smaller recordsets, they can be ideal. I've dodged them mostly from habit in writing code.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well I certainly wouldn't use them in a calculation for a field in a report... that can make the report horribly slow!
    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

  8. #8
    Join Date
    Apr 2006
    Posts
    12

    Thumbs up Using a query to Enable/Disable

    Thank you both!!

    Cheri

Posting Permissions

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