0

Have just got back into coding in Microsoft Access, so it might be something stupid.

Situation: Sqlite database linked to Access db via ODBC. Have created a form which has bound controls from a table.

Due to the way sqlite stores dates, as text, I am not able to use the calendar picker. I thought easy I can create an unbound text box control, set the format to date and then I will have a date picker. Then in VBA just get it to change the value of the bound date control, which would be hidden.

Me.txt_Date_of_birth.Value = Format(Me.Txtdate_of_birth_with_calandar_control.Text, global_date_format)

It works it changes the value of the bound control (which for testing is not hidden). But when I change records I am getting an error of

Write conflict. This record has been changed by another user since you started editing it. If you save the record, you will overwrite the changes the other user made.

From my testing it is being caused by this unbound control updating the bound control. Anyone know a simple fix? And I would prefer not to get my data from a query that formats the text field into a date field.

And I can confirm that the error does not occur when I create an access table and try to update a bound control.

Gustav
  • 53,498
  • 7
  • 29
  • 55
Henry Maher
  • 25
  • 7
  • 1
    Maybe the advice given by David-W-Fenton on [this question](https://stackoverflow.com/questions/973076/linked-access-db-record-has-been-changed-by-another-user) will help. Must you use MS Access? – Crowcoder Dec 02 '17 at 13:25

2 Answers2

0

Try saving the record at once:

Me.txt_Date_of_birth.Value = Format(Me.Txtdate_of_birth_with_calandar_control.Text, global_date_format)
Me.Dirty = False
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Good idea but I get the same error. No real big deal I am just modifying my scripts and create the db side in Access and forget the idea of using SQLite. – Henry Maher Dec 03 '17 at 04:13
  • 1
    Yes. SQLite is great for many purposes, but using it alone as a backend for Access doesn't make much sense. – Gustav Dec 03 '17 at 11:29
  • what an odd comment. – Henry Maher Dec 04 '17 at 13:06
  • Thought you where beating up poor ms-access for a moment. Pls disregard above comment ... Point taken. But I just wanted to do table and query design in SQL rather than using the UI. The original issue was that I could not use the DEFAULT syntax. So I was being forced to go back into the access UI to then add the default values. Was just getting a little bit upset with access, about 4 hours of trying to find examples. But with the help I'm using the ADO.net connection which allows me to use default. So am back to front and back in Access.Yippie quick form creations. – Henry Maher Dec 04 '17 at 13:13
  • OK. I felt lost for a moment. – Gustav Dec 04 '17 at 13:19
0

Does this table have a primary key? - in the eyes of Access - by which I mean when you look at the table in design view with Access.....

If not try adding that.

Cahaba Data
  • 624
  • 1
  • 4
  • 4