Programming Forums
User Name Password Register
 

RSS Feed
FORUM INDEX | TODAY'S POSTS | UNANSWERED THREADS | ADVANCED SEARCH

Reply
 
Thread Tools Display Modes
Old Jan 25th, 2009, 4:41 PM   #1
Vindicator
Newbie
 
Join Date: Jan 2009
Posts: 4
Rep Power: 0 Vindicator is on a distinguished road
An Access records changing and sorting problem

Hi there (Im new),

Introduction
This is a MS Access coding problem, which has stumped me. Hopefully you guys deal with VBA problems in MS Access. I have seen MS Access related problems in searches. Hopefully there are no rules against asking more than one question(I read about none).

Background Not needed to answer question
Basically I have a database in which individuals have to go to the hospital for medical reasons, on a number of occasions. These individuals have undergone many physical tests before such hospitalizations. For each patient, I have to find the closest test date before each hospitalization.

The Problem
Not my main question but I just want to know. Is is possible to modify the calculated fields within a query using VBA? As far as I can tell it is not.

If you guys could help me with either question, that will solve my problem (As i have two methods of doing what i need).

Whenever i try to modify a record in a query it gives the error, that the "database or object is read only".
I have taken a look at the properties of this query and it is unlocked to all. Furthermore it does not have a Totals Row, a calculated field, or a field that is linked to a calculated field. The query is simply a collection of fields taken from 3 linked tables.
I am using the dynamic recordset function to try and modify a blank field.

My second problem is when running through a query made table and using the recordset function I have run into sorting problems (My program relies on patients being first sorted then i can apply search methods for each).
Apparently when it comes to tables, when the recordset function gathers my records it does not gather them in the order one sees on the table. Neither can one sort the recordset. If i want it ordered I would have to use the index function. Does all this sounds correct so far? I was hoping i would not have to index my records as I havent though how i will know what patient is indexed to what. Is there another method?

If these questions are not so easily answered and coding is required i will post some.

Cheers.
Vindicator is offline   Reply With Quote
Old Jul 8th, 2009, 1:29 AM   #2
DTecMeister
Newbie
 
Join Date: Jul 2009
Location: Rome, NY
Posts: 1
Rep Power: 0 DTecMeister is on a distinguished road
Re: An Access records changing and sorting problem

Hi there (Im new),

Introduction
This is a MS Access coding problem, which has stumped me. Hopefully you guys deal with VBA problems in MS Access. I have seen MS Access related problems in searches. Hopefully there are no rules against asking more than one question(I read about none).

Background Not needed to answer question
Basically I have a database in which individuals have to go to the hospital for medical reasons, on a number of occasions. These individuals have undergone many physical tests before such hospitalizations. For each patient, I have to find the closest test date before each hospitalization.

The Problem
Not my main question but I just want to know. Is is possible to modify the calculated fields within a query using VBA? As far as I can tell it is not.

No, you need to have a blank field not associated to a field then use code to modify that field in OnLoad or some other action.

If you guys could help me with either question, that will solve my problem (As i have two methods of doing what i need).

Whenever i try to modify a record in a query it gives the error, that the "database or object is read only".
I have taken a look at the properties of this query and it is unlocked to all. Furthermore it does not have a Totals Row, a calculated field, or a field that is linked to a calculated field. The query is simply a collection of fields taken from 3 linked tables.
I am using the dynamic recordset function to try and modify a blank field.

My second problem is when running through a query made table and using the recordset function I have run into sorting problems (My program relies on patients being first sorted then i can apply search methods for each).
Apparently when it comes to tables, when the recordset function gathers my records it does not gather them in the order one sees on the table. Neither can one sort the recordset. You can use the sort property of the recordset to do this. It creates temp indexes and handles them for you.If i want it ordered I would have to use the index function. Does all this sounds correct so far? I was hoping i would not have to index my records as I havent though how i will know what patient is indexed to what. Is there another method?

This is an issue that requires some research and/or reading. You need to read up on referencial integrity, database design, third normal form, and creating one-to-many and many-to-many relationships.
You can sort without a table index, but it can be slow. More importantly, a poorly organized database isn't very useful at all.
If your 3 tables aren't linked via unique indexed columns, you will have issues being able to update them in MSAccess.

I know this isn't what you wanted to hear, but there is more to Access than throwing something together in ad-hoc fashion. The good part is it makes things easy and fun once you're on track.

If these questions are not so easily answered and coding is required i will post some.
DTecMeister is offline   Reply With Quote
Old Jul 8th, 2009, 10:10 PM   #3
vb5prgrmr
Hobbyist Programmer
 
Join Date: Mar 2009
Posts: 117
Rep Power: 6 vb5prgrmr is on a distinguished road
Re: An Access records changing and sorting problem

Okay, I think I can answer a few or your questions.

#1) Reason you cannot update or add to a recordset that is held together by joins. When you try to update or add a new record to lets say the last child of the relationship, the other two tables are also recieving this update/add request and thus the action is failing because in parent table nothing is changing, in secondary child/parent table nothing is changing or they think you are trying to add a duplicate record. To solve this you need to have an independent RS to the table you want to modify and then refresh the joined RS to view the new information. NOTE: this is based upon experience and guesses as to why I could not get these things to work so I can't point you to any documentation that backs me up.

#2) Access is kind of funny and you need to be careful when creating your tables. I have created tables that I thought had only one index and that index being the primary key autonumber but come to find out, because of the default settings of Access, access was adding indexes based upon the way I named my fields and thus a simple query was returned in an order that I was not expecting. Now I solved this by using an order by claus for some or by removing those extra indexes I did not want or by reordering the precidence of those indexes.

Hope this helps.


Good Luck
__________________
If anyone has helped you solve your problem, please mark your thread as solved.

Thanks
vb5prgrmr is offline   Reply With Quote
Reply

Bookmarks

« Previous Thread in Forum | Next Thread in Forum »

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Sorting grimpirate Software Design and Algorithms 18 Jan 24th, 2008 9:04 PM




DaniWeb IT Discussion Community
All times are GMT -5. The time now is 10:38 AM.

Powered by vBulletin® Version 3.7.0, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2007 DaniWeb® LLC