Lookup Issue

[kenc]

Hi,
I'd like to start off by saying, in the time I've spent tearing my hair out with Access, I've learned: Java,VB,ASP, Office VBA and Javascript.
It's absolutely horrendous and I'm only posting here because I'm about to take the gaspipe. My new boss insists on using Access, so i'm stuck with it (for now).

My questions are as follows (and I'd be grateful for any replies):

1. When using forms/subforms, which keys are neccessary? i.e. If i link an author to his books (using a 1-many relationship) are both the Authors.AuthID AND Books.AuthID keys neccessary?

2. Say I've a form displaying a patient and a subform displaying related doctors that patient has seen, can I use a combobox (with lookup) to change the doctors? (or is that impossible as related records aren't editable?)

3. Can a textbox be bound to a query? (I've tried DLookup but can't get it working - could be a syntax prob but I can't work it out)

Thanks
Ken

 

[quest4]

The gaspipe is probably an easy out for the new boss, but Access is easy once you catch on. So let's try Q1, link the the Pkeys, even if the one in the subform is invisible, and you make visible what you weant to see, thru the properties of the forms controls. Q2, comboboxes are the fastest and eaiest way to lookup things. Set them up in the table, the second tab can do that. Q3, yes, bound or unbound and FYI 4U DlookUp is nice, but in larger dbases it can be very slow, and it is very finnicy about its' syntax. Access help is very usefull for a lot of things and here is a reference site that might help you out:
http://www.microsoft-accesssolutions.co.uk/table-of-contents.htm
Good luck with the rest of your project and if you need anymore help, just ask. hth.

 

[kenc]

thanks for that dude, i'll check out that site ;-)

 

[quest4]

Your welcome, glad I could help.

 

[kenc]

hey, since you're online, can you answer one more thing for me?
If a form is based on a query (with say, 2 or more tables in it), is it editable?
Thanks
K

 

[quest4]

A form is a window for the table's data/records, it can create new records, edit existing records and delete records. hth.

 

[kenc]

hey, yeah i got that - it's just that when a form is based on one table, it's (obviously) editable, however, access seems to have trouble writing back to tables when a form is comprised of more than one table.

 

[quest4]

This is where relationship come in to play, make sure that referential integrety is check, as well as cascade update and delete. Also make sure that master/child relationships are set. This is all assuming that the tables are normalized correctly, but not manidory, just very helpful. You might want to read the three articles on dbase normalization at the site I gave you. When everything is correct, like an engine, it will purr like a kitten, trust me on that one. Your just a little out of sink right now, like I said if you like and can, post a stripped down copy of your dbase and I will look at it, but please leave one or two records in it. hth.

 

[kenc]

thanks, here you go - i've attached the database.
the table schema is as follows:

1. Patients (i use 'px' as shorthand for 'patient')
2. GP's (the doctor associated with a patient)
3. Disciplines (the doctor's area of expertise)
4. Diseases (the disease associated with a patient)

I'd like to build an editable form displaying a patient and his doctor, the doctors discipline and the patients disease.
I'm not sure whether to base the form on a query that uses fields from all the tables or base the form on one table (tblpatients) then add individual controls that use the other tables as their rowsource/controlsource.

Thanks
Ken

 

[quest4]

Thank you for the attachment, I check the cascade upate and delete for all of the relationships. That is the best I can do right now, unless you can send the form and, if the record source is a query, I would need that also. Other than that everthing looks alright.

 

[quest4]

I just discover a little something here. I remove the relationships that were there and I changed the txtboxes to comboboxes and set the record sources to for the comboboxes, so I really don't need the relationships that were there. Check it out and see what you think. On you form, change the txtboxes to comboboxes, right mouse the control and select change to.

 

[kenc]

Sorry I haven't built a form for it! - I was hoping you'd give me a few pointers about how to do that based on those tables
K

 

[quest4]

I included a simple maintainance form, table and 2 modules to run it. I would use this for the maintainance form for your tables tblDisiplines and tblDiseases. In the form please note the way I crease a new record, also note the dehavior of the cmdButtons at the bottom of the form. Also notice that the sequence numbers for the table are not
Access autonumber, look at the code in the form's OnCurrent event. autonumber can be a trouble maker in curtain situations. In the tblGP, I don't know if there can be more than one disipline per GPID, but I have it setup as one per ID now and again you could use a larger version of this form. On the last table, tblPatients, you have both GPID and DiseaseID as comboboxes, again assumed one of each. Don't worry you can change them to multiselect listboxes if you like. I am not sure if you need a form with a subform or not at this time. That is just two forms and the wizard imports one into the other and they are linked by something like a patientID or what ever. Well this should give you somethings to think about and play with. hth.