Think I might have missed something here..

[RCheesley]

Hi all,

I wonder if people can help, I have got my db designed but think I must have missed a step or set something up wrong.

I can email the DB to people but would rather not attach it for all and sundry to view forever more! Please drop me a pm with your email addy.

Basically it uses a query to pull in fields from several tables to enable me to use one form (with several tabs) to fill out data into several tables.

The idea being we use a paper proforma at the moment and I want to use this DB to analyse the returns.

I think I've got my queries and subforms working, but when I entered in some dummy data it was assigning the same formID for more than one "patient". The data remained in the fields when I went to create a new record.

Would appreciate it if someone could point out the obvious as I'm sure it's something daft I've done with setting it up! I'm a fairly new newbie to the more advanced side of access so please don't just paste a load of VBA as I wouldn't have a clue what to do with it

Many thanks in anticipation!

Ruth

 

[neileg]

How are you populating the control that holds the formID?

One possibility is that the parent/child links that sychronise the form and subform aren't working.

 

[RCheesley]

Hi Neil,

I am using a query pulling in the fields from the tables.

Ruth

 

[neileg]

But how is the ID 'assigned'?

 

[RCheesley]

Neil,

Not sure what you mean, the ID fields are autonumber fields. Each tab has a hidden autonumber field for that tab's ID.

Ruth

 

[neileg]

I'm groping in the dark here with so little information.

Tell me about the query or queries you are using and how the tabs on the forms are bound to the queries.

 

[RCheesley]

Neil,

Have had a play around this evening looking at doing things differently, as I just couldn't get the query working properly. I will attempt to upload the last version I got to at work on Friday when I get in tomorrow (around mid morning) if it would be useful.

I tried creating an autoform for each table i wanted to populate and put these into the form's tabs as a subform on each tab (which I then set to transparent etc to hide the subform aspect - I just want it to appear as if they were completing a paper form).

Still can't get anything working, think i am missing something fundemental or have got completely the wrong idea with relationships and am going around in endless circles with my lack of proper understanding preventing me from even knowing where to start with troubleshooting.

What I want to happen is:

1. From dashboard (I assume) the user will either select to scroll through the current records or to add a new (I assume I can set it to open the form in data entry mode for the latter but not sure how to set things up to do the former) - this is a later stage thing, at the moment I just want the thing to work!!

2. One form (frmRCAdetails) contains everything the user needs to enter to complete the form

3. New NHS no is entered which creates a new patient

4. Patient details completed, user clicks on the next tab (Patient history) which creates a new record in the patient history table and hence a blank form to fill in) and the same for each and every following tab.

I haven't completed all the captions etc as I didnt see the point without a working database, hence why some are still raw text.

On the patient history page I intend to put a popup which links to tblpreabhx allowing them to enter multiple instances of medication the patient has been prescribed. I'll also add other similar forms later on. I have not even started to research this - I had it working OK as a subform but a popup would be nicer.

I have had to work on this in 2007 as I don't have 2003 at home, if this is of relevance.

Ruth

 

[RCheesley]

Neil,

Just realised that I may have deleted the existing relationships before I uploaded the file - I did have ptID in tblptdetails linked 1tomany to ptID in the RCAptdetails table, then RCAFormID from this table linked to the corresponding RCAID in other forms - but I think this should be 1-2-1 and it was coming up as 1-to-Many when I made the link?

Ruth

 

[neileg]

OK, I've had a look. The db is read only so that doesn't help. However, looking at frmrcadetails, there's nothing to tie the various sub forms together so there's no synchronisation. Even with relationships defined, you still need to explicitly transfer the data. Normally we would use a query that pulls from all the tables, or use the form/ subform links that Access provides. However, you don't have any data in the main form so that isn't going to work. You seem to have put a lot of effort into this, yet you're missing some of the fundamentals.

 

[RCheesley]

Hi Neil,

I originally tried using a query pulling in all the fields from the respective tables but this did not work. Rather than use subforms at that point, I had each item as a text box or tick box.

Not sure what you mean regarding using the form/subform links that Access provides - I inserted the subform by creating a form from the table (e.g. frmptdetails was an autoform from tblptdetails) and then used the "Insert Subform" button to insert a subform, using an existing form. Does that make sense?

What do you mean by "you don't have any data in the main form so that isn't going to work"?

Ruth

 

[neileg]

One of the strengths of Access is its form/subform handling. Where you have a one to many relationship, you can use the form to handle the one side and a subform to handle the many side. Access will automatically synchronise the form and subform so that only related records are displayed, and will populate the foreign key field of any new records in the subform. All of your data is in subforms, so there is no way this is going to work. I assume that tblptdetails is the main table. If you put this data into the form, and then reapply the other subforms then the parent/child links should be set by the wizard and your data will link up.

In your relationship diagram, the only defined relationship is between TrustID and Trustname. It would be better to call the field TrustID in both tables.

It is good practice to base your forms on queries instead of the table. This makes it easier to make modifications such as sorting the data at a later date.

 

[RCheesley]

Hi Neil,

Thanks for the advice. I had initially based all my forms on queries and had all the relationships defined - as I mentioned on an earlier message I had no joy with this so thought to try subforms. Granted I could have done this from a query rather than a table.

I guess I'll just be using an excel spreadsheet as I don't have time to look at getting this working now.

Ruth

 

[Rabbie]

Hi Neil,

Thanks for the advice. I had initially based all my forms on queries and had all the relationships defined - as I mentioned on an earlier message I had no joy with this so thought to try subforms. Granted I could have done this from a query rather than a table.

I guess I'll just be using an excel spreadsheet as I don't have time to look at getting this working now.

Ruth

Forms/subforms work much better if they are based on queries linking the relevant tables. Access without relationships rather defeats the point so it's probably best you stick to excel til lyou have more time