Button to open form linked to previous subform

[YNWA]

Hi, I have a button on a subform, this opens a new form called scores. This works fine, you can then enter info into scores and it saves to the scores table.

How do I get the scores form to link to the contact subform?

I have used the following code:

Private Sub Command10_Click()
On Error GoTo Err_Command10_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frmScores"
    
    stLinkCriteria = "[contactID]=" & Me![contactID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command10_Click:
    Exit Sub
Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click
    
End Sub

However it will not link the two together, so I can not tell which score relates to which contact.

Anyone have any ideas or help on this?

Thanks
Will

 

[YNWA]

This is what I have.

If you open frmChild, then you can navigate using the tabs at the top. (Child Contacts tab is empty ignore it).

On the Child Referrals tab, you will see a referral section at the top then individual contacts at the bottom. In the contacts is an Open Form button next to each contact.

This is where I need the form that is opened to link to that particular contact.

I have entered dummy data and no contactID is entered, if I go back into that score record and enter the relvant contactID then next time I view it, I can see it.

I need this contactID to be assigned to that score automatically.

Any ideas

Cheers
Will

 

[YNWA]

Any ideas on this?

The button works, it opens a the Scores form however it shows a blank page instead of linking it to the recorded the button was clicked on.

Surely there is a way to link the two together?

 

[DCrake]

Will

Had a quick look at it and in theory the relationships look right however you need to rename them to stop conflicts. for Example

PKChildID in primary key table
and
FKChildID in foreign key table

Also as a child A can have referral 1,2,3,etc. So can child B so you need composite keys in your child tables to like them to the correct parent.

David

 

[YNWA]

So change PK ChildID in primary table (tblChild) to ID, then in tblRefer change keep the FK ChildID as is. Repeat this for Refer table ID, Contacts table ID ?

Cheers mate.

 

[YNWA]

It seems to me, that the button works, if I manually type in a contactID in the pop up scores form, when I open that form again on that specific contact, I can see all scores relating to that contact.

So somehow I need the contactID of the contactform to transfer to the scoresform when I click the open form button and type something into the scores form.

THis works on the subforms with Child form - Refer Form and also Refer form to Contact form. But it just won't work when clicking the "Open Form" button.

I am stumped, do I need an On Current or something like that in the scores form?

Do I need the record source of the pop up scores form to be a query or is the scores table ok?

 

[YNWA]

Any further help on this, still can't get it to work.

Could someone edit the attached database if possible please?

Thanks
Will

 

[YNWA]

ANy please?

 

[vbaInet]

Here are some good links for you:

http://allenbrowne.com/ser-28.html
http://allenbrowne.com/casu-04.html

For his main homepage: http://allenbrowne.com/tips.html

You can't go wrong

 

[YNWA]

Cheers for the tips, but everything I try just doesn;t seem to work.

How hard can this be?

Why doesn't the command wizard for a open form command button do this? It asks what fields to link together but it doesnt work.

Arhh the pain of it.

 

[vbaInet]

Zip, upload and post your db (explaining the form that I should open and the form that should be filtered).

 

[YNWA]

Cheers bud.

I have a form called frmChild, which contains 2 tabs (Child Details and Child Referrals) The first table Child Details contains demographic info, easy. The next page contains a subform called frmRefer, which is linked to the childID in the frmChild. So when I enter a referral date, it pulls through the correct ID to link the two, which it currently does.

Within the frmRefer is a contact subform, called frmContact. When I enter a contact, I need this to pull the referID from frmRefer and link the two together. Which it currently does.

Now on the contact subform is a command button called cmdOpenScores, when this is clicked a form called frmScores pops up. I then enter a score in the two dropdown menus. This form needs to be linked back to the contact page, so each score relates to a contact, which can then be traced to a childID. But it doesn't seem to pull a contactID through, thus everytime I open the form its a blank score form with no previous data for that contact.

Ideally I would like to develop the frmScores so a user enters their scores, then clicks submit and the scores are sent to a table/query and also displayed in an unbound table like field on the scores form for easy reference in the future. Each user ideally would click a submit button to submit their scores and it would show which contactID it came from.

The database is for 2 nurses to enter data into the datbase on patients with specialist needs. The child and parents identify a proplem and score that problems from 1 to 10, these are then saved and re-scored at each contact. We can then pull out differences and high/low scores etc...

1 patients has many referrals
1 referral has many contacts
1 contact has many scores

Hope that makes sense, cheers again for your help.

 

[vbaInet]

Have a look at the attached. You will find a "Delete" button on your frmScores form. Implement that because it would be needed

 

[YNWA]

Have a look at the attached. You will find a "Delete" button on your frmScores form. Implement that because it would be needed

Cheers mate, one problem though.

Everything seems to work fine, until I enter info into the scores form.

When I enter the problem type, then select a parent and child score, I get an error saying "You cannot add or change a record because a related record is required in tblContacts.

Then I click ok and the error says I can not save record at this time do I want to close anyway, click yes or no and what ever you click it bring back another error saying Fields cannot be empty and it wipes everything from the scores form.

THen the scores form will then not close.

I need to close the main form first, then enter something into the scores form to be capable of closing it.

Any ideas?

Also a submit button to submit the scores to a table would be helpful, then I can design an unbound table to showcase each submitted score on the scores form. Does that sound ok?

 

[vbaInet]

Everything seems to work fine, until I enter info into the scores form.

When I enter the problem type, then select a parent and child score, I get an error saying "You cannot add or change a record because a related record is required in tblContacts.

Then I click ok and the error says I can not save record at this time do I want to close anyway, click yes or no and what ever you click it bring back another error saying Fields cannot be empty and it wipes everything from the scores form.

THen the scores form will then not close.

I need to close the main form first, then enter something into the scores form to be capable of closing it.

When I initially opened your db I noticed alot of blank IDs. I cleaned that up, ran some tests and it worked. However, your database is seriously lacking normalization. Lookup that term and try to normalize your tables before you proceed (to avoid much bigger problems you may encounter).

Also a submit button to submit the scores to a table would be helpful, then I can design an unbound table to showcase each submitted score on the scores form. Does that sound ok?

It's up to you how you want that controlled. Access would normally save new records once entered. You can use the Before Update to implement your thoughts. If it's a repetitive task then it might be a bit of a pain getting the user to confirm every new record. Put that into consideration.

 

[YNWA]

When I initially opened your db I noticed alot of blank IDs. I cleaned that up, ran some tests and it worked. However, your database is seriously lacking normalization. Lookup that term and try to normalize your tables before you proceed (to avoid much bigger problems you may encounter).


It's up to you how you want that controlled. Access would normally save new records once entered. You can use the Before Update to implement your thoughts. If it's a repetitive task then it might be a bit of a pain getting the user to confirm every new record. Put that into consideration.

The blank ID's where due to the contactID not pulling through when entering scores. Everything else is set up correct as I see it.

WHats else is wrong with it? I have read normalization etc... and thought I had followed it.

Each table has an ID (PK). Each table is linked via PK and FK.

Child table ID PK
Refer table ID PK and ChildID FK
Contact table ID PK and Refer table ID FK
Scores table ID PK and Contact table ID FK.

What would you suggest to improve?



I have deleted all data and started from scrach and I still get the same error.

 

[vbaInet]

I will advise you on the normalization later. In the meantime, tell me the exact steps to take to replicate the same error and I will give that a whirl?

 

[YNWA]

I will advise you on the normalization later. In the meantime, tell me the exact steps to take to replicate the same error and I will give that a whirl?

Open frmChild, go to the referral tab, go down to the contact details section, enter a new line of info eg. date 1/1/2010, reason anything. Click Open Form button, enter a problem in problem fields, select a child score and then select a parent score. Try to close the scores form window, thats when the error appears.

However to get rid of the score window, you need to close the main form window behind it then enter some info into scores form, it will then close.

 

[vbaInet]

Post your db as it is and I will run it based on your data.

 

[YNWA]

Post your db as it is and I will run it based on your data.

See attached mate. Cheers.