One-to-many problem, multiple row per record

[jzwp22]

Overall it looks OK, but since I don't know the details of your application like you do I cannot say 100%. I did notice that you have a field in tblOverseesBusinessLinks for nature of relationship, shouldn't this be a foreign key that references the tblNatureOfRelationship? Now comparing tblOverseesBusinessLinks and tblStaffInstitutions, I see a lot of similar fields (staffID, natureofrelationship, and contact). Can you combine these 2 tables into 1 generic table and then add 1 field to designate the record as a business or an institution?

 

[lostwithaccess]

you have a field in tblOverseesBusinessLinks for nature of relationship, shouldn't this be a foreign key that references the tblNatureOfRelationship?

tblOverseasBusinessLinks is a recent addition, and you are right to pull me up on it, although field natureofrelationship will by coincidence have some overlap with the same in the other table, it will have different data so I will change the name now. Thanks for pointing that out, saves a few issues later I'm sure!

I like your idea of combining the two tables then classing records as either institutions or businesses, I'll look further into that now. My first thought though is that the data collected for a business link is not the same as that for an institute, would empty fields be a problem?

 

[jzwp22]

No, having some empty fields in a record is not a problem as long as they are not key fields like staffID.

 

[lostwithaccess]

Because I am unable to get hold of a reliable and complete list of world universities - with related city and country details, I have been trying to make a combo box on my form that sources its info from the very field it puts data into, so that the list self-populates as the database grows, hopefully helping to reduce duplicates. The list will allow new entries for universities not in the list

I have managed to get this to kind of work, but I am unable to hide the primary key field of the table, so it is coming up with the primary key (autonumber) and then the list of previously submitted universities. Is there a way to get rid of this?

Also, perhaps more immediately important, someone reset the network modem as I was working on my database, kicking me out... Now I am back online, but when I try and open my form I get asked to 'Enter Parameter Value', I have two subforms in this which will not display and I get asked for the value twice, so I presume this is related, but I have never had to do this so although it may be a simple fix, I have no understanding of the problem. Would anyone be able to help?

 

[jzwp22]

I have managed to get this to kind of work, but I am unable to hide the primary key field of the table, so it is coming up with the primary key (autonumber) and then the list of previously submitted universities. Is there a way to get rid of this?

The column widths property of the combo box governs what columns will be displayed in the combo box. Just set the column width to zero if you don't want your users to see it. The first column with a non-zero width is the column that will be displayed once a selection is made.

...but when I try and open my form I get asked to 'Enter Parameter Value', I have two subforms in this which will not display and I get asked for the value twice, so I presume this is related, but I have never had to do this so although it may be a simple fix, I have no understanding of the problem. Would anyone be able to help?

This typically happens when something is misspelled (field or table name), however, since you got disconnected while in the database, the database might have gotten corrupted. You might try the compact and repair utility within Access. Out of curiosity, why would getting disconnect impact your database. Where is the database file?

 

[lostwithaccess]

I thought I had managed to fix the parameter value problem, I added the primary key value of each subforms' corresponding table to it, also making it invisible, and this seemed to solve the problem. But now after a few computer problems, leading to a restart, I now open my DB and once again it is asking me for the parameter values (pk values are still there).

I'm still looking but really not sure where to look this time. I even tried deleting a few of the combo boxes and rebuilding them in case I had somehow changed something by accident, but still no joy.

Up until that point; I had managed to get the basics of the self-populating university list going, and eliminated the dual-column part of the combo box as you said, but now if I select a university that has previously been entered, it is the primary key of the original record that is saved - not the university name under a new record.... Not having a good day with the technology!

 

[lostwithaccess]

I did also have the two subforms on separate tabs off of the main form, which I just deleted, moving the subforms both onto the main form, which has got rid of the parameter value message box, however then on opening the form I got another message box -

"The expression On Current you entered as the evernt property setting produced the following error: Return without GoSub.
*The expression may not result in the name of a macro, the name of a user defined function, or [Event Proceedure].
*There may have been an error evaluating the function, event, or macro."

After a bit of investigation I have managed to now get rid of this by deleting the event proceedure in "On Current", was a bit of a shot in the dark as I dont know what it means or any coding, but as yet it seems to be working. Now I've just got to try and build up by data input form again... Something tells me I could end up in the same tricky place again..

Still working on the self-populating university list

 

[jzwp22]

Regarding populating the university list, you can use the Not In List event of the combo box to add records to the table that holds the universities.

 

[lostwithaccess]

Thanks jzwp22, NotInList seems to be the ideal solution, I have started working with some code I was able to copy and paste in, some of which I have been able to adapt as necessary, and although this seems to be working (the combo box is drawing universities from tblInstitutions, and allowing new records to be input, and it is storing these selections in tblStaffInstitutions) I am getting an error message everytime the event is run saying:

"The text you entered isnt an item in the list, select an item from the list, or enter text that matches one of the listed items"

Would you mind checking my coding for any obvious errors? This is the first code I have ever worked with so I am very much still learning.

Private Sub combo55_NotInList(NewData As String, Response As Integer)
On Error GoTo combo55_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The institution " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Institution")
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "frmNewInstitution", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
MsgBox "The new institution has been added to the list." _
, vbInformation, "Institution"
Response = acDataErrAdded
Else
MsgBox "Please choose an institution from the list." _
, vbInformation, "Institution"
Response = acDataErrContinue
End If
combo55_NotInList_Exit:
Exit Sub
combo55_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume combo55_NotInList_Exit
End Sub

 

