Combo Box not working in Access 2016 (1 Viewer)

[Chintsapete]

This used to work on Office 2007 no problem, I upgraded recently to office 2016 and since then it gives me an error on open: The control name is misspelled or refers to a control that doesn’t exist. Said control does exist and the macro seems to work as far as the goto control.

I have a form based on a query with 3 columns:

FromDate: DateSerial(Year([PayDate]),Month([PayDate]),1)

ToDate: DateSerial(Year([PayDate]),Month([PayDate])+1,0)

MonthSel: Format([PayDate],"mmm-yyyy")

Above query works fine

I open the form with command button with the embedded macro:

OpenForm -> FormName

GotoControl -> ControlName (Combo Box showing current month, when I select any previous month, it used to update the ToDate – FromDate to month selected)

SetProperty -> ControlName -> Property -> Enabled -> Value -> True



That control has a macro:

SearchForRecord

-Object Type: blank

Object Name: blank

Record -> First

Where Condition -> = ="[Month] = " & "'" & [Screen].[ActiveControl] & "'"

Something must have changed from Office 2007 to Office 2016 that this is not working no more. This is virtually the only thing in my database that doesn’t seem to work after I upgraded to Office 2016.

Any help would be appreciated
Thanks
Pete

 

[CJ_London]

looks like your table has a field called 'MonthSel' and your macro is looking for a field called 'Month', this would generate the error message you are getting

Note that Month is a reserved word and should not be used for table and field names.

 

[Chintsapete]

Hi CJ
thank you for the quick reply, I didn't spot the macro error and the combo box works again after I changed it. However the annoying error on open persists for some odd reason I can't figure out. However I can live with it for now.
I'm aware of the reserved words, unfortunately I still have the odd legacy words in use from before I was aware of those ones. I thought I had them all out but here you go you found another one for me.
Thank you so much, your help is highly appreciated.
Pete

 

[Chintsapete]

Hi CJ
I just realized that in the OpenForm macro the set property is not needed, after I deleted that line in the macro it works perfect again.
Thanks again
Pete

 

[CJ_London]

I very much doubt a change from 2007 to 2016 would cause a macro to change 'MonthSel' to 'Month' so any errors are likely to be caused by someone changing something.

Macro's are not really the right tool for anything more than a very simple application, if only because they are difficult to debug. I advise change them to vba code - then errors like this can easily be found on compilation rather than during runtime

 

[Chintsapete]

Now you are saying this, I was thinking about it for a bit.
Possibly because of the error opening the form caused by SetProperty I noticed that 'Month' was used and changed it to MonthSel without changing the Sql statement in the macro.
However I think that the SetProperty line is a potential issue between 2007 and 2016 version. I'm not professional enough in Access programming to be 100% sure.
I'm sure your right with the VBA code, again unfortunately I'm not provisioned enough with VBA. I built the whole database on the side with a lot of help from this forum I got it together. It's not perfect but it does what it needs to. Maybe one day I have enough time to perfect it. I'm sure if I get there this forum will be busy with my questions again.
Thank you for taking the time

 

[bob fitz]

I'm sure your right with the VBA code, again unfortunately I'm not provisioned enough with VBA.

FYI There is a wizard available when a form is in Design mode which would convert all macros to VBA or you

 

[CJ_London]

I'm not professional enough in Access programming to be 100% sure.

I don't use macros for the reasons given.

The equivalent vba code would be

controlname.enabled=true

and that is something I use frequently and have not detected any issues

It may have something to do with your order of events since you attempt to go to a control which is potentially disabled - and that you can't do if it is disabled. - you would get an error along the lines of 'Access can't move the focus to ....'

swapping the order of those two lines would resolve the issue