referencing forms

[dbertanjoli]

I hope someone will be available to help me (please see attached zipped database). I posted the same question under the table section but they told me the problem is in the form design. My simplified database is attached


I exported a list of names from excel file to my tblNames. On my 'main' form I have a listbox with these names. So my idea is to create a custom menu bar at the top that will have 3 forms so we can enter research activities related to each individual (please see attached database for better understanding). The problem I am facing right now is when I click a name and then select 'ResearchCategories' Form from the forms, enter data and want to go to next record I get this error message: : 'You can not add or change a record because a related record is required in 'tblActivities'.

Can anyone please be able to help me.

Many thanks,
Debbie

 

[boblarson]

Looks to me that your relationships are a bit off. Why are you trying to store the ID from tblActivities into tblAcademicRole, etc.?

What is the purpose behind your tblActivities which has only ID IDName and LastUpdated for fields?

 

[dbertanjoli]

Hi Bob,
Thank you for your reply,
How would you do it? I have a list of name and each name might have several reseach interests, several academic roles and so on.
Deb

 

[boblarson]

Hi Bob,
Thank you for your reply,
How would you do it? I have a list of name and each name might have several reseach interests, several academic roles and so on.
Deb

See here for the table structure that supports that.

See the relationships to see what I actually did (I also renamed a few things to kind of match up with things better and be easier to tell what is what).

I did nothing to your forms at this point (you'll need to go make some changes there to match up to the new table structure). We can help more with that as needed.

 

[dbertanjoli]

Hi Bob,
Thank you very much. It is almost working. The problem I am having is that data entered through the researchcategories form is not associating with names. I build a query called results and it shows 0 records. The tblResearchCategoires has around 12 records. I am doing something wrong with referencing. Please see attached database.
Many thanks again.
Deb

 

[Simon_MT]

I could get the Main Form to do anything but I noticed:

xrefNamesResearchCategories is empty

ResearchCategoriesbyID can be linked directly to tblNames and does necessarily need a Criteria if it is attached to a Form when a filter can be used.

Simon

 

[boblarson]

I could get the Main Form to do anything but I noticed:

xrefNamesResearchCategories is empty

ResearchCategoriesbyID can be linked directly to tblNames and does necessarily need a Criteria if it is attached to a Form when a filter can be used.

Simon

Simon - it is possible for a person to have more than one Research category so the junction table is required. The ResearchCategoryID should not be in the tblNames.

 

[boblarson]

Also, I just was able to download and look at it but the research categories table does not have the right data in it. It should be CATEGORIES and not duplicates like it shows. I'll upload a changed one to show.

 

[dbertanjoli]

Many thanks Bob,
I haven't seen any attachment. Was it suppose to be one?
Debbie

 

[boblarson]

Turns out that I had forgotten to remove that other categories table and linked it accidentally instead of the CategoryType table.

I would use subforms for most of this myself and I didn't see how you wanted to open the research categories form from the main form so I didn't do anything yet with that except change the record source for it.

 

[Simon_MT]

You don't need to a Dlookup if the NameID in xrefNamesResearchCategories you simply use the relational functionality of a database.

Simon

 

[boblarson]

You don't need to a Dlookup if the NameID in xrefNamesResearchCategories you simply use the relational functionality of a database.

Simon

Very good point. I had missed seeing that in the form.

 

[dbertanjoli]

Bob,
Now we are going backward

By clicking on somebody's name and opening the 'researchcategories' form I should be able to enter/or see all records related to that individual. With this change we have just made regardles who I select I see the same data in the 'reseachcategories' form for everybody.

The reason why I am not using subforms is because I will have at least 10 forms under the form dropdown at the top of the main form relating data to each individual.

When I open the table: xrefNamesResearchCategories I should be able to see IDs for all newly entered recods, NameID and IDResearchCategory but I don't.
I am attaching a previous version we were working on. I have a feeling I didn't link properly main form with researchcategories form.

I would greatly appreciate any help.
Debbie

 

[dbertanjoli]

I just made a few changes to table relationships and it seems to be working. So if I select a name and go to the reseachcategories form, enter data it associates it to that person's id. Please let me know if these table relationships make sense and won't give me some troubles later on? Please see attached.

 

[boblarson]

Boy, I sure don't know what to say. You've gone and messed up the relationships and removed the xref tables from it. I gave you an almost perfect copy and you destroyed it. The only thing that is wrong with the one I gave you is that the research form doesn't populate the person's id because you have not given enough direction on how you want to open it. There is NOTHING on your main form which you click on or select to open that form. If you had, then I could tie them together. So I told you that the way to populate that was going to be needed.

So, let me build a couple of examples for you using what I had built. What you have now is totally screwed up as far as the relationships go and how you should be storing the data.

It may take a little while but I'll be back with a more complete example based on my last upload.

 

[boblarson]

Here you go with a fully functioning Research form and buttons to get there. Take a look at the code (on both the main form and the research form) and look in the xref table as it now has what you need. You can build the other two forms off of this and use the button's code however you wind up calling the forms.

 

[dbertanjoli]

Bob,
Sorry to bug you again, but the frmResearchCategory doesn't let me save more than 1 or two records under the same person. usually after the first/second record is saved it breaks into debugging mode.

Many thanks,
Debbie

 

[dbertanjoli]

Re: referencing forms - one more problem

My main form has a list box with names. By selecting the name I then click on the resecarchcategories subform to enter data for that person. After entering a first record and want to add another one by using 'add another' button breaks into debugging mode. Please see attached database and let me know if you can help me fix this?

Many thanks,
Debbie

 

[Simon_MT]

Just before I fully understand your requirements how large is your department i.e. Academics are there. Do some Teach and some only do Research? It just seems complicated to get to the information.

I would use subforms to enter information rather than combiboxes.

Simon

 

[boblarson]

Re: referencing forms - one more problem

My main form has a list box with names. By selecting the name I then click on the resecarchcategories subform to enter data for that person. After entering a first record and want to add another one by using 'add another' button breaks into debugging mode. Please see attached database and let me know if you can help me fix this?

Many thanks,
Debbie

I'll have to do that tomorrow as I am home sick today and just popped in quickly. I am not feeling up to attacking that at the moment but I know what you're talking about and I had made an incorrect assumption about a few things so unless someone else pops in with the fixes I will try to do so tomrrow morning, if my workload at work allows.