none
Runtime Error 3075 Newbie Question RRS feed

  • Question

  • Hi all

    I have an after update event on a combo box which simply turns the control label red if a condition is met.

    The problem is that the combo box is on a query form and it is possible/likely that a user will want to manually delete the combo box content (i. e. remove this filed from the search criteria) for a second or subsequent query.

    When I delete the content of the combo box the after update event triggers the 3075 runtime error.

    The error message is "Syntax error (missing operator) in query expression 'ConID1= OR ConID2='.

    The after update code is:

    Private Sub CmbContract_AfterUpdate()
    If DCount("*", "tbLinkContract", "ConID1=" & Me.CmbContract.Column(0) & " OR ConID2=" & Me.CmbContract.Column(0)) > 0 Then
    Me.CMBContract_Label.ForeColor = vbRed
    Else
    Me.CMBContract_Label.ForeColor = vbBlack
    'If Not IsNull(DLookup("ConID1", "TbLinkContract", "ConID1 = " & Val(CmbContract.Column(0)))) Then
    'MsgBox "This contract is linked to another"
    End If
    End Sub

    Can I add some code to check for a null value somehow to prevent the "If DCount(...." line running?

    Thanks

    Phil

    Saturday, December 5, 2015 9:41 PM

Answers

  • Hi Phil. I see what you're saying now. You're right that a label can't use CF, but you can also use a Textbox as a Label and then use CF on it. Here's an example expression to use DCount() within CF:

    • Marked as answer by TheHC Sunday, December 6, 2015 5:21 PM
    Sunday, December 6, 2015 3:26 PM

All replies

  • Hi Phil. Rather than use code, have you tried using Conditional Formatting? I'm not sure if it will work, but you might give it a try just in case. Just a thought...
    Saturday, December 5, 2015 10:27 PM
  • Thanks DB Guy

    I am happy to be proved wrong, but I think that the test being run in the after update event is too complex to be entered as an expression under conditional formatting.

    Being a Newbie, I hadn't heard of c.f. before,  but having googled it, it looks useful.  It seems largely to be targeted at format changes based on field values and calculations within the current form, rather than on referenced  tables as in this case.

    I am still on Access 2007 for home use, so functionality may have improved in later versions.

    Phil

    Saturday, December 5, 2015 11:16 PM
  • Hi Phil. I'm not in front of a computer now bit did you try using CF and it didn't work? I thought you were only trying to change the fore color to red, which is within the capabilities of CF.  The tricky party is in building your expression, which if I am not mistaken will be able to handle DCount().
    • Edited by .theDBguy Saturday, December 5, 2015 11:43 PM
    Saturday, December 5, 2015 11:42 PM
  • Hi DB Guy

    No I didn't try to use CF as such.  Having not used it before, I googled the general use.  Examples of expressions seemed to be limited to calculating/comparing values based on fields in the current form, the result of which alters format.

    To be honest, I am struggling enough with the vba, hence all the recent posts.  I have tried pasting the DCount code into the expression field (omitting the leading "If") but this doesn't seem to work.  Currently I am altering the colour of a label with the vba code method, but this doesn't seem an option with CF (only controls seem to accept CF).  As noted earlier I am using Access 2007 at home. From the technet articles, the CF in this version may be less capable than 2010 looking at this https://msdn.microsoft.com/en-us/library/office/gg508986(v=office.14).aspx

    But it may be that the funcionality is there, but with more limited user hand-holding.

    If you have any links which describe the building of a DCount style expression they would be appreciated.

    Thanks again

    Phil

    Sunday, December 6, 2015 9:49 AM
  • Hi Phil. I see what you're saying now. You're right that a label can't use CF, but you can also use a Textbox as a Label and then use CF on it. Here's an example expression to use DCount() within CF:

    • Marked as answer by TheHC Sunday, December 6, 2015 5:21 PM
    Sunday, December 6, 2015 3:26 PM
  • Hi DB Guy

    Thanks for the continued interest!

    To test the CF, I added a text box alongside the existing label (the latter still controlled by vba).

    Having applied the CF to the text box I found that the expression worked once the "ME." was removed from the references to the CmbContract control.  So the expression now reads:

    DCount("*","tbLinkContract","ConID1=" & CmbContract.Column(0) & " OR ConID2=" & CmbContract.Column(0))>0

    What is strange is the reaction time - the vba changes the label format immediately, but the CF has about 1 second delay on the text box.

    Many thanks for your help resolving this issue.

    Phil

    Sunday, December 6, 2015 5:21 PM
  • Hi Phil. You're welcome. If I could see your file, I might be able to help you find a better solution. Good luck with your project.
    Sunday, December 6, 2015 6:02 PM
  • Hi DB Guy

    Thanks for the offer, but this database is only for internal use, so doesn't have to be ultra pretty.  The CF method applied to the text box works fine just to alert the user to linked contracts.

    I may look at the aesthetics again, but for now (with your help) I have a procedure for creating the required query forms.  That is of course until I think of another "must have" feature.

    Phil

    Sunday, December 6, 2015 9:26 PM
  • Hi Phil. Sounds good. Just holler if you need more help. Cheers!
    Sunday, December 6, 2015 9:58 PM