MS Access Form Blank When Linked to MS SQL - Unable to Add Records (2 Viewers)

[michaelH]

I am starting a new post per the advice of another user as I posted on an 8-year-old post without a resolution to my issue. So here it is.

I am in the process of transitioning my Access Backend database to an MS SQL database and I am having a couple of issues. My primary issue is stated below.

My control source for the form is a query created from a single linked table from the Access Backend. All functionality works i.e. displays all records and allows the users to delete and add new records. When I change the control source query to use the new MS SQL Backend table the form is blank and no form controls are displayed and the user no longer can scroll through the records, update, delete or add.

The form displays the data based on a default date range set in the form which is set for the current day. This causes no results in the query then causing the form to be blank, when I change the default date range to a range that contains data the form opens correctly with the form controls and all functionality EXCEPT entering a new record.

Any help getting to a solution will be greatly appreciated.

I have a quick video of my screen showing how it works when using the linked Access table vs MS SQL table but I am not sure how to post that.

**Edit**

One other note to point out. When I switch my source table from the linked access to the linked SQL table the "New (blank) Record" selector is disabled whereas when I use the linked access table the "New (blank) Record" selector is enabled.

I have checked all the permissions to the table and are below.

TABLE_QUALIFIER TABLE_OWNER TABLE_NAME GRANTOR GRANTEE PRIVILEGE IS_GRANTABLE
SouthForkActivityTracking dbo tblAccomplishment dbo dbo DELETE YES
SouthForkActivityTracking dbo tblAccomplishment dbo dbo INSERT YES
SouthForkActivityTracking dbo tblAccomplishment dbo dbo REFERENCES YES
SouthForkActivityTracking dbo tblAccomplishment dbo dbo SELECT YES
SouthForkActivityTracking dbo tblAccomplishment dbo dbo UPDATE YES

 

[Isaac]

Please post specifics regarding your code/filter on the dates thing.

Also, if you are ending up with a non-updateable datasheet in general, you may have forgotten to specify a primary key when first linking.

 

[michaelH]

Thanks, Isaac,

The datasheet is updateable directly in the table and the control source query for the form, this issue is when the results of the control source query return 0 records (no records for the current day have been entered yet) I am unable to make any updates or changes via the form because it blanks out.

As for the logic on the date range. I have to date fields in the form which have default values set to Date() for txtStartDate and Date() for txtEndDate.

The control source query uses these fields for the range - Between [Forms]![frmAccomplishment]![txtStartDate] And [Forms]![frmAccomplishment]![txtEndDate].

Let me know if this identifies what you asked for or if you need further clarification.

 

[Isaac]

Maybe you have a Time value in sql server. So your criteria ends up telling sql server,

Between 12/09/2020 12:00 AM and 12/09/2020 12:00 AM

 

[michaelH]

No that is not it., There are no records for 12/09/2020, so there is no date, and if there was it would not have a time stamp. The control source form works fine for ALL other date ranges except a range where there are no results in the control source query due to no records for that range.

 

[Isaac]

Sorry, I'm getting confused.

If there are no records, you expect a blank form.
? Right?

 

[pbaldy]

I am not sure how to post that.

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

 

[pbaldy]

@Isaac , I think they mean the controls don't show either:

no form controls are displayed

This would be due to either the form or the underlying query not allowing new records.

 

[michaelH]

Sorry, I'm getting confused.

If there are no records, you expect a blank form.
? Right?

When I say blank form I mean blank as in no controls or fields when the source control query has no results.

See Snapshot 1 when sorce control query has data. From here the user can modify or delete the record. See See Snapshot 2 When I change the dates to a date range when there is no data the form loses all form controls and text boxes.

Snapshot 1 - With data linked to SQL table

Snapshot 2 - With no data linked to the SQL table

Now if I change my control source query to use the linked Access table it works when there is data see Snapshot 3 and no data see Snapshot 4, Snapshot 4 is when the user would add a new record or they could select the new record button which is disable when using the SQL table.

