Data from ComboBox not populating Date field

[RobFII]

Hello all. I have a quick question. I'm sure it's an easy answer and I am just missing something with this. I have a legacy 2008 MDB database that was converted over to a SQL server. Conversion went as planned and made the old Database a SQL front end. Linked the tables to the new SQL tables, all is working as intended.



The database is a very simple Cert of Calibration database. There are only three tables, Data, Equip and CofC Data. The Equip table holds the Equipment that is used for Calibration, the asset number and the Calibration Due Date. The Form used to enter the Cal data for our products has a ComboBox drop down and the user would select a piece of Equip and it would populate the fields in the form with the ID, Description, Asses and Cal Due Date. Whomever originally created the DB NEVER used the Equip table. Rather they coded the different pieces of Eq for the COmboBox as a Value List. This would have been fine but with 10 ComboxBox fileds, anytime the Cal date needed to be updated was a PITA. THus my idea to utilize the Equip table instead.

With the new Query, the data is getting pulled from the Equip table and populating all the fields on the Form EXCEPT the Cal Date field (CD10).
Here is the Code I am using for the On Change Event for Field ID10

Private Sub ID10_Change()
Me.Equip10.Value = Me.ID10.Column(1)
Me.Tag10.Value = Me.ID10.Column(2)
Me.CD10.Value = Me.ID10.Column(3)

The CD field is the only field not populating the data from the Equip table and not sure what I am missing.

Any suggestions would be grateful!

[Welshgasman]

Combos start at index 0?

Have you got the correct number of columns in it's property.

[RobFII]

Hello. I had to double check lol. Yes, starts at 0 and there are four columns with the Date column being the fourth or (3) in the code. The Equip10 and Tag10 fields populate just fine.

[RobFII]

I think I need to clarify what I am asking for. SO the ComboVox dropdown shows all the correct information. When I select an Item, not all of the data is getting populated in the form.The CD10 (Date) field does not populate. All the others do.

[Welshgasman]

Show us the results of the combobox source. I can only assume there is something wrong with the date structure.
Are you using standard date in SQL or some fancy DateTime2 format?

You need to attach pics here, you cannot just copy and paste, though it looks like you can.

What version of Access are you using now?

https://www.google.com/search?q=sql+...hrome&ie=UTF-8

[CJ_London]

I presume you have set the combo columncount property to 4?

[RobFII]

Here is the rub of all this. I didn't perform the conversion to SQL, we have a third party company take care of this. I think you are correct in assuming there is something strange with the SQL date field type. Originally when the conversion was done, they had the all the date fields set to Date with time. I reached out to the company that did the conversion and they modified the field to just be date with yyyy-mm-dd format. Now the dates display correctly on the form for the existing data.

We use Office 365 Apps for Enterprise.

Thanks for the heads up on pics, yeah I was wondering why the Post never got...posted.

First is the Combox and second is the Linked SQL Table it's pulling from.

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.

[Welshgasman]

Perhaps they set the date type to datetime2, when just datetime would be enough?
What happens when you do need to store the time?

[RobFII]

I presume you have set the combo columncount property to 4?

Ok it's official, I am a dumbass! I knew it was something simple I was over looking!!!! The column count was set to 2!!!! The date column is 3!!!! Sometimes an extra set of eyes is all it takes.

Thank you SO MUCH! I can move forward with getting this deployed now.

Thank you again!

[Welshgasman]

I asked that in post #2

[RobFII]

I asked that in post #2

You did indeed. I was bouncing back and forth with a few tasks and just missed it. My apologies.

I very much appreciate the assistance.