MS Access Form Blank When Linked to MS SQL - Unable to Add Records

[pbaldy]

I'm stumped then. The only times I've seen the whole form go blank was when those 2 conditions were met (no records returned and no ability to add new records).

Microsoft Access tips: Why does my form go completely blank?

Explains why no controls show on Microsoft Access forms sometimes, and how to address this issue.

allenbrowne.com

 

[michaelH]

Trying to upload a short video showing and describing the issue at hand.

 

[michaelH]

If you zip it you should be able to attach with the Attach files button under the reply area.

I managed to make the video shorter and I was able to upload it.

 

[pbaldy]

I think we understood the problem. Can you add a record in the query directly when the source is the SQL table? Is the form's Allow Additions property set to Yes?

 

[michaelH]

I think we understood the problem. Can you add a record in the query directly when the source is the SQL table? Is the form's Allow Additions property set to Yes?

Yes, I can add/deleted/modify in the table and the source query directly when opened. Yes the form values are set to yes.

 

[CJ_London]

If I'm understanding correctly, the issue is that the controls disappear when no records are returned and the source table (tblAccomplishment) to the query in post #19 is a linked sql server table rather than a linked access table.

not sure if it is relevant and just a theory but your query contains a number of vba functions that will not be recognised in sql server - so what happens is the data is returned to the access environment for those functions to be implemented. If there are no records to be returned, perhaps the state of the recordset is different to the state where you are using an access linked table.

It's just a theory - to test, remove the calculated fields then select a date range where no records are returned. Do you then see the controls?

Another possible issue is the field names have spaces and non alpha numeric characters - ([Narrative (Text)],[Date (Numeric)],[SF/MC],etc) and a reserved word (Date). The latter two in particular can cause unexplainable errors or failures. At the very least put Date in square brackets and again as a test, exclude the fields with non alphanumeric characters. It is also worth noting that Text and Numeric are also reserved words and I do recall a situation a few years ago where a reserved word surrounded by brackets caused issues. It's on this forum somewhere.

Might also be worthwhile removing the filter and order by properties of the form - the recordsource is already sorted by date anyway, just include the rest in your query criteria if appropriate

And double check your other form properties - setting Data Entry to yes means it will not show current records, it only allows for new records to be entered.

 

[michaelH]

If I'm understanding correctly, the issue is that the controls disappear when no records are returned and the source table (tblAccomplishment) to the query in post #19 is a linked sql server table rather than a linked access table.

not sure if it is relevant and just a theory but your query contains a number of vba functions that will not be recognised in sql server - so what happens is the data is returned to the access environment for those functions to be implemented. If there are no records to be returned, perhaps the state of the recordset is different to the state where you are using an access linked table.

It's just a theory - to test, remove the calculated fields then select a date range where no records are returned. Do you then see the controls?

Another possible issue is the field names have spaces and non alpha numeric characters - ([Narrative (Text)],[Date (Numeric)],[SF/MC],etc) and a reserved word (Date). The latter two in particular can cause unexplainable errors or failures. At the very least put Date in square brackets and again as a test, exclude the fields with non alphanumeric characters. It is also worth noting that Text and Numeric are also reserved words and I do recall a situation a few years ago where a reserved word surrounded by brackets caused issues. It's on this forum somewhere.

Might also be worthwhile removing the filter and order by properties of the form - the recordsource is already sorted by date anyway, just include the rest in your query criteria if appropriate

And double check your other form properties - setting Data Entry to yes means it will not show current records, it only allows for new records to be entered.

Thank you for your response CJ...

Your understanding of the issue is correct.

Good call on the vba functions that are not recognized by SQL server, I did not catch that. Unfortunately, that did not resolve the issue. I replaced the calculated fields with "" in order not to break anything due to missing fields. The form still returns with no controls or text boxes for data entry.

Regarding all the non-standardized names and use of reserved words, I WOULD NEVER DO THIS. This database was developed by someone else and I am trying to transition the backend to SQL. Looks like it was more than I originally expected. I will need more time to test a more standardized naming convention and will post any results.

As for the filter and order by properties, I have tried it without these as well to no avail. I do agree with moving the sort out of the form and to the source control and it is on my list of updates of things to do.

The other form properties setting Data Entry set to Yes, the normal setting is was No, it being set to Yes was another one of my feeble attempts to get it to work.

 

[CJ_London]

OK, I'm out of ideas.

To test whether the problem is with sql server returning a zero recordcount recordset. Suggest try with another table and a temporary form.

other thoughts - in the form current event, put a bit of code to test the recordset is valid - perhaps something like

msgbox me.recordset.recordcount

test with and without data being returned

 

[michaelH]

OK, I'm out of ideas.

To test whether the problem is with sql server returning a zero recordcount recordset. Suggest try with another table and a temporary form.

other thoughts - in the form current event, put a bit of code to test the recordset is valid - perhaps something like

msgbox me.recordset.recordcount

test with and without data being returned

Hello all, I am back, and still no resolution/solution for my issue.

CJ_London,

I have done what you asked, when I add the code msgbox me.recordset.recordcount to the current event it displays the recorded count correctly when accessing the linked access table, however, when I switch the source table in the query to the SQL table I get the same results no form or form controls and NO message box displays at all as if it is getting stopped in the middle of the process.

I have also done the following. I created a new table in SQL and created a new form connected directly to the table with no query and I can add records just fine in the new form.

When I create a new form and connect it directly to the SQL table causing me the issue I can add records just fine.

Any other suggestions?

***EDIT***

I mentioned above I can create a new form using the table only and I have all functionality, however, when I create a new form using a query to access the SQL table I can only view the records.

 

[isladogs]

Apologies but I've not read all of this lengthy thread.
Are you saying that a form which has your SQL table as its record source is editable but if the form is based on a query it becomes read only?

 

[michaelH]

Apologies but I've not read all of this lengthy thread.
Are you saying that a form which has your SQL table as its record source is editable but if the form is based on a query it becomes read only?

In short, yes this is my underlying issue at this point. I uploaded a short video showing my primary issue and the source.

 

[isladogs]

There are many reasons why a query becomes read only.
Allen Browne summarised those reasons here Microsoft Access tips: Why is my query read-only? (allenbrowne.com)

 

[michaelH]

There are many reasons why a query becomes read only.
Allen Browne summarised those reasons here Microsoft Access tips: Why is my query read-only? (allenbrowne.com)

Thanks, isladogs, I have already seen and been through this list and none of which I have identified as the cause.

 

[michaelH]

Hi all,

I just wanted to post an update here on my status.

Even though I was unable to get the form to function as expected I was able to create a separate form for my new data entry and it works fine. Unfortunately, I was unable to utilize the already built form but in the end, it works how I want.

Thanks to everyone for their thoughts and efforts.

Not sure if I need to do anything else with the post since no solution was provided.

 

[pbaldy]

Not sure if I need to do anything else with the post since no solution was provided.

No need to do anything, thanks for updating it. Sorry we couldn't help, this was an odd one.

 

[isladogs]

Hi. Glad you have found a workround.
I suggest you mark the thread as SOLVED - there isn't a RESOLVED button!