Snapshot 3 - With data linked to the Access table

Snapshot 4 - With no data linked to the Access table

 

[Isaac]

@Isaac , I think they mean the controls don't show either:



This would be due to either the form or the underlying query not allowing new records.

Right - which goes back to my first post about a non updateable table, or at least cannot add new.

 

[michaelH]

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

Thanks pbaldy, I just tried that, and unfortunately, it is too large. I will work on getting it smaller to upload. Thanks for the tip on the upload.

 

[michaelH]

Right - which goes back to my first post about a non updateable table, or at least cannot add new.

As I mentioned, I can update, modify, delete, and add through the source control query and the table directly, I also posted all the permissions for the table showing I have full permissions.

 

[michaelH]

@Isaac , I think they mean the controls don't show either:



This would be due to either the form or the underlying query not allowing new records.

As I mentioned, I can add/delete/modify through the source query just fine. I have checked the form properties and it allows edits and allows new records just not when I connect to the linked SQL table.

 

[Isaac]

You cannot add records in a subform if there is no parent record to associate it with

 

[michaelH]

You cannot add records in a subform if there is no parent record to associate it with

Do you mean when I am connected to a SQL backend table only?

This works fine when linked to an Access backend table so I am not sure I understand why this is.

My apologies also when I ask for clarification on what you mean by a subform? I am only using one form for the process, there are no subforms in this design.

 

[Isaac]

I apologize, I misinterpreted the picture., thought I saw an upper and a lower

 

[michaelH]

I apologize, I misinterpreted the picture., thought I saw an upper and a lower

No apology necessary, it happens. I am using one form that reads/displays the data from the source control query and the user should be able to select the new record button and get the 4th snapshot to enter a new record just as they would if the linked table was Access.

 

[Isaac]

what's the recordsource of the form? just the table itself, or a sql statement?

 

[michaelH]

what's the recordsource of the form? just the table itself, or a sql statement?

As I mentioned the control source is a query that references the one table.

Here is the SQL from the query.

SELECT tblAccomplishment.Date, tblAccomplishment.[Date (Numeric)], tblAccomplishment.[SF/MC], tblAccomplishment.District, tblAccomplishment.Coordinator, tblAccomplishment.[Man Days], tblAccomplishment.Project, IIf(InStr(1,[Project],"$",1)>0,Val(Mid([Project],InStr(1,[Project],"$",1)+1,30))) AS pjtRate, IIf(InStr(1,[Project],"$",1)>0,Val(Mid([Project],InStr(1,[Project],"$",1)+1,30)*Val([Man Days]))) AS pjtValue, IIf(InStr(1,[Project],"$",1)>0,Val(Mid([Project],InStr(1,[Project],"$",1)+1,30)*Val([Man Days Fire]))) AS pjtValueFire, tblAccomplishment.Location, tblAccomplishment.[Acres Completed], tblAccomplishment.[Narrative (Numeric)], tblAccomplishment.[Narrative (Text)], tblAccomplishment.[Trees Planted], tblAccomplishment.[Tubes Placed], tblAccomplishment.[Traps Set], tblAccomplishment.[Animals Caught], tblAccomplishment.[Feet of Trail], tblAccomplishment.[Chemical, Gallons], tblAccomplishment.[Charge Code], tblAccomplishment.[Miles of Road], tblAccomplishment.[Date Completed], tblAccomplishment.[Man Days Fire], tblAccomplishment.UoM, tblAccomplishment.UoM_Rate
FROM tblAccomplishment
WHERE (((tblAccomplishment.Date) Between [Forms]![frmAccomplishment]![txtStartDate] And [Forms]![frmAccomplishment]![txtEndDate]))
ORDER BY tblAccomplishment.Date DESC;

 

[michaelH]

@Isaac , I think they mean the controls don't show either:



This would be due to either the form or the underlying query not allowing new records.

Thanks for the response, I have check these several times and it is not the case.