Congratulations to AcaCandy on her 100,000th post!
There's no such thing as a stupid question, but they're the easiest to answer.
JoinTour
Login
 
Tag Cloud
acer backup black screen blue screen boot bsod computer connection crash css dell driver drivers email error ethernet excel explorer firefox firefox 3 game hard drive internet internet explorer itunes laptop linux malware monitor network networking nvidia outlook outlook 2003 outlook 2007 outlook express partition problem router slow software sound trojan usb video virus vista windows windows xp wireless
Business Applications
Search
Search in:
 
Advanced Search
Tech Support Guy Forums > Software & Hardware > Business Applications >
Access: one form to another


HELLO AND WELCOME! Before you can post your question, you'll have to register -- it's completely free! Click here to join today! We highly recommend that you print a copy of our Guide for New Members. Enjoy!

Closed Thread
 
Thread Tools
cb@chrisb.eu's Avatar
Computer Specs
Junior Member with 23 posts.
 
Join Date: Jul 2008
Experience: Intermediate
02-Jul-2008, 10:35 AM #1
Question Access: one form to another
Hi. I've got two main tables (Patient Details [PD] and Blood Tests [BT]) They are linked on Hospital Number, Many (BT) to one (PD), as one patient (with only one hospital number) can have more than one blood test.
The main front form is PD, with macro buttons opening other forms, all retaining the name and hospital number (=Forms!Patient Details form! Hospital No, etc) from the patient selected on the PD form.
The blood test input form, however, refuses to save without a separately entered BT Hospital Number.
Following other posts, I've tried using text boxes and patient / child sub forms to separately store this number but without luck: as far as the form was concerned, I was BT writing to BT, so they refused to write.
Both BT and PD queries (which feed each form) also store their own and each other's hospital numbers.
Any ideas?
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
02-Jul-2008, 11:13 AM #2
Can you post a zipped copy of the database, less the data for us to look at the Data, query and form designs?
cb@chrisb.eu's Avatar
Computer Specs
Junior Member with 23 posts.
 
Join Date: Jul 2008
Experience: Intermediate
02-Jul-2008, 12:24 PM #3
Smile Access: one form to another
Hi OBP.

Thanks. The BT table I referred to for brevity is actually called [FONT='Arial','sans-serif']Haemoglobinopathy Investigations (you can see why I called it BT for short). The main PD form is point 1 on the switchboard. The BT (HI) New button is the first lower button on the top of the PD form.[/font]
Attached Files
File Type: zip Empty SCTSP Database.zip (457.9 KB, 5 views)
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
02-Jul-2008, 01:37 PM #4
I am sure that your Relationships are not correct. I will post something tomorrow.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
03-Jul-2008, 07:15 AM #5
Hello, I have had a look at your database and I think that the Relationships are causing you problems and will continue to do so.
I am not sure what will work in the way of changes as I do not know how your data is structured.
You have the Hospital No. Indexed, but with Duplicates "OK", can you actually have duplicates?
If you can't have duplicates then the Hospital number should be changed to With No Duplicates and made a Key field. This can then be used to link the Patient to all of the other Patient related Tables.
If you do have duplicate Hospital Nos in the table then it can't be used to link to the other fields and you should switch to the PatientId instead.
__________________
.
.
OBP
I do not give up easily
cb@chrisb.eu's Avatar
Computer Specs
Junior Member with 23 posts.
 
Join Date: Jul 2008
Experience: Intermediate
03-Jul-2008, 01:53 PM #6
Quote:
Originally Posted by OBP View Post
Hello, I have had a look at your database and I think that the Relationships are causing you problems and will continue to do so.
I am not sure what will work in the way of changes as I do not know how your data is structured.
You have the Hospital No. Indexed, but with Duplicates "OK", can you actually have duplicates?
If you can't have duplicates then the Hospital number should be changed to With No Duplicates and made a Key field. This can then be used to link the Patient to all of the other Patient related Tables.
If you do have duplicate Hospital Nos in the table then it can't be used to link to the other fields and you should switch to the PatientId instead.
Hi OBP.
The Patient details table has no duplicates, as each person is unique. The HI table does have duplicates, as blood tests can be varied and / or repeated.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
03-Jul-2008, 03:01 PM #7
Have a look at this version.
I could make a lot of recommendations if you want them.
Attached Files
File Type: zip Empty SCTSP Database.zip (462.6 KB, 5 views)
cb@chrisb.eu's Avatar
Computer Specs
Junior Member with 23 posts.
 
Join Date: Jul 2008
Experience: Intermediate
04-Jul-2008, 03:33 AM #8
Smile Access: one form to another
Hi OBP.

Thanks. I note the star layout, which I'd tried earlier, but you've got much more organised referential integrity set up. I'll paste the data back in and see what happens next week.

