Can't enter data into form -Access linked to SQL (1 Viewer)

[a.phillips]

I have a database with Access 03 as the front end, and SQL sever 2008 as the back end.

I have a form, which uses a query - named 'visit_main' as a record source which used many linked tables, here are 3 example tables it uses:

Visit
Visit ID (PK)
Date of visit
PtID
Demographic ID

Tests
Visit ID (PK)
English
Maths
Science

Lifestyle
Visit ID (PK)
Weight
Height
BMI

I have linked 'visit main' to the form, so that people can enter data into the tables. The Visit ID an auto number, so when a new visit is added one is automatically generated.

I have 'tests' as a subform, with the 'Visit ID' used as the link to the child and master field. This works fine, and the data is entered and stored in SQL Server

I do not have tests in a subform, instead I have the textboxs directly on the form for the data to be entered, with the control source as 'visit_main'.
The problem is I can't enter any data at all into these textboxes.

I'm think this is because it doesn't know what the visit ID is. When I enter the correct visit ID into SQL sever it then allows me to enter the other data via access no problem.

What can I do so that all of the data can be entered via access?


I have tried to explain as best as possible, but if you need to know any other information please ask, thanks in advance for any advice I get given.

 

[RossWindows]

Since you have a recordsource consisting of multiple linked tables, I would venture to guess that your recordset is not updateable.

If you open visit_main manually, does it let you insert a new record?

 

[missinglinq]

...I have 'tests' as a subform...

...I do not have tests in a subform...

I'm a little confused the above contradictory statements, but my guess would also be that you have a Read-Only Query! Allen Browne gives an excellent explanation of the phenomena here:

http://allenbrowne.com/ser-61.html

Your data for Visits needs to be in a Subform linked to your unmentioned Patient Form (I assume you have such a creature) and the data for Tests and Lifestyle need to be done as Subforms of the Visits Form.

Linq ;0)>

 

[Galaxiom]

Visit
Visit ID (PK)
Date of visit
PtID
Demographic ID

Tests
Visit ID (PK)
English
Maths
Science

Lifestyle
Visit ID (PK)
Weight
Height
BMI

If these tables all have Visit_ID as their primary key I see no reason why they should not all be part of the same table.

 

[a.phillips]

I'm a little confused the above contradictory statements, but my guess would also be that you have a Read-Only Query! Allen Browne gives an excellent explanation of the phenomena here:

http://allenbrowne.com/ser-61.html

Your data for Visits needs to be in a Subform linked to your unmentioned Patient Form (I assume you have such a creature) and the data for Tests and Lifestyle need to be done as Subforms of the Visits Form.

Linq ;0)>

Sorry, i got mixed up due to them just been exampels, tests is in a subform, and lifestyle has textbox directly on the main form.

Yes your right, the query is read only. Thanks I'll have a read!

 

[a.phillips]

One problem, you said that the data for tests and lifestyle needs to be done as subforms in the visits form.

I know that this would work, but the form uses a tab control, and fields from one table would may be in diffrent tabs, therefore would need to be in diffrent subforms (or the same subform on a diffrent page).

Would this be a problem, as it would try to create a new PK for the 'same' entry?

Thanks

 

[JEASDALE]

(Office 2010) I have information on an Excel Database and I have designed a Form in Access - the details occur ok in the Access form, but I can't add or change any of the details on the form - I get a Recordsetset not Updateable message.
I would like the information I add or change to be in the form and transferred to Excel database.
JEASDALE

 

[RossWindows]

Hi Jeasdale and welcome to the forum!
Thank you very much for taking the time to write about the issue you are experiencing. I'm positive we can shed some light on the subject and get your application running as intended.

It sounds as though your particular situation is much different than the original poster's and because of this, I would advise that you post your question in a new thread; even though the apparent error message is the same.

Also, be careful about referring to Excel as a database. You will undoubtedly be asked to read up and familiarize yourself with RDBMS's and relational database theory.

I'll be looking for your thread and will try to help you as much as possible!