Solved Odd Behavior in a form

[pdesantis8791]

Hi All,



First off I am a bit of a novice with Access, having said that I am experiencing some strange behavior with a form. I have a combo box which is populated by the values from a table. The table is 6 fields wide, 1st field is the autonumber ID, the remaining fields are:

1. FiscalYear (Integer)
2. FiscalMonth (Integer)
3. CalendarMonth (Text)
4. MonthStartDate (Small Date)
5. MonthEndDate (Small Date)

The combo box is set to pull down the elements from the table using the following embedded query:

SELECT HD_FiscalDateInfo.CalendarMonth, HD_FiscalDateInfo.FiscalYear, HD_FiscalDateInfo.FiscalMonth, HD_FiscalDateInfo.MonthStartDate, HD_FiscalDateInfo.MonthEndDate FROM HD_FiscalDateInfo ORDER BY HD_FiscalDateInfo.FiscalYear, HD_FiscalDateInfo.FiscalMonth;

And the column widths are set as: 0.45";0.625";0.7077";0";0";0"

There are 2 text boxes on the form: “Start Date” and “End Date” which have the following control source:

=[Combo2].[column](3)

=[Combo2].[column](4)



The logic is to select the calendar month and the fiscal start and end dates will populate in the text boxes, and it worked great.



Now here is the issue, when I shut-down the database and restart it the interaction on the form does not work and I get NAME? error in the text boxes, and the text boxes do not get populated with the dates. However, if I add a control to the form of any type (button, text box, combo, …) the combo box works again perfectly.



I do not understand what is going on with the form and combo box that would cause this behavior. I am using Microsoft® Access® for Microsoft 365 MSO (Version 2406 Build 16.0.17726.20078) 64-bit

If anyone can help me with solving this problem, I would be most appreciative. Thank you in advanced for your help with this.

 

[tvanstiphout]

I recommend you add the ID as the first column. The Start and End date then become column index 4 and 5.
You already are specifying widths for 6 columns.
ColumnCount should be 6.

Does the combobox have a ControlSource?

Re #Name?: at that point, is the combobox loaded? Is Combo2 indeed its (bad) name?
In the immediate window (Ctrl+G) run this:
?Screen.ActiveForm.Combo2.RowSource

Adding a control should have no bearing on this situation. That is weird.

 

[pdesantis8791]

Hi Tom,

Thanks for your reply, the combo box does not have a control source, and yes "Combo2" is the name of the combo box (just building a prototype for now).

I agree that it is weird behavior. It is sort of like the the form is asleep until I add the control, then it wakes up.

 

[tvanstiphout]

Can you post the app, stripped down to the bare essentials, and without any PII?

 

[pdesantis8791]

Hi Tom,

I cant compress it enough to get it through. The system does not accept a .rar file which is better compression than .zip. It will take me some time to skinny the db down some.

 

[Gasman]

Hi Tom,

I cant compress it enough to get it through. The system does not accept a .rar file which is better compression than .zip. It will take me some time to skinny the db down some.

Create a new DB and just import what is required to see the issue.

 

[Pat Hartman]

Combo boxes only work reliably when the bound column for the RowSource is unique. Therefore, include the ID as the first column, then adjust the references to be (4) and (5)

You didn't say if the combo was bound. Also, if the column isn't bound, the next time the form opens, the referenced fields will not have any values since the combo will be empty.

 

[pdesantis8791]

Hi,

Thanks all for the feedback and help it is very much appreciate. I think I got it resolved, it was a combination of network issues (wrong sub-net on the router & local vs. network database) and the index in the combo box. I have tested it and re-tested it on a few different machines and it now works the way intended. Thanks again.