I note your offer of more suggestions which I would appreciate, particularly since you've now seen the database.

Thanks again for spending your valuable time on this.
Much appreciated.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
04-Jul-2008, 04:43 AM #9
cb, most of the recommendations involve Form formatting and usage.
First of all it would be good if you removed the "Record Selectors" and "Dividing Lines" which are for use with forms in Continuous Mode and not really needed in Single Form mode.
You should also set the Form's "Allow Design Changes" to "Design View Only" to remove the "Properties" when the Form is open.
You do not need a normal version of the Forms and an Add Record version of the same form, you just need to open the Form in "Add" mode (see the Command Button for the Blood Tests).
I would use VBA for the Command Buttons that open forms and reports as it is more versatile then Macros.

Finally as this database is "Patient Focused", ie just about everything is related to the Patient and their Hospital Number, this database would work extremely well with a "Tabbed" mainform and subforms.
It would privide the Users with everything in one place and avoid you having to "transfer" Hospital Numbers when opening forms, as that would be taken care of by the Main/Subform links.
__________________
.
.
OBP
I do not give up easily
cb@chrisb.eu's Avatar
Computer Specs
Junior Member with 23 posts.
 
Join Date: Jul 2008
Experience: Intermediate
09-Jul-2008, 01:29 PM #10
Access: one form to another
Thanks, OBP.
I had hoped to re-link my full data tables, but the Relationship type stays Indeterminate, and the next message is that 'No Unique index has been found for the Referenced Field of the Primary Table (sigh). So I then tried to paste my data into each of your tables, by table or by line, but no luck so far. Any ideas?

Responses to your earlier points:
Single form: noted.
Design changes: noted.
Add forms: these were designed to come from the new patient form, and needed to be called by different macros to add rather than edit, but the Macro 'Add' command was disfunctional. That might improve given your new layout.
Command buttons: this has to be used in Access 2003, which doesn't much like VBA, IMHO.
Tabbed forms: noted, but some of these forms are fairly big.

Thanks again for your help.
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
09-Jul-2008, 01:47 PM #11
You have to make the Patient Table's Hospital Number field Indexed and "Unique" ie "Duplicates allowed" = "No" to overcome that error message.
cb@chrisb.eu's Avatar
Computer Specs
Junior Member with 23 posts.
 
Join Date: Jul 2008
Experience: Intermediate
10-Jul-2008, 01:29 PM #12
Access: one form to another
Hi

That's solved the linking problem: thanks. But I'm still stumped with the original problem: on the Blood Test (BT) form that feeds both (linked) PD and BT tables, it won't write to the BT table, and therefore doesn't allow you to save the form. Mini version (with 6 real, but re-named) customers attached. If you open the Patient Details form, and try to add a new blood test form for any of the existing clients, it still refuses. Any idea why?

Thanks again for your help.
Attached Files
File Type: zip SCTSP Database - Reduced.zip (469.8 KB, 4 views)
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
10-Jul-2008, 02:20 PM #13
Mainly because you are using your form with a Macro instead of my form with VBA.
cb@chrisb.eu's Avatar
Computer Specs
Junior Member with 23 posts.
 
Join Date: Jul 2008
Experience: Intermediate
11-Jul-2008, 06:22 AM #14
Access: one form to another
Hiya.

This is from the form you sent back to me. The New HI form is called by VBA rather than a macro. As you'll see, I get the same problem with VBA that I get with a Macro?

Cheers

Chris B
Attached Files
File Type: zip New Haemoglobinopathy Investigations Form 110708.zip (356.6 KB, 5 views)
OBP's Avatar
OBP OBP is offline
Computer Specs
Distinguished Member with 6,554 posts.
 
Join Date: Mar 2005
Location: UK
Experience: An old Basic Programmer
11-Jul-2008, 07:06 AM #15
Chris, I have just used the version that I sent you to add data to that form, the only thing that I had to do to get it to work was create an Interpretation record to select in the Combo.
See attached database
Attached Files
File Type: zip Empty SCTSP Database.zip (472.1 KB, 8 views)
Closed Thread

THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
WELCOME TO TECH SUPPORT GUY! Are you looking for the solution to your computer problem? Join our site today to ask your question -- for free! Our site is run completely by volunteers who help people like you solve computer problems. See our Welcome Guide to get started.



Thread Tools


You Are Using:
Server ID
Advertisements do not imply our endorsement of that product or service.
All times are GMT -4. The time now is 02:55 PM.
Copyright © 1996 - 2008 TechGuy, Inc. All rights reserved.
Powered by vBulletin, Copyright © 2000 - 2008, Jelsoft Enterprises Ltd.
Search Engine Optimization by vBSEO 3.1.0
Powered by Cermak Technologies, Inc.