Runtime Error 3075 Newbie Question

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

Answer 1

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...

Answer 2

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

Answer 3

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().

Answer 4

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

Answer 5

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:

Answer 6

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

Answer 7

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.

Answer 8

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

Answer 9

Hi Phil. Sounds good. Just holler if you need more help. Cheers!