Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    34

    Unanswered: Enabling/Disabling Command Button with If statement

    Hi all,

    I tried looking through the forum hoping that something like this might've been answered already, but I wasn't able to find what I was looking for exactly.

    Here's what I have.

    Two tables [Main Table] and [Dupe_CR]

    On Main Table I would like to have a command button that links to the Dupe_CR table depending on whether a certain field on main equals the dupe. If there is something available, I'd like the command button to be enabled, else I'd like it to be disabled.

    I understand how to code simple VB, but I'm a little stumped on how to get the two fields to show if they're equal or not b/c I'm not too familiar with access syntax.

    Here's what I have so far.

    -----------------------------

    Private Sub Command113_Click()
    On Error GoTo Err_Command113_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    Dim mainform As String
    Dim dupeform As String


    stDocName = "Dupe_CR"
    stLinkCriteria = "[RQ#]=" & "'" & Me![txtRQ] & "'"
    mainform = [Main Table].[txtRQ]
    dupeform = [Dupe_CR].[RQ#]


    If mainform = dupeform Then

    Command113.Enabled = True
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Else
    Command113.Enabled = False

    End If

    Exit_Command113_Click:
    Exit Sub

    Err_Command113_Click:
    MsgBox Err.Description
    Resume Exit_Command113_Click

    End Sub

    -------------------------

    The text in red is where I'm stumped right now, but if anyone can also recommend a better way of doing this, I'd appreciate it very much.

    Thanks in advance!

  2. #2
    Join Date
    Feb 2004
    Posts
    214

    ...

    How are you pulling both tables onto your form?
    Have you created a join and running that join to your form?
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  3. #3
    Join Date
    Sep 2007
    Posts
    34
    currently, the way how i have it is that if you click on the command button on the main form, it will open up the dupe_cr form with the respected RQ numbers aligning. that's how i have the two joined togther right now.

    also, under relationships, the txtRQ in main is joined with RQ# in dupe_cr.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's something I've done...

    1. I have a function which opens the recordset to see if there's a matching value (excluding the current record) and will return the ID of the matching value (or 0 if no value matches). I put the function in the afterupdate event of a specific field I'm matching on. Here's what the function might look like for your situation (I'm doing this from memory so there MAY be punctuation errors.):

    Function retMatchingRecordID(varMatchFieldName as variant) as variant
    if isnull(varMatchFieldName) then
    msgbox "error - no value called to function. Check on value. Returning 0."
    retMatchingRecordID = 0
    exit function
    end if
    dim rs as adodb.recordset
    set rs = new adodb.recordset
    dim strSQL as string

    For string matches...
    strSQL = "Select * from MyDupTableName where MatchFieldName = '" & varMatchFieldName & "'"
    For number matches...
    strSQL = "Select * from MyDupTableName where MatchFieldName = " & varMatchFieldName & ""
    For date matches...
    strSQL = "Select * from MyDupTableName where MatchFieldName = #" & varMatchFieldName & "#"


    rs.open strSQL, currentproject.connection, adopenKeyset, adlockReadOnly
    if rs.eof and rs.bof 'ie. No records
    retMatchingRecordID = 0
    else
    retMatchingRecordID = rs!IDField
    end if
    End Function

    and then you would call this function like this...

    Dim MIDField as variant 'temp holding variable for matching ID field function so it's not called twice
    MIDField = retMatchingRecordID(Forms!MyFormName!MyFieldNameTo Match)
    if MIDField <> 0 then 'There is a matching record.
    Forms!MyFormName!Command113.Enabled = false
    and optionally if you wanted to open another form based on the matching value...
    docmd.openform "MyDupForm",,"[IDField] = " & MIDField & ""
    else
    Forms!MyFormName!Command113.Enabled = True
    end if

    Note - I like to use Forms!MyFormName...versus me! or no prefix at all. It makes sure that MSAccess knows what form and field to look at.
    Last edited by pkstormy; 11-11-08 at 00:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2007
    Posts
    34
    thanks a lot for this!

    i just have a quick questions ...

    strSQL = "Select * from MyDupTableName where MatchFieldName = '" & varMatchFieldName & "'"

    what's the difference between MatchFieldName and varMatchFieldName?

    Thanks and best regards.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question new thread please. it hleps you, it helps potential respondents

    strSQL = "Select * from MyDupTableName where MatchFieldName = '" & varMatchFieldName & "'"

    MyDupTableName is the name of the table that you are working on
    MatchFieldName is the name of the column in MyDupTableName
    varMatchFieldName is the name of the control on yoour form or variable in a code module or form

    so if your table was called Persons, the column was called surname, and the control / variable in your form was called tbSearchfor
    then it would be

    strSQL = "Select * from Persons where Surname = '" & tbSearchfor & "'"
    becuase Access/VBA can mangle quote marks one technique I tend to use is to encapusalte quoted feilds with a chr$(34) the Ascii "
    eg
    strSQL = "Select * from Persons where Surname = " & chr$(34) & tbSearchfor & chr$(34)
    ..to me its easier to read and less chance of VBA playing silly beggars with it

    if the column you were searching for was numeric
    strSQL = "Select * from Persons where ID = " & tbSearchfor
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Thanks for helping explain my post healdem. The only difference I have with healdem's code is this...

    strSQL = "Select * from Persons where Surname = " & chr$(34) & tbSearchfor & chr$(34)

    where I usually put this

    strSQL = "Select * from Persons where Surname like """ & tbSearchfor & """"

    as this takes care of apostrophies (which I think healdem's code does also though anyway.) I can't recall why but there was some reason I didn't like using the chr$(34).
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think it comes down to personal preference
    the main reason why I started to use chr$ was the IDE's attempt at being helpful ended up with carnage.. masses of "s appearing.

    I prefer using chr$(34) to indicate that there is a specific text encapsulation going on.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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