[jzwp22]

I'm not sure what the undo command is doing for you, but other than that I don't see anything wrong with the code. You might try commenting it out (put a single apostrophe in front of the line) and running the code to see if it helps.

Private Sub combo55_NotInList(NewData As String, Response As Integer)
On Error GoTo combo55_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The institution " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Institution")
If intAnswer = vbYes Then
[COLOR="Red"]DoCmd.RunCommand acCmdUndo[/COLOR]
DoCmd.OpenForm "frmNewInstitution", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
MsgBox "The new institution has been added to the list." _
, vbInformation, "Institution"
Response = acDataErrAdded
Else
MsgBox "Please choose an institution from the list." _
, vbInformation, "Institution"
Response = acDataErrContinue
End If
combo55_NotInList_Exit:
Exit Sub
combo55_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume combo55_NotInList_Exit
End Sub

 

[lostwithaccess]

Commenting it out did the job, so I have now deleted it, thank you.

Things are coming along now, but I seem to have hit an old stumbling block - My main form frmStaff has 2 subforms on it for tblStaffInstitutions and tblOverseasBusinessLinks, but when I enter a new Staff Institution, the StaffID is not carrying over to tblStaffInstitutions. What I currently get in tblStaffInstitutions is all the Institution information but no StaffID to relate it.

tblStaffInstitutions has field fkStaffID related to pkStaffID in tblStaff, and fkInstitutionID from pkInstitutionID in tblInstitutions.

I dont quite understand how this is going wrong because it is working for tblOverseasBusinessLinks which is related in the same way

EDIT -> For anyone else needing to know:

I have now found out how to do this - Right click your subform (in layout view) and click properties, then check your child and master fields are correctly linked, mine were not...

 

[jzwp22]

Glad to hear that you figured it out. You may want to check the relationship window to make sure the relationship between staff and staff institution is correct there. If relationships are set up properly in the relationship window, Access will automatically populate the master/child linking fields when you place a subform on a main form (when based on related tables).

 

[lostwithaccess]

If I could question your technical know-how once more - I now have most of my database built, and have been looking at creating some reports, but because of the primary and foreign keys throughout my database, my data is now all stored in numbers (which refer back to what I have called the 'supporting data' tables such as 'Title', 'Role' etc)

So when it comes to creating a report all I get out is numbers. I have been reading which has lead me onto the need queries (I believe), but when I create one of these it still leaves me in the same situation of being left with a set of numbers (the 'right' numbers, but still not the information in a useful form).

Any pointers to get me going would be greatly appreciated as always

 

[jzwp22]

When you join two tables via their primary-->foreign key relationship in a query, you then have access to all fields from both tables. You would create a new query and include both tables (or more tables as necessary) (the main table and the "supporting data" table). The relationships you created should also show up in the design grid view of the query.You just need to add the fields (the titlename, rolename fields etc.) you want to the query in the design grid. When you base a report on this new query, all the fields you selected will then be available for you to use in the report.

 

[lostwithaccess]

In checking my subforms (I have a subform in a subform in a form) it appears some data is not carrying through properly, which is strange because in some ways it is working. I have:

tblStaff
tblStaffInstitutions
tblStaffInstitutionsNatureofRelationship

When I look at tblStaff, then hit the subform (+) button, tblStaffInstitutions drops down fine - displaying all the relevant info. When I then hit the + button in tblStaffInstitutions it rightly opens tblStaffInstitutionNatureOfRelationship, but displays no records. Furthermore if I input data here it does get stored in the right table, alongside the records that should be displayed in the first place.

I hope I have explained this ok, any ideas?

 

[jzwp22]

You will need to check to make sure that the subform and subsubform are properly linked. From design view, right click the frame of the subsubform and then go to Properties-->Data and make sure that the Master and Child link field properties have the correct fields referenced; if not click on the ... and add the fields manually (once you click the ..., Access may attempt to fill them with what it thinks are the correct fields based on your relationship window)

 

[lostwithaccess]

Just checked that but no luck unfortunately. Currently the subform and subsubform are linking master field pkStaffInstitutionID to child field fkStaffInstitutionID.

Any other ideas? I'm pretty stumped. I would have thought that because the form is able to put data into the right table it would mean it is connected ok, but looking at the table as a sub to the main table just isnt having it.

 

[jzwp22]

So the form is working properly? If you go to the individual tables (not using the +) are the records correctly populated? If so, then the problem lies with the way the table datasheets are linked.

 

[lostwithaccess]

To the best of my knowledge the form is working properly, when I check tblStaffInstitutionNatureofRelationship it is creating a new record when necessary, storing:

pkStaffINstNatureRelateID
fkStaffInstitutionID
fkNatureofRelationshipID

I am not sure how to go about checking the way table datasheets are linked, I presume this is different to relationships?

 

[jzwp22]

In Access 2003 (since that is what I have at work), open the table (tblStaffInstitutions) in design view then on the main toolbar click View-->Properties. You should see the subdatasheet name and the master and child linking field properties. Correct as necessary.