Access Form Linked to SQL Server: Automatically Generated Record Source Corrupted in Design View

Question

I'm facing an issue with Access forms linked to SQL Server tables. I typically set the forms' RecordSource property using VBA in the Form_Load event, like this:

Set Me.Recordset = CurrentDb.OpenRecordset("some SQL")

I'm using linked tables to SQL Server.

Everything works fine until I go into the form's design view and make some changes (e.g., move textboxes). Afterward, the automatically generated RecordSource in the form properties becomes corrupted (incomplete) and produces an error.

For example:

Select field1,field2,field3 from Table where field4="test data"

changes to:

Select field1,field2,field3 from Table where field4="te

Such Record Source naturally produces an error and is very time consuming because that happens every time I change something in form design.

The only option that I am left with is that I manually delete Record Source in properties window and let it populate again automatically on Form_Load again.

Answer 1

Set the RecordSource directly at the form open event:

Me.RecordSource = "Select field1,field2,field3 from Table where field4='test